-1

I have a data frame as below;

ID Transaction_Date Label
101 Null Group1
101 2021-12-29 Group2
102 2021-01-01 Group1
102 2021-11-01 Group2
103 2021-11-15 Group3
104 2021-12-15 Group2
105 2021-01-15 Group1

Then I have a list of IDs. For example, [101, 102, 103]

I would like to search IDs in the list, then update the label column based on the comparison with Transaction_Date.

So, If an ID is not found in the list AND Transaction_Date > Today, THEN update that ID's Label column to Group5, otherwise, mark as Group6.

This is can be done with a for-loop but I was trying to find a better way.

EZ72
  • 61
  • 7
  • Will you please provide a sample dataframe containing your expected output? –  Mar 22 '22 at 16:54

1 Answers1

1

This should work for you:

df['Label'] = np.where(~df['ID'].isin(ids) & (pd.to_datetime(df['Transaction_Date']) > pd.Timestamp.today()), 'Group5', 'Group6')

Output:

>>> df
    ID Transaction_Date   Label
0  101              NaT  Group6
1  101       2021-12-29  Group6
2  102       2021-01-01  Group6
3  102       2021-11-01  Group6
4  103       2021-11-15  Group6
5  104       2021-12-15  Group6
6  105       2021-01-15  Group6

(Note that in your provided dataset, there are no dates greater than today's date, so there are no Group5's in the sample you provided, but I assume that's not true with your real dataset.)