CODEWITHSUNDEEP
pythonpandaspivot-table
Spartan07
Spartan07

Reputation: 103

Reshaping Pandas dataframe grouping variables

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?

Upvotes: 1

Views: 94

Answers (1)

jezrael
jezrael

Reputation: 862511

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

Upvotes: 4

Related Questions