自动创建数据帧的Python循环

Question

我正在尝试创建一个循环，它将为每个滴答返回: 1.一个不同的数据帧(以代码机的名称命名) 2.将时间列转换为"normal“day 3，它(新的时间)将被用作该数据帧的索引。

如果我运行它的每一个滴答，它是没有问题的工作。我很感激你的帮助！

import requests
import pandas as pd
desired_width = 320
pd.set_option('display.width', desired_width)

data = pd.DataFrame()
tickers = ['BTC', 'ETH', 'XRP']  # pools of tickers to get
for t in tickers:  # a loop to get data ticker by ticker
        url = 'https://min-api.cryptocompare.com/data/histoday' + \
              '?fsym=' + \
                t +\
              '&tsym=USD' + \
              '&limit=600000000000' + \
              '&aggregate=1' + \
              '&e=CCCAGG'
        response = requests.get(url)
        data[t] = response.json()['Data']
        #the following 2 lines I failed to execute
        #data[t]['time'] = pd.to_datetime(data[t]['time'], unit='s')
        #data[t].index = data[t]['time']
        print("downloading data for: " + t)
        print("data for:" + t, data.head(5))

我的结果是所有三个代码的一个数据帧：

数据来源:XRP BTC {‘时间’：1342742400，‘关闭’：8.52，‘高’：8.{“时间”：1342742400，“结束”：0，“高”：0，“……”{“时间”：1342742400，“结束”：0，“高”：0，“1”{“时间”：1342828800，“结束”：8.85，“高”：9.{“时间”：1342828800，“结束”：0，“高”：0，“……”{“时间”：1342828800，“结束”：0，“高”：0，“l.2”{“时间”：1342915200，“结束”：8.41，“高”：8.{“时间”：1342915200，“结束”：0，“高”：0，“……”{“时间”：1342915200，“结束”：0，“高”：0，“l.3”{“时间”：1343001600，“结束”：8.45，“高”：9.{“时间”：1343001600，“关闭”：0，“高”：0，“l.{”时间“：1343001600，”关闭“：0，”高“：0，”l.4“{”时间“：1343088000，”关闭“：8.6，”高“：8.8.{‘时间’：1343088000，‘关闭’：0，‘高’：0，‘我.’{‘时间’：1343088000，‘关闭’：0，‘高’：0，‘’.

我在Windows 10上使用python3.6和py魅力+ anconda

Answer 1

我认为您可以将json_normalize用于dinctionary of DataFrame，concat用于DataFrame和MultiIndex，一级是tickers。

from pandas.io.json import json_normalize

data = {}
tickers = ['BTC', 'ETH', 'XRP']  # pools of tickers to get
for t in tickers:  # a loop to get data ticker by ticker
        url = 'https://min-api.cryptocompare.com/data/histoday' + \
              '?fsym=' + \
                t +\
              '&tsym=USD' + \
              '&limit=600000000000' + \
              '&aggregate=1' + \
              '&e=CCCAGG'
        response = requests.get(url)
        data[t] = json_normalize(response.json()['Data'])

df = pd.concat(data)
print (df.head())

       close  high   low  open        time  volumefrom    volumeto
BTC 0   8.52  8.87  7.60  8.87  1342742400   154661.12  1267523.74
    1   8.85  9.70  7.96  8.52  1342828800   139906.90  1242153.88
    2   8.41  8.97  8.27  8.85  1342915200    30070.67   259113.81
    3   8.45  9.20  7.75  8.41  1343001600   146396.18  1238579.49
    4   8.60  8.85  8.34  8.45  1343088000    40946.86   353506.54

然后，对于选择的每一个级别都是可以使用的：

print (df.xs('BTC').head())

#print (df.loc['BTC'].head())

   close  high   low  open        time  volumefrom    volumeto
0   8.52  8.87  7.60  8.87  1342742400   154661.12  1267523.74
1   8.85  9.70  7.96  8.52  1342828800   139906.90  1242153.88
2   8.41  8.97  8.27  8.85  1342915200    30070.67   259113.81
3   8.45  9.20  7.75  8.41  1343001600   146396.18  1238579.49
4   8.60  8.85  8.34  8.45  1343088000    40946.86   353506.54

另一种方法不是concat，而是创建dictionary

data = {}
tickers = ['BTC', 'ETH', 'XRP']  # pools of tickers to get
for t in tickers:  # a loop to get data ticker by ticker
        url = 'https://min-api.cryptocompare.com/data/histoday' + \
              '?fsym=' + \
                t +\
              '&tsym=USD' + \
              '&limit=600000000000' + \
              '&aggregate=1' + \
              '&e=CCCAGG'
        response = requests.get(url)
        data[t] = json_normalize(response.json()['Data'])
        data[t] = data[t].set_index(pd.to_datetime(data[t]['time'], unit='s'))

print (data['BTC'].head())

            close  high   low  open        time  volumefrom    volumeto
time                                                                   
2012-07-20   8.52  8.87  7.60  8.87  1342742400   154661.12  1267523.74
2012-07-21   8.85  9.70  7.96  8.52  1342828800   139906.90  1242153.88
2012-07-22   8.41  8.97  8.27  8.85  1342915200    30070.67   259113.81
2012-07-23   8.45  9.20  7.75  8.41  1343001600   146396.18  1238579.49
2012-07-24   8.60  8.85  8.34  8.45  1343088000    40946.86   353506.54

编辑:如果希望全局变量不推荐解决方案：

data = {}
tickers = ['BTC', 'ETH', 'XRP']  # pools of tickers to get
for t in tickers:  # a loop to get data ticker by ticker
        url = 'https://min-api.cryptocompare.com/data/histoday' + \
              '?fsym=' + \
                t +\
              '&tsym=USD' + \
              '&limit=600000000000' + \
              '&aggregate=1' + \
              '&e=CCCAGG'
        response = requests.get(url)
        globals()['df_' + str(t)] = json_normalize(response.json()['Data'])
        globals()['df_' + str(t)] = globals()['df_' + str(t)].set_index(pd.to_datetime(globals()['df_' + str(t)]['time'], unit='s'))

print (df_BTC.head())

            close  high   low  open        time  volumefrom    volumeto
time                                                                   
2012-07-20   8.52  8.87  7.60  8.87  1342742400   154661.12  1267523.74
2012-07-21   8.85  9.70  7.96  8.52  1342828800   139906.90  1242153.88
2012-07-22   8.41  8.97  8.27  8.85  1342915200    30070.67   259113.81
2012-07-23   8.45  9.20  7.75  8.41  1343001600   146396.18  1238579.49
2012-07-24   8.60  8.85  8.34  8.45  1343088000    40946.86   353506.54

Answer 2

我定义了一个函数来获取数据，而不是使用循环来获取所有的代码。这解决了问题。

import requests
import datetime
import pandas as pd
import matplotlib.pyplot as plt
desired_width = 320
pd.set_option('display.width', desired_width)

#function to download the Historical HOUR data
def hourly_price_historical(symbol, comparison_symbol, limit, aggregate, exchange=''):
    url = 'https://min-api.cryptocompare.com/data/histohour?fsym={}&tsym={}&limit={}&aggregate={}'\
            .format(symbol.upper(), comparison_symbol.upper(), limit, aggregate)
    if exchange:
        url += '&e={}'.format(exchange)
    page = requests.get(url)
    data = page.json()['Data']
    df = pd.DataFrame(data)
    df['timestamp'] = [datetime.datetime.fromtimestamp(d) for d in df.time]
    df = df.drop('time', 1)
    df.set_index('timestamp')
    return df


data = {}
tickers = ['BTC', 'ETH', 'XRP']                 # pools of tickers to get
for t in tickers:                                                           # a loop to get data ticker by ticker
    data[t] = hour_data = hourly_price_historical(t,'USD', 9999999,1)       # calling the function defined above
    print("Getting the data for: ", t)
    globals()['df_' + str(t)] = data