summing the number of occurrences per day pandas

Question

I have a data set like so in a pandas dataframe:

                                  score timestamp                                  2013-06-29 00:52:28+00:00        -0.420070 2013-06-29 00:51:53+00:00        -0.445720 2013-06-28 16:40:43+00:00         0.508161 2013-06-28 15:10:30+00:00         0.921474 2013-06-28 15:10:17+00:00         0.876710 

I need to get counts for the number of measurements, that occur so I am looking for something like this:

                                    count    timestamp    2013-06-29                       2    2013-06-28                       3 

I do not care about the sentiment column I want the count of the occurrences per day.

Answer 1

If your timestamp index is a DatetimeIndex:

import io import pandas as pd content = '''\ timestamp  score 2013-06-29 00:52:28+00:00        -0.420070 2013-06-29 00:51:53+00:00        -0.445720 2013-06-28 16:40:43+00:00         0.508161 2013-06-28 15:10:30+00:00         0.921474 2013-06-28 15:10:17+00:00         0.876710 '''  df = pd.read_table(io.BytesIO(content), sep='\s{2,}', parse_dates=[0], index_col=[0])  print(df) 

so df looks like this:

                        score timestamp                     2013-06-29 00:52:28 -0.420070 2013-06-29 00:51:53 -0.445720 2013-06-28 16:40:43  0.508161 2013-06-28 15:10:30  0.921474 2013-06-28 15:10:17  0.876710  print(df.index) # <class 'pandas.tseries.index.DatetimeIndex'> 

You can use:

print(df.groupby(df.index.date).count()) 

which yields

            score 2013-06-28      3 2013-06-29      2 

---

Note the importance of the parse_dates parameter. Without it, the index would just be a pandas.core.index.Index object. In which case you could not use df.index.date.

So the answer depends on the type(df.index), which you have not shown...