Transposing a pandas dataframe with multiple columns to multiple rows

Question

I have dataset in the following format

source  adset     date  impression  click   spend
fb      abc       1/1/19    100 140 20
fb      abc       1/2/19    200 130 40
cl      xyz       1/1/19    300 200 50
cl      xyz       1/2/19    500 200 50

how to use pandas to get the format as below?

source  adset        kpi    1/1/19  1/2/19
fb     abc         impression   100 200
fb     abc         click    140 130
fb     abc         spend    20  40
cl     abc         impression   300 500
cl     abc         click    200 200
cl     abc         spend    50  50

jezrael · Accepted Answer · 2019-12-06 07:36:09Z

You can reshape by DataFrame.set_index, DataFrame.stack and Series.unstack:

df1 = (df.set_index(['source','adset','date'])
        .rename_axis('kpi', axis=1)
        .stack()
        .unstack(level=2)
        .reset_index())
print (df1)
date source adset         kpi  1/1/19  1/2/19
0        cl   xyz  impression     300     500
1        cl   xyz       click     200     200
2        cl   xyz       spend      50      50
3        fb   abc  impression     100     200
4        fb   abc       click     140     130
5        fb   abc       spend      20      40

Another solution with DataFrame.melt and DataFrame.pivot_table:

df1 = (df.melt(['source','adset','date'], var_name='kpi')
        .pivot_table(index=['source','adset', 'kpi'], columns='date', values='value')
        .reset_index())

print (df1)
date source adset         kpi  1/1/19  1/2/19
0        cl   xyz       click     200     200
1        cl   xyz  impression     300     500
2        cl   xyz       spend      50      50
3        fb   abc       click     140     130
4        fb   abc  impression     100     200
5        fb   abc       spend      20      40

Collectives™ on Stack Overflow

Transposing a pandas dataframe with multiple columns to multiple rows

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related