Convert column headers to row values

Question

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.

Please see [Ask] and provide a [MCVE] of your attempt. – r.ook Oct 24 '18 at 17:26 — r.ook, Oct 24 '18 at 17:26

score 5 · Answer 1 · answered Oct 24 '18 at 17:28

5

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

answered Oct 24 '18 at 17:28

piRSquared

285,575
57
475
624

Thanks. I haven't used dictionaries at all so this seems pretty good to start on. – Chinmay Oct 24 '18 at 17:34

cs95 · Accepted Answer · 2018-10-24T17:34:16.867

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

edited Oct 24 '18 at 17:34

answered Oct 24 '18 at 17:28

cs95

379,657
97
704
746

Thanks, I was able to stack it but couldn't figure out the second part. – Chinmay Oct 24 '18 at 17:35
@Chinmay I added a much more efficient alternative above without any stacking or grouping. – cs95 Oct 24 '18 at 17:35
May I ask a question about `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 .. – BENY Oct 24 '18 at 17:43
@Wen I'm not familiar with the groupby dict syntax... sorry :( – cs95 Oct 24 '18 at 17:49
@coldspeed let me open a new question maybe – BENY Oct 24 '18 at 17:50
2

@Wen I think because when aggregating along a groupby on axis=1, `','.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))` – piRSquared Oct 24 '18 at 17:55
@piRSquared that is great sir , hope you can post the answer https://stackoverflow.com/questions/52975294/groupby-agg-with-join-not-produce-the-expected-output – BENY Oct 24 '18 at 17:58

score 3 · Answer 3 · answered Oct 24 '18 at 17:42

3

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

answered Oct 24 '18 at 17:42

BENY

317,841
20
164
234

Convert column headers to row values

3 Answers3