Not sure if you need the multi index for the columns, but would something like this be what you're looking for?
Given the following two input dataframes
| X | Y | handle | |:----------|:----------|:--------------------| | ETHEUR E3 | XMRUSD E3 | ETHEUR E3_XMRUSD E3 | | ETHEUR E3 | TRXETH E3 | ETHEUR E3_TRXETH E3 | | ETHEUR E3 | ETHUSD E3 | ETHEUR E3_ETHUSD E3 | | ETHEUR E3 | BTCEUR E3 | ETHEUR E3_BTCEUR E3 | | ETHEUR E3 | ETHBTC E3 | ETHEUR E3_ETHBTC E3 |
| symbol | time | close | high | low | open | volume | |:----------|:--------------------|-----------:|-----------:|-----------:|-----------:|---------:| | ETHEUR E3 | 2021-01-18 04:41:00 | 988.73 | 988.73 | 988.727 | 988.727 | 8.40472 | | ETHEUR E3 | 2021-01-18 04:42:00 | 988.73 | 988.73 | 988.727 | 988.727 | 0 | | ETHEUR E3 | 2021-01-18 04:43:00 | 988.73 | 988.73 | 988.727 | 988.727 | 0 | | ETHEUR E3 | 2021-01-18 04:44:00 | 990.62 | 990.62 | 989.64 | 989.64 | 0.158394 | | ETHEUR E3 | 2021-01-18 04:45:00 | 995.01 | 995.01 | 995.01 | 995.01 | 0.006665 | | ANTETH E3 | 2021-01-18 04:56:00 | 0.003745 | 0.003745 | 0.003745 | 0.003745 | 0 | | <PERSON> | 2021-01-18 04:57:00 | 0.003745 | 0.003745 | 0.003745 | 0.003745 | 0 | | <PERSON> | 2021-01-18 04:58:00 | 0.003745 | 0.003745 | 0.003745 | 0.003745 | 0 | | <PERSON> | 2021-01-18 04:59:00 | 0.003745 | 0.003745 | 0.003745 | 0.003745 | 0 | | <PERSON> | 2021-01-18 05:00:00 | 0.003745 | 0.003745 | 0.003745 | 0.003745 | 0 |
you can use pandas.merge to join the tables twice for each of the currency pairs.
combined = (
df1
# join data for first currency pair
.merge(df2, how="left", left_on="X", right_on="symbol")
# join data for second currency pair
.merge(df2, how="left", left_on=["Y", "time"], right_on=["symbol", "time"], suffixes=("_X", "_Y"))
)
# get columns names for multi index
cols = [x.split("_")[<IP_ADDRESS>-1] if "_" in x else ["", x] for x in combined.columns]
# set new column names
combined.columns = pd.MultiIndex.from_arrays(list(zip(*cols)))
# select required columns
combined = combined[[x for x in combined. 60