How to print json data from url to excel?

Question

import urllib 
import json
import re
import csv
from bs4 import BeautifulSoup

game_code = open("/Users//Desktop/PYTHON/gc.txt").read()

game_code = game_code.split("\r")


for gc in game_code:

    htmltext =urllib.urlopen("http://cluster.leaguestat.com/feed/index.php?feed=gc&key=f109cf290fcf50d4&client_code=ohl&game_id="+gc+"&lang_code=en&fmt=json&tab=pxpverbose")

    soup= BeautifulSoup(htmltext, "html.parser")
    j= json.loads(soup.text)
    summary = ['GC'],['Pxpverbose']
    for event in summary:
        print gc, ["event"]

I can not seem to access the lib to print the proper headers and row. I ultimately want to export specific rows to csv. I downloaded python 2 days ago, so i am very new. I needed this one data set for a project. Any advice or direction would be greatly appreciated.

Here are a few game codes if anyone wanted to take a look. Thanks

21127,20788,20922,20752,21094,21196,21295,21159,21128,20854,21057

Daniel · Accepted Answer · 2016-05-13 04:08:18Z

1

Here are a few thoughts:

I'd like to point out the excellent requests as an alternative to urllib for all your HTTP needs in Python (you may need to pip install requests).
requests comes with a built-in json decoder (you don't need BeautifulSoup).
In fact, you have already imported a great module (csv) to print headers and rows of data. You can also use this module to write the data to a file.
Your data is returned as a dictionary (dict) in Python, a data structure indexed by keys. You can access the values (I think this is what you mean by "specific rows") in your data with these keys.

One of many possible ways to accomplish what you want:

import requests
import csv

game_code = open("/Users//Desktop/PYTHON/gc.txt").read()
game_code = game_code.split("\r")

for gc in game_code:
    r = requests.get("http://cluster.leaguestat.com/feed/index.php?feed=gc&key=f109cf290fcf50d4&client_code=ohl&game_id="+gc+"&lang_code=en&fmt=json&tab=pxpverbose")
    data = r.json()

    with open("my_data.csv", "a") as csvfile:
        wr = csv.writer(csvfile,delimiter=',')
        for summary in data["GC"]["Pxpverbose"]:
            wr.writerow([gc,summary["event"]])
            # add keys to write additional values;
            # e.g. summary["some-key"].  Example: 
            # wr.writerow([gc,summary["event"],summary["id"]])

edited May 13, 2016 at 4:08

answered May 12, 2016 at 18:29

Daniel

2,4995 gold badges23 silver badges40 bronze badges

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

4 Comments

denn9268 Over a year ago

Thank you! @Daniel What would be the best way to add on additional keys?

Daniel Over a year ago

My pleasure! Added comments to my answer; happy to clarify if it doesn't make sense.

Daniel Over a year ago

That means that you are trying to reference a key that doesn't exist in each "summary" dictionary. Without going down a rabbit hole here in the comments, you might look at error handling: docs.python.org/2/tutorial/errors.html#handling-exceptions. A little advanced if you're just starting out in Python, but that's the right direction. The idea would be:

try: wr.writerow([gc,summary["event"],summary["some-key-that-doesn't-exist"]]) except KeyError: #do something with the error

Daniel Over a year ago

My pleasure! Since you are new to Stackoverflow, the best way to say "thanks" is to accept and up-vote the answer: stackoverflow.com/help/someone-answers. Good luck!

Ownaginatious · Accepted Answer · 2016-05-09 06:07:52Z

0

You don't need beautiful soup for this; the data can be read directly from the URL into JSON format.

import urllib, json
response = urllib.urlopen("http://cluster.leaguestat.com/feed/index.php?feed=gc&key=f109cf290fcf50d4&client_code=ohl&game_id=" + gc +"&lang_code=en&fmt=json&tab=pxpverbose")
data = json.loads(response.read())

At this point, data is the parsed JSON of your web page.

Excel can read csv files, so easiest route would be exporting the data you want into a CSV file using this library.

This should be enough to get you started. Modify fieldnames to include specific event details in the columns of the csv file.

import csv

with open('my_games.csv', 'w') as csvfile:
    fieldnames = ['event', 'id']
    writer = csv.DictWriter(csvfile, fieldnames=fieldnames,
                            extrasaction='ignore')
    writer.writeheader()
    for event in data['GC']['Pxpverbose']:
        writer.writerow(event)

edited May 9, 2016 at 6:07

answered May 8, 2016 at 23:30

Ownaginatious

7974 silver badges14 bronze badges

3 Comments

denn9268 Over a year ago

Thank you for the help. Still prints out as a blank .csv with just header names. It seems to be having trouble locating the dictionary keys of the actual data I want.

Ownaginatious Over a year ago

@denn9268 whoops, looked like the code had a couple bugs. Should give you a CSV file containing a columns of event and ID now.

denn9268 Over a year ago

I am getting error "return self.writer.writerow(self._dict_to_list(rowdict))"

Collectives™ on Stack Overflow

How to print json data from url to excel?

2 Answers 2

4 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related