How to merge the results of an API call inside a loop to a pandas data frame?

user_7317408 0
Viewed 128

Hi apologies for the noob question...

I have written some code:

with RESTClient(key) as client:
    from_ = "2020-01-09"
    to = "2021-01-10"

    for i in all_tickers:
        ticker =  i['ticker']
        r = client.stocks_equities_aggregates(ticker, 1, "day", from_, to, unadjusted=False)

        print(f"Daily aggregates for {r.ticker} between {from_} and {to}.")

        try:
            df = pd.DataFrame(r.results, columns=["t", "v", "vw", "o", "c", "h", "l", "n"])
            df['ticker'] = ticker
            df = df.append(df)

        except:
            print('nothing')

Which outputs:

Minute aggregates for A between 2022-01-01 and 2022-02-01.
{'v': 1606323.0, 'vw': 155.8021, 'o': 159, 'c': 156.48, 'h': 159.44, 'l': 153.93, 't': 1641186000000, 'n': 24318}
{'v': 2233958.0, 'vw': 151.518, 'o': 155.49, 'c': 151.19, 'h': 155.63, 'l': 149.7, 't': 1641272400000, 'n': 34707}
{'v': 2370529.0, 'vw': 149.9716, 'o': 150.83, 'c': 148.6, 'h': 153.1, 'l': 148.53, 't': 1641358800000, 'n': 27421}
{'v': 2298277.0, 'vw': 148.4397, 'o': 148.85, 'c': 149.12, 'h': 149.96, 'l': 145.58, 't': 1641445200000, 'n': 34441}
{'v': 2058658.0, 'vw': 146.4352, 'o': 149.12, 'c': 145.15, 'h': 149.73, 'l': 145.09, 't': 1641531600000, 'n': 28611}
{'v': 2548145.0, 'vw': 143.2162, 'o': 143.29, 'c': 145.16, 'h': 145.31, 'l': 140.86, 't': 1641790800000, 'n': 37241}
{'v': 2194208.0, 'vw': 146.0091, 'o': 145, 'c': 146.64, 'h': 146.94, 'l': 143.81, 't': 1641877200000, 'n': 22781}
{'v': 2250847.0, 'vw': 149.3025, 'o': 147.8, 'c': 149.51, 'h': 150.39, 'l': 147.55, 't': 1641963600000, 'n': 27392}
{'v': 1741764.0, 'vw': 145.7333, 'o': 149.46, 'c': 145.17, 'h': 149.54, 'l': 144.85, 't': 1642050000000, 'n': 23437}
{'v': 2225442.0, 'vw': 143.9446, 'o': 144.04, 'c': 144.68, 'h': 145.15, 'l': 142.36, 't': 1642136400000, 'n': 28295}
{'v': 1907368.0, 'vw': 141.2762, 'o': 142.42, 'c': 140.47, 'h': 143.24, 'l': 140.34, 't': 1642482000000, 'n': 27031}
{'v': 1472206.0, 'vw': 141.538, 'o': 140.67, 'c': 140.43, 'h': 143.6, 'l': 140.26, 't': 1642568400000, 'n': 23595}
{'v': 1861384.0, 'vw': 140.9367, 'o': 141.38, 'c': 139.48, 'h': 143.14, 'l': 139.05, 't': 1642654800000, 'n': 26612}
{'v': 1878663.0, 'vw': 138.4591, 'o': 139.54, 'c': 137.51, 'h': 140.49, 'l': 137.49, 't': 1642741200000, 'n': 27133}
{'v': 2155299.0, 'vw': 135.3192, 'o': 136.38, 'c': 138.12, 'h': 138.49, 'l': 131.28, 't': 1643000400000, 'n': 32745}
{'v': 1705313.0, 'vw': 134.473, 'o': 135.36, 'c': 134.57, 'h': 136.62, 'l': 132.65, 't': 1643086800000, 'n': 24457}
{'v': 1999575.0, 'vw': 134.5836, 'o': 135.54, 'c': 133.51, 'h': 138.0454, 'l': 132.27, 't': 1643173200000, 'n': 28088}
{'v': 1715819.0, 'vw': 133.1775, 'o': 135.28, 'c': 132.09, 'h': 136.36, 'l': 131.68, 't': 1643259600000, 'n': 25556}
{'v': 2174805.0, 'vw': 135.3363, 'o': 133, 'c': 137.06, 'h': 137.4, 'l': 131.215, 't': 1643346000000, 'n': 21446}
{'v': 1702950.0, 'vw': 138.8672, 'o': 137.32, 'c': 139.32, 'h': 139.47, 'l': 136.9729, 't': 1643605200000, 'n': 21984}
{'v': 1655987.0, 'vw': 140.2601, 'o': 140.53, 'c': 141.03, 'h': 141.27, 'l': 138.45, 't': 1643691600000, 'n': 25755}
Minute aggregates for AA between 2022-01-01 and 2022-02-01.
{'v': 6208442.0, 'vw': 60.9882, 'o': 60.24, 'c': 60.36, 'h': 62.61, 'l': 60.09, 't': 1641186000000, 'n': 49209}
{'v': 7943653.0, 'vw': 58.135, 'o': 60.68, 'c': 57.53, 'h': 61.15, 'l': 57.21, 't': 1641272400000, 'n': 59085}
{'v': 7599751.0, 'vw': 60.0291, 'o': 58.95, 'c': 58.55, 'h': 61.79, 'l': 58.445, 't': 1641358800000, 'n': 65793}
{'v': 4363058.0, 'vw': 58.3964, 'o': 58.94, 'c': 58.45, 'h': 59.4911, 'l': 57.25, 't': 1641445200000, 'n': 39017}
{'v': 8071270.0, 'vw': 61.7246, 'o': 60.14, 'c': 62.37, 'h': 62.89, 'l': 59.65, 't': 1641531600000, 'n': 62728}
{'v': 5653472.0, 'vw': 61.268, 'o': 61.62, 'c': 61.54, 'h': 62.71, 'l': 60.441, 't': 1641790800000, 'n': 43504}
{'v': 6003582.0, 'vw': 61.0876, 'o': 60.71, 'c': 62.2, 'h': 62.25, 'l': 59.12, 't': 1641877200000, 'n': 50028}
{'v': 6434989.0, 'vw': 62.1745, 'o': 63.66, 'c': 61.88, 'h': 64.37, 'l': 60.86, 't': 1641963600000, 'n': 53063}
{'v': 5769838.0, 'vw': 61.5961, 'o': 61.75, 'c': 60.51, 'h': 63.26, 'l': 60.37, 't': 1642050000000, 'n': 46975}
{'v': 4397108.0, 'vw': 60.5607, 'o': 60.27, 'c': 61.39, 'h': 61.44, 'l': 59.34, 't': 1642136400000, 'n': 41558}
{'v': 5994091.0, 'vw': 60.0163, 'o': 60.5, 'c': 60.05, 'h': 61.56, 'l': 58.8, 't': 1642482000000, 'n': 55228}
{'v': 7851084.0, 'vw': 60.0468, 'o': 61.39, 'c': 59.63, 'h': 61.93, 'l': 58.885, 't': 1642568400000, 'n': 62775}
{'v': 15925959.0, 'vw': 62.0662, 'o': 62.1, 'c': 61.25, 'h': 64.25, 'l': 59.97, 't': 1642654800000, 'n': 127187}
{'v': 11024982.0, 'vw': 57.5373, 'o': 60.02, 'c': 56.21, 'h': 60.15, 'l': 56.04, 't': 1642741200000, 'n': 99811}
{'v': 9209629.0, 'vw': 56.091, 'o': 53.81, 'c': 58.02, 'h': 58.2, 'l': 53.26, 't': 1643000400000, 'n': 83494}
{'v': 7780587.0, 'vw': 59.9004, 'o': 57.51, 'c': 61.21, 'h': 61.6, 'l': 56.7608, 't': 1643086800000, 'n': 66222}
{'v': 9267426.0, 'vw': 61.8703, 'o': 61.54, 'c': 60.75, 'h': 63.64, 'l': 59.88, 't': 1643173200000, 'n': 76224}
{'v': 6445290.0, 'vw': 59.0007, 'o': 60.6, 'c': 58.03, 'h': 61.6599, 'l': 57.47, 't': 1643259600000, 'n': 55506}
{'v': 6987869.0, 'vw': 56.9155, 'o': 58, 'c': 57.4, 'h': 58.39, 'l': 55.58, 't': 1643346000000, 'n': 62130}
{'v': 7206053.0, 'vw': 56.1436, 'o': 56.94, 'c': 56.71, 'h': 57.02, 'l': 55.02, 't': 1643605200000, 'n': 59567}
{'v': 5939348.0, 'vw': 57.6426, 'o': 57.99, 'c': 58.17, 'h': 58.44, 'l': 56.73, 't': 1643691600000, 'n': 55371}
...

