How to convert multiple columns of dictionaries to a dataframe?

I’m using the cryptocompare API and trying to pull the historical data for a bunch of different tickers.

Tickers are read from an excel spreadsheet and then loaded through the API to get the historical prices for each ticker.

This is the dataframe that I am currently receiving: https://docs.google.com/spreadsheets/d/1jQ7F0H2D-voTBxjHI3QVPTRNyR4m4qKob8NE04JWVY0/edit?usp=sharing

I am wondering a few things:

1. How do I get the index to be the date and
2. How would I access just the ‘close’ data point?

This is currently my code:

import pandas as pd

import cryptocompare
import datetime

df = pd.read_excel('C:/Users/Jacob/Downloads/Crypto Tickers1.xlsx', sheet_name='Sheet1')

tickers_list = df['Ticker'].tolist()
data = pd.DataFrame(columns=tickers_list)

for ticker in tickers_list:
    data[ticker] = cryptocompare.get_historical_price_day(ticker, 'USD', limit = 1000 , exchange='CCCAGG', toTs=datetime.datetime(2020,9,22)) 

export_excel = data.to_excel(r'C:/Users/Jacob/Downloads/Crypto Scrape df.xlsx', sheet_name='Sheet1', index= True)

Sample data

• Top 5 rows of Crypto Scrape df.xlsx

