python - Pandas merge columns with the same name

Question

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

question from:https://stackoverflow.com/questions/65860353/pandas-merge-columns-with-the-same-name

Answer 1

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