Loop to append multiple lists into dataframe python

Question

I have to requests.get() two urls from yahoo utilizing YQL that returns a json object. I'm getting back a json objects that I store into a list(). Then I'm looping to parse the data and creating a dic to then create a pandas data frame. Happened that only one list is getting appended to the data frame. Seems like in the last iteration, the 2nd list overwrites the first list. At this point, I can't figure out how to iterate on the list to append() both elements of the list. Here is my code...

import requests
import pandas as pd


urls = ['https://query.yahooapis.com/v1/public/yql?q=select%20*%20from%20yahoo.finance.historicaldata%20where%20symbol%20in%20(%22DIA%22%2C%22SPY%22%2C%22IWN%22)%20and%20startDate%20%3D%20%222015-01-01%22%20and%20endDate%20%3D%20%222015-10-31%22&format=json&diagnostics=true&env=store%3A%2F%2Fdatatables.org%2Falltableswithkeys&callback=',
        'https://query.yahooapis.com/v1/public/yql?q=select%20*%20from%20yahoo.finance.historicaldata%20where%20symbol%20in%20(%22DIA%22%2C%22SPY%22%2C%22IWN%22)%20and%20startDate%20%3D%20%222015-11-01%22%20and%20endDate%20%3D%20%222016-08-31%22&format=json&diagnostics=true&env=store%3A%2F%2Fdatatables.org%2Falltableswithkeys&callback=']


for url in urls:
    data = requests.get(url)
    data_json = data.json()

    quote_list = []
    for quote in data_json['query']['results']['quote']:
        quote_dic = {'symbol': quote['Symbol'],
                 'date': quote['Date'],
                 'volume': quote['Volume'],
                 'low': quote['Low'],
                 'high': quote['High'],
                 'open': quote['Open'],
                 'close': quote['Close'],
                 'adj_close': quote['Adj_Close']}

        quote_list.append(quote_dic)

    quote_df = pd.DataFrame(quote_list)

quote_df.to_csv('stocks.csv')

I need to be able to append the entire list() into the data frame. What would be the fix for this code?

Why store the json in a list? json is essentially a dictionary natively and can be loaded into a dictionary with no parsing or translation. — zenlc2000
– zenlc2000, Commented Nov 8, 2016 at 20:42
I'm not too familiar with Pandas dataframes however, according to stackoverflow.com/questions/31695108/… it looks pretty simple. — zenlc2000
– zenlc2000, Commented Nov 8, 2016 at 20:50

Julien Marrec · Accepted Answer · 2016-11-08 20:52:42Z

4

Just create a list of dataframes, and concat them at the end of the loop:

df_list = []
for url in urls:
    data = requests.get(url)
    data_json = data.json()

    df = pd.DataFrame(data_json['query']['results']['quote'])
    df_list.append(df)

quote_df = pd.concat(df_list)
quote_df.to_csv('stocks.csv')

answered Nov 8, 2016 at 20:52

Julien Marrec

11.9k5 gold badges51 silver badges67 bronze badges

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

Comments

user7075507 · Accepted Answer · 2016-11-10 04:21:54Z

1

How about this solution?

import urllib
import re
import json

symbolslist = open("C:/Users/your_path_here/Desktop/stock_symbols.txt").read()
symbolslist = symbolslist.split("\n")

for symbol in symbolslist:
    myfile = open("C:/Users/your_path_here/Desktop/" +symbol +".txt", "w+")
    myfile.close()

    htmltext = urllib.urlopen("http://www.bloomberg.com/markets/chart/data/1D/"+ symbol+ ":US")
    data = json.load(htmltext)
    datapoints = data["data_values"]

    myfile = open("C:/Users/rshuell001/Desktop/symbols/" +symbol +".txt", "a")
    for point in datapoints:
        myfile.write(str(symbol+","+str(point[0])+","+str(point[1])+"\n"))
    myfile.close()

In this file "C:/Users/your_path_here/Desktop/symbols/amex.txt" you have the following tickers

ibm
sbux
msft

answered Nov 10, 2016 at 4:21

user7075507

1 Comment

redeemefy Over a year ago

The above solution worked well since I'm connecting to yahoo api and inserting to postgresql without saving data to hard drive. I'm having an issue though,which is in this post

Collectives™ on Stack Overflow

Loop to append multiple lists into dataframe python

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related