,Unnamed: 0,BTC,ETH,USDT,XRP,BCH,DOT,BNB,LINK,CRO,LTC,BSV,ADA,USDC,EOS,TRX,XMR,XTZ,XLM,NEO,LEO,XEM,HT,ATOM,DAI,VET,WBTC,YFI,MIOTA,DASH,UMA,LEND,ETC,ZEC,TUSD,THETA,MKR,ONT,UNI,BUSD,DGB,SNX,OMG,OKB,CELO,FTT,COMP,DOGE,BAT,ALGO
0,0,"{'time': 1514332800, 'close': 15416.64, 'high': 16514.59, 'low': 14534.66, 'open': 15757.02, 'volumefrom': 138705.28, 'volumeto': 2162831128.76}","{'time': 1514332800, 'close': 739.94, 'high': 766.53, 'low': 706.67, 'open': 753.44, 'volumefrom': 474557.77, 'volumeto': 352542483.1}","{'time': 1514332800, 'close': 1.01, 'high': 1.03, 'low': 0.9977, 'open': 1, 'volumefrom': 1893983.71, 'volumeto': 1905757.27}","{'time': 1514332800, 'close': 1.21, 'high': 1.28, 'low': 1.07, 'open': 1.07, 'volumefrom': 366816779.57, 'volumeto': 426150824.52}","{'time': 1514332800, 'close': 2710.64, 'high': 2924.47, 'low': 2568.85, 'open': 2921.89, 'volumefrom': 58588.61, 'volumeto': 161281901.39}","{'time': 1514332800, 'close': 0, 'high': 0, 'low': 0, 'open': 0, 'volumefrom': 0, 'volumeto': 0}","{'time': 1514332800, 'close': 0, 'high': 0, 'low': 0, 'open': 0, 'volumefrom': 0, 'volumeto': 0}","{'time': 1514332800, 'close': 0, 'high': 0, 'low': 0, 'open': 0, 'volumefrom': 0, 'volumeto': 0}","{'time': 1514332800, 'high': 0, 'low': 0, 'open': 0, 'volumefrom': 0, 'volumeto': 0, 'close': 0}","{'time': 1514332800, 'close': 264.07, 'high': 285.32, 'low': 252.87, 'open': 279.47, 'volumefrom': 812937.24, 'volumeto': 219103646.23}","{'time': 1514332800, 'close': 0, 'high': 0, 'low': 0, 'open': 0, 'volumefrom': 0, 'volumeto': 0}","{'time': 1514332800, 'close': 0, 'high': 0, 'low': 0, 'open': 0, 'volumefrom': 0, 'volumeto': 0}","{'time': 1514332800, 'close': 0, 'high': 0, 'low': 0, 'open': 0, 'volumefrom': 0, 'volumeto': 0}","{'time': 1514332800, 'close': 8.72, 'high': 9.41, 'low': 8.06, 'open': 8.6, 'volumefrom': 5228997.52, 'volumeto': 45882343.74}","{'time': 1514332800, 'close': 0.03649, 'high': 0.0418, 'low': 0.03366, 'open': 0.03762, 'volumefrom': 21261366.83, 'volumeto': 790115.86}","{'time': 1514332800, 'close': 375.87, 'high': 409.15, 'low': 342.36, 'open': 357.33, 'volumefrom': 114281.49, 'volumeto': 42798003.92}","{'time': 1514332800, 'close': 3.87, 'high': 4.28, 'low': 3.61, 'open': 4.22, 'volumefrom': 83509.97, 'volumeto': 326988.85}","{'time': 1514332800, 'close': 0.227, 'high': 0.23, 'low': 0.211, 'open': 0.2221, 'volumefrom': 25228631.38, 'volumeto': 5590962.68}","{'time': 1514332800, 'close': 65.99, 'high': 69.59, 'low': 61.69, 'open': 64.94, 'volumefrom': 371667.22, 'volumeto': 24419564.66}","{'time': 1514332800, 'close': 0, 'high': 0, 'low': 0, 'open': 0, 'volumefrom': 0, 'volumeto': 0}","{'time': 1514332800, 'high': 0.9669, 'low': 0.8832, 'open': 0.9785, 'volumefrom': 37818131.45, 'volumeto': 35163298.62, 'close': 0.9298}","{'time': 1514332800, 'high': 0, 'low': 0, 'open': 0, 'volumefrom': 0, 'volumeto': 0, 'close': 0}","{'time': 1514332800, 'close': 0, 'high': 0, 'low': 0, 'open': 0, 'volumefrom': 0, 'volumeto': 0}","{'time': 1514332800, 'close': 1.01, 'high': 1.01, 'low': 1.01, 'open': 1.01, 'volumefrom': 12600.24, 'volumeto': 12789.25}","{'time': 1514332800, 'close': 1.99, 'high': 2.99, 'low': 1.4, 'open': 1.41, 'volumefrom': 104104.2, 'volumeto': 199275.68}","{'time': 1514332800, 'high': 0, 'low': 0, 'open': 0, 'volumefrom': 0, 'volumeto': 0, 'close': 0}","{'time': 1514332800, 'high': 0, 'low': 0, 'open': 0, 'volumefrom': 0, 'volumeto': 0, 'close': 0}","{'time': 1514332800, 'close': 3.7, 'high': 4.34, 'low': 3.58, 'open': 3.77, 'volumefrom': 41018358.72, 'volumeto': 160528719.68}","{'time': 1514332800, 'close': 1133.36, 'high': 1210.54, 'low': 1103.32, 'open': 1181.28, 'volumefrom': 23144.48, 'volumeto': 26954078.18}","{'time': 1514332800, 'close': 0, 'high': 0, 'low': 0, 'open': 0, 'volumefrom': 0, 'volumeto': 0}","{'time': 1514332800, 'high': 0.08554, 'low': 0.07459, 'open': 0.08529, 'volumefrom': 28035120.34, 'volumeto': 2209167.48, 'close': 0.0788}","{'time': 1514332800, 'close': 28.78, 'high': 30.74, 'low': 27.59, 'open': 29.99, 'volumefrom': 832030.36, 'volumeto': 24459145.35}","{'time': 1514332800, 'close': 517.26, 'high': 558.79, 'low': 501.44, 'open': 546.2, 'volumefrom': 38968.71, 'volumeto': 20853518.55}","{'time': 1514332800, 'high': 0, 'low': 0, 'open': 0, 'volumefrom': 0, 'volumeto': 0, 'close': 0}","{'time': 1514332800, 'high': 0, 'low': 0, 'open': 0, 'volumefrom': 0, 'volumeto': 0, 'close': 0}","{'time': 1514332800, 'close': 0, 'high': 0, 'low': 0, 'open': 0, 'volumefrom': 0, 'volumeto': 0}","{'time': 1514332800, 'close': 0, 'high': 0, 'low': 0, 'open': 0, 'volumefrom': 0, 'volumeto': 0}","{'time': 1514332800, 'close': 0, 'high': 0, 'low': 0, 'open': 0, 'volumefrom': 0, 'volumeto': 0}","{'time': 1514332800, 'high': 0, 'low': 0, 'open': 0, 'volumefrom': 0, 'volumeto': 0, 'close': 0}","{'time': 1514332800, 'high': 0.07554, 'low': 0.05596, 'open': 0.06665, 'volumefrom': 896863642.17, 'volumeto': 57793893.1, 'close': 0.06444}","{'time': 1514332800, 'high': 0, 'low': 0, 'open': 0, 'volumefrom': 0, 'volumeto': 0, 'close': 0}","{'time': 1514332800, 'close': 14.88, 'high': 15.74, 'low': 13.93, 'open': 15.12, 'volumefrom': 876604.62, 'volumeto': 13122887.28}","{'time': 1514332800, 'high': 0, 'low': 0, 'open': 0, 'volumefrom': 0, 'volumeto': 0, 'close': 0}","{'time': 1514332800, 'close': 0, 'high': 0, 'low': 0, 'open': 0, 'volumefrom': 0, 'volumeto': 0}","{'time': 1514332800, 'close': 0, 'high': 0, 'low': 0, 'open': 0, 'volumefrom': 0, 'volumeto': 0}","{'time': 1514332800, 'close': 0, 'high': 0, 'low': 0, 'open': 0, 'volumefrom': 0, 'volumeto': 0}","{'time': 1514332800, 'high': 0.009404, 'low': 0.008325, 'open': 0.009297, 'volumefrom': 4399747103.97, 'volumeto': 37983016.75, 'close': 0.008633}","{'time': 1514332800, 'close': 0.32, 'high': 0.7733, 'low': 0.32, 'open': 0.59, 'volumefrom': 929.78, 'volumeto': 615.48}","{'time': 1514332800, 'close': 0, 'high': 0, 'low': 0, 'open': 0, 'volumefrom': 0, 'volumeto': 0}"
1,1,"{'time': 1514419200, 'close': 14398.7, 'high': 15505.51, 'low': 13466.07, 'open': 15416.34, 'volumefrom': 170366.63, 'volumeto': 2425912717.56}","{'time': 1514419200, 'close': 716.69, 'high': 741.46, 'low': 671.23, 'open': 739.93, 'volumefrom': 624468.48, 'volumeto': 437323299.68}","{'time': 1514419200, 'close': 1.02, 'high': 1.03, 'low': 1, 'open': 1.01, 'volumefrom': 5163794.08, 'volumeto': 5257404.82}","{'time': 1514419200, 'close': 1.26, 'high': 1.31, 'low': 1.1, 'open': 1.21, 'volumefrom': 286904797.52, 'volumeto': 346845252.53}","{'time': 1514419200, 'close': 2484.96, 'high': 2727.03, 'low': 2291.94, 'open': 2710.64, 'volumefrom': 79023.66, 'volumeto': 193654890.44}","{'time': 1514419200, 'close': 0, 'high': 0, 'low': 0, 'open': 0, 'volumefrom': 0, 'volumeto': 0}","{'time': 1514419200, 'close': 0, 'high': 0, 'low': 0, 'open': 0, 'volumefrom': 0, 'volumeto': 0}","{'time': 1514419200, 'close': 0, 'high': 0, 'low': 0, 'open': 0, 'volumefrom': 0, 'volumeto': 0}","{'time': 1514419200, 'high': 0, 'low': 0, 'open': 0, 'volumefrom': 0, 'volumeto': 0, 'close': 0}","{'time': 1514419200, 'close': 249.86, 'high': 265.65, 'low': 224.07, 'open': 264.07, 'volumefrom': 1155310.98, 'volumeto': 281385850.91}","{'time': 1514419200, 'close': 0, 'high': 0, 'low': 0, 'open': 0, 'volumefrom': 0, 'volumeto': 0}","{'time': 1514419200, 'close': 0, 'high': 0, 'low': 0, 'open': 0, 'volumefrom': 0, 'volumeto': 0}","{'time': 1514419200, 'close': 0, 'high': 0, 'low': 0, 'open': 0, 'volumefrom': 0, 'volumeto': 0}","{'time': 1514419200, 'close': 8.1, 'high': 8.76, 'low': 7.26, 'open': 8.72, 'volumefrom': 4746439.34, 'volumeto': 37397942.8}","{'time': 1514419200, 'close': 0.03984, 'high': 0.04017, 'low': 0.03001, 'open': 0.03649, 'volumefrom': 17691297.73, 'volumeto': 603769.47}","{'time': 1514419200, 'close': 349.95, 'high': 376.59, 'low': 325.25, 'open': 375.87, 'volumefrom': 81761.16, 'volumeto': 27881671.11}","{'time': 1514419200, 'close': 3.74, 'high': 3.91, 'low': 3.4, 'open': 3.87, 'volumefrom': 55483.4, 'volumeto': 205367.74}","{'time': 1514419200, 'close': 0.2174, 'high': 0.227, 'low': 0.1922, 'open': 0.227, 'volumefrom': 25744822.88, 'volumeto': 5312919.04}","{'time': 1514419200, 'close': 64.82, 'high': 66.36, 'low': 56.12, 'open': 65.99, 'volumefrom': 371435.33, 'volumeto': 22318099.09}","{'time': 1514419200, 'close': 0, 'high': 0, 'low': 0, 'open': 0, 'volumefrom': 0, 'volumeto': 0}","{'time': 1514419200, 'high': 0.9083, 'low': 0.818, 'open': 0.9299, 'volumefrom': 38319761.88, 'volumeto': 34621904.86, 'close': 0.9035}","{'time': 1514419200, 'high': 0, 'low': 0, 'open': 0, 'volumefrom': 0, 'volumeto': 0, 'close': 0}","{'time': 1514419200, 'close': 0, 'high': 0, 'low': 0, 'open': 0, 'volumefrom': 0, 'volumeto': 0}","{'time': 1514419200, 'close': 1.1, 'high': 1.1, 'low': 1.01, 'open': 1.01, 'volumefrom': 423.92, 'volumeto': 430.41}","{'time': 1514419200, 'close': 1.94, 'high': 2.17, 'low': 1.6, 'open': 1.99, 'volumefrom': 58749.5, 'volumeto': 106627.68}","{'time': 1514419200, 'high': 0, 'low': 0, 'open': 0, 'volumefrom': 0, 'volumeto': 0, 'close': 0}","{'time': 1514419200, 'high': 0, 'low': 0, 'open': 0, 'volumefrom': 0, 'volumeto': 0, 'close': 0}","{'time': 1514419200, 'close': 3.65, 'high': 3.77, 'low': 3.21, 'open': 3.7, 'volumefrom': 27430927.78, 'volumeto': 94241062.36}","{'time': 1514419200, 'close': 1103.06, 'high': 1138.81, 'low': 992.24, 'open': 1133.5, 'volumefrom': 24238.95, 'volumeto': 25310848.28}","{'time': 1514419200, 'close': 0, 'high': 0, 'low': 0, 'open': 0, 'volumefrom': 0, 'volumeto': 0}","{'time': 1514419200, 'high': 0.1413, 'low': 0.07071, 'open': 0.0788, 'volumefrom': 86238782.2, 'volumeto': 10020946.49, 'close': 0.1162}","{'time': 1514419200, 'close': 26.89, 'high': 28.95, 'low': 25.18, 'open': 28.78, 'volumefrom': 888110, 'volumeto': 23440767.94}","{'time': 1514419200, 'close': 478.64, 'high': 524.43, 'low': 452.53, 'open': 517.26, 'volumefrom': 36493.86, 'volumeto': 17473141.22}","{'time': 1514419200, 'high': 0, 'low': 0, 'open': 0, 'volumefrom': 0, 'volumeto': 0, 'close': 0}","{'time': 1514419200, 'high': 0, 'low': 0, 'open': 0, 'volumefrom': 0, 'volumeto': 0, 'close': 0}","{'time': 1514419200, 'close': 0, 'high': 0, 'low': 0, 'open': 0, 'volumefrom': 0, 'volumeto': 0}","{'time': 1514419200, 'close': 0, 'high': 0, 'low': 0, 'open': 0, 'volumefrom': 0, 'volumeto': 0}","{'time': 1514419200, 'close': 0, 'high': 0, 'low': 0, 'open': 0, 'volumefrom': 0, 'volumeto': 0}","{'time': 1514419200, 'high': 0, 'low': 0, 'open': 0, 'volumefrom': 0, 'volumeto': 0, 'close': 0}","{'time': 1514419200, 'high': 0.06263, 'low': 0.05515, 'open': 0.06444, 'volumefrom': 543236449.66, 'volumeto': 32148733.09, 'close': 0.05918}","{'time': 1514419200, 'high': 0, 'low': 0, 'open': 0, 'volumefrom': 0, 'volumeto': 0, 'close': 0}","{'time': 1514419200, 'close': 15, 'high': 15.24, 'low': 12.63, 'open': 14.88, 'volumefrom': 1024862.1, 'volumeto': 14110907.17}","{'time': 1514419200, 'high': 0, 'low': 0, 'open': 0, 'volumefrom': 0, 'volumeto': 0, 'close': 0}","{'time': 1514419200, 'close': 0, 'high': 0, 'low': 0, 'open': 0, 'volumefrom': 0, 'volumeto': 0}","{'time': 1514419200, 'close': 0, 'high': 0, 'low': 0, 'open': 0, 'volumefrom': 0, 'volumeto': 0}","{'time': 1514419200, 'close': 0, 'high': 0, 'low': 0, 'open': 0, 'volumefrom': 0, 'volumeto': 0}","{'time': 1514419200, 'high': 0.008351, 'low': 0.007487, 'open': 0.008787, 'volumefrom': 3546300769.93, 'volumeto': 29104490.42, 'close': 0.008207}","{'time': 1514419200, 'close': 0.6891, 'high': 0.7499, 'low': 0.32, 'open': 0.32, 'volumefrom': 40.93, 'volumeto': 19.72}","{'time': 1514419200, 'close': 0, 'high': 0, 'low': 0, 'open': 0, 'volumefrom': 0, 'volumeto': 0}"
2,2,"{'time': 1514505600, 'close': 14392.57, 'high': 15109.81, 'low': 13951.08, 'open': 14398.45, 'volumefrom': 118874.63, 'volumeto': 1733583750.2}","{'time': 1514505600, 'close': 739.6, 'high': 755.2, 'low': 710.2, 'open': 716.69, 'volumefrom': 533531.26, 'volumeto': 392497843.64}","{'time': 1514505600, 'close': 1.02, 'high': 1.06, 'low': 1.01, 'open': 1.02, 'volumefrom': 1855015.02, 'volumeto': 1891615.8}","{'time': 1514505600, 'close': 1.93, 'high': 1.97, 'low': 1.25, 'open': 1.26, 'volumefrom': 664919154.54, 'volumeto': 1092825146.85}","{'time': 1514505600, 'close': 2619.32, 'high': 2884.96, 'low': 2311.24, 'open': 2486.54, 'volumefrom': 107608.19, 'volumeto': 279681307.4}","{'time': 1514505600, 'close': 0, 'high': 0, 'low': 0, 'open': 0, 'volumefrom': 0, 'volumeto': 0}","{'time': 1514505600, 'close': 0, 'high': 0, 'low': 0, 'open': 0, 'volumefrom': 0, 'volumeto': 0}","{'time': 1514505600, 'close': 0, 'high': 0, 'low': 0, 'open': 0, 'volumefrom': 0, 'volumeto': 0}","{'time': 1514505600, 'high': 0, 'low': 0, 'open': 0, 'volumefrom': 0, 'volumeto': 0, 'close': 0}","{'time': 1514505600, 'close': 243.13, 'high': 258.36, 'low': 236.39, 'open': 249.84, 'volumefrom': 836578.77, 'volumeto': 207115905.19}","{'time': 1514505600, 'close': 0, 'high': 0, 'low': 0, 'open': 0, 'volumefrom': 0, 'volumeto': 0}","{'time': 1514505600, 'close': 0, 'high': 0, 'low': 0, 'open': 0, 'volumefrom': 0, 'volumeto': 0}","{'time': 1514505600, 'close': 0, 'high': 0, 'low': 0, 'open': 0, 'volumefrom': 0, 'volumeto': 0}","{'time': 1514505600, 'close': 8.12, 'high': 8.73, 'low': 7.9, 'open': 8.1, 'volumefrom': 3977974.07, 'volumeto': 33063363.07}","{'time': 1514505600, 'close': 0.03543, 'high': 0.04244, 'low': 0.03346, 'open': 0.03983, 'volumefrom': 19908957.97, 'volumeto': 744970.78}","{'time': 1514505600, 'close': 349.19, 'high': 367.77, 'low': 335.96, 'open': 349.95, 'volumefrom': 77817.68, 'volumeto': 27514086.17}","{'time': 1514505600, 'close': 3.71, 'high': 4.44, 'low': 3.65, 'open': 3.74, 'volumefrom': 63975.6, 'volumeto': 262117.42}","{'time': 1514505600, 'close': 0.317, 'high': 0.33, 'low': 0.2155, 'open': 0.2174, 'volumefrom': 72792938.47, 'volumeto': 19734849.44}","{'time': 1514505600, 'close': 72.75, 'high': 74.19, 'low': 62.01, 'open': 64.84, 'volumefrom': 349887.23, 'volumeto': 23816112.34}","{'time': 1514505600, 'close': 0, 'high': 0, 'low': 0, 'open': 0, 'volumefrom': 0, 'volumeto': 0}","{'time': 1514505600, 'high': 1.05, 'low': 0.8788, 'open': 0.9029, 'volumefrom': 61461211.26, 'volumeto': 63612353.65, 'close': 1.035}","{'time': 1514505600, 'high': 0, 'low': 0, 'open': 0, 'volumefrom': 0, 'volumeto': 0, 'close': 0}","{'time': 1514505600, 'close': 0, 'high': 0, 'low': 0, 'open': 0, 'volumefrom': 0, 'volumeto': 0}","{'time': 1514505600, 'close': 1.1, 'high': 1.1, 'low': 1.1, 'open': 1.1, 'volumefrom': 11824.88, 'volumeto': 13007.36}","{'time': 1514505600, 'close': 2.31, 'high': 2.89, 'low': 1.81, 'open': 1.94, 'volumefrom': 72472.8, 'volumeto': 164568.24}","{'time': 1514505600, 'high': 0, 'low': 0, 'open': 0, 'volumefrom': 0, 'volumeto': 0, 'close': 0}","{'time': 1514505600, 'high': 0, 'low': 0, 'open': 0, 'volumefrom': 0, 'volumeto': 0, 'close': 0}","{'time': 1514505600, 'close': 3.65, 'high': 3.91, 'low': 3.43, 'open': 3.65, 'volumefrom': 21093666.08, 'volumeto': 77358590.64}","{'time': 1514505600, 'close': 1089.31, 'high': 1144.02, 'low': 1023.52, 'open': 1103.06, 'volumefrom': 23556.71, 'volumeto': 25796086.74}","{'time': 1514505600, 'close': 0, 'high': 0, 'low': 0, 'open': 0, 'volumefrom': 0, 'volumeto': 0}","{'time': 1514505600, 'high': 0.259, 'low': 0.1157, 'open': 0.1156, 'volumefrom': 180918770.41, 'volumeto': 39765945.74, 'close': 0.2198}","{'time': 1514505600, 'close': 27.38, 'high': 28.63, 'low': 25.96, 'open': 26.89, 'volumefrom': 949572.82, 'volumeto': 26128205.27}","{'time': 1514505600, 'close': 519.04, 'high': 553.15, 'low': 461.62, 'open': 478.64, 'volumefrom': 52148.45, 'volumeto': 26471275.94}","{'time': 1514505600, 'high': 0, 'low': 0, 'open': 0, 'volumefrom': 0, 'volumeto': 0, 'close': 0}","{'time': 1514505600, 'high': 0, 'low': 0, 'open': 0, 'volumefrom': 0, 'volumeto': 0, 'close': 0}","{'time': 1514505600, 'close': 0, 'high': 0, 'low': 0, 'open': 0, 'volumefrom': 0, 'volumeto': 0}","{'time': 1514505600, 'close': 0, 'high': 0, 'low': 0, 'open': 0, 'volumefrom': 0, 'volumeto': 0}","{'time': 1514505600, 'close': 0, 'high': 0, 'low': 0, 'open': 0, 'volumefrom': 0, 'volumeto': 0}","{'time': 1514505600, 'high': 0, 'low': 0, 'open': 0, 'volumefrom': 0, 'volumeto': 0, 'close': 0}","{'time': 1514505600, 'high': 0.06937, 'low': 0.05872, 'open': 0.05918, 'volumefrom': 581339936.14, 'volumeto': 38821880.94, 'close': 0.06678}","{'time': 1514505600, 'high': 0, 'low': 0, 'open': 0, 'volumefrom': 0, 'volumeto': 0, 'close': 0}","{'time': 1514505600, 'close': 15.87, 'high': 16.76, 'low': 14.4, 'open': 15, 'volumefrom': 1486836.82, 'volumeto': 23332762.58}","{'time': 1514505600, 'high': 0, 'low': 0, 'open': 0, 'volumefrom': 0, 'volumeto': 0, 'close': 0}","{'time': 1514505600, 'close': 0, 'high': 0, 'low': 0, 'open': 0, 'volumefrom': 0, 'volumeto': 0}","{'time': 1514505600, 'close': 0, 'high': 0, 'low': 0, 'open': 0, 'volumefrom': 0, 'volumeto': 0}","{'time': 1514505600, 'close': 0, 'high': 0, 'low': 0, 'open': 0, 'volumefrom': 0, 'volumeto': 0}","{'time': 1514505600, 'high': 0.009355, 'low': 0.007772, 'open': 0.008207, 'volumefrom': 4557615427.91, 'volumeto': 42636492.33, 'close': 0.009355}","{'time': 1514505600, 'close': 0.4013, 'high': 0.6891, 'low': 0.4013, 'open': 0.6891, 'volumefrom': 419.17, 'volumeto': 172.94}","{'time': 1514505600, 'close': 0, 'high': 0, 'low': 0, 'open': 0, 'volumefrom': 0, 'volumeto': 0}"
3,3,"{'time': 1514592000, 'close': 12531.52, 'high': 14461.46, 'low': 11962.09, 'open': 14392.14, 'volumefrom': 182065.44, 'volumeto': 2387311023.42}","{'time': 1514592000, 'close': 692.99, 'high': 741.67, 'low': 663.88, 'open': 739.6, 'volumefrom': 654093.06, 'volumeto': 455555629.24}","{'time': 1514592000, 'close': 1.02, 'high': 1.49, 'low': 1.02, 'open': 1.02, 'volumefrom': 4235414.16, 'volumeto': 4373808.9}","{'time': 1514592000, 'close': 1.86, 'high': 2.44, 'low': 1.77, 'open': 1.93, 'volumefrom': 756289917.24, 'volumeto': 1565228735.26}","{'time': 1514592000, 'close': 2209.96, 'high': 2634.36, 'low': 2103.91, 'open': 2622.42, 'volumefrom': 95886.67, 'volumeto': 221420859.69}","{'time': 1514592000, 'close': 0, 'high': 0, 'low': 0, 'open': 0, 'volumefrom': 0, 'volumeto': 0}","{'time': 1514592000, 'close': 0, 'high': 0, 'low': 0, 'open': 0, 'volumefrom': 0, 'volumeto': 0}","{'time': 1514592000, 'close': 0, 'high': 0, 'low': 0, 'open': 0, 'volumefrom': 0, 'volumeto': 0}","{'time': 1514592000, 'high': 0, 'low': 0, 'open': 0, 'volumefrom': 0, 'volumeto': 0, 'close': 0}","{'time': 1514592000, 'close': 212.07, 'high': 243.42, 'low': 202.02, 'open': 243.13, 'volumefrom': 1218741.31, 'volumeto': 268737310.82}","{'time': 1514592000, 'close': 0, 'high': 0, 'low': 0, 'open': 0, 'volumefrom': 0, 'volumeto': 0}","{'time': 1514592000, 'close': 0.59, 'high': 0.6941, 'low': 0.42, 'open': 0.4955, 'volumefrom': 24118261.7, 'volumeto': 14016860.69}","{'time': 1514592000, 'close': 0, 'high': 0, 'low': 0, 'open': 0, 'volumefrom': 0, 'volumeto': 0}","{'time': 1514592000, 'close': 7.08, 'high': 8.19, 'low': 6.52, 'open': 8.12, 'volumefrom': 5483898.78, 'volumeto': 39561494.38}","{'time': 1514592000, 'close': 0.03751, 'high': 0.04134, 'low': 0.0288, 'open': 0.03543, 'volumefrom': 18454838.11, 'volumeto': 650470.81}","{'time': 1514592000, 'close': 304.06, 'high': 352.37, 'low': 288.08, 'open': 349.22, 'volumefrom': 90295.26, 'volumeto': 28290138.8}","{'time': 1514592000, 'close': 3.78, 'high': 4.14, 'low': 3.65, 'open': 3.71, 'volumefrom': 70011.81, 'volumeto': 268886.56}","{'time': 1514592000, 'close': 0.309, 'high': 0.37, 'low': 0.259, 'open': 0.316, 'volumefrom': 111504571.73, 'volumeto': 35282622.91}","{'time': 1514592000, 'close': 70.47, 'high': 73.99, 'low': 60.46, 'open': 72.75, 'volumefrom': 557587.78, 'volumeto': 36933855.39}","{'time': 1514592000, 'close': 0, 'high': 0, 'low': 0, 'open': 0, 'volumefrom': 0, 'volumeto': 0}","{'time': 1514592000, 'high': 0.9387, 'low': 0.8211, 'open': 1.037, 'volumefrom': 48783253.89, 'volumeto': 43524419.12, 'close': 0.8922}","{'time': 1514592000, 'high': 0, 'low': 0, 'open': 0, 'volumefrom': 0, 'volumeto': 0, 'close': 0}","{'time': 1514592000, 'close': 0, 'high': 0, 'low': 0, 'open': 0, 'volumefrom': 0, 'volumeto': 0}","{'time': 1514592000, 'close': 1.1, 'high': 1.1, 'low': 1.1, 'open': 1.1, 'volumefrom': 0, 'volumeto': 0}","{'time': 1514592000, 'close': 2.06, 'high': 2.54, 'low': 1.9, 'open': 2.31, 'volumefrom': 55597, 'volumeto': 117893.68}","{'time': 1514592000, 'high': 0, 'low': 0, 'open': 0, 'volumefrom': 0, 'volumeto': 0, 'close': 0}","{'time': 1514592000, 'high': 0, 'low': 0, 'open': 0, 'volumefrom': 0, 'volumeto': 0, 'close': 0}","{'time': 1514592000, 'close': 3.24, 'high': 3.74, 'low': 2.9, 'open': 3.65, 'volumefrom': 31534137.33, 'volumeto': 101368721.21}","{'time': 1514592000, 'close': 943.14, 'high': 1095.32, 'low': 875.9, 'open': 1089.16, 'volumefrom': 32783.59, 'volumeto': 31635527.04}","{'time': 1514592000, 'close': 0, 'high': 0, 'low': 0, 'open': 0, 'volumefrom': 0, 'volumeto': 0}","{'time': 1514592000, 'high': 0.2071, 'low': 0.1422, 'open': 0.2199, 'volumefrom': 53627980.44, 'volumeto': 8757449.21, 'close': 0.1633}","{'time': 1514592000, 'close': 23.84, 'high': 27.37, 'low': 22.77, 'open': 27.35, 'volumefrom': 1377675.17, 'volumeto': 34204056.4}","{'time': 1514592000, 'close': 446.56, 'high': 519.35, 'low': 417.02, 'open': 519.04, 'volumefrom': 55900.68, 'volumeto': 25326397.58}","{'time': 1514592000, 'high': 0, 'low': 0, 'open': 0, 'volumefrom': 0, 'volumeto': 0, 'close': 0}","{'time': 1514592000, 'high': 0, 'low': 0, 'open': 0, 'volumefrom': 0, 'volumeto': 0, 'close': 0}","{'time': 1514592000, 'close': 0, 'high': 0, 'low': 0, 'open': 0, 'volumefrom': 0, 'volumeto': 0}","{'time': 1514592000, 'close': 0, 'high': 0, 'low': 0, 'open': 0, 'volumefrom': 0, 'volumeto': 0}","{'time': 1514592000, 'close': 0, 'high': 0, 'low': 0, 'open': 0, 'volumefrom': 0, 'volumeto': 0}","{'time': 1514592000, 'high': 0, 'low': 0, 'open': 0, 'volumefrom': 0, 'volumeto': 0, 'close': 0}","{'time': 1514592000, 'high': 0.06955, 'low': 0.05389, 'open': 0.06678, 'volumefrom': 709641569.01, 'volumeto': 48468519.16, 'close': 0.0683}","{'time': 1514592000, 'high': 0, 'low': 0, 'open': 0, 'volumefrom': 0, 'volumeto': 0, 'close': 0}","{'time': 1514592000, 'close': 16.48, 'high': 16.5, 'low': 12.57, 'open': 15.87, 'volumefrom': 1482656.31, 'volumeto': 21305398.55}","{'time': 1514592000, 'high': 0, 'low': 0, 'open': 0, 'volumefrom': 0, 'volumeto': 0, 'close': 0}","{'time': 1514592000, 'close': 0, 'high': 0, 'low': 0, 'open': 0, 'volumefrom': 0, 'volumeto': 0}","{'time': 1514592000, 'close': 0, 'high': 0, 'low': 0, 'open': 0, 'volumefrom': 0, 'volumeto': 0}","{'time': 1514592000, 'close': 0, 'high': 0, 'low': 0, 'open': 0, 'volumefrom': 0, 'volumeto': 0}","{'time': 1514592000, 'high': 0.008271, 'low': 0.007394, 'open': 0.009355, 'volumefrom': 4085865840.54, 'volumeto': 31747177.58, 'close': 0.00777}","{'time': 1514592000, 'close': 0.4001, 'high': 0.6573, 'low': 0.4001, 'open': 0.4013, 'volumefrom': 172.65, 'volumeto': 84.46}","{'time': 1514592000, 'close': 0, 'high': 0, 'low': 0, 'open': 0, 'volumefrom': 0, 'volumeto': 0}"
4,4,"{'time': 1514678400, 'close': 13850.4, 'high': 14241.82, 'low': 12359.43, 'open': 12532.38, 'volumefrom': 111270.55, 'volumeto': 1492142483.58}","{'time': 1514678400, 'close': 741.13, 'high': 746.62, 'low': 686.44, 'open': 692.99, 'volumefrom': 361409.91, 'volumeto': 259822610.45}","{'time': 1514678400, 'close': 1.02, 'high': 1.11, 'low': 1.02, 'open': 1.02, 'volumefrom': 1043043.31, 'volumeto': 1069474.26}","{'time': 1514678400, 'close': 1.98, 'high': 2.09, 'low': 1.61, 'open': 1.86, 'volumefrom': 275180874.46, 'volumeto': 514273018.98}","{'time': 1514678400, 'close': 2371.83, 'high': 2485.77, 'low': 2165.12, 'open': 2210.18, 'volumefrom': 55810.75, 'volumeto': 131177357.02}","{'time': 1514678400, 'close': 0, 'high': 0, 'low': 0, 'open': 0, 'volumefrom': 0, 'volumeto': 0}","{'time': 1514678400, 'close': 0, 'high': 0, 'low': 0, 'open': 0, 'volumefrom': 0, 'volumeto': 0}","{'time': 1514678400, 'close': 0, 'high': 0, 'low': 0, 'open': 0, 'volumefrom': 0, 'volumeto': 0}","{'time': 1514678400, 'high': 0, 'low': 0, 'open': 0, 'volumefrom': 0, 'volumeto': 0, 'close': 0}","{'time': 1514678400, 'close': 226.52, 'high': 235.4, 'low': 207.95, 'open': 212.07, 'volumefrom': 841309.91, 'volumeto': 187138183.8}","{'time': 1514678400, 'close': 0, 'high': 0, 'low': 0, 'open': 0, 'volumefrom': 0, 'volumeto': 0}","{'time': 1514678400, 'close': 0.71, 'high': 0.74, 'low': 0.59, 'open': 0.59, 'volumefrom': 13107255.34, 'volumeto': 8971147.7}","{'time': 1514678400, 'close': 0, 'high': 0, 'low': 0, 'open': 0, 'volumefrom': 0, 'volumeto': 0}","{'time': 1514678400, 'close': 7.69, 'high': 7.82, 'low': 7, 'open': 7.08, 'volumefrom': 2386645.58, 'volumeto': 17720129.43}","{'time': 1514678400, 'close': 0.04862, 'high': 0.04936, 'low': 0.03376, 'open': 0.03752, 'volumefrom': 15377991.11, 'volumeto': 644515.53}","{'time': 1514678400, 'close': 331.83, 'high': 340.84, 'low': 300.76, 'open': 304.06, 'volumefrom': 42848.89, 'volumeto': 13835138.11}","{'time': 1514678400, 'close': 4.05, 'high': 4.22, 'low': 3.67, 'open': 3.76, 'volumefrom': 70924.54, 'volumeto': 276536.87}","{'time': 1514678400, 'close': 0.354, 'high': 0.3586, 'low': 0.2953, 'open': 0.3043, 'volumefrom': 34074086.26, 'volumeto': 11174996.49}","{'time': 1514678400, 'close': 74.66, 'high': 77.39, 'low': 66.54, 'open': 70.47, 'volumefrom': 372563.2, 'volumeto': 27088430.81}","{'time': 1514678400, 'close': 0, 'high': 0, 'low': 0, 'open': 0, 'volumefrom': 0, 'volumeto': 0}","{'time': 1514678400, 'high': 1.072, 'low': 0.95, 'open': 0.8933, 'volumefrom': 38007610.28, 'volumeto': 38615732.04, 'close': 1.016}","{'time': 1514678400, 'high': 0, 'low': 0, 'open': 0, 'volumefrom': 0, 'volumeto': 0, 'close': 0}","{'time': 1514678400, 'close': 0, 'high': 0, 'low': 0, 'open': 0, 'volumefrom': 0, 'volumeto': 0}","{'time': 1514678400, 'close': 1.1, 'high': 1.1, 'low': 1.1, 'open': 1.1, 'volumefrom': 0, 'volumeto': 0}","{'time': 1514678400, 'close': 2.29, 'high': 2.55, 'low': 1.92, 'open': 2.06, 'volumefrom': 35073.7, 'volumeto': 77164.15}","{'time': 1514678400, 'high': 0, 'low': 0, 'open': 0, 'volumefrom': 0, 'volumeto': 0, 'close': 0}","{'time': 1514678400, 'high': 0, 'low': 0, 'open': 0, 'volumefrom': 0, 'volumeto': 0, 'close': 0}","{'time': 1514678400, 'close': 3.51, 'high': 3.69, 'low': 3.23, 'open': 3.24, 'volumefrom': 16386887.03, 'volumeto': 56908518.99}","{'time': 1514678400, 'close': 1013.89, 'high': 1054.13, 'low': 924.33, 'open': 943.1, 'volumefrom': 19125.73, 'volumeto': 18988339.51}","{'time': 1514678400, 'close': 0, 'high': 0, 'low': 0, 'open': 0, 'volumefrom': 0, 'volumeto': 0}","{'time': 1514678400, 'high': 0.2214, 'low': 0.164, 'open': 0.1595, 'volumefrom': 36845661.2, 'volumeto': 6396406.78, 'close': 0.1736}","{'time': 1514678400, 'close': 26.05, 'high': 26.61, 'low': 23.62, 'open': 23.84, 'volumefrom': 747488.47, 'volumeto': 18825166.44}","{'time': 1514678400, 'close': 479.29, 'high': 490.19, 'low': 440.42, 'open': 446.76, 'volumefrom': 23582.2, 'volumeto': 11020836.3}","{'time': 1514678400, 'high': 0, 'low': 0, 'open': 0, 'volumefrom': 0, 'volumeto': 0, 'close': 0}","{'time': 1514678400, 'high': 0, 'low': 0, 'open': 0, 'volumefrom': 0, 'volumeto': 0, 'close': 0}","{'time': 1514678400, 'close': 0, 'high': 0, 'low': 0, 'open': 0, 'volumefrom': 0, 'volumeto': 0}","{'time': 1514678400, 'close': 0, 'high': 0, 'low': 0, 'open': 0, 'volumefrom': 0, 'volumeto': 0}","{'time': 1514678400, 'close': 0, 'high': 0, 'low': 0, 'open': 0, 'volumefrom': 0, 'volumeto': 0}","{'time': 1514678400, 'high': 0, 'low': 0, 'open': 0, 'volumefrom': 0, 'volumeto': 0, 'close': 0}","{'time': 1514678400, 'high': 0.07936, 'low': 0.06371, 'open': 0.06843, 'volumefrom': 636944599.74, 'volumeto': 44637077.55, 'close': 0.07008}","{'time': 1514678400, 'high': 0, 'low': 0, 'open': 0, 'volumefrom': 0, 'volumeto': 0, 'close': 0}","{'time': 1514678400, 'close': 19.6, 'high': 21.99, 'low': 15.21, 'open': 16.48, 'volumefrom': 3480056.44, 'volumeto': 65460738.73}","{'time': 1514678400, 'high': 0, 'low': 0, 'open': 0, 'volumefrom': 0, 'volumeto': 0, 'close': 0}","{'time': 1514678400, 'close': 0, 'high': 0, 'low': 0, 'open': 0, 'volumefrom': 0, 'volumeto': 0}","{'time': 1514678400, 'close': 0, 'high': 0, 'low': 0, 'open': 0, 'volumefrom': 0, 'volumeto': 0}","{'time': 1514678400, 'close': 0, 'high': 0, 'low': 0, 'open': 0, 'volumefrom': 0, 'volumeto': 0}","{'time': 1514678400, 'high': 0.009418, 'low': 0.008172, 'open': 0.007645, 'volumefrom': 3199992553.38, 'volumeto': 28809532.96, 'close': 0.009003}","{'time': 1514678400, 'close': 0.591, 'high': 0.5925, 'low': 0.4001, 'open': 0.4001, 'volumefrom': 83.8, 'volumeto': 40.05}","{'time': 1514678400, 'close': 0, 'high': 0, 'low': 0, 'open': 0, 'volumefrom': 0, 'volumeto': 0}"

