How do I forward fill na's with condition of 2 other cells being equal in pandas?

user_14602203 0
Viewed 74

I have customer transaction data where some invoice numbers are missing. I would like to fill the missing invoice numbers with the preceding row value if both the customer id's are equal in the rows and the transaction amounts are equal. Date is not important.

An example of what the data looks like is:

 date  customer amount  invoice
01/13    A        10      1
02/13    B        20      2
03/13    B        20      NA
04/13    C        30      3
05/13    C        60      NA
06/13    D        50      4

and what I am trying to create is:

 date  customer amount  invoice
01/13    A        10      1
02/13    B        20      2
03/13    B        20      2
04/13    C        30      3
05/13    C        60      NA      - this NA remains because amount does not match
06/13    D        50      4
missing-data pandas python
2 Answers

Update: Add a specific column to ffill, thanks to @David Erickson's comment.

You can use groupby and ffill.

df['invoice'] = df.groupby(['customer', 'amount'])['invoice'].ffill()
user_29561350

Emma's answer is the solution here: ( df['invoice'] = df.groupby(['customer', 'amount'])['invoice'].ffill() )

However, the following answer might be useful if you had some conditions outside of what can be done with groupby, so I will keep.

You can use ffill() with a mask statement to fill conditionally:

df['invoice'] = df['invoice'].mask(df.duplicated(['customer', 'amount']),
                                   df['invoice'].ffill())
df
Out[1]: 
    date customer  amount  invoice
0  01/13        A      10      1.0
1  02/13        B      20      2.0
2  03/13        B      20      2.0
3  04/13        C      30      3.0
4  05/13        C      60      NaN
5  06/13        D      50      4.0
user_63667700
Related