Pull date and numbers from json and append them to pandas dataframe-CodePudding

I want to pull the date and the powerball numbers and append them to a pandas dataframe. I have made the columns, but I can't seem to get the data to the column. When I go to . But when I try to list the number I.E. ['8'] or ['9'] it doesn't append the data. I've been working on this for about 3 days. Thanks in advance.

###########
# MODULES #
###########
import json
import requests
import urllib
import pandas as pd

###########
# HISTORY #
###########

#We need to pull the data from the website.
#Then we need to organize the numbers based off of position.
#Basically it will be several lists of numbers
URL = "https://data.ny.gov/api/views/d6yy-54nr/rows"
r = requests.get(URL)
my_json = r.json()
#my_json_dumps = json.dumps(my_json, indent = 2)
#print(my_json_dumps)
df = pd.DataFrame(columns=["Date","Powerball Numbers","1","2","3","4","5","6","7"])#Create columns in df
for item in my_json['data']:
    df = pd.DataFrame(my_json['data'])
    l_date = df.iloc['8']#Trying to pull columns from json
    p_num =  (df.iloc['9'])#Trying to pull columns from json
    df = df.append({"Date": l_date,
                    "Powerball Numbers": p_num, 
                    },ignore_index=True)
    #test = item['id']
    print(l_date)

EDIT: This is what I am trying to get.

CodePudding user response：

Try with this:

Old code:

l_date = df.iloc['8'] #Trying to pull columns from json
p_num =  (df.iloc['9']) #Trying to pull columns from json

Change this lines with quotations df.iloc['8'] :

l_date = df.iloc[8] # without quotation
p_num =  (df.iloc[9]) # without quotation

Work fine. The result is:

0                       row-w3y7.4r9a-caat
1     00000000-0000-0000-3711-B495A26E6E8B
2                                        0
3                               1619710755
4                                     None
5                               1619710755
6                                     None
7                                      { }
8                      2020-10-24T00:00:00
9                        18 20 27 45 65 06
10                                       2
Name: 8, dtype: object
0                       row-w3y7.4r9a-caat
1     00000000-0000-0000-3711-B495A26E6E8B
2                                        0
3                               1619710755
4                                     None
5                               1619710755
6                                     None
7                                      { }
8                      2020-10-24T00:00:00
9                        18 20 27 45 65 06
10                                       2

I hope you find it useful.

CodePudding user response：

This is how I did it:

URL = "https://data.ny.gov/resource/d6yy-54nr.json"
r = requests.get(URL)
my_json = r.json()
for item in my_json:
    df = pd.DataFrame(my_json)
    l_date = item['draw_date']#Trying to pull columns from json
    p_num =  item['winning_numbers']#Trying to pull columns from json
    n_list = list(map(int, p_num.split())) #Convert powerball numbers into list
    n_1 = n_list[0]
    n_2 = n_list[1]
    n_3 = n_list[2]
    n_4 = n_list[3]
    n_5 = n_list[4]
    n_6 = n_list[5]
    df1 = pd.DataFrame(columns=["Date","Powerball Numbers", "1","2","3","4","5","6"])#Create columns in df
    df = df1.append({"Date": l_date,
                    "Powerball Numbers": p_num, 
                    "1": n_1,
                    "2": n_2,
                    "3": n_3,
                    "4": n_4,
                    "5": n_5,
                    "6": n_6,
                    },ignore_index=True)
    print(df)

Which produced