Count occurrences of a string in a Dataframe with a DateTimeIndex

Question

I have a DataFrame with a time series like so:

timestamp   v            IceCreamOrder  Location
2018-01-03  02:21:16     Chocolate      South
2018-01-03  12:41:12     Vanilla        North
2018-01-03  14:32:15     Strawberry     North
2018-01-03  15:32:15     Strawberry     North
2018-01-04  02:21:16     Strawberry     North
2018-01-04  02:21:16     Rasberry       North
2018-01-04  12:41:12     Vanilla        North
2018-01-05  15:32:15     Chocolate      North

And I want to get the counts like this:

timestamp   strawberry  chocolate
1/2/14      0           1
1/3/14      2           0
1/4/14      1           0
1/4/14      0           0
1/4/14      0           0
1/5/14      0           1

Since this is time series data, I've been storing the timestamp in pandas datetimeindex format.

I started by trying to get the counts for 'strawberry'. I ended up with this code that doesn't work.

mydf = (inputdf.set_index('timestamp').groupby(pd.Grouper(freq = 'D'))['IceCreamOrder'].count('Strawberry'))

Which results in an error:

TypeError: count() takes 1 positional argument but 2 were given

Any help would be greatly appreciated.

Do you want the counts for just `chocolate` and `strawberry`? Or all types — user3483203, Aug 20 '18 at 05:04
@lespaul - Thanks for new data, there are 3 times `1/4/14` or typo in output? — jezrael, Aug 20 '18 at 05:32

jezrael · Accepted Answer · 2018-08-20T05:09:59.353

Use eq (==) for compare column by string and aggregate sum for count True values, because Trues are processes like 1s:

#convert to datetimes if necessary
inputdf['timestamp'] = pd.to_datetime(inputdf['timestamp'], format='%m/%d/%y')
print (inputdf)
   timestamp IceCreamOrder Location
0 2018-01-02     Chocolate    South
1 2018-01-03       Vanilla    North
2 2018-01-03    Strawberry    North
3 2018-01-03    Strawberry    North
4 2018-01-04    Strawberry    North
5 2018-01-04      Rasberry    North
6 2018-01-04       Vanilla    North
7 2018-01-05     Chocolate    North

mydf = (inputdf.set_index('timestamp')['IceCreamOrder']
               .eq('Strawberry')
               .groupby(pd.Grouper(freq = 'D'))
               .sum())
print (mydf)
timestamp
2018-01-02    0.0
2018-01-03    2.0
2018-01-04    1.0
2018-01-05    0.0
Freq: D, Name: IceCreamOrder, dtype: float64

If want count all types add column IceCreamOrder to groupby and aggregate GroupBy.size:

mydf1 = (inputdf.set_index('timestamp')
               .groupby([pd.Grouper(freq = 'D'), 'IceCreamOrder'])
               .size())
print (mydf1)
timestamp   IceCreamOrder
2018-01-02  Chocolate        1
2018-01-03  Strawberry       2
            Vanilla          1
2018-01-04  Rasberry         1
            Strawberry       1
            Vanilla          1
2018-01-05  Chocolate        1
dtype: int64

mydf1 = (inputdf.set_index('timestamp')
               .groupby([pd.Grouper(freq = 'D'),'IceCreamOrder'])
               .size()
               .unstack(fill_value=0))
print (mydf1)
IceCreamOrder  Chocolate  Rasberry  Strawberry  Vanilla
timestamp                                              
2018-01-02             1         0           0        0
2018-01-03             0         0           2        1
2018-01-04             0         1           1        1
2018-01-05             1         0           0        0

If all datetimes have no times:

mydf1 = (inputdf.groupby(['timestamp', 'IceCreamOrder'])
                .size()
                .unstack(fill_value=0))
print (mydf1)
IceCreamOrder  Chocolate  Rasberry  Strawberry  Vanilla
timestamp                                              
2018-01-02             1         0           0        0
2018-01-03             0         0           2        1
2018-01-04             0         1           1        1
2018-01-05             1         0           0        0

user3483203 · Answer 2 · 2018-08-20T05:14:54.123

Using pivot_table:

df.pivot_table(
    index='timestamp', columns='IceCreamOrder', aggfunc='size'
).fillna(0).astype(int)

IceCreamOrder  Chocolate  Rasberry  Strawberry  Vanilla
timestamp
2018-01-02             1         0           0        0
2018-01-03             0         0           2        1
2018-01-04             0         1           1        1
2018-01-05             1         0           0        0

Or crosstab:

pd.crosstab(df.timestamp, df.IceCreamOrder)

IceCreamOrder  Chocolate  Rasberry  Strawberry  Vanilla
timestamp
2018-01-02             1         0           0        0
2018-01-03             0         0           2        1
2018-01-04             0         1           1        1
2018-01-05             1         0           0        0

if your timestamp column has times, simply remove them before using these operations using dt.date (if you don't want to modify the column, perhaps create a new Series to use for pivoting):

df.timestamp = df.timestamp.dt.date

Count occurrences of a string in a Dataframe with a DateTimeIndex

2 Answers2