How to select row with minimum distance

user_7287543 0
Viewed 112

I am examining the NYC MVA data set. Out of the 1,697,572 records, I've determined that approximately 518,000 are missing ZIP code data:

Minimal Data Sample

   CRASH DATE CRASH TIME BOROUGH ZIP CODE  LATITUDE  LONGITUDE                      LOCATION
0  07/13/2019       4:10     NaN      NaN  40.69114  -73.80488    POINT (-73.80488 40.69114)
1  06/27/2019      21:30     NaN      NaN  40.58353  -73.98418  POINT (-73.984184 40.583534)
2  07/05/2019      19:40     NaN      NaN  40.61017  -73.92289   POINT (-73.92289 40.610165)
3  06/30/2019       2:30     NaN      NaN  40.70916  -73.84107   POINT (-73.84107 40.709156)
4  07/18/2019      17:50     NaN      NaN  40.74617  -73.82473   POINT (-73.82473 40.746174)

np.sort(df['ZIP CODE'].astype('str').unique())[:10]
[Out]: array(['     ', '10000', '10000.0', '10001', '10001.0', '10002',
       '10002.0', '10003', '10003.0', '10004'], dtype=object)

empty = np.sort(df['ZIP CODE'].astype('str').unique())[0]
empty_cells = df['ZIP CODE'].isin([empty, np.nan])
len(empty_cells[empty_cells==True])
[Out]: 518797

Considering the size of the data set, I know that I can probably correct a lot of these by relying on the Euclidean distance of the closest MVA with ZIP data based on the LONGITUDE and LATITUDE features.

For starters, I tried to create a new column that would simply find the lowest distance between two longitudinal points using the following:

apply(lambda x: df.loc[min(abs(df['LONGITUDE'] - df.loc[x, 'LONGITUDE'])),:])

But with this code, my computer's fans nearly send me airborne. I shut it down before my computer did something bad.

I know there's a way to create a column that will allow me to select the minimum Euclidean distance, but I'm just not sure how to write that initial selection code.

pandas apply lambda python
1 Answers

I don't think that it'll be the best solution for getting ZIP codes, but I use BallTree to get nearest neighbors by distance. Something like this:

from sklearn.neighbors import BallTree

tree = BallTree(df_mva[['Latitude', 'Longitude']].values, leaf_size=2)

df['distance_nearest'], df['id_nearest'] = tree.query(
    df[['latitude', 'longitude']].values, # The input array for the query
    k=1, # The number of nearest neighbors
)

df_merged = pd.merge(df,
                df_mva,
                left_on='id_nearest',
                right_index=True)

You can adapt it to your code, probably with creating 2 dataframes with df being the one without the ZIP codes and df_mva being the one with ZIP codes.

user_32147970
Related