pandas - 根据 Pandas 中给定的特定条件从给定日期创建 year_category

Question

我有一个数据框，如下所示。这是两个保健品从2016年12月到2018年11月的销售数据。

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

因为是3年的数据。我想根据以下条件创建一个名为 year_category 的新列。

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

预期输出:

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

Answer 1

您可以使用 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
                      )

输出:

   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