I have two DataFrames:
df - the core DataFrame with columns/cells that I want to expandmaptable - a maptable DataFrame that maps certain columnsAn example:
maptable:
id | period
A | winter
B | summer
A | summer
nan | summer
B | nan
df:
id | period | other_col
A | None | X
B | summer | Y
C | None | Z
D | spring | D
D | NaN
How can I only map the cells in df that are None/empty/nan using the maptable and the identifier column id?
Use Series.map and then fill NaN with Series.fillna:
df['period']= df['period'].fillna(df['id'].map(maptable.set_index('id')['period']))
#alternative
#df['period']= (df['id'].map(maptable.set_index('id')['period'])
# .where(df['period'].isnull(),df['period']))
Output
id other_col period
0 A X winter
1 B Y summer
2 C Z NaN
3 D D spring
EDIT DataFrame.merge
new_df= (df.merge(maptable,on = 'id',how = 'left')
.assign(period = lambda x: x['period_x'].fillna(x['period_y']))
.loc[:,df.columns])
print(new_df)
id period other_col
0 A winter X
1 A summer X
2 B summer Y
3 C NaN Z
4 D spring D
# Creating your dataframes
maptable = pd.DataFrame([{"id":"A","period":"winter"},{"id":"B","period":"summer"}])
df = pd.DataFrame({"id":["A","B","C","D"], "period":[None, "summer", None, "spring"], "other_col":list('XYZD')})
# Merging both dataframes on the "id" key
df1 = pd.merge(left=df, right=maptable, on="id", how="left")
df1["period"] = [x if not pd.isnull(x) else y for x, y in zip(df1["period_x"], df1["period_y"])]
df1.drop(["period_x", "period_y"], axis=1, inplace=True)
print(df1)
Output:
id other_col period
0 A X winter
1 B Y summer
2 C Z NaN
3 D D spring
df['period'].fillna(df['id'].map(maptable.set_index('id')['period']))?