Skip to main content
 首页 » 编程设计

python之如何计算周差并在 python pandas 中添加缺少的周数

2024年12月31日8lvdongjie

我有一个这样的数据框,我必须得到缺少的周值并在它们之间计数

year    Data    Id 
20180406    57170   A 
20180413    55150   A 
20180420    51109   A 
20180427    57170   A 
20180504    55150   A 
20180525    51109   A 

输出应该是这样的。

Id Start year end-year count  
A  20180420      20180420      1 
A  20180518      20180525      2 

请您参考如下方法:

使用:

#converting to week period starts in Thursday 
df['year'] = pd.to_datetime(df['year'], format='%Y%m%d').dt.to_period('W-Thu') 
#resample by start of months with asfreq 
df1 = (df.set_index('year') 
         .groupby('Id')['Id'] 
         .resample('W-Thu') 
         .asfreq() 
         .rename('val') 
         .reset_index()) 
print (df1) 
  Id                  year  val 
0  A 2018-04-06/2018-04-12    A 
1  A 2018-04-13/2018-04-19    A 
2  A 2018-04-20/2018-04-26    A 
3  A 2018-04-27/2018-05-03    A 
4  A 2018-05-04/2018-05-10    A 
5  A 2018-05-11/2018-05-17  NaN 
6  A 2018-05-18/2018-05-24  NaN 
7  A 2018-05-25/2018-05-31    A 
 
#onverting to datetimes with starts dates 
#http://pandas.pydata.org/pandas-docs/stable/timeseries.html#converting-between-representations 
df1['year'] = df1['year'].dt.to_timestamp('D', how='s') 
print (df1) 
  Id       year  val 
0  A 2018-04-06    A 
1  A 2018-04-13    A 
2  A 2018-04-20    A 
3  A 2018-04-27    A 
4  A 2018-05-04    A 
5  A 2018-05-11  NaN 
6  A 2018-05-18  NaN 
7  A 2018-05-25    A 

m = df1['val'].notnull().rename('g') 
#create index by cumulative sum for unique groups for consecutive NaNs 
df1.index = m.cumsum() 
 
#filter only NaNs row and aggregate first, last and count. 
df2 = (df1[~m.values].groupby(['Id', 'g'])['year'] 
                     .agg(['first','last','size']) 
                     .reset_index(level=1, drop=True) 
                     .reset_index()) 
 
print (df2) 
  Id      first       last  size 
0  A 2018-05-11 2018-05-18     2