How do i unstack a dataframe so i can divide two columns?-CodePudding

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

CodePudding user response：

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