Create combined data frame from JSON using pandas

Question

I'm importing data from Alpha_Vantage in JSON format:

{
    "Meta Data": {
        "1. Information": "Daily Prices (open, high, low, close) and Volumes",
        "2. Symbol": "MSFT",
        "3. Last Refreshed": "2018-08-02",
        "4. Output Size": "Compact",
        "5. Time Zone": "US/Eastern"
    },
    "Time Series (Daily)": {
        "2018-08-02": {
            "1. open": "105.4000",
            "2. high": "108.0900",
            "3. low": "104.8400",
            "4. close": "107.5700",
            "5. volume": "26080662"
        },...

I want to extract different data from different tickers and combine them having the date as index and the "4. close" column of each ticker. So far this is what I have:

from alpha_vantage.timeseries import TimeSeries
from pprint import pprint

tickers = ['KHC', 'TSLA']
for t in range(len(tickers)):
  ts = TimeSeries(key='my_api_key', output_format='pandas')
   data, meta_data = ts.get_daily(symbol= tickers[t], 
                                    outputsize='compact')
  accu = data['4. close'].head()
  data_merged = data.merge(accu.to_frame(), how='left'\ 
                            , left_on='date' 
                            , right_index=True)

  pprint(data_merged.head)

At the moment there is a key error for 'date' in left_on, even though when printing a single ticker the key appears in the column. Tying another key just messes the data. Any idea? Also, how to print the ticker name at the top of every column?

'date' is probably the index, not a column. What does accu.to_frame().head() show? — DYZ
– DYZ, Commented Aug 3, 2018 at 2:31
@DYZ 'date' and '4. close' of every ticker, but separated since I'm not able to merge it. — Juan Medel
– Juan Medel, Commented Aug 3, 2018 at 2:44
So, data is your dataframe. In your code fragment, you attempt to merge it with its own head, which makes no sense. What you want is to merge the dataframes obtained at each loop iteration. — DYZ
– DYZ, Commented Aug 3, 2018 at 2:50

DYZ · Accepted Answer · 2018-08-03 03:00:35Z

1

You need to collect the obtained "4. close" Series into a dictionary and then build a DataFrame from the dictionary:

tickers = ['KHC', 'TSLA']
ts = TimeSeries(key='my_api_key', output_format='pandas')

closes_4 = {} # Start with an empty dictionary

for t in tickers:
    data, _ = ts.get_daily(symbol=t, outputsize='compact')
    closes_4[t] = data['4. close'] # Add a series to the dict

close_df = pd.DataFrame(closes_4)

edited Aug 3, 2018 at 3:00

answered Aug 3, 2018 at 2:52

DYZ

57.3k10 gold badges73 silver badges101 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Create combined data frame from JSON using pandas

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related