I have a dataframe which looks like this
Wash_Month Wash_Day
0 3 2
1 4 3
I need to convert the same to this
Input Value
0 Wash_Month/Wash_Day 3,2
1 Wash_Month/Wash_Day 4,3
I tried merging the two columns but wasn't able to convert the column headers to row values
Thanks.
Here is cute way of doing it
pd.DataFrame(dict(
Input='/'.join(df),
Value=[*map(','.join, zip(*map(df.astype(str).get, df)))]
))
Input Value
0 Wash_Month/Wash_Day 3,2
1 Wash_Month/Wash_Day 4,3
This is a more efficient solution. I break down the steps:
# Compute the Value column with `agg`.
v = df.astype(str).agg(','.join)
# Compute the Input column with `df.columns.str.cat`
v.index = [df.columns.str.cat(sep='/')] * len(v)
# Reset the index.
v.rename_axis('Input').to_frame('Value').reset_index()
Input Value
0 Wash_Month/Wash_Day 3,2
1 Wash_Month/Wash_Day 4,3
Alternative (slower). Reshape your data a bit with stack:
v = df.stack().astype(str).reset_index(level=1)
v.columns = ['Input', 'Value']
print(v)
Input Value
0 Wash_Month 3
0 Wash_Day 2
1 Wash_Month 4
1 Wash_Day 3
Look at the index(!). Now, call groupby and agg:
v.groupby(level=0).agg({'Input': '/'.join, 'Value':','.join})
Input Value
0 Wash_Month/Wash_Day 3,2
1 Wash_Month/Wash_Day 4,3
df.astype(str).groupby({'Wash_Month':'Wash_Month/Wash_Day','Wash_Day':'Wash_Month/Wash_Day'},axis=1).agg(','.join) why return all column name ..
','.join is being applied to the dataframe and not the columns of the dataframe. and ','.join(df) returns the concatenation of the column names which is different than the expected df.apply(','.join). Try this to see my point: df.astype(str).groupby({'Wash_Month':'Wash_Month/Wash_Day','Wash_Day':'Wash_Month/Wash_Day'}, axis=1).apply(lambda d: d.apply(','.join))Using groupby with dict
d={'Wash_Month':'Wash_Month/Wash_Day','Wash_Day':'Wash_Month/Wash_Day'}
df.T.astype(str).groupby(d).agg(','.join).stack()
Out[319]:
Wash_Month/Wash_Day 0 3,2
1 4,3