I have two tables which I am trying to merge, df_a and df_b, but both are missing some values. The merge happens fine, however I'm trying to speed up the process of filling in the NaN values after the merge.
The data:
df_a = pd.DataFrame(data={
'id': [1, 2, 3, 1, 6, 5, 3],
'name': [np.nan, 'two', 'three', 'one', np.nan, np.nan, np.nan],
'length': ['l1', 'l2', 'l3', 'l1', 'l6', np.nan, np.nan],
'Q1': [11, 22, 33, 11, 66, 55, 33],
'Q2': [111, 222, 333, 111, 666, 555, 333]})
df_b = pd.DataFrame(data={
'id': [5, 2, 4, 1, 3, 1, 7, 3],
'name': ['five', 'two', 'four', 'one_extra', 'three', 'one', 'seven', 'three'],
'length': ['l5', 'l2', 'l4', 'l1', 'l3', 'l1', 'l7', 'l3_extra'],
'Q1': [2055, 2022, 2044, 2011, 2033, 2011, 2077, 2033],
'Q2': [2055, 2022, 2044, 2011, 2033, 2011, 2077, 2033]})
df_m = df_a.merge(df_b, on=['id', 'name', 'length'], how='outer',
suffixes=['_a', '_b'])
Output:
id name length Q1 Q2
0 1 NaN l1 11 111
1 2 two l2 22 222
2 3 three l3 33 333
3 1 one l1 11 111
4 6 NaN l6 66 666
5 5 NaN NaN 55 555
6 3 NaN NaN 33 333
id name length Q1 Q2
0 5 five l5 2055 2055
1 2 two l2 2022 2022
2 4 four l4 2044 2044
3 1 one_extra l1 2011 2011
4 3 three l3 2033 2033
5 1 one l1 2011 2011
6 7 seven l7 2077 2077
7 3 three l3_extra 2033 2033
id name length Q1_a Q2_a Q1_b Q2_b
0 1 NaN l1 11.0 111.0 NaN NaN
3 1 one l1 11.0 111.0 2011.0 2011.0
9 1 one_extra l1 NaN NaN 2011.0 2011.0
1 2 two l2 22.0 222.0 2022.0 2022.0
2 3 three l3 33.0 333.0 2033.0 2033.0
6 3 NaN NaN 33.0 333.0 NaN NaN
11 3 three l3_extra NaN NaN 2033.0 2033.0
8 4 four l4 NaN NaN 2044.0 2044.0
5 5 NaN NaN 55.0 555.0 NaN NaN
7 5 five l5 NaN NaN 2055.0 2055.0
4 6 NaN l6 66.0 666.0 NaN NaN
10 7 seven l7 NaN NaN 2077.0 2077.0
The Endpoint
I'm trying to get df_m into a state where any NaN values are filled in based on their values from a corresponding id column. Any time there's a duplicate id, I want to keep both values, so that I end up with the following df:
id name length Q1_a Q2_a Q1_b Q2_b
0 1 one l1 11.0 111.0 2011.0 2011.0
1 1 one_extra l1 11.0 111.0 2011.0 2011.0
2 2 two l2 22.0 222.0 2022.0 2022.0
3 3 three l3 33.0 333.0 2033.0 2033.0
4 3 three l3_extra 33.0 333.0 2033.0 2033.0
5 4 four l4 NaN NaN 2044.0 2044.0
6 5 five l5 55.0 555.0 2055.0 2055.0
7 6 NaN l6 66.0 666.0 NaN NaN
8 7 seven l7 NaN NaN 2077.0 2077.0
Note that there are some NaN values, where there was an entry in one table, and not in the other, e.g. there's no entry in df_a for id=4, so there's NaNs at Q1_a and Q2_a.
My solution so far
The following works, but I'd like to vectorise this code, as it takes more than 4 seconds with only ~200 rows:
dupe_ids = pd.value_counts(df_m.id)[
pd.value_counts(df_m.id) > 1].index.values
for dupe_id in dupe_ids:
df_m.loc[df_m.id == dupe_id] = df_m.loc[df_m.id == dupe_id].ffill().bfill()
df_m = df_m.drop_duplicates().sort_values(by='id').reset_index(drop=True)
I tried vectorising the code, as follows, but the results were strange (see id=4).
df_m.sort_values(by='id').groupby(
'id').ffill().bfill().drop_duplicates().reset_index(drop=True)
Output:
id name length Q1_a Q2_a Q1_b Q2_b
0 1 one l1 11.0 111.0 2011.0 2011.0
1 1 one_extra l1 11.0 111.0 2011.0 2011.0
2 2 two l2 22.0 222.0 2022.0 2022.0
3 3 three l3 33.0 333.0 2033.0 2033.0
4 3 three l3_extra 33.0 333.0 2033.0 2033.0
5 4 four l4 55.0 555.0 2044.0 2044.0
6 5 five l5 55.0 555.0 2055.0 2055.0
7 6 seven l6 66.0 666.0 2077.0 2077.0
8 7 seven l7 NaN NaN 2077.0 2077.0
The two tables I'm actually trying to merge are quite a bit more complex, so the example above leads to much more strange behaviour than is seen there. There are over 100 columns, and different columns can have NaN values in different places.
Any help would be greatly appreciated!