df fruit year sale important
0 apple 2010
1 apple 2011
2 apple 2012
3 apple 2013
4 apple 2014 True Yes
5 apple 2015 True
6 apple 2017 True
7 apple 2018 True
7 apple 2019
8 apple 2020 True Yes
9 banana 2010
...
How could I generate the "important" column?
It's Yes if per fruit: (1) There is a sale that year and (2) there was no sale the year before and (3) there is at least a 3-year gap between that year and the previous "important" year.
Please try if this works for your case. Assuming that df is sorted by fruit and year.
for i in df['fruit'].unique():
df1 = df[(df['sale'] == 'True') & (df['sale'].shift() != 'True') & (df['fruit'] == i)]
df1 = df1[(df1['year'].diff() >=3) | (df1['year'].diff().fillna(0) == 0)]
df.loc[df.index.isin(df1.index), 'important'] = 'Yes'
Prints:
fruit year sale important
0 apple 2010 None NaN
1 apple 2011 None NaN
2 apple 2012 None NaN
3 apple 2013 None NaN
4 apple 2014 True Yes
5 apple 2015 True NaN
6 apple 2017 True NaN
7 apple 2018 True NaN
8 apple 2019 None NaN
9 apple 2020 True Yes
10 banana 2010 True NaN
I tested with few random combination of fruits and years and was working as far as I could check.