Accessing JSON elements with Python and Pandas

Question

I have a few questions about JSON in Pandas! I have gotten the data loaded into a dataframe and can search effectively with the query code below. Three questions I have:

How can I get the individual pieces of 'coord' (i.e. 'coord.lon')
How do I assign specific columns to variables in the code? (i.e. state = df.loc[df['name'] == city, ['state'])?
How can I add additional search terms? (i.e. df['name'] == city AND df['state'] == state)

Here is the code I have so far to pull the records I need:

query = df.loc[df['name'] == city, ['id', 'name', 'state', 'coord']]

Here is a sample of the JSON response I am trying to search:

{
    "id": 5074329,
    "name": "Oakland",
    "state": "NE",
    "country": "US",
    "coord": {
        "lon": -96.466972,
        "lat": 41.835831
    }
},
{
    "id": 5074472,
    "name": "Omaha",
    "state": "NE",
    "country": "US",
    "coord": {
        "lon": -95.93779,
        "lat": 41.25861
    }
}

As always, THANKS!!

edit: Here is a picture of a couple rows of the dataframe:

url = 'http://bulk.openweathermap.org/sample/city.list.json.gz'

df = pd.read_json(url)

Can you show the dataframe you are generating from the json file? — JMA
– JMA, Commented Jun 2, 2021 at 14:04
@JMA - Thanks for getting back to me. I have added a screenshot above. — Mike Zoucha
– Mike Zoucha, Commented Jun 2, 2021 at 14:19

Nk03 · Accepted Answer · 2021-06-02 14:36:44Z

If you have the dataframe like this:

        id     name state country                                  coord
0  5074329  Oakland    NE      US  {'lon': -96.466972, 'lat': 41.835831}
1  5074472    Omaha    NE      US    {'lon': -95.93779, 'lat': 41.25861}

To access lon you can use the str method:

df['lon'] = df.coord.str['lon'] 

#output
        id     name state country                                  coord  \
0  5074329  Oakland    NE      US  {'lon': -96.466972, 'lat': 41.835831}   
1  5074472    Omaha    NE      US    {'lon': -95.93779, 'lat': 41.25861}   

         lon  
0 -96.466972  
1 -95.937790

I guess you wanna do boolean indexing here:

city =  'Omaha'
state = df.loc[df['name'] == city]['state']

The above code will return the pandas series. If you want only the 1st value use iloc:

city =  'Omaha'
state = df.loc[df['name'] == city]['state'].iloc[0]

# output
'NE'

This is quite similar to the 2nd part:

city = 'Omaha'
state = 'NE'
suset_df = df.loc[(df['name'] == city) & (df['state'] == state)]

# OUTPUT
        id   name state country                                coord       lon
1  5074472  Omaha    NE      US  {'lon': -95.93779, 'lat': 41.25861} -95.93779

Collectives™ on Stack Overflow

Accessing JSON elements with Python and Pandas

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related