How to correctly use .apply(lambda x:) on dataframe column

Question

The issue I'm having is an error Im receiving from df_modified['lat'] = df.coordinates.apply(lambda x: x[0]) It returns error TypeError: 'float' object is not subscriptable. Since "coordinates" is already a list (see JSON SNIPPET) I was trying to use lambda to pull out the element [0] and place it in a new column named "lat" and place element [1] in a new column named "long". Any help with this problem would be appreciated. Thank you!

import pandas as pd
import json
import requests
from pandas.io.json import json_normalize

# READS IN JSON
source = requests.get('www.url.com')
data = json.loads(source.text)

# Flattens the JSON data since it had nested dictionaries
df = pd.io.json.json_normalize(data)

# Renamed "lat_long.coordinates" because the "." was confusing .apply() function
df.rename(columns={'lat_long.coordinates': 'coordinates'}, inplace=True)

# Created a new data frame with seleted columns
df_modified = df.loc[:, ['county_name', 'arrests', 'incident_count']]

# Attempt to create a new column "lat" and "long" and place the elemnts accordingly  i.e. [-75.802503,  41.820569]
df_modified['lat'] = df.coordinates.apply(lambda x: x[0])
df_modified['long'] = df.coordinates.apply(lambda x: x[1])

print(df_modified.head(30))

SAMPLE JSON SNIPPET

{
    ":@computed_region_amqz_jbr4": "587",
    ":@computed_region_d3gw_znnf": "18",
    ":@computed_region_nmsq_hqvv": "55",
    ":@computed_region_r6rf_p9et": "36",
    ":@computed_region_rayf_jjgk": "295",
    "arrests": "1",
    "county_code": "44",
    "county_code_text": "44",
    "county_name": "Mifflin",
    "fips_county_code": "087",
    "fips_state_code": "42",
    "incident_count": "1",
    "lat_long": {
      "type": "Point",
      "coordinates": [
        -77.620031,
        40.612749
      ]
    }

I think the column needs to be passed to apply function `df.apply( function(x): x['lat'][0] )`. like [here](https://stackoverflow.com/questions/37428218/how-to-properly-apply-a-lambda-function-into-a-pandas-data-frame-column) — Aureliano Guedes, Feb 13 '19 at 20:54
Last time I tried to work with json normalized DFs I thought my head was going to explode. — roganjosh, Feb 13 '19 at 21:00

score 0 · Accepted Answer · answered Feb 13 '19 at 20:57

0

You can do it the other way around. Take the lat and long prior to filtering the columns.

import pandas as pd

import json

with open('sample.json') as infile:
    data = json.load(infile)

df = pd.io.json.json_normalize(data)

df.rename(columns={'lat_long.coordinates': 'coordinates'}, inplace=True)
df['lat'] = df['coordinates'].apply(lambda x: x[0])
df['long'] = df['coordinates'].apply(lambda x: x[1])

# Created a new data frame with seleted columns
df_modified = df.loc[:, ['county_name', 'arrests', 'incident_count', 'lat', 
                         'long']]

answered Feb 13 '19 at 20:57

roganjosh

12,594
4
29
46

still receiving `TypeError: 'float' object is not subscriptable` – prime90 Feb 13 '19 at 21:48
@prime90 I used your exact json snippet, just added an extra `}` to make it valid.. Are you sure you're not mixing up `df` and `df_modified`? This code works fine for me so I can't do much if I can't reproduce :/ – roganjosh Feb 13 '19 at 21:51
So the problem was I had a missing data in coordinates! I should have checked beforehand. Your solution worked perfectly. Thank you. – prime90 Feb 14 '19 at 01:51

How to correctly use .apply(lambda x:) on dataframe column

1 Answers1