Lets say I have two dfs - teachers and departments
teacher_id, name, dep_id
1, ABC, 10
2, XYZ, 20
dep_id
10
20
40
50
I want to duplicate teachers rows for the missing departments. so the output needs to be:
teacher_id, name, dep_id
1, ABC, 10
1, ABC, 20
1, ABC, 40
1, ABC, 50
2, XYZ, 20
2, XYZ, 10
2, XYZ, 40
2, XYZ, 50
Outer Join and merges with pandas will put a NaN for teacher_id and name. I want to duplicate the values. Any ideas how to do this simple ?
You can use df.groupby with Groupby.apply with custom func which filters out missing dep_id then add them to respective teacher_id using pd.concat and use df.ffill()
#df
# teacher_id name dep_id
#0 1 ABC 10
#1 2 XYZ 20
#df1
# dep_id
#0 10
#1 20
#2 40
#3 50
def fill(df):
m = df1['dep_id'].isin(df['dep_id'])
missing = df1.loc[~m]
return pd.concat([df, missing]).ffill()
df.groupby('teacher_id',group_keys=False).apply(fill)
teacher_id name dep_id
0 1.0 ABC 10
1 1.0 ABC 20
2 1.0 ABC 40
3 1.0 ABC 50
1 2.0 XYZ 20
0 2.0 XYZ 10
2 2.0 XYZ 40
3 2.0 XYZ 50