Writing multiple requests to one Csv with Python

Question

I'm having an issue combining a couple scripts together. I've tried placing writer.writerow in a couple different places but I've run out of ideas. As you can see from the output it loops through the last loop and repeats the first two loops until the 3rd is complete than moves onto the second. Thank you for taking a look!

import csv
import requests
import os

outfile = open("NHL_Home_Skater.csv","a",newline='')
writer = csv.writer(outfile)
writer.writerow(["Date","Player","PlayerId","Pos","TM","Opp","GameId","GP","G","A","Points","Shots","Spctg","PPG","PPP","SHG","SHP","TOI","Shifts","PIM","Player","GameId","BS","Give Aways","Take Aways","Missed Shots","Player","GameId","EvTOI","PpTOI","ShTOI"])

req = requests.get('http://www.nhl.com/stats/rest/skaters?isAggregate=false&reportType=basic&isGame=true&reportName=skatersummary&sort=[{%22property%22:%22playerName%22,%22direction%22:%22ASC%22}]&factCayenneExp=gamesPlayed%3E=1&cayenneExp=gameDate%3E=%222017-10-04%22%20and%20gameDate%3C=%222017-12-31%22%20and%20gameTypeId=2%20and%20gameLocationCode=%22H%22') 
data = req.json()['data']
for item in data:
    Player = item['playerName']
    date = item['gameDate']
    Pos = item['playerPositionCode']
    TM = item['teamAbbrev']
    Opp = item['opponentTeamAbbrev']
    GameId = item['gameId']
    GP = item['gamesPlayed']
    G = item['goals']
    A = item['assists']
    Shots = item['shots']
    PIM = item['penaltyMinutes']
    PlayerId = item['playerId']
    Points = item['points']
    PPG = item['ppGoals']
    PPP = item['ppPoints']
    SHG = item['shGoals']
    SHP = item['shPoints']
    Shifts = item['shiftsPerGame']
    Spctg = item['shootingPctg']
    TOI = item['timeOnIcePerGame']

    writer.writerow([date,Player,PlayerId,Pos,TM,Opp,GameId,GP,G,A,Points,Shots,Spctg,PPG,PPP,SHG,SHP,TOI,Shifts,PIM])

    req2 = requests.get('http://www.nhl.com/stats/rest/skaters?isAggregate=false&reportType=basic&isGame=true&reportName=realtime&sort=[{%22property%22:%22playerName%22,%22direction%22:%22ASC%22}]&factCayenneExp=gamesPlayed%3E=1&cayenneExp=gameDate%3E=%222017-10-04%22%20and%20gameDate%3C=%222017-12-31%22%20and%20gameTypeId=2%20and%20gameLocationCode=%22H%22') 
    data2 = req2.json()['data']
    for item in data2:
        Player2 = item['playerName']
        GameId2 = item['gameId']
        BS = item['blockedShots']
        GiveAways = item['giveaways']
        TakeAways = item['takeaways']
        MissedShots = item['missedShots']

        writer.writerow([Player,GameId,BS,GiveAways,TakeAways,MissedShots])

        req3 = requests.get('http://www.nhl.com/stats/rest/skaters?isAggregate=false&reportType=basic&isGame=true&reportName=timeonice&sort=[{%22property%22:%22playerName%22,%22direction%22:%22ASC%22}]&factCayenneExp=gamesPlayed%3E=1&cayenneExp=gameDate%3E=%222017-10-04%22%20and%20gameDate%3C=%222017-12-31%22%20and%20gameTypeId=2%20and%20gameLocationCode=%22H%22') 
        data3 = req3.json()['data']
        for item in data3:
            Player3 = item['playerName']
            GameId3 = item['gameId']
            EvTOI = item['evTimeOnIce']
            PpTOI = item['ppTimeOnIce']
            ShTOI = item['shTimeOnIce']

            writer.writerow([Player, GameId, EvTOI, PpTOI, ShTOI])
outfile.close()

Ouput:

2017-10-28T23:00:00Z A.J. Greer 8478421 L COL CHI 2017020165 1 0 0 0 0 0.0 0 0 0 0 373.0 10.0 2 A.J. Greer 2017020165 0 0 1 0 A.J. Greer 2017020165 373 0 0
2017-10-28T23:00:00Z A.J. Greer 8478421 L COL CHI 2017020165 1 0 0 0 0 0.0 0 0 0 0 373.0 10.0 2 A.J. Greer 2017020165 0 0 1 0 A.J. Greer 2017020194 486 0 0
2017-10-28T23:00:00Z A.J. Greer 8478421 L COL CHI 2017020165 1 0 0 0 0 0.0 0 0 0 0 373.0 10.0 2 A.J. Greer 2017020165 0 0 1 0 A.J. Greer 2017020134 498 4 0
2017-10-28T23:00:00Z A.J. Greer 8478421 L COL CHI 2017020165 1 0 0 0 0 0.0 0 0 0 0 373.0 10.0 2 A.J. Greer 2017020165 0 0 1 0 Aaron Ekblad 2017020190 1035 88 126

New Output:

Date    Player  PlayerId    Pos TM  Opp GameId  GP  G   A   Points  Shots   Spctg   PPG PPP SHG SHP TOI Shifts  PIM

