python - 如何使用 map、reduce、apply 或 python 中的其他函数(在本例中)转换 DataFrame？

Question

我有这样的数据框:

df = pd.DataFrame({'id': [111,222], 'CycleOfRepricingAnchorTime': ['27.04.2018', '09.06.2018'], 'CycleOfRepricing': ['3M','5M'] }) 
df['CycleOfRepricingAnchorTime'] = pd.to_datetime(df['CycleOfRepricingAnchorTime'] ) 
df

我需要得到这样的DataFrame:

结果DataFrame:第一列是id，第二列是Date，频率等于这个id的'CycleOfRepricing'。最大日期为 2019 年 12 月 31 日

我曾尝试用apply、map等方法解决此类任务，但我没有成功，因为我只能获取对象

 df.apply(lambda x: \
        pd.date_range(start = x.CycleOfRepricingAnchorTime, \
                         end = pd.to_datetime('31.12.2019'),
                         freq = x.CycleOfRepricing), axis = 1)

我将不胜感激。

Answer 1

更新以匹配每个期间的月份日期。

df.assign(ReportingTime=df.apply(lambda x: \
        pd.date_range(start = x.CycleOfRepricingAnchorTime, \
                         end = pd.to_datetime('31.12.2019'),
                         freq = x.CycleOfRepricing+'S')+
                                pd.Timedelta(days=x.CycleOfRepricingAnchorTime.day-1), 
                      axis = 1)).explode('ReportingTime').to_markdown()

输出:

|    |   id | CycleOfRepricingAnchorTime   | CycleOfRepricing   | ReportingTime       |
|---:|-----:|:-----------------------------|:-------------------|:--------------------|
|  0 |  111 | 2018-04-27 00:00:00          | 3M                 | 2018-05-27 00:00:00 |
|  0 |  111 | 2018-04-27 00:00:00          | 3M                 | 2018-08-27 00:00:00 |
|  0 |  111 | 2018-04-27 00:00:00          | 3M                 | 2018-11-27 00:00:00 |
|  0 |  111 | 2018-04-27 00:00:00          | 3M                 | 2019-02-27 00:00:00 |
|  0 |  111 | 2018-04-27 00:00:00          | 3M                 | 2019-05-27 00:00:00 |
|  0 |  111 | 2018-04-27 00:00:00          | 3M                 | 2019-08-27 00:00:00 |
|  0 |  111 | 2018-04-27 00:00:00          | 3M                 | 2019-11-27 00:00:00 |
|  1 |  222 | 2018-09-06 00:00:00          | 5M                 | 2018-10-06 00:00:00 |
|  1 |  222 | 2018-09-06 00:00:00          | 5M                 | 2019-03-06 00:00:00 |
|  1 |  222 | 2018-09-06 00:00:00          | 5M                 | 2019-08-06 00:00:00 |

尝试使用 pandas 版本 0.25.0+:

df.assign(ReportingTime=df.apply(lambda x: \
        pd.date_range(start = x.CycleOfRepricingAnchorTime, \
                         end = pd.to_datetime('31.12.2019'),
                         freq = x.CycleOfRepricing), axis = 1)).explode('ReportingTime')

输出:

|    |   id | CycleOfRepricingAnchorTime   | CycleOfRepricing   | ReportingTime       |
|---:|-----:|:-----------------------------|:-------------------|:--------------------|
|  0 |  111 | 2018-04-27 00:00:00          | 3M                 | 2018-04-30 00:00:00 |
|  0 |  111 | 2018-04-27 00:00:00          | 3M                 | 2018-07-31 00:00:00 |
|  0 |  111 | 2018-04-27 00:00:00          | 3M                 | 2018-10-31 00:00:00 |
|  0 |  111 | 2018-04-27 00:00:00          | 3M                 | 2019-01-31 00:00:00 |
|  0 |  111 | 2018-04-27 00:00:00          | 3M                 | 2019-04-30 00:00:00 |
|  0 |  111 | 2018-04-27 00:00:00          | 3M                 | 2019-07-31 00:00:00 |
|  0 |  111 | 2018-04-27 00:00:00          | 3M                 | 2019-10-31 00:00:00 |
|  1 |  222 | 2018-09-06 00:00:00          | 5M                 | 2018-09-30 00:00:00 |
|  1 |  222 | 2018-09-06 00:00:00          | 5M                 | 2019-02-28 00:00:00 |
|  1 |  222 | 2018-09-06 00:00:00          | 5M                 | 2019-07-31 00:00:00 |
|  1 |  222 | 2018-09-06 00:00:00          | 5M                 | 2019-12-31 00:00:00 |