python - 如何从每行的字符串中提取年份并用这些年份生成新行

Question

我有一个如下所示的数据框。

df = pd.DataFrame({'business_id':["a","b","c"], 
                   'time':['2016-04-26 19:49:16, 2016-08-30 18:36:57, 2016-11-18 01:54:50, 2017-04-20 18:39:06', 
                           '2017-03-12 17:47:20, 2018-09-10 16:40:17',
                           '2014-02-28 12:11:16, 2019-05-30 18:36:57, 2019-07-20 04:54:28'] })

这是由上述代码生成的输入。

business_id                                       time
      a            '2016-04-26 19:49:16, 2016-08-30 18:36:57, 2016-11-18 01:54:50, 2017-04-20 18:39:06'
      b                        '2017-03-12 17:47:20, 2018-09-10 16:40:17'
      c            '2014-02-28 12:11:16, 2019-05-30 18:36:57, 2019-07-20 04:54:28'

下面是我想要生成的输出。

business_id                year
    a                      2016 
    a                      2016
    a                      2016
    a                      2017
    b                      2017
    b                      2018
    c                      2014
    c                      2019
    c                      2019

我应该怎么做？

Answer 1

如果您只需要年份和 ID:

df[['business_id']].merge(df['time'].str
                        .extractall('(?P<year>\d{4})-')
                        .reset_index(1,drop=True),
                      left_index=True,
                      right_index=True)

输出:

  business_id  year
0           a  2016
0           a  2016
0           a  2016
0           a  2017
1           b  2017
1           b  2018
2           c  2014
2           c  2019
2           c  2019