Python Pandas fill missing zipcode with values from another datafrane based on conditions

user_1905726 0
Viewed 1067

I have a dataset in which I add coordinates to cities based on zip-codes but several of these zip-codes are missing. Also, in some cases cities are missing, states are missing, or both are missing. For example:

     ca_df[['OWNER_CITY', 'OWNER_STATE', 'OWNER_ZIP']] 

     OWNER_CITY OWNER_STATE OWNER_ZIP
   495  MIAMI SHORE PA
   496      SEATTLE

However, a second dataset has city, state & the matching zip-codes. This one is complete without any missing values.

df_coord.head() 

    OWNER_ZIP   CITY    STATE    
 0  71937   Cove        AR   
 1  72044   Edgemont    AR   
 2  56171   Sherburn    MN

I want to fill in the missing zip-codes in the first dataframe if:

  1. Zip-code is empty
  2. City is present
  3. State is present

This is an all-or-nothing operations means, either all three criteria are met and the zip-code gets filled or nothing changes.

However, this is a fairly large dataset with > 50 million records so ideally I want to vectorize the operation by working column-wise.

Technically, that would fit np.where but as far as I know, np.where only takes of condition in the following format: 

df1['OWNER_ZIP'] = np.where(df["cond"] ==X, df_coord['OWNER_ZIP'], "")

How do I ensure I only fill missing zip-codes when all conditions are met?

pandas numpy python
4 Answers

Given ca_df:

    OWNER_CITY OWNER_STATE OWNER_ZIP
0  Miami Shore     Florida       111
1  Los Angeles  California       NaN
2      Houston         NaN       NaN

and df_coord:

  OWNER_ZIP         CITY       STATE
0       111  Miami Shore     Florida
1       222  Los Angeles  California
2       333      Houston       Texas

You can use pd.notna along with pd.DataFrame#index like this:

inferrable_zips_df = pd.notna(ca_df["OWNER_CITY"]) & pd.notna(ca_df["OWNER_STATE"])
is_inferrable_zip = ca_df.index.isin(df_coord[inferrable_zips_df].index)

ca_df.loc[is_inferrable_zip, "OWNER_ZIP"] = df_coord["OWNER_ZIP"]

with ca_df resulting as:

    OWNER_CITY OWNER_STATE OWNER_ZIP
0  Miami Shore     Florida       111
1  Los Angeles  California       222
2      Houston         NaN       NaN

I've changed the "" to np.nan, but if you still wish to use "" then you just need to change pd.notna(ca_df[...]) to ca_df[...] == "".

user_53192670

You can combine numpy.where statements to combine multiple rules. This should give you the array of row indices which abide to each of the three rules:

np.where(df["OWNER_ZIP"] == X) and np.where(df["CITY"] == Y) and np.where(df["STATE"] == Z)
user_77976460

Use:

print (df_coord)
   OWNER_ZIP         CITY STATE
0      71937         Cove    AR
1      72044     Edgemont    AR
2      56171     Sherburn    MN
3        123  MIAMI SHORE    PA
4        789      SEATTLE    AA

print (ca_df)
  OWNER_ZIP   OWNER_CITY OWNER_STATE
0       NaN          NaN         NaN
1     72044     Edgemont          AR
2     56171          NaN          MN
3       NaN  MIAMI SHORE          PA
4       NaN      SEATTLE         NaN

First is necessary test if same dtypes in columns matching:

#or convert ca_df['OWNER_ZIP'] to integers
df_coord['OWNER_ZIP'] = df_coord['OWNER_ZIP'].astype(str)

print (df_coord.dtypes)
OWNER_ZIP    object
CITY         object
STATE        object
dtype: object

print (ca_df.dtypes)

OWNER_ZIP      object
OWNER_CITY     object
OWNER_STATE    object
dtype: object

Then filter for each combinations of columns - missing and non missing values and add new data by merge, then convert index to same like filtered data and assign back:

mask1 = ca_df['OWNER_CITY'].notna() & ca_df['OWNER_STATE'].notna()  & ca_df['OWNER_ZIP'].isna()
df1 = ca_df[mask1].drop('OWNER_ZIP', axis=1).merge(df_coord.rename(columns={'CITY':'OWNER_CITY','STATE':'OWNER_STATE'})).set_index(ca_df.index[mask1])
ca_df.loc[mask1, ['OWNER_ZIP','OWNER_CITY','OWNER_STATE']] = df1

mask2 = ca_df['OWNER_CITY'].notna() & ca_df['OWNER_STATE'].isna()  & ca_df['OWNER_ZIP'].isna()
df2 = ca_df[mask2].drop(['OWNER_ZIP','OWNER_STATE'], axis=1).merge(df_coord.rename(columns={'CITY':'OWNER_CITY','STATE':'OWNER_STATE'})).set_index(ca_df.index[mask2])
ca_df.loc[mask2, ['OWNER_ZIP','OWNER_CITY','OWNER_STATE']] = df2

mask3 = ca_df['OWNER_CITY'].isna() & ca_df['OWNER_STATE'].notna()  & ca_df['OWNER_ZIP'].notna()
df3 = ca_df[mask3].drop(['OWNER_CITY'], axis=1).merge(df_coord.rename(columns={'CITY':'OWNER_CITY','STATE':'OWNER_STATE'})).set_index(ca_df.index[mask3])
ca_df.loc[mask3, ['OWNER_ZIP','OWNER_CITY','OWNER_STATE']] = df3

print (ca_df)
  OWNER_ZIP   OWNER_CITY OWNER_STATE
0       NaN          NaN         NaN
1     72044     Edgemont          AR
2     56171     Sherburn          MN
3       123  MIAMI SHORE          PA
4       789      SEATTLE          AA
user_29010020

You can do a left join on these dataframes considering join on the columns 'city' and 'state'. That would give you the zip-code corresponding to a city and state if both values are non-null in the first dataframe (OWNER_CITY, OWNER_STATE, OWNER_ZIP) and since it would be a left join, it would also preserve your rows which either don't have a zip-code or have null/empty city and state values.

user_99638640
Related