I have a pandas dataframe, df1
:
id level val
1 l1 0.2
1 l2 0.1
and another dataframe which contains type for l1
level, df2
:
id type
1 A
Similarly, df3
which contains type for l2
level as well as new ids:
id new_id type
1 19 A
1 19 B
1 20 A
1 20 B
Now I want type for l1
level from df2
. And for l2
level from df3
, while also resetting ids and level. This is what I am doing:
df2['level'] = 'l1'
df3['level'] = 'l2'
df1 = pd.merge(df1, df2, how='outer', on=['id','level'])
df1 = pd.merge(df1, df3, how='outer', on=['id','level'], suffixes=["_", ""])
df1['id'] = np.where(df1['new_id'].isnull(), df1['id'], df1['new_id'])
df1['type'] = np.where(df1['type'].isnull(), df1['type_'], df1['type'])
df1['level'] = np.where(df1['level']=='l2', 'l3', df1['level'])
df1 = df1.drop('new_id', 1)
df1 = df1.drop('type_', 1)
Which finally gives me, df1
:
id level val type
1 l1 0.2 A
19 l3 0.1 A
19 l3 0.1 B
20 l3 0.1 A
20 l3 0.1 B
Since df1
is a large dataframe, I want to avoid using np.where
thrice, i.e., I am looking for a faster approach to get this. Thanks!