Transform df columns into separate rows-CodePudding

I have df like this:

sending_currency	date	EUR	USD	...
ARS	2021-01-01	1.23	4.56	...

And I would like to transform it into:

date	currencies	exchange_rate
2021-01-01	ARS->EUR	1.23
2021-01-01	ARS->USD	4.56

I've tried pivot_table, but it doesn't seem to work. I would really appreciate every suggestion!

CodePudding user response：

As mentioned in the comment, you can use pd.melt():

import pandas as pd

df = pd.DataFrame({"sending_currency": ["ARS", "ARS", "JPY"],
                   "dates": [0, 7, 7], 
                   "EUR": [1.31, 3.21, 11.3], 
                   "USD": [4.7, 2.6, 8.6],
                   "JPY": [23.11, 17.2, None]})

df_new = df.melt(id_vars=df.columns[:2], value_vars=df.columns[2:], 
                 var_name="currencies", value_name="exchange_rate")

Output so far:

  sending_currency  dates currencies  exchange_rate
0              ARS      0        EUR           1.31
1              ARS      7        EUR           3.21
2              JPY      7        EUR          11.30
3              ARS      0        USD           4.70
4              ARS      7        USD           2.60
5              JPY      7        USD           8.60
6              ARS      0        JPY          23.11
7              ARS      7        JPY          17.20
8              JPY      7        JPY            NaN

You can combine the two columns containing the currency names but I would refrain from this and keep them separately.

df_new["currencies"] = df_new["sending_currency"]   ">"   df_new["currencies"]
df_new.pop("sending_currency")

Final output:

   dates currencies  exchange_rate
0      0    ARS>EUR           1.31
1      7    ARS>EUR           3.21
2      7    JPY>EUR          11.30
3      0    ARS>USD           4.70
4      7    ARS>USD           2.60
5      7    JPY>USD           8.60
6      0    ARS>JPY          23.11
7      7    ARS>JPY          17.20
8      7    JPY>JPY            NaN