CODEWITHSUNDEEP
pythonpandas
FaCoffee
FaCoffee

Reputation: 7909

Pandas: grouping values by date-time index

I have the following dataframe:

                     Values
Date_Time

2016-01-04 12:00:00  778000
2016-01-04 18:00:00  35
2016-02-04 04:00:00  45
2016-02-04 11:00:00  47
2016-03-04 07:00:00  51

I want to count how many occurrences I have for each day:

                    Occurrences
Date_Time

2016-01-04           2
2016-02-04           2
2016-03-04           1

And I am trying to achieve it by doing: df2=df.groupby.index.count_values(), but the result is not what I wish it was. What am I doing wrong?

Upvotes: 0

Views: 55

Answers (1)

jezrael
jezrael

Reputation: 862511

You are close, there are 2 possible solutions - convert DatetimeIndex to dates by DatetimeIndex.date - then is necessary converting to Series or remove times by DatetimeIndex.floor - get DatetimeIndex again:

s = pd.Series(df.index.date).value_counts()
print (s)
2016-01-04    2
2016-02-04    2
2016-03-04    1
dtype: int64

print (s.index)
Index([2016-01-04, 2016-02-04, 2016-03-04], dtype='object')

print (type(s.index[0]))
<class 'datetime.date'>

s = df.index.floor('d').value_counts()
print (s)
2016-01-04    2
2016-02-04    2
2016-03-04    1
dtype: int64

print (s.index)
DatetimeIndex(['2016-02-04', '2016-01-04', '2016-03-04'], dtype='datetime64[ns]', freq=None)

Last is possible create one column DataFrame with set index names use Series.to_frame and rename_axis:

s = df.index.floor('d').value_counts().to_frame('Occurrences').rename_axis('Date_Time')
print (s)
            Occurrences
Date_Time              
2016-02-04            2
2016-01-04            2
2016-03-04            1

Upvotes: 1

Related Questions