CODEWITHSUNDEEP
pythonpandaspandas-groupby
Adam
Adam

Reputation: 483

How do I transpose results of Pandas.Groupby

I have created a grouped data frame total_hours by employee id and total hours they worked each week

id  week     duration
4       35       7.000000
        36       2.000000
        ...      ...
        40       5.000000

How do you transpose the results to the following format?

id      35  36  37  38  39  40
4       7.0 2.0 7.0 2.0 4.0 5.0

I have tried total_hours.T but that is not a valid for SeriesGroupBy.

Upvotes: 3

Views: 1809

Answers (2)

jezrael
jezrael

Reputation: 862681

I think you need Series.unstack if df is DataFrame with MultiIndex:

#e.g. possible created df
df = df.groupby(['id', 'week']).sum()

print (df.index)
MultiIndex([(4, 35),
            (4, 36),
            (4, 40)],
           names=['id', 'week'])


df1 = df['duration'].unstack()
print (df1)
week   35   36   40
id                 
4     7.0  2.0  5.0

If need id to column:

df1 = df['duration'].unstack().reset_index().rename_axis(None, axis=1)
print (df1)
   id   35   36   40
0   4  7.0  2.0  5.0

Alternative is if need aggregation sum:

df1 = df.pivot_table(index='id', columns='week', values='duration', aggfunc='sum')

df1 = df1.reset_index().rename_axis(None, axis=1)

Upvotes: 2

algorythms
algorythms

Reputation: 1585

try using this: 

df  = df.pivot(index='id', columns='week', values='duration')

Upvotes: 3

Related Questions