Creating a multi-indexed column dataframe

Question

Two pandas DataFrames populated by API. Need joined DataFrame in specific format.

Current State - Two dataframes each indexed by timestamp

eth_df:
                  close          symbol
timestamp
1541376000000     206.814430     eth
1541462400000     209.108877     eth


btc_df:
                  close          symbol
timestamp
1541376000000     6351.06194     btc
1541462400000     6415.443409    btc

Desired State - Indexed by timestamp and Multi-indexed column by symbol

portfolio_df:
                  eth            btc
                  close          close
timestamp
1541376000000     206.814430     6351.06194
1541462400000     209.108877     6415.443409

Edit 1: Request from community: Will you please add the results of eth_df.to_dict() and btc_df.to_dict() to the question?

Here's the code and results for both:

btc = cg.get_coin_market_chart_range_by_id('bitcoin','usd', start_date, end_date)

    portfolio_df = pd.DataFrame(data=btc['prices'], columns=['timestamp','close'])
    portfolio_df['symbol'] = 'btc'
    portfolio_df = portfolio_df.set_index('timestamp')
    portfolio_df.to_dict()
{'close': {1541376000000: 6351.061941056285,
  1541462400000: 6415.443408541094,
  1541548800000: 6474.847290336688,

show more (open the raw output data in a text editor) ...

  1627344000000: 'btc',
  1627430400000: 'btc',
  1627516800000: 'btc',
  1627603200000: 'btc',
  1627689600000: 'btc',
  ...}}



eth = cg.get_coin_market_chart_range_by_id('ethereum','usd', start_date, end_date)
eth_df = pd.DataFrame(data=eth['prices'], columns=['timestamp','close'])
eth_df['symbol'] = 'eth'
eth_df = eth_df.set_index('timestamp')
eth_df.to_dict()

{'close': {1541376000000: 206.8144295995958,
  1541462400000: 209.10887661978714,
  1541548800000: 219.16088708430863,

show more (open the raw output data in a text editor) ...

  1627344000000: 'eth',
  1627430400000: 'eth',
  1627516800000: 'eth',
...}}


btc = cg.get_coin_market_chart_range_by_id('bitcoin','usd', start_date, end_date)

Will you please add the results of `eth_df.to_dict()` and `btc_df.to_dict()` to the question? — , Nov 06 '21 at 16:56

StupidWolf · Accepted Answer · 2021-11-06T18:26:03.040

I am not very familiar with CoinGeckoAPI, so assuming you get the data frame something like below, you don't set the index first:

from pycoingecko import CoinGeckoAPI
from datetime import datetime
cg = CoinGeckoAPI()

start_date, end_date = 1497484800,1499138400

btc = cg.get_coin_market_chart_range_by_id('bitcoin','usd', start_date, end_date)
btc_df = pd.DataFrame(data=btc['prices'], columns=['timestamp','close'])
btc_df['symbol'] = 'btc'

eth = cg.get_coin_market_chart_range_by_id('ethereum','usd', start_date, end_date)
eth_df = pd.DataFrame(data=eth['prices'], columns=['timestamp','close'])
eth_df['symbol'] = 'eth'

You concat the dataframes and do a pivot:

portfolio_df = pd.concat([btc_df,eth_df]).pivot_table(index="timestamp",columns="symbol")

Then swap the levels:

portfolio_df = portfolio_df.swaplevel(axis=1)
portfolio_df

      symbol    btc     eth
                close   close
timestamp       
1497484800000   2444.493712 346.369070
1497571200000   2513.810348 358.284517
1497657600000   2683.571344 372.357011
1497744000000   2577.219361 359.438712
1497830400000   2620.136451 362.044289

This is great. Ultra close to what I need. Is there a way to swap the levels? So the symbols are level 0 and close colums are level 1? So: MultiIndex([( 'btc', 'close'), ( 'eth', 'close')])? — Jacob R, Nov 06 '21 at 18:21
@JacobR see updated, and also https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.swaplevel.html — StupidWolf, Nov 06 '21 at 18:26

Creating a multi-indexed column dataframe

1 Answers1