Pandas groupby count number of days

user_10260243 0
Viewed 71

Suppose I have the following dataframe:

df = pd.DataFrame({'c': ['abc', 'def', 'wyx', 'abc', 'wyx'], 'begin_date': ['2020-01-01', '2000-12-23', '2003-07-07', '2005-03-02', '2004-01-01'], 'end_date': ['2020-01-31', '2001-02-02', '2004-03-02', '2005-04-01', '2004-07-04']})

df

  c  begin_date    end_date
 abc  2020-01-01  2020-01-31
 def  2000-12-23  2001-02-02
 wyx  2003-07-07  2004-03-02
 abc  2005-03-02  2005-04-01
 wyx  2004-01-01  2004-07-04

And I want to group by c and count the number of unique days that are between begin_date and end_date for a row of that c, my final dataframe would be:

end = pd.DataFrame({'c': ['abc', 'def', 'wyx'], 'count': [30, 41, 363]})

end
   c  count
 abc     30
 def     41
 wyx    363
pandas dataframe datetime python
1 Answers

The first thing you should do is to convert to datetime type, then you can subtract and groupby:

s = (pd.to_datetime(df.end_date) - pd.to_datetime(df.begin_date)).dt.days
s.groupby(df['c']).sum()

Update: to remove duplicates days, we can resample:

df['begin_date'] = pd.to_datetime(df['begin_date'])
df['end_date'] = pd.to_datetime(df['end_date'])

(pd.concat(pd.DataFrame({
    'c':x['c'],
    'count':pd.date_range(x.begin_date, x.end_date)
}) for _,x in df.iterrows())
   .drop_duplicates()
   .groupby('c').size()
   .reset_index(name='count')
)

Output:

     c  count
0  abc     62
1  def     42
2  wyx    364
user_42384080
Related