Python & Pandas - Group by day and count for each day

Question

I am new on pandas and for now i don't get how to arrange my time serie, take a look at it :

date & time of connection
19/06/2017 12:39
19/06/2017 12:40
19/06/2017 13:11
20/06/2017 12:02
20/06/2017 12:04
21/06/2017 09:32
21/06/2017 18:23
21/06/2017 18:51
21/06/2017 19:08
21/06/2017 19:50
22/06/2017 13:22
22/06/2017 13:41
22/06/2017 18:01
23/06/2017 16:18
23/06/2017 17:00
23/06/2017 19:25
23/06/2017 20:58
23/06/2017 21:03
23/06/2017 21:05

This is a sample of a dataset of 130 k raws,I tried : df.groupby('date & time of connection')['date & time of connection'].apply(list)

Not enough i guess

I think i should :

Create a dictionnary with index from dd/mm/yyyy to dd/mm/yyyy
Convert "date & time of connection" type dateTime to Date
Group and count Date of "date & time of connection"
Put the numbers i count inside the dictionary ?

What do you think about my logic ? Do you know some tutos ? Thank you very much

jezrael · Accepted Answer · 2018-02-24T11:12:48.750

You can use dt.floor for convert to dates and then value_counts or groupby with size:

df = (pd.to_datetime(df['date & time of connection'])
       .dt.floor('d')
       .value_counts()
       .rename_axis('date')
       .reset_index(name='count'))
print (df)
        date  count
0 2017-06-23      6
1 2017-06-21      5
2 2017-06-19      3
3 2017-06-22      3
4 2017-06-20      2

Or:

s = pd.to_datetime(df['date & time of connection'])
df = s.groupby(s.dt.floor('d')).size().reset_index(name='count')
print (df)
  date & time of connection  count
0                2017-06-19      3
1                2017-06-20      2
2                2017-06-21      5
3                2017-06-22      3
4                2017-06-23      6

Timings:

np.random.seed(1542)

N = 220000
a = np.unique(np.random.randint(N, size=int(N/2)))
df = pd.DataFrame(pd.date_range('2000-01-01', freq='37T', periods=N)).drop(a)
df.columns = ['date & time of connection']
df['date & time of connection'] = df['date & time of connection'].dt.strftime('%d/%m/%Y %H:%M:%S')
print (df.head()) 

In [193]: %%timeit
     ...: df['date & time of connection']=pd.to_datetime(df['date & time of connection'])
     ...: df1 = df.groupby(by=df['date & time of connection'].dt.date).count()
     ...: 
539 ms ± 45.9 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)

In [194]: %%timeit
     ...: df1 = (pd.to_datetime(df['date & time of connection'])
     ...:        .dt.floor('d')
     ...:        .value_counts()
     ...:        .rename_axis('date')
     ...:        .reset_index(name='count'))
     ...: 
12.4 ms ± 350 µs per loop (mean ± std. dev. of 7 runs, 100 loops each)

In [195]: %%timeit
     ...: s = pd.to_datetime(df['date & time of connection'])
     ...: df2 = s.groupby(s.dt.floor('d')).size().reset_index(name='count')
     ...: 
17.7 ms ± 140 µs per loop (mean ± std. dev. of 7 runs, 100 loops each)

score 33 · Answer 2 · answered Feb 24 '18 at 10:46

33

To make sure your columns in in date format.

df['date & time of connection']=pd.to_datetime(df['date & time of connection'])

Then you can group the data by date and do a count:

df.groupby(by=df['date & time of connection'].dt.date).count()
Out[10]: 
                           date & time of connection
date & time of connection                           
2017-06-19                                         3
2017-06-20                                         2
2017-06-21                                         5
2017-06-22                                         3
2017-06-23                                         6

answered Feb 24 '18 at 10:46

Allen Qin

19,507
8
51
67

7

Using `.dt.date` much, much more elegant than using the `floor` function above. – ijoseph Jul 09 '19 at 23:54
To make this a bit more nice, it would be better if we only see the `date` and `count` columns in the output, like the accepted answer. If `df` has multiple columns, this code will result in having extra columns which is not that pretty for the presentation! – Pedram Apr 01 '20 at 07:42

score 12 · Answer 3 · answered Oct 01 '19 at 08:25

12

Hey I found easy way to do this with resample.

# Set the date column as index column.
df = df.set_index('your_date_column')

# Make counts
df_counts = df.your_date_column.resample('D').count()

Although your column name is long and contains spaces, which makes me a little cringy. I would use dashes instead of spaces.

answered Oct 01 '19 at 08:25

Jaan Olev

139
2
5

4

You don't need to make the `date column` the `index`. You can use `on` instead: `resample('D',on='your_date_column')` – Cornelius Roemer Dec 23 '21 at 07:19

Python & Pandas - Group by day and count for each day

3 Answers3

Linked