import pandas as pd
from ast import literal_eval

# load the file
df = pd.read_excel('Crypto Scrape df.xlsx', sheet_name='Sheet1')

# drop the Unnamed column
df.drop(columns=['Unnamed: 0'], inplace=True)

# apply literal_eval to all columns to convert them from strings to dicts
df = df.applymap(literal_eval)

# create a dict of dataframes in a dict comprehension
df_dict = {col: pd.json_normalize(df[col]) for col in df.columns}

# add a ticker column
for k, d in df_dict.items():
    df_dict[k]['ticker'] = k

# combine all the dicts into a single dataframe
df = pd.concat(df_dict.values()).reset_index(drop=True)

# convert the time column to a datetime format
df.time = pd.to_datetime(df.time, unit='s')

# set the time column as the index
df.set_index('time', inplace=True)

# to get only close values under each ticker with time as the index
dfp = df[['close', 'ticker']].pivot(columns='ticker', values='close')

# set the column and index name as None, if desired
dfp.columns.name = None
dfp.index.name = None

# display(dfp.iloc[:5, :5])

             ADA  ALGO  ATOM     BAT      BCH
2017-12-27  0.00   0.0   0.0  0.3200  2710.64
2017-12-28  0.00   0.0   0.0  0.6891  2484.96
2017-12-29  0.00   0.0   0.0  0.4013  2619.32
2017-12-30  0.59   0.0   0.0  0.4001  2209.96
2017-12-31  0.71   0.0   0.0  0.5910  2371.83