pivoting, stack, unstack

import seaborn as sns
import pandas as pd
import numpy as np

df_wide = pd.read_csv('rates.csv', parse_dates=['Time'])
df_wide.columns = ['Date'] + ['rate_%s' % col for col in df_wide.columns[1:]]
df_wide.head()
Date rate_USD rate_JPY rate_BGN rate_CZK rate_DKK rate_GBP rate_CHF
0 2024-01-17 1.0877 160.65 1.9558 24.755 7.4586 0.85818 0.9406
1 2024-01-16 1.0882 159.64 1.9558 24.710 7.4582 0.86078 0.9361
2 2024-01-15 1.0945 159.67 1.9558 24.714 7.4590 0.86075 0.9351
3 2024-01-12 1.0942 159.17 1.9558 24.689 7.4565 0.85950 0.9350
4 2024-01-11 1.0987 159.71 1.9558 24.659 7.4568 0.86145 0.9338

Unstack a dataframe in ‘’long’’ format, transform it into ‘’wide’’ format

df_long = pd.wide_to_long(df_wide, stubnames=["rate_"], i='Date', j='currency', suffix='[A-Z]*')
df_long
rate_
Date currency
2024-01-17 USD 1.0877
2024-01-16 USD 1.0882
2024-01-15 USD 1.0945
2024-01-12 USD 1.0942
2024-01-11 USD 1.0987
... ... ...
2023-10-26 CHF 0.9466
2023-10-25 CHF 0.9474
2023-10-24 CHF 0.9501
2023-10-23 CHF 0.9461
2023-10-20 CHF 0.9442

427 rows × 1 columns

df_long.unstack()
rate_
currency BGN CHF CZK DKK GBP JPY USD
Date
2024-01-17 1.9558 0.9406 24.755 7.4586 0.85818 160.65 1.0877
2024-01-16 1.9558 0.9361 24.710 7.4582 0.86078 159.64 1.0882
2024-01-15 1.9558 0.9351 24.714 7.4590 0.86075 159.67 1.0945
2024-01-12 1.9558 0.9350 24.689 7.4565 0.85950 159.17 1.0942
2024-01-11 1.9558 0.9338 24.659 7.4568 0.86145 159.71 1.0987
... ... ... ... ... ... ... ...
2023-10-26 1.9558 0.9466 24.714 7.4632 0.87170 158.48 1.0540
2023-10-25 1.9558 0.9474 24.693 7.4639 0.87240 158.55 1.0576
2023-10-24 1.9558 0.9501 24.659 7.4648 0.87025 159.26 1.0632
2023-10-23 1.9558 0.9461 24.645 7.4634 0.87153 158.91 1.0597
2023-10-20 1.9558 0.9442 24.704 7.4620 0.87213 158.80 1.0591

61 rows × 7 columns

pd.DataFrame(df_long.stack())
0
Date currency
2024-01-17 USD rate_ 1.0877
2024-01-16 USD rate_ 1.0882
2024-01-15 USD rate_ 1.0945
2024-01-12 USD rate_ 1.0942
2024-01-11 USD rate_ 1.0987
... ... ... ...
2023-10-26 CHF rate_ 0.9466
2023-10-25 CHF rate_ 0.9474
2023-10-24 CHF rate_ 0.9501
2023-10-23 CHF rate_ 0.9461
2023-10-20 CHF rate_ 0.9442

427 rows × 1 columns

Hierarchical index (“MultiIndex”) for the columns

df_long.unstack("currency").columns
MultiIndex([('rate_', 'BGN'),
            ('rate_', 'CHF'),
            ('rate_', 'CZK'),
            ('rate_', 'DKK'),
            ('rate_', 'GBP'),
            ('rate_', 'JPY'),
            ('rate_', 'USD')],
           names=[None, 'currency'])

Two rate values per (date, currency), one at noon, one at midnight

df_long2 = df_long.copy()
df_long2.columns = ['rate_noon']
df_long2['rate_midnight'] = df_long2['rate_noon'] + 0.05*np.random.normal(size=len(df_long2))
df_long2
rate_noon rate_midnight
Date currency
2024-01-17 USD 1.0877 1.103294
2024-01-16 USD 1.0882 1.072144
2024-01-15 USD 1.0945 1.082852
2024-01-12 USD 1.0942 1.082111
2024-01-11 USD 1.0987 1.086369
... ... ... ...
2023-10-26 CHF 0.9466 0.941300
2023-10-25 CHF 0.9474 0.955020
2023-10-24 CHF 0.9501 0.855274
2023-10-23 CHF 0.9461 0.990630
2023-10-20 CHF 0.9442 0.974553

427 rows × 2 columns

df_long2.unstack()
rate_noon rate_midnight
currency BGN CHF CZK DKK GBP JPY USD BGN CHF CZK DKK GBP JPY USD
Date
2024-01-17 1.9558 0.9406 24.755 7.4586 0.85818 160.65 1.0877 1.923953 0.878921 24.856766 7.488026 0.847618 160.641512 1.103294
2024-01-16 1.9558 0.9361 24.710 7.4582 0.86078 159.64 1.0882 1.986664 0.977484 24.698306 7.481745 0.861115 159.583811 1.072144
2024-01-15 1.9558 0.9351 24.714 7.4590 0.86075 159.67 1.0945 2.018060 0.998977 24.652378 7.470879 0.829283 159.718074 1.082852
2024-01-12 1.9558 0.9350 24.689 7.4565 0.85950 159.17 1.0942 1.998543 0.957735 24.662907 7.443184 0.873813 159.118027 1.082111
2024-01-11 1.9558 0.9338 24.659 7.4568 0.86145 159.71 1.0987 1.865244 0.941308 24.651283 7.432263 0.862730 159.774494 1.086369
... ... ... ... ... ... ... ... ... ... ... ... ... ... ...
2023-10-26 1.9558 0.9466 24.714 7.4632 0.87170 158.48 1.0540 1.890512 0.941300 24.690727 7.558914 0.886643 158.491733 1.013392
2023-10-25 1.9558 0.9474 24.693 7.4639 0.87240 158.55 1.0576 1.981156 0.955020 24.672194 7.438202 0.853076 158.545532 1.029814
2023-10-24 1.9558 0.9501 24.659 7.4648 0.87025 159.26 1.0632 1.955538 0.855274 24.646200 7.479805 0.814988 159.229689 0.951851
2023-10-23 1.9558 0.9461 24.645 7.4634 0.87153 158.91 1.0597 1.933293 0.990630 24.703152 7.411403 0.798109 158.837550 1.000429
2023-10-20 1.9558 0.9442 24.704 7.4620 0.87213 158.80 1.0591 1.994263 0.974553 24.783879 7.531236 0.873255 158.763231 1.087555

61 rows × 14 columns