Python: Pandas how to add a column to duplicated values of dataframe which are in ascending order?

user_19388292 0
Viewed 31

I have the following DataFrame:

name  date
test   2022-03-04
test   2022-03-05
test   2022-03-06
test   2022-03-17
test   2022-03-18
test   2022-03-21
test2  2022-03-04
test2  2022-03-05
test2  2022-03-15
test2  2022-03-19
test2  2022-03-21
test2  2022-04-16
test3  2022-03-14
test3  2022-03-15
test3  2022-03-23
test3  2022-03-27
test4  2022-03-20
test4  2022-04-15
test4  2022-04-17
test5  2022-03-01
test5  2022-03-04
test5  2022-03-06
test5  2022-03-12
test5  2022-04-04
test5  2022-04-10
test5  2022-04-14
test5  2022-05-04
test6  2022-03-05
test6  2022-03-15
test6  2022-06-20
test6  2022-06-24
test6  2022-06-27

How can I add a column old_data with value yes for duplicate old records in combination of (name , date) which have data at least greater than 3 values? The date column is in ascending order.

I want to produce this output:

name  date           old_data
test    2022-03-04  yes
test    2022-03-05  yes
test    2022-03-06  yes
test    2022-03-17  
test    2022-03-18  
test    2022-03-21  
test2   2022-03-04  yes
test2   2022-03-05  yes
test2   2022-03-15  yes
test2   2022-03-19  
test2   2022-03-21
test2   2022-04-16  
test3   2022-03-14  yes
test3   2022-03-15  
test3   2022-03-23  
test3   2022-03-27  
test4   2022-03-20  
test4   2022-04-15  
test4   2022-04-17  
test5   2022-03-01  yes
test5   2022-03-04  yes
test5   2022-03-06  yes
test5   2022-03-12  yes
test5   2022-04-04  yes
test5   2022-04-10  
test5   2022-04-14  
test5   2022-05-04  
test6   2022-03-05  yes
test6   2022-03-15  yes
test6   2022-06-20  
test6   2022-06-24  
test6   2022-06-27

This is my attempt:

df['old_data'] = np.where(df.groupby('name').cumcount().ge(4), 'yes', '')
pandas dataframe python-3.x python
1 Answers

Use GroupBy.cumcount with ascending=False for counter descending and instead greater or equal 4 use 3:

df['old_data'] = np.where(df.groupby('name').cumcount(ascending=False).ge(3), 'yes', '')

Another idea with GroupBy.rank:

df['date'] = pd.to_datetime(df['date'])

m = df.groupby('name')['date'].rank(method='dense', ascending=False).gt(3)
df['old_data'] = np.where(m, 'yes', '')

print (df)

     name        date old_data
0    test  2022-03-04      yes
1    test  2022-03-05      yes
2    test  2022-03-06      yes
3    test  2022-03-17         
4    test  2022-03-18         
5    test  2022-03-21         
6   test2  2022-03-04      yes
7   test2  2022-03-05      yes
8   test2  2022-03-15      yes
9   test2  2022-03-19         
10  test2  2022-03-21         
11  test2  2022-04-16         
12  test3  2022-03-14      yes
13  test3  2022-03-15         
14  test3  2022-03-23         
15  test3  2022-03-27         
16  test4  2022-03-20         
17  test4  2022-04-15         
18  test4  2022-04-17         
19  test5  2022-03-01      yes
20  test5  2022-03-04      yes
21  test5  2022-03-06      yes
22  test5  2022-03-12      yes
23  test5  2022-04-04      yes
24  test5  2022-04-10         
25  test5  2022-04-14         
26  test5  2022-05-04         
27  test6  2022-03-05      yes
28  test6  2022-03-15      yes
29  test6  2022-06-20         
30  test6  2022-06-24         
31  test6  2022-06-27
user_29010020
Related