Pandas dataframe operations

Question

I have the following dataframe,

df = pd.DataFrame({
'CARD_NO': [000, 001, 002, 002, 001, 111],
'request_code': [2400,2200,2400,3300,5500,6600],
'merch_id': [1, 2, 1, 3, 3, 5],
'resp_code': [0, 1, 0, 1, 1, 1]})

Based on this requirement,

inquiries = df[(df.request_code == 2400) & (df.merch_id == 1) & (df.resp_code == 0)]

I need to flag records in df for which CARD_NO == CARD_NO where inquiries is True.
If inquiries returns:

[6 rows x 4 columns]
index    CARD_NO  merch_id  request_code  resp_code
0        0         1          2400          0
2        2         1          2400          0

Then df should look like so:

index     CARD_NO  merch_id  request_code  resp_code    flag
0        0         1          2400          0            N
1        1         2          2200          1            N
2        2         1          2400          0            N      
3        2         3          3300          1            Y
4        1         3          5500          1            N
5      111         5          6600          1            N

I've tried several merges, but cannot seem to get the result I want. Any help would be greatly appreciated. Thank you.

Your requirements are a bit fuzzy, could you make them more precise? — filmor
– filmor, Commented Mar 13, 2014 at 13:35
There are 2 types of records in the df. Ones which match the requirements and ones who don't. For each record in the df, i need to first figure out which ones hold true for the requirements. Then for each record in the dataframe except for those that hold true for the requirements, i need to find whether or not their CARD_NO matches CARD_NO in the set of records that have all the requirements. Does this help? It's an odd use case. — Zihs
– Zihs, Commented Mar 13, 2014 at 15:10

Jichao · Accepted Answer · 2014-03-13 17:27:27Z

1

the following should work if I understand your question correctly, which is that you want to set the flag is ture only when the CARD_NO is in the filtered group but the row itself is not in the filtered group.

import numpy as np
filter = (df.request_code == 2400) & (df.merch_id == 1) & (df.resp_code == 0)
df['flag']=np.where(~filter & df.CARD_NO.isin(df.ix[filter, 'CARD_NO']), 'Y', 'N')

answered Mar 13, 2014 at 17:27

Jichao

815 bronze badges

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

1 Comment

Zihs Over a year ago

this looks good! i'll let you know how it handles my larger dataset.

filmor · Accepted Answer · 2014-03-13 13:30:52Z

0

filtered = (df.request_code == 2400) & (df.merch_id == 1) & (df.resp_code == 0)
df["flag"] = filtered.map(lambda x: "Y" if x else "N")

answered Mar 13, 2014 at 13:30

filmor

32.6k6 gold badges53 silver badges48 bronze badges

2 Comments

Zihs Over a year ago

This does well to find the records that have all requirements. But it does not find other records in the df that have a CARD_NO in common with records that have all the requirements. I will try to use this as a base. thanks!

filmor Over a year ago

How is the accepted solution any different apart from the fact that filtered -> ~filtered & df.CARD_NO.isin(df.ix[filtered, 'CARD_NO'])?

Collectives™ on Stack Overflow

Pandas dataframe operations

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related