python - pandas 时间间隔到时间序列

Question

如何使用 Python(和 pandas)将时间间隔数据转换为时间序列数据？

这是我之前的数据帧作为时间间隔:

code    start_dt                    end_dt                      ent_value
156600  1960-01-01  2016-04-21  H:CXP
156600  1960-01-01  2016-01-03  46927
156600  1998-08-31  2016-01-03  5516751
156600  1960-01-01  1998-08-30  4501242

对于 code 和 ent_value 的每个组合，我们希望在该组合的开始日期和结束日期内的每一天在框架中都有一行(因此作为时间序列):

code    as_of_dt   ent_value
156600  1960-01-01 H:CXP
156600  1960-01-02 H:CXP
156600  1960-01-03 H:CXP
156600  1960-01-01 46927
156600  1960-01-02 46927
156600  1960-01-03 46927
156600  1960-01-01 5516751
156600  1960-01-02 5516751
156600  1960-01-03 5516751
...
156600  2016-01-01 H:CXP
156600  2016-01-02 H:CXP
156600  2016-01-03 H:CXP
156600  2016-01-01 46927
156600  2016-01-02 46927
156600  2016-01-03 46927
156600  2016-01-01 5516751
156600  2016-01-02 5516751
156600  2016-01-03 5516751

如何有效地做到这一点？

Answer 1

试试这个:

In [17]: %paste
(df.groupby(['code','ent_value'])
   .apply(lambda x: pd.DataFrame({'as_of_dt':pd.date_range(x.start_dt.min(), x.end_dt.max())}))
   .reset_index()
   .drop('level_2', 1)
)
## -- End pasted text --
Out[17]:
         code ent_value   as_of_dt
0      156600   4501242 1960-01-01
1      156600   4501242 1960-01-02
2      156600   4501242 1960-01-03
3      156600   4501242 1960-01-04
4      156600   4501242 1960-01-05
5      156600   4501242 1960-01-06
6      156600   4501242 1960-01-07
7      156600   4501242 1960-01-08
8      156600   4501242 1960-01-09
9      156600   4501242 1960-01-10
10     156600   4501242 1960-01-11
11     156600   4501242 1960-01-12
12     156600   4501242 1960-01-13
13     156600   4501242 1960-01-14
14     156600   4501242 1960-01-15
15     156600   4501242 1960-01-16
16     156600   4501242 1960-01-17
17     156600   4501242 1960-01-18
18     156600   4501242 1960-01-19
19     156600   4501242 1960-01-20
20     156600   4501242 1960-01-21
21     156600   4501242 1960-01-22
22     156600   4501242 1960-01-23
23     156600   4501242 1960-01-24
24     156600   4501242 1960-01-25
25     156600   4501242 1960-01-26
26     156600   4501242 1960-01-27
27     156600   4501242 1960-01-28
28     156600   4501242 1960-01-29
29     156600   4501242 1960-01-30
...       ...       ...        ...
61450  156600     H:CXP 2016-03-23
61451  156600     H:CXP 2016-03-24
61452  156600     H:CXP 2016-03-25
61453  156600     H:CXP 2016-03-26
61454  156600     H:CXP 2016-03-27
61455  156600     H:CXP 2016-03-28
61456  156600     H:CXP 2016-03-29
61457  156600     H:CXP 2016-03-30
61458  156600     H:CXP 2016-03-31
61459  156600     H:CXP 2016-04-01
61460  156600     H:CXP 2016-04-02
61461  156600     H:CXP 2016-04-03
61462  156600     H:CXP 2016-04-04
61463  156600     H:CXP 2016-04-05
61464  156600     H:CXP 2016-04-06
61465  156600     H:CXP 2016-04-07
61466  156600     H:CXP 2016-04-08
61467  156600     H:CXP 2016-04-09
61468  156600     H:CXP 2016-04-10
61469  156600     H:CXP 2016-04-11
61470  156600     H:CXP 2016-04-12
61471  156600     H:CXP 2016-04-13
61472  156600     H:CXP 2016-04-14
61473  156600     H:CXP 2016-04-15
61474  156600     H:CXP 2016-04-16
61475  156600     H:CXP 2016-04-17
61476  156600     H:CXP 2016-04-18
61477  156600     H:CXP 2016-04-19
61478  156600     H:CXP 2016-04-20
61479  156600     H:CXP 2016-04-21

[61480 rows x 3 columns]

使用较小的日期范围测试 DF:

In [19]: df
Out[19]:
     code   start_dt     end_dt ent_value
0  156600 1960-01-01 1960-01-04     H:CXP
1  156600 1960-01-04 1960-01-09     46927
2  156600 1998-08-31 1998-09-04   5516751
3  156600 1965-01-01 1965-01-04   4501242

In [20]: (df.groupby(['code','ent_value'])
   ....:    .apply(lambda x: pd.DataFrame({'as_of_dt':pd.date_range(x.start_dt.min(), x.end_dt.max())}))
   ....:    .reset_index()
   ....:    .drop('level_2', 1)
   ....: )
Out[20]:
      code ent_value   as_of_dt
0   156600   4501242 1965-01-01
1   156600   4501242 1965-01-02
2   156600   4501242 1965-01-03
3   156600   4501242 1965-01-04
4   156600     46927 1960-01-04
5   156600     46927 1960-01-05
6   156600     46927 1960-01-06
7   156600     46927 1960-01-07
8   156600     46927 1960-01-08
9   156600     46927 1960-01-09
10  156600   5516751 1998-08-31
11  156600   5516751 1998-09-01
12  156600   5516751 1998-09-02
13  156600   5516751 1998-09-03
14  156600   5516751 1998-09-04
15  156600     H:CXP 1960-01-01
16  156600     H:CXP 1960-01-02
17  156600     H:CXP 1960-01-03
18  156600     H:CXP 1960-01-04