Reshaping Pandas dataframe grouping variables

Question

I have a pandas dataFrame in the following format

   ID   Name  
0   1    Jim  
1   1  Jimmy  
2   2   Mark  
3   2  Marko  
4   3  Sergi  
4   3  Sergi

I want to reshape the dataframe in the following format

   ID  Name_1   Name_2  
0   1     Jim    Jimmy  
1   2    Mark    Marko  
2   3   Sergi    Sergi

So that I can compare the two names. I am unable to use pd.pivot or pd.pivottable for this requirement. Should be fairly simple. Please, can you suggest how to do this?

jezrael · Accepted Answer · 2016-12-03 08:10:01Z

4

You can use cumcount with pivot, last add_prefix to column names:

df['groups'] = df.groupby('ID').cumcount() + 1
df = df.pivot(index='ID', columns='groups', values='Name').add_prefix('Name_')
print (df)
groups Name_1 Name_2
ID                  
1         Jim  Jimmy
2        Mark  Marko
3       Sergi  Sergi

Another solution with groupby and unstack, last add_prefix to column names:

df1 = df.groupby('ID')["Name"] \
        .apply(lambda x: pd.Series(x.values)) \
        .unstack(1)  \
        .rename(columns=lambda x: x+1) \
        .add_prefix('Name_')
print (df1)
   Name_1 Name_2
ID              
1     Jim  Jimmy
2    Mark  Marko
3   Sergi  Sergi

edited Dec 3, 2016 at 8:10

answered Dec 3, 2016 at 7:21

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Spartan07 Over a year ago

One more question? I am new to using stackoverflow for pandas. Can you tell me how to represent dataframes when posting a question ?

jezrael Over a year ago

so for net row need change from 1 1 Jimmy to ' 1 1 Jimmy' (4 spaces before).

Collectives™ on Stack Overflow

Reshaping Pandas dataframe grouping variables

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related