CSV file and loop list of URL in Python

Question

I've been trying to loop a CSV file, a list of URL, with this code, to scrape and store data in Excel. With one URL I could do it, but cant seem to find a way to do that with a list of URL (stock market tickers). This is my code:

import requests
import json
import csv
import pandas as pd

Urls = open('AcoesURLJsonCompleta.csv')
for row in Urls:
    obj_id = row.strip().split(',')

headers = {'user-agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/80.0.3987.149 Safari/537.36'}
jsonData = requests.get(row, headers=headers).json()
data = {
    'Ticker': [],
    'Beta': [],
    'DY': [],
    'VOL': [],
    'P/L': [],
    'Cresc5A': [],
    'LPA': [],
    'VPA': [],
    'Ultimo': []
  }

ticker = jsonData['ric']
beta = jsonData['beta']
DY = jsonData['current_dividend_yield_ttm']
VOL = jsonData['share_volume_3m']
PL = jsonData['pe_normalized_annual']
cresc5a = jsonData['eps_growth_5y']
LPA = jsonData['eps_normalized_annual']
VPA = jsonData['book_value_share_quarterly']
Ultimo = jsonData['last']

data['Ticker'].append(ticker)
data['Beta'].append(beta)
data['DY'].append(DY)
data['VOL'].append(VOL)
data['P/L'].append(PL)
data['Cresc5A'].append(cresc5a)
data['LPA'].append(LPA)
data['VPA'].append(VPA)
data['Ultimo'].append(Ultimo)

table = pd.DataFrame(data, columns=['Ticker', 'Beta', 'DY', 'VOL', 'P/L', 'Cresc5A', 'LPA', 'VPA', 'Ultimo'])
table.index = table.index + 1
table.to_csv('CompleteData.csv', sep=',', encoding='utf-8', index=False)
print(table)

The output is always a KeyError:with those jsonData, as KeyError: 'beta' for example. How to fix this?

0buz · Accepted Answer · 2020-05-01 18:36:49Z

Assuming your urls are valid and you don't have other validation errors (like KeyError), you need to loop through all of them and build a dataframe for each. Then append the dataframe to the csv file, with a structure such as:

for row in Urls:
    headers = {
        'user-agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/80.0.3987.149 Safari/537.36'}
    jsonData = requests.get(row, headers=headers).json()
    data = {
        'Ticker': [],
        'Beta': [],
        'DY': [],
        'VOL': [],
        'P/L': [],
        'Cresc5A': [],
        'LPA': [],
        'VPA': [],
        'Ultimo': []
    }
    ticker = jsonData['ric']
    beta = jsonData['beta']
    DY = jsonData['current_dividend_yield_ttm']
    VOL = jsonData['share_volume_3m']
    PL = jsonData['pe_normalized_annual']
    cresc5a = jsonData['eps_growth_5y']
    LPA = jsonData['eps_normalized_annual']
    VPA = jsonData['book_value_share_quarterly']
    Ultimo = jsonData['last']

    data['Ticker'].append(ticker)
    data['Beta'].append(beta)
    data['DY'].append(DY)
    data['VOL'].append(VOL)
    data['P/L'].append(PL)
    data['Cresc5A'].append(cresc5a)
    data['LPA'].append(LPA)
    data['VPA'].append(VPA)
    data['Ultimo'].append(Ultimo)
    table = pd.DataFrame(data, columns=['Ticker', 'Beta', 'DY', 'VOL', 'P/L', 'Cresc5A', 'LPA', 'VPA', 'Ultimo'])
    with open("append_to_csv.csv", 'a') as f:
        table.to_csv(f, mode='a', header=not f.tell(), index=False)

Georgi Ganchev · Accepted Answer · 2020-05-01 00:50:42Z

0

Seems to me you're using beta instead of Beta. Just fix the capital letter.

answered May 1, 2020 at 0:50

Georgi Ganchev

1165 bronze badges

1 Comment

Dlintz Over a year ago

Thank you for your answer. I did change that, and the output was the same. Any other thoughts?

Collectives™ on Stack Overflow

CSV file and loop list of URL in Python

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related