Using Python (Pandas) groupby and datetime to sum values in the morning and afternoon for different markets

Asked Jun 30 '21 at 20:02

Active Jun 30 '21 at 20:02

Viewed 316 times

I have a datetime column that looks like the following:

And a "market" column that has sting values such as ['NYC', LA', 'HOU', etc.] I also have a column "ID" that has random Order IDs. I am trying to look at each market by day (data goes back 5 months) and count the number of IDs that occur before noon and the number that occur after noon. My first step was to groupby market and date, but I don't know how to 1) break up date into day and hour (so I can group it further) or 2) shortcut that and find a way to just count Order IDs for everyday before noon and after noon (will need two new columns)

My attempt is below... the issue is that it's keeping the hhmmss on the date column.

I've also tried to resample by D and H, but that isn't doing the trick for me yet...

Can anyone help me groupby market and day, then count Order IDs that occur before noon in one field and after noon in a second field?

asked Jun 30 '21 at 20:02

Kyle Reagan

1

Create an hour column using `df['date'].dt.hour` – ifly6 Jun 30 '21 at 20:36
I use this to get a numerical value for hour -> `output_df['hour'] = pd.Categorical(output_df['DELIVERY_DUE_BY_CUSTOM_REFERENCE'].dt.hour, categories=range(24))`, but I am still struggling to sum by hour for a given day – Kyle Reagan Jun 30 '21 at 20:45
1

Map them to morning and afternoon then use that as one of your groupby keys – ifly6 Jun 30 '21 at 20:46
Okay, before that step, how can I group by day? Do I need to split my datetime column to remove hhmmss? – Kyle Reagan Jun 30 '21 at 20:48
1

Create a column using `df['date'].dt.floor('D')` – ifly6 Jun 30 '21 at 20:51
is the datetime field normalized to utc or are you also dealing with time zone differences and does it matter? – itprorh66 Jun 30 '21 at 21:09
normalized to utc – Kyle Reagan Jun 30 '21 at 21:47

Using Python (Pandas) groupby and datetime to sum values in the morning and afternoon for different markets

0 Answers0