CODEWITHSUNDEEP
pythonpandasdataframemerge
Scott Skiles
Scott Skiles

Reputation: 3847

Merge two DataFrames, keep column entries, and set non-matches to zero

Overview

How can I merge two pandas DataFames so that every date is included and empty values are set to zero? I took a good look at pd.merge_asof and I did not see an example in there to fit my use case and I cannot get this to work properly.

Code

# imports
import pandas as pd
import numpy as np

# shared data
column_names = ['date', 'gross_profit', 'costs', 'factory_id']

# df1 construction
range_1 = pd.date_range('2019-01-01', periods=3, freq='2D')
gross_profit_1 = [100, 200, 300]
costs_1 = [-20, -30, -40]
factory_id_1 = ['A', 'A', 'A']
values_1 = np.array([range_1, gross_profit_1, costs_1, factory_id_1]).T
df1 = pd.DataFrame(values_1, index=range_1, columns=column_names)

# df2 construction
range_2 = pd.date_range('2019-01-02', periods=3, freq='2D')
gross_profit_2 = [400, -300, 900]
costs_1 = [-90, -80, -70]
factory_id_2 = ['B', 'B', 'B']
values_2 = np.array([range_2, gross_profit_2, costs_2, factory_id_2]).T
df2 = pd.DataFrame(values_2, index=range_2, columns=column_names)

>>> print(df1)
               date                gross_profit     costs     factory_id
2019-01-01     2019-01-01 00:00:00     100           -20          A
2019-01-03     2019-01-03 00:00:00     200           -30          A
2019-01-05     2019-01-05 00:00:00     300           -40          A

>>> print(df2) 
               date                 gross_profit     costs     factory_id
2019-01-02     2019-01-02 00:00:00     400           -90          B
2019-01-04     2019-01-04 00:00:00    -300           -80          B
2019-01-06     2019-01-06 00:00:00     900           -70          B

Desired merged_df:

>>> print(merged_df) 
               date                 gross_profit_A   gross_profit_B
2019-01-01     2019-01-01 00:00:00       100              0
2019-01-02     2019-01-02 00:00:00        0              400
2019-01-03     2019-01-03 00:00:00       200              0
2019-01-04     2019-01-04 00:00:00        0             -300      
2019-01-05     2019-01-05 00:00:00       300              0
2019-01-06     2019-01-06 00:00:00        0              900

Example resulting calculation:

total_gross_profit = merged_df.gross_profit_A + merged_df.gross_profit_B
cumulative_gross_profit = np.cumsum(total_gross_profit)
>>> print(cumulative_gross_profit)

2019-01-01    100
2019-01-02    500
2019-01-03    700
2019-01-04    400
2019-01-05    700
2019-01-06    1500
Freq: 1D, Name: cumulative_gross_profit, dtype: object

I have included costs in each DataFrame because I want to make it obvious that I eventually want to do this for more than one column.

Upvotes: 0

Views: 287

Answers (1)

BENY
BENY

Reputation: 323286

Here is one way concat

pd.concat([df1[['gross_profit']].\
     add_suffix(df1.factory_id[0]),
        df2[['gross_profit']].\
           add_suffix(df2.factory_id[0])],axis=0,sort=True).\
             sort_index().\
fillna(0)
Out[163]: 
            gross_profitA  gross_profitB
2019-01-01            100              0
2019-01-02              0            400
2019-01-03            200              0
2019-01-04              0           -300
2019-01-05            300              0
2019-01-06              0            900

Upvotes: 2

Related Questions