I have the following DataFrame and ultimately want to divide the MarketCap of BTC by the Marketcap of ETH
Date Asset Price MarketCap
1/1/2022 BTC $10 $10000
1/2/2022 BTC $12 $10000
1/3/2022 BTC $11 $10000
1/1/2022 ETH $5 $2000
1/2/2022 ETH $6 $2000
1/3/2022 ETH $5 $2000
What is the most efficient way of converting the dataframe to this so i can do df['marketcap_btc'] / df['marketcap_eth']
Date Price_BTC MarketCap_BTC Price_ETH MarketCap_ETH
1/1/2022 $10 $10000 $5 $2000
1/2/2022 $12 $10000 $6 $2000
1/3/2022 $11 $10000 $5 $2000
Or if there is a more efficient alternative method that would work without restructuring the dataframe
You can pivot, convert to_numeric and assign:
(df.pivot(index='Date', columns='Asset')
.assign(**{'btc/eth': lambda d: pd.to_numeric(d[('MarketCap', 'BTC')].str.strip('$'))
/pd.to_numeric(d[('MarketCap', 'ETH')].str.strip('$'))})
)
output:
Price MarketCap btc/eth
Asset BTC ETH BTC ETH
Date
1/1/2022 $10 $5 $10000 $2000 5.0
1/2/2022 $12 $6 $10000 $2000 5.0
1/3/2022 $11 $5 $10000 $2000 5.0
Variant:
out = (df
.pivot(index='Date', columns='Asset')
.pipe(lambda d: d.set_axis(d.columns.map('_'.join), axis=1))
.assign(**{'btc/eth': lambda d: pd.to_numeric(d['MarketCap_BTC'].str.strip('$'))
/pd.to_numeric(d['MarketCap_ETH'].str.strip('$'))})
)
output:
Price_BTC Price_ETH MarketCap_BTC MarketCap_ETH btc/eth
Date
1/1/2022 $10 $5 $10000 $2000 5.0
1/2/2022 $12 $6 $10000 $2000 5.0
1/3/2022 $11 $5 $10000 $2000 5.0