CODEWITHSUNDEEP
pythonpandasdataframeweb-scraping
NoobAtPython
NoobAtPython

Reputation: 25

Scraped json data want to output CSV file

So I have this data that I scraped

[
    {
        "id": 4321069,
        "points": 52535,
        "name": "Dennis",
        "avatar": "",
        "leaderboardPosition": 1,
        "rank": ""
    },
    {
        "id": 9281450,
        "points": 40930,
        "name": "Dinh",
        "avatar": "https://uploads-us-west-2.insided.com/koodo-en/icon/90x90/aeaf8cc1-65b2-4d07-a838-1f078bbd2b60.png",
        "leaderboardPosition": 2,
        "rank": ""
    },
    {
        "id": 1087209,
        "points": 26053,
        "name": "Sophia",
        "avatar": "https://uploads-us-west-2.insided.com/koodo-en/icon/90x90/c3e9ffb1-df72-46e8-9cd5-c66a000e98fa.png",
        "leaderboardPosition": 3,
        "rank": ""

And so on... Big leaderboard of 20 ppl

Scraped with this code

import json
import requests
import pandas as pd

url_all_time = 'https://community.koodomobile.com/widget/pointsLeaderboard?period=allTime&maxResults=20&excludeRoles='

# print for all time:

data = requests.get(url_all_time).json()
# for item in data:

# uncomment this to print all data:
# print(json.dumps(data, indent=4))

for item in data:
    print(item['name'], item['points'])

And I want to be able to create a table that ressembles this Grey = number of points

Every time I scrape data, I want it to update the table with the number of points with a new data stamped as the header. So basically what I was thinking is that my index = usernames and the header = date. The problem is, I can't even get to make a csv file with that NAME/POINTS columns.

The only thing I have succeeded doing so far is writing ALL the data into a csv file. I haven't been able to pinpoint the data I want like in the print command.

EDIT : After reading what @Shijith posted I succeeded at transferring data to .csv but with what I have in mind (add more data as time flies), I was asking myself if I should do a code with an Index or without.

WITH

import pandas as pd

url_all_time = 'https://community.koodomobile.com/widget/pointsLeaderboard?period=allTime&maxResults=20&excludeRoles='
data = pd.read_json(url_all_time)
table = pd.DataFrame.from_records(data, index=['name'], columns=['points','name'])

table.to_csv('products.csv', index=True, encoding='utf-8')

WITHOUT

import pandas as pd

url_all_time = 'https://community.koodomobile.com/widget/pointsLeaderboard?period=allTime&maxResults=20&excludeRoles='
data = pd.read_json(url_all_time)
table = pd.DataFrame.from_records(data, columns=['points','name'])

table.to_csv('products.csv', index=False, encoding='utf-8')

Upvotes: 0

Views: 63

Answers (1)

Chris
Chris

Reputation: 16147

Have you tried just reading the json directly into a pandas dataframe? From here it should be pretty easy to transform it like you want. You could add a column for today's date and pivot it.

import pandas as pd
url_all_time = 'https://community.koodomobile.com/widget/pointsLeaderboard?period=allTime&maxResults=20&excludeRoles='
df = pd.read_json(url_all_time)

data['date'] = pd.Timestamp.today().strftime('%m-%d-%Y')
data.pivot(index='name',columns='date',values='points')

Upvotes: 1

Related Questions