Pandas Convert Multiple Columns to Rows

Question

I have a pandas dataframe as follows:

     code    title      amount_1  amount_2   currency_1    currency_2
0     246       ex           500       550          USD           GBP
1     300       am           200       250          USD           GBP
2     315      ple           300       325          USD           GBP

I'd like to get this into the format

code    title   amount   currency
246        ex      500        USD
246        ex      550        GBP

All of the currencies are the same. How can I get this format? I've tried using melt and reset_index, but neither seemed to do exactly what I need. Thank you

jezrael · Accepted Answer · 2021-04-19 11:36:25Z

2

Use wide_to_long:

df1 = pd.wide_to_long(df, 
                      stubnames=['amount','currency'], 
                      i=['code','title'], 
                      j='measure', sep='_').reset_index()

print (df1)
   code title  measure  amount currency
0   246    ex        1     500      USD
1   246    ex        2     550      GBP
2   300    am        1     200      USD
3   300    am        2     250      GBP
4   315   ple        1     300      USD
5   315   ple        2     325      GBP

answered Apr 19, 2021 at 11:36

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Sean Payne Over a year ago

You seem to be everywhere with great answers. Thank you for this!

Collectives™ on Stack Overflow

Pandas Convert Multiple Columns to Rows

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related