Merge multiple pandas dataframe along with np.where to process values

Question

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!

Answer 1

keyword arguments

I had to check the documentation to find out what the 1 means in df1.drop('new_id', 1). More clear would be df1.drop('new_id', axis=1), or even better: df1.drop(columns=["type_", "new_id"])

Don't overwrite raw data

You add columns and values to df1, which makes finding problems harder.

With a bit of reshuffling, and using df.join instead of pd.merge, you can make your intent a bit more clear

df6= df1.join(
 df2.assign(level="l1").set_index(["id", "level"]),
 how="outer",
 on=["id", "level"],
).join(
 df3.assign(level="l2").set_index(["id", "level"]),
 how="outer",
 on=["id", "level"],
 lsuffix="_",
)

np.where

If you're looking for null, you can use fillna or combine_first. To replace 'l2' with 'l3', you can use Series.replace.

df7 = df6.assign(
 id=df6["new_id"].fillna(df6["id"]).astype(int),
 type=df6["type_"].fillna(df6["type"]),
 level=df6["level"].replace("l2", "l3")
).drop(columns=["type_", "new_id"])

I don't know whether this approach will be faster, but to me, what happens here is a lot more clear.