CODEWITHSUNDEEP
pythonpandasdataframe
Mark T
Mark T

Reputation: 155

How to count total days in pandas dataframe

I have a df column with dates and hours / minutes:

0   2019-09-13 06:00:00
1   2019-09-13 06:05:00
2   2019-09-13 06:10:00
3   2019-09-13 06:15:00
4   2019-09-13 06:20:00
Name: Date, dtype: datetime64[ns]

I need to count how many days the dataframe contains.

I tried it like this:

sample_length = len(df.groupby(df['Date'].dt.date).first())

and

sample_length = len(df.groupby(df['Date'].dt.date))

But the number I get seems wrong. Do you know another method of counting the days?

Upvotes: 1

Views: 6402

Answers (4)

Pranav Shetty
Pranav Shetty

Reputation: 36

In order to calculate the total number of unique dates in the given time series data example we can use:

print(len(pd.to_datetime(df['Date']).dt.date.unique()))

Upvotes: 1

Christian Eslabon
Christian Eslabon

Reputation: 185

import pandas as pd
df = pd.DataFrame({'Date': ['2019-09-13 06:00:00',
                            '2019-09-13 06:05:00',
                            '2019-09-13 06:10:00',
                            '2019-09-13 06:15:00',
                            '2019-09-13 06:20:00']
                   },
                   dtype = 'datetime64[ns]'
                  )

df = df.set_index('Date')
_count_of_days = df.resample('D').first().shape[0]
print(_count_of_days)

Upvotes: 0

Emil Wåreus
Emil Wåreus

Reputation: 41

I'm not completely sure what you want to do here. Do you want to count the number of unique days (Monday/Tuesday/...), monthly dates (1-31 ish), yearly dates (1-365), or unique dates (unique days since the dawn of time)?

From a pandas series, you can use {series}.value_counts() to get the number of entries for each unique value, or simply get all unique values with {series}.unique()

import pandas as pd
df = pd.DataFrame(pd.DatetimeIndex(['2016-10-08 07:34:13', '2015-11-15 06:12:48',
               '2015-01-24 10:11:04', '2015-03-26 16:23:53',
               '2017-04-01 00:38:21', '2015-03-19 03:47:54',
               '2015-12-30 07:32:32', '2015-11-10 20:39:36',
               '2015-06-24 05:48:09', '2015-03-19 16:05:19'],
              dtype='datetime64[ns]', freq=None), columns = ["date"])

days (Monday/Tuesday/...):

df.date.dt.dayofweek.value_counts()

monthly dates (1-31 ish)

df.date.dt.day.value_counts()

yearly dates (1-365)

df.date.dt.dayofyear.value_counts()

unique dates (unique days since the dawn of time)

df.date.dt.date.value_counts()

To get the number of unique entries from any of the above, simply add .shape[0]

Upvotes: 1

Shubham Sharma
Shubham Sharma

Reputation: 71689

Consider the sample dates:

sample = pd.date_range('2019-09-12 06:00:00', periods=50, freq='4h')
df = pd.DataFrame({'date': sample})

                  date
0  2019-09-12 06:00:00
1  2019-09-12 10:00:00
2  2019-09-12 14:00:00
3  2019-09-12 18:00:00
4  2019-09-12 22:00:00
5  2019-09-13 02:00:00
6  2019-09-13 06:00:00
...
47 2019-09-20 02:00:00
48 2019-09-20 06:00:00
49 2019-09-20 10:00:00

Use, DataFrame.groupby to group the dataframe on df['date'].dt.date and use the aggregate function GroupBy.size:

count = df.groupby(df['date'].dt.date).size()

# print(count)
date
2019-09-12    5
2019-09-13    6
2019-09-14    6
2019-09-15    6
2019-09-16    6
2019-09-17    6
2019-09-18    6
2019-09-19    6
2019-09-20    3
dtype: int64

Upvotes: 2

Related Questions