[FIXED] How do I convert multiple columns to individual rows/values if there is no other unique column in pandas?

Issue

Following is the example

Rates, values 2019Q01	Rates, values 2019Q02	Rates, values 2019Q03	Rates, values 2019Q04	Sales, values 2019Q01	Sales, values 2019Q02	Sales, values 2019Q03	Sales, values 2019Q04
100	150	200	300	400	450	500	600

Resultant should be

Period	Rates, values	Sales, values
2019Q01	100	400
2019Q02	150	450
2019Q03	200	500
2019Q04	300	600

I've tried melt and wide_to_long, but unable to get the result. Thanks

Solution

try via columns attribute and then stack():

df.columns=df.columns.str.replace('values','').str.split(', ',expand=True)
df=df.stack().droplevel(0).rename_axis(index='Period').add_suffix(', values').reset_index()

OR as suggested by @Cytorak

df.columns = df.columns.str.rsplit(' ', 1, expand=True)
df=df.stack().droplevel(0).rename_axis(index='Period').reset_index()

output of df:

    Period      Rates, values   Sales, values
0   2019Q01     100             400
1   2019Q02     150             450
2   2019Q03     200             500
3   2019Q04     300             600

Answered By - Anurag Dabas