Groupby, transpose and append in Pandas?

Dawny33 picture Dawny33 · Jul 14, 2016 · Viewed 8.2k times · Source

I have a dataframe which looks like this:

enter image description here

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?

Answer

piRSquared picture piRSquared · Jul 14, 2016

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()

enter image description here

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()

enter image description here