CODEWITHSUNDEEP
pythonpandas
emax
emax

Reputation: 7245

Python: how to reshape a dataframe based on reapeted rows?

I have a dataframe that looks like the following

df
     Name   Val1   Val2
0    Mark    0      3
1    Mark    2      3
2    Mark    5      6
3    Mark    7      8
4    Paul   -1      0
5    Paul    2     -3

I would like to have something like this

df
       Name  Val1_0    Val1_1   Val1_2   Val1_3  Val2_0    Val2_1   Val2_2   Val2_3
0      Mark    0          2       5        7       3         3        6        8
1      Paul   -1          2      Nan      Nan      0        -3      Nan     Nan

Upvotes: 3

Views: 61

Answers (2)

SeaBean
SeaBean

Reputation: 23217

You can use .unstack() with .cumcount(), as follows:

df['g'] = df.groupby('Name').cumcount()
df_out = df.set_index(['Name', 'g']).unstack()
df_out.columns = df_out.columns.map(lambda x: f'{x[0]}_{x[1]}')
df_out = df_out.reset_index()

Result:

print(df_out)

   Name  Val1_0  Val1_1  Val1_2  Val1_3  Val2_0  Val2_1  Val2_2  Val2_3
0  Mark     0.0     2.0     5.0     7.0     3.0     3.0     6.0     8.0
1  Paul    -1.0     2.0     NaN     NaN     0.0    -3.0     NaN     NaN

Upvotes: 0

BENY
BENY

Reputation: 323226

Try with cumcount then pivot

df['new'] = df.groupby('Name').cumcount().astype(str)
out = df.pivot(index = 'Name',columns = 'new', values=['Val1','Val2'])
out.columns = out.columns.map('_'.join)
out.reset_index(inplace=True)
Out[170]: 
   Name  Val1_0  Val1_1  Val1_2  Val1_3  Val2_0  Val2_1  Val2_2  Val2_3
0  Mark     0.0     2.0     5.0     7.0     3.0     3.0     6.0     8.0
1  Paul    -1.0     2.0     NaN     NaN     0.0    -3.0     NaN     NaN

Upvotes: 1

Related Questions