filling in null values of location in a dataframe

Question

data set in question

so, i have a dataframe with columns latitude and longitude that are float, along with that there are columns borough, on street name, cross street name, off street name which are a string containing a location. the null values are as follows

• BOROUGH 598045
• LATITUDE : 199705
• LONGITUDE : 199705
• ON STREET NAME : 394986
• CROSS STREET NAME : 725867
• OFF STREET NAME : 1656305

i want to fill in the null values of latitude and longitude. so i want to use GEOPANDAS to find the associated location's lat and long to fill in the missing values. i am having trouble coming up with the code.

essentially the code is supposed to

1. check the columns BOROUGH,ON STREET NAME,CROSS STREET NAME,OFF STREET NAME if all are null drop them
2. then check if the latitude and/or latitude are null (also select the instances where lat and long are 0.0)
3. then check the columns BOROUGH, ON STREET, CROSS STREET and OFF STREET (in this order) get their location
4. and fill in the latitude and location

i have tried

location_fill = location _group.loc[(location_group['LATITUDE'].isna()) | (location_group['LATITUDE'] == 0.0)]
to_fill.dropna(subset=['BOROUGH', 'ON STREET NAME', 'CROSS STREET NAME', 'OFF STREET NAME'], how='all')

but i have no idea how to implement the rest of the code

Answer 1

You can install geocoder to get coordinates. Try something like:

import pandas as pd
import geocoder  # pip install geocoder

# Read data (only first 1000 rows)
df = pd.read_csv('NYPD_Motor_Vehicle_Collisions.csv', low_memory=False, nrows=1000)

# Clean names
cols = ['ON STREET NAME', 'CROSS STREET NAME', 'OFF STREET NAME', 'BOROUGH']
for col in cols:
    df[col] = df[col].str.replace(r'\s+', ' ', regex=True).str.strip()

# Remove bad records
df = df.dropna(subset=cols, how='all')

# Find locations for these records
df1 = df[df['LATITUDE'].isna() | df['LATITUDE'].eq(0)]

# Determine what you want to keep to find coordinates
locs = df1[cols].bfill(axis=1).iloc[:, 0].rename('OSM')

# Find coordinates
coords = {}
for loc in locs.drop_duplicates():
    print(loc)
    g = geocoder.osm(f'{loc}, New York')
    if g:
        coords[loc] = {'LATITUDE': g.osm['y'],
                       'LONGITUDE': g.osm['x'],
                       'LOCATION': f"({g.osm['y']}, {g.osm['x']})"}

osm = pd.DataFrame.from_dict(coords, orient='index')

# Update missing values
out = (locs.reset_index().merge(osm, left_on='OSM', right_index=True)
           .set_index('index').drop(columns='OSM'))
df.loc[out.index, ['LATITUDE', 'LONGITUDE', 'LOCATION']] = out

Output:

>>> df
           DATE   TIME    BOROUGH  ZIP CODE   LATITUDE  LONGITUDE  ... UNIQUE KEY            VEHICLE TYPE CODE 1            VEHICLE TYPE CODE 2 VEHICLE TYPE CODE 3  VEHICLE TYPE CODE 4  VEHICLE TYPE CODE 5
0    08/04/2017   0:00     QUEENS   11436.0  40.666885 -73.790405  ...    3725017              PASSENGER VEHICLE              PASSENGER VEHICLE                 NaN                  NaN                  NaN
1    08/04/2017   0:00        NaN       NaN  40.719950 -74.008590  ...    3725047                  PICK-UP TRUCK  SPORT UTILITY / STATION WAGON                 NaN                  NaN                  NaN
2    08/04/2017   0:00        NaN       NaN  40.718666 -73.963500  ...    3725533  SPORT UTILITY / STATION WAGON              PASSENGER VEHICLE                 NaN                  NaN                  NaN
3    08/04/2017   0:00        NaN       NaN  40.754677 -73.975815  ...    3724870  SPORT UTILITY / STATION WAGON                           TAXI                 NaN                  NaN                  NaN
7    08/04/2017   0:00      BRONX   10460.0  40.836770 -73.885970  ...    3725174              PASSENGER VEHICLE                            NaN                 NaN                  NaN                  NaN
..          ...    ...        ...       ...        ...        ...  ...        ...                            ...                            ...                 ...                  ...                  ...
995  08/03/2017  20:15        NaN       NaN  40.741024 -73.978620  ...    3724516                           TAXI                     MOTORCYCLE                 NaN                  NaN                  NaN
996  08/03/2017  20:15        NaN       NaN  40.774315 -73.961611  ...    3724248  SPORT UTILITY / STATION WAGON              PASSENGER VEHICLE                 NaN                  NaN                  NaN
997  08/03/2017  20:20  MANHATTAN   10029.0  40.798622 -73.941630  ...    3724948              PASSENGER VEHICLE                            NaN                 NaN                  NaN                  NaN
998  08/03/2017  20:20     QUEENS   11379.0  40.725750 -73.877560  ...    3724699  SPORT UTILITY / STATION WAGON  SPORT UTILITY / STATION WAGON                 NaN                  NaN                  NaN
999  08/03/2017  20:30   BROOKLYN   11201.0  40.702534 -74.013985  ...    3724240              PASSENGER VEHICLE              PASSENGER VEHICLE                 NaN                  NaN                  NaN

[888 rows x 29 columns]

Before update:

>>> (df['LATITUDE'].isna() | df['LATITUDE'].eq(0)).sum()
70

After update:

>>> (df['LATITUDE'].isna() | df['LATITUDE'].eq(0)).sum()
9