CODEWITHSUNDEEP
pythonpandas
raam
raam

Reputation: 241

How to calculate weeks difference and add missing Weeks with count in python pandas

I am having a data frame like this I have to get missing Weeks value and count between them

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

The output should be like this.

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

Upvotes: 2

Views: 109

Answers (1)

jezrael
jezrael

Reputation: 862581

Use:

#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

Upvotes: 1

Related Questions