CODEWITHSUNDEEP
pythonjsonpandas
Anthony Richard
Anthony Richard

Reputation: 137

Pandas read JSON into Excel

I am trying to parse JSON data from an URL. I have fetched the data and parsed it into a dataframe. From the looks of it, I am missing a step.

Data Returns in JSON format in excel but my data frame returns two columns: entry number and JSON Text

import urllib.request
import json
import pandas
with urllib.request.urlopen("https://raw.githubusercontent.com/gavinr/usa-
mcdonalds-locations/master/mcdonalds.geojson") as url:
data = json.loads(url.read().decode())
print(data)
json_parsed = json.dumps(data)
print(json_parsed)

df=pandas.read_json(json_parsed)
writer = pandas.ExcelWriter('Mcdonaldsstorelist.xlsx')
df.to_excel(writer,'Sheet1')
writer.save()

Upvotes: 2

Views: 1899

Answers (1)

cs95
cs95

Reputation: 402363

I believe you can use json_normalize:

df = pd.io.json.json_normalize(data['features'])

df.head()

      geometry.coordinates geometry.type    properties.address  \
0  [-80.140924, 25.789141]         Point         1601 ALTON RD   
1  [-80.218683, 25.765501]         Point        1400 SW 8TH ST   
2  [-80.185108, 25.849872]         Point    8116 BISCAYNE BLVD   
3   [-80.37197, 25.550894]         Point    23351 SW 112TH AVE   
4   [-80.36734, 25.579132]         Point  10855 CARIBBEAN BLVD   

  properties.archCard properties.city properties.driveThru  \
0                   Y     MIAMI BEACH                    Y   
1                   Y           MIAMI                    Y   
2                   Y           MIAMI                    Y   
3                   N       HOMESTEAD                    Y   
4                   Y           MIAMI                    Y   

  properties.freeWifi properties.phone properties.playplace properties.state  \
0                   Y    (305)672-7055                    N               FL   
1                   Y    (305)285-0974                    Y               FL   
2                   Y    (305)756-0400                    N               FL   
3                   Y    (305)258-7837                    N               FL   
4                   Y    (305)254-3487                    Y               FL   

  properties.storeNumber properties.storeType             properties.storeUrl  \
0                  14372         FREESTANDING  http://www.mcflorida.com/14372   
1                   7408         FREESTANDING   http://www.mcflorida.com/7408   
2                  11511         FREESTANDING  http://www.mcflorida.com/11511   
3                  34014         FREESTANDING                             NaN   
4                  12215         FREESTANDING  http://www.mcflorida.com/12215   

  properties.zip     type  
0     33139-2420  Feature  
1          33135  Feature  
2          33138  Feature  
3          33032  Feature  
4          33157  Feature  

df.columns

Index(['geometry.coordinates', 'geometry.type', 'properties.address',
       'properties.archCard', 'properties.city', 'properties.driveThru',
       'properties.freeWifi', 'properties.phone', 'properties.playplace',
       'properties.state', 'properties.storeNumber', 'properties.storeType',
       'properties.storeUrl', 'properties.zip', 'type'],
      dtype='object')

Upvotes: 2

Related Questions