Create year_category from the given date based the given specific condition in pandas

user_8901845 0
Viewed 20

I have a data frame as shown below. which is a sales data of two health care product starting from December 2016 to November 2018.

product     profit      bougt_date      discount
   A         50         2016-12-01      5
   A         50         2017-01-03      4
   B         200        2016-12-24      10
   A         50         2017-01-18      3
   B         200        2017-01-28      15
   A         50         2017-01-18      6
   B         200        2017-01-28      20
   A         50         2017-04-18      6
   B         200        2017-12-08      25
   A         50         2017-11-18      6
   B         200        2017-08-21      20
   B         200        2017-12-28      30
   A         50         2018-03-18      10
   B         300        2018-06-08      45
   B         300        2018-09-20      50
   A         50         2018-11-18      8
   B         300        2018-11-28      35

Since it is a 3 years data. I would like to create a new column called year_category based on the following criteria.

if bougt_date is from December 2016 to November 2017 both including. then year_category = 2017.
if bought_date is from December 2017 to November 2018, then year_category = 2018
if bought_date is from December 2018 to November 2019, then year_category = 2019

Expected Output:

product     profit      bougt_date      discount    year_category
   A         50         2016-12-01      5           2017
   A         50         2017-01-03      4           2017
   B         200        2016-12-24      10          2017
   A         50         2017-01-18      3           2017
   B         200        2017-01-28      15          2017
   A         50         2017-01-18      6           2017
   B         200        2017-01-28      20          2017
   A         50         2017-04-18      6           2017
   B         200        2017-12-08      25          2018
   A         50         2017-11-18      6           2017
   B         200        2017-08-21      20          2017
   B         200        2017-12-28      30          2018
   A         50         2018-03-18      10          2018
   B         300        2018-06-08      45          2018
   B         300        2018-09-20      50          2018       
   A         50         2018-11-18      8           2018
   B         300        2018-11-28      35          2018
pandas-groupby pandas
1 Answers

You can extract the fiscal year with to_period:

df['year_category'] = (pd.to_datetime(df.bougt_date)   # convert to datetime
                         .dt.to_period('Q-NOV')        # fiscal year ending in November
                         .dt.qyear                     # extract fiscal year
                      )

Output:

   product  profit  bougt_date  discount  year_category
0        A      50  2016-12-01         5           2017
1        A      50  2017-01-03         4           2017
2        B     200  2016-12-24        10           2017
3        A      50  2017-01-18         3           2017
4        B     200  2017-01-28        15           2017
5        A      50  2017-01-18         6           2017
6        B     200  2017-01-28        20           2017
7        A      50  2017-04-18         6           2017
8        B     200  2017-12-08        25           2018
9        A      50  2017-11-18         6           2017
10       B     200  2017-08-21        20           2017
11       B     200  2017-12-28        30           2018
12       A      50  2018-03-18        10           2018
13       B     300  2018-06-08        45           2018
14       B     300  2018-09-20        50           2018
15       A      50  2018-11-18         8           2018
16       B     300  2018-11-28        35           2018
user_42384080
Related