Groupby, transpose and append in Pandas?

Question

I have a dataframe which looks like this:

Each user has 10 records. Now, I want to create a dataframe which looks like this:

userid  name1  name2  ... name10

which means I need to invert every 10 records of the column name and append to a new dataframe.

So, how do it do it? Is there any way I can do it in Pandas?

piRSquared · Accepted Answer · 2016-07-14T09:47:14.887

20

groupby('userid') then reset_index within each group to enumerate consistently across groups. Then unstack to get columns.

df.groupby('userid')['name'].apply(lambda df: df.reset_index(drop=True)).unstack()

Demonstration

df = pd.DataFrame([
        [123, 'abc'],
        [123, 'abc'],
        [456, 'def'],
        [123, 'abc'],
        [123, 'abc'],
        [456, 'def'],
        [456, 'def'],
        [456, 'def'],
    ], columns=['userid', 'name'])

df.sort_values('userid').groupby('userid')['name'].apply(lambda df: df.reset_index(drop=True)).unstack()

if you don't want the userid as the index, add reset_index to the end.

df.sort_values('userid').groupby('userid')['name'].apply(lambda df: df.reset_index(drop=True)).unstack().reset_index()

edited Jul 14 '16 at 09:47

answered Jul 14 '16 at 08:54

piRSquared

285,575
57
475
624

You Sir, is my hero. Thanks for explaining too :) – Dawny33 Jul 14 '16 at 08:57
Possible to include how to collapse the extra `userid` row? I vaguely remember that there's a way to do it. (Can't remember unfortunately :() – Dawny33 Jul 14 '16 at 09:31

score 5 · Answer 2 · answered Aug 10 '21 at 12:57

You may also be interested in pandas.DataFrame.pivot

See this example dataframe:

df
    userid  name  values
0   123     A     1    
1   123     B     2    
2   123     C     3    
3   456     A     4    
4   456     B     5    
5   456     C     6

using df.pivot

df.pivot(index='userid', columns='name', values='values')
name  A   B   C
userid
123  1   2   3
456  4   5   6

Groupby, transpose and append in Pandas?

2 Answers2

Demonstration

Linked