2

I have formatted my data through pandas in such a way that I get the number of orders that are placed in every 2 hour period for the past 3 months. I need to get the total amount of order that is placed for each timeslot based on the day of the week.


Converted    OrderCount day_of_week
2/1/2019 0:00   2   Friday
2/1/2019 2:00   0   Friday
2/1/2019 4:00   0   Friday
2/1/2019 6:00   0   Friday
2/1/2019 8:00   0   Friday
2/1/2019 10:00  1   Friday
2/1/2019 12:00  2   Friday
2/1/2019 14:00  3   Friday
2/1/2019 16:00  5   Friday
2/2/2019 0:00   2   Saturday
2/2/2019 2:00   1   Saturday
2/2/2019 4:00   0   Saturday
2/2/2019 6:00   0   Saturday
2/2/2019 8:00   0   Saturday

Where Converted is my index and OrderCount column contains the count of orders by timeslot(2hr)

I have tried the following code

df.groupby([df.index.hour, df.index.weekday]).count()

But this give totally different result

What is want is the total number of orders placed on a particular day based on the timeslot

Ex

Converted       OrderCount   day_of_week
2/1/2019 0:00   2         Friday
2/8/2019 0:00   5         Friday
2/2/2019 4:00   1         Saturday
2/9/2019 4:00   10        Saturday

The Output Should be

TimeSlot OrderCount day_of_week
0:00     7           Friday
4:00     11          Saturday

Where total 7 is (5+2) and 11 is (1+11)

Mohamed rafiq
  • 33
  • 2
  • You data only shows 16 order, but your expected results has 18. – Scott Boston Apr 16 '19 at 17:03
  • Pretty sure you want `sum()` not `count()`. But you need to post your data in a reproducible format for us to help you. – Ben G Apr 16 '19 at 17:05
  • Hi @ScottBoston and @ Ben G, I just gave an example of how the date should be. Please don't look at the number of orders. Please look at the date also in the example table, the date is different but the timeslot is the same. – Mohamed rafiq Apr 16 '19 at 18:19
  • Did you have a look at https://stackoverflow.com/questions/39922986/pandas-group-by-and-sum – Kacperito Apr 16 '19 at 19:54
  • Try `df.groupby([df.index.strftime('%H:%M'),'day_of_week']).sum().reset_index()` – Scott Boston Apr 16 '19 at 20:17
  • Thanks, @ScottBoston, your solution is pretty much the same as I was able to do. I did `df1["time"] = df.index.hour ` and grouped them using `df1 = df1.groupby(["day_of_week","time"]).sum()` – Mohamed rafiq Apr 17 '19 at 05:56

0 Answers0