Pandas merge columns with the same name

user_9990457 0
Viewed 1276

I have the following Dataframe:

Timestamp participant level gold participant level gold
1 1 100 6000 2 76 4200
2 1 150 5000 2 120 3700

I am trying to change the Dataframe so that all rows from columns named the same is moved below each other, while keeping the column named timestamp:

Timestamp participant level gold
1 1 100 6000
2 1 150 5000
1 2 76 4200
2 2 120 3700

To be clear, the example above is a small sample, the actual Dataframe has a lot of columnes named the same, and a lot more rows. Hence, the solution needs to take that in to account.

Thanks!

pandas python
2 Answers

Idea is deduplicated duplicated columns names by GroupBy.cumcount for counter and then reshape by DataFrame.stack:

df = df.set_index('Timestamp')
s = df.columns.to_series()

df.columns = [df.columns, s.groupby(s).cumcount()]

df = df.stack().reset_index(level=1, drop=True).reset_index()

If columns names are not duplicated and added . with number:

print (df)
   Timestamp  participant  level  gold  participant.1  level.1  gold.1
0          1            1    100  6000              2       76    4200
1          2            1    150  5000              2      120    3700

df = df.set_index('Timestamp')

df.columns = pd.MultiIndex.from_frame(df.columns.str.split('.', expand=True)
                                        .to_frame().fillna('0'))

df = df.stack().reset_index(level=1, drop=True).reset_index()
print (df)
0  Timestamp  gold  level  participant
0          1  6000    100            1
1          1  4200     76            2
2          2  5000    150            1
3          2  3700    120            2
user_29010020

Hope this helps

df1=pd.concat([df.iloc[:,0],df.loc[:,df.columns.duplicates()]],axis=1)
df2=df.loc[:,~df.columns.duplicates()]
df=pd.concat([df1,df2],axis=1)
user_66593880
Related