2017-11-02T23:00:00Z    A.J. Greer  8478421 L   COL CAR 2017020194  1   0   1   1   0   0   0   0   0   0   486 12  0

2017-10-28T23:00:00Z    A.J. Greer  8478421 L   COL CHI 2017020165  1   0   0   0   0   0   0   0   0   0   373 10  2

2017-10-24T23:00:00Z    A.J. Greer  8478421 L   COL DAL 2017020134  1   0   0   0   2   0   0   0   0   0   502 13  0

Hai Vu · Accepted Answer · 2017-12-20 13:50:58Z

1

This problem is not hard because of the complexity, but because the amount of data is large (nearly 10,000 rows of data), plus each rows has about 30+ columns. After reading your code, I believe I understand what you want for your output: a combination the 3 sets of data. Here is my attempt:

import csv
import os
import requests

outfile = open("NHL_Home_Skater.csv", 'a', newline='')
writer = csv.writer(outfile)
header=[
    "Date","Player","PlayerId","Pos","TM",
    "Opp","GameId","GP","G","A",
    "Points","Shots","Spctg","PPG","PPP",
    "SHG","SHP","TOI", "Shifts", "PIM",

    "Player2","GameId","BS","Give Aways","Take Aways", "Missed Shots",
    "Player3","GameId","EvTOI","PpTOI","ShTOI"]
writer.writerow(header)

req = requests.get('http://www.nhl.com/stats/rest/skaters?isAggregate=false&reportType=basic&isGame=true&reportName=skatersummary&sort=[{%22property%22:%22playerName%22,%22direction%22:%22ASC%22}]&factCayenneExp=gamesPlayed%3E=1&cayenneExp=gameDate%3E=%222017-10-04%22%20and%20gameDate%3C=%222017-12-31%22%20and%20gameTypeId=2%20and%20gameLocationCode=%22H%22')
data = req.json()['data']

req2 = requests.get('http://www.nhl.com/stats/rest/skaters?isAggregate=false&reportType=basic&isGame=true&reportName=realtime&sort=[{%22property%22:%22playerName%22,%22direction%22:%22ASC%22}]&factCayenneExp=gamesPlayed%3E=1&cayenneExp=gameDate%3E=%222017-10-04%22%20and%20gameDate%3C=%222017-12-31%22%20and%20gameTypeId=2%20and%20gameLocationCode=%22H%22')
data2 = req2.json()['data']

req3 = requests.get('http://www.nhl.com/stats/rest/skaters?isAggregate=false&reportType=basic&isGame=true&reportName=timeonice&sort=[{%22property%22:%22playerName%22,%22direction%22:%22ASC%22}]&factCayenneExp=gamesPlayed%3E=1&cayenneExp=gameDate%3E=%222017-10-04%22%20and%20gameDate%3C=%222017-12-31%22%20and%20gameTypeId=2%20and%20gameLocationCode=%22H%22')
data3 = req3.json()['data']

for item, item2, item3 in zip(data, data2, data3):
    row = (
        item['gameDate'],
        item['playerName'],
        item['playerId'],
        item['playerPositionCode'],
        item['teamAbbrev'],
        item['opponentTeamAbbrev'],
        item['gameId'],
        item['gamesPlayed'],
        item['goals'],
        item['assists'],
        item['points'],
        item['shots'],
        item['shootingPctg'],
        item['ppGoals'],
        item['ppPoints'],
        item['shGoals'],
        item['shPoints'],
        item['timeOnIcePerGame'],
        item['shiftsPerGame'],
        item['penaltyMinutes'],

        item2['playerName'],
        item2['gameId'],
        item2['blockedShots'],
        item2['giveaways'],
        item2['takeaways'],
        item2['missedShots'],

        item3['playerName'],
        item3['gameId'],
        item3['evTimeOnIce'],
        item3['ppTimeOnIce'],
        item3['shTimeOnIce'],
        )
    writer.writerow(row)

Notes

This solution makes only 3 requests as opposed to tens of millions requests
Based on your code, I assume that the rows in the 3 data sets are of the same order, meaning they refer to the same player ID and game ID.
For each row in the 3 data sets, I constructed a row with 31 columns for output and write it out just once per loop.
There is only 1 loop, not 3 nested one.

edited Dec 20, 2017 at 13:50

answered Dec 20, 2017 at 6:54

Hai Vu

41.4k16 gold badges75 silver badges106 bronze badges

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

4 Comments

Michael T Johnson Over a year ago

Thank you for taking the time to look over this. Everything looks great except the blank row in between each data row. I edited my New Ouput above and I was wondering if there is a way to remove those empty rows?

Hai Vu Over a year ago

Now I understand why you have newline='' in the open() statement. Put it back in and you are set.

Michael T Johnson Over a year ago

What does the zip mean in for item in zip()

Hai Vu Over a year ago

Sorry for the late reply, but here is the documentation for the zip function, it has example as well. docs.python.org/2/library/functions.html#zip

Collectives™ on Stack Overflow

Writing multiple requests to one Csv with Python

1 Answer 1

Notes

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Notes

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related