When I call the DF to display it:

df.rename(columns={'t': 'date'}, inplace=True) # why can't I change this above?
df.drop(columns=['vw', 'n'], inplace=True)
df.rename(columns={'v': 'volume', 'o': 'open', 'c': 'close', 'h': 'high', 'l': 'low'}, inplace=True)
df['date'] = pd.to_datetime(df['date'],unit='ms')
df['date'] = df['date'].dt.date
df

It is only storing the last symbol in the loop:

date    volume  open    close   high    low ticker
0   2020-01-09  1048301.0   14.84   14.88   15.0400 14.7100 ZUO
1   2020-01-10  1143633.0   14.88   15.03   15.0800 14.7600 ZUO
2   2020-01-13  1609506.0   15.10   15.45   15.5650 14.9300 ZUO
3   2020-01-14  956361.0    15.50   15.34   15.6000 15.2100 ZUO
4   2020-01-15  1152240.0   15.55   15.50   15.9000 15.4600 ZUO
...

How do I append each bit of information from r.results to a single dataframe?

polygon.io jupyter-notebook pandas python
1 Answers

With the line below, you're overwriting the variable df with every loop in your for i in all_tickers for loop:

df = pd.DataFrame(r.results, columns=["t", "v", "vw", "o", "c", "h", "l", "n"])

It looks like r.results is a list of dictionaries. If so, it would make more sense to create a list of Pandas dataframes, which you can then combine into one at the end. Something like this:

with RESTClient(key) as client:
    from_ = "2020-01-09"
    to = "2021-01-10"
    df_list = []
    for i in all_tickers:
        ticker =  i['ticker']
        r = client.stocks_equities_aggregates(ticker, 1, "day", from_, to, unadjusted=False)

        print(f"Daily aggregates for {r.ticker} between {from_} and {to}.")

        try:
            df = pd.DataFrame(r.results, columns=["t", "v", "vw", "o", "c", "h", "l", "n"])
            df['ticker'] = ticker
            df_list.append(df)

        except:
            print('nothing')
    combined_df = pd.concat(df_list,ignore_index=True)

You can then make the final edits afterwards:

## Select just the columns you want
final_df = combined_df[['t','v','o','c','h','l','ticker']]
## Rename them
final_df.columns = ['date','volume','open','close','high','low','ticker']
## Format the date column
final_df['date'] = pd.to_datetime(final_df['date'],unit='ms').dt.date
user_88677210
Related