combine and replace value dataframe pandas

Question

I have two dataframes with the same date and client id, but with a different amount.

I try to get another dataframe with dfA amount value and keep the another 0's on dfB when dfA does not exist

dfA:
    client_id  date         amount
0     1        2020-07-11    100
1     1        2020-07-10    90
2     1        2020-07-09    80
3     1        2020-07-12    70
3     1        2020-07-01    86

dfB:
    client_id  date         amount
0     1        2020-07-11    0
1     1        2020-07-10    0
2     1        2020-07-09    0
3     1        2020-07-07    0
4     1        2020-07-06    0
5     1        2020-07-05    0
5     1        2020-07-04    0
3     1        2020-07-03    0
4     1        2020-07-02    0
5     1        2020-07-01    0

I want to get:

dfResult:
    client_id  date         amount
0     1        2020-07-11    100
1     1        2020-07-10    90
2     1        2020-07-09    80
3     1        2020-07-07    70
4     1        2020-07-06    0
5     1        2020-07-05    0
5     1        2020-07-04    0
3     1        2020-07-03    0
4     1        2020-07-02    0
5     1        2020-07-01    86

rhug123 · Accepted Answer · 2020-07-17 20:59:04Z

1

You can concat the df's together, sort by amount and then drop duplicates.

dfResult = pd.concat([dfA,dfB]).sort_values(by='amout',ascending = False).drop_duplicates(subset=['client_id','date'],keep='first').reset_index().sort_values(by=['client id','date'],ascending = (True,False))

edited Jul 17, 2020 at 20:59

answered Jul 17, 2020 at 20:46

rhug123

8,8801 gold badge14 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

pedroooo Over a year ago

hello, sorry aditional TypeError: Cannot compare type 'Timestamp' with type 'date'

pedroooo Over a year ago

the dfb dataframe

pedroooo Over a year ago

date_range = pd.date_range(date_begin.date(), date_end.date()) data = pd.DataFrame([], columns=['client_id', 'date', 'amount']) data['date'] = date_range

sushanth · Accepted Answer · 2020-07-17 21:01:54Z

0

try this,

(
    dfB.date.map(
        dfA.set_index('date')['amount'].to_dict()
    ).fillna(0.0)
)

Or

(
    dfB.merge(
        dfA, on=['client_id', 'date'], suffixes=("_x", ""), how='left'
    ).fillna(0.0).drop(columns=["amount_x"])
)

   client_id        date  amount
0          1  2020-07-11  100.0
1          1  2020-07-10   90.0
2          1  2020-07-09   80.0
3          1  2020-07-07    0.0
4          1  2020-07-06    0.0
5          1  2020-07-05    0.0
5          1  2020-07-04    0.0
3          1  2020-07-03    0.0
4          1  2020-07-02    0.0
5          1  2020-07-01   86.0

edited Jul 17, 2020 at 21:01

answered Jul 17, 2020 at 20:52

sushanth

8,2923 gold badges20 silver badges31 bronze badges

1 Comment

Quang Hoang Over a year ago

This would ignore the client_id which (I guess) is kinda important

Collectives™ on Stack Overflow

combine and replace value dataframe pandas

2 Answers 2

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related