Merge Two Dataframes by Index without loosing data on empty rows

Question

say I want to merge two dataframes on a matching index using pandas, but one of the df's is missing some indexes, how could I do this without losing any data? E.g.

            price  
21-02-2022  2 
22-02-2022  1 
23-02-2022  3 

            sales  
22-02-2022  2

should output:

            price  sales
21-02-2022  2      0
22-02-2022  1      2
23-02-2022  3      0

Thanks in advance and sorry for the noob question!

score 1 · Answer 1 · answered Jul 29 '22 at 23:50

1

What you describe is called an outer join, which you can achieve as follows:

df1.join(df2, how='outer').fillna(0)

Notably, you must fillna as pandas, by default, will put in NaN values for non-matching rows.

answered Jul 29 '22 at 23:50

Kraigolas

5,121
3
12
37

1

Note that if you have existing NAs in `df2`, this will fill them, which my be unwanted. In such case, `reindex` `df2` with a `fill_value=0` prior to `merge`. – mozway Jul 30 '22 at 00:51

score 0 · Accepted Answer · answered Jul 29 '22 at 23:50

0

df.merge(df2, how='left',
        left_index=True,
        right_index=True).fillna(0).astype(int)

            price   sales
21-02-2022      2       0
22-02-2022      1       2
23-02-2022      3       0

answered Jul 29 '22 at 23:50

Naveed

11,495
2
14
21

score 0 · Answer 3 · answered Jul 29 '22 at 23:56

0

You can just concat them together on axis=1

df = pd.concat([df1, df2], axis=1).fillna(0).astype(int)
print(df)

Output:

            price  sales
21-02-2022      2      0
22-02-2022      1      2
23-02-2022      3      0

answered Jul 29 '22 at 23:56

BeRT2me

12,699
2
13
31

Merge Two Dataframes by Index without loosing data on empty rows

3 Answers3