Using pandas and datetime in Jupyter to see during what hours no sales were ever made (on any day)

Viewed 55

So I have sales data that I'm trying to analyze. I have datetime data ["Order Date Time"] and I'd like to see the most common hours for sales but more importantly I'd like to see what minutes have NO sales.

I have been spinning my wheels for a while and I can't get my brain around a solution. Any help is greatly appreciated.

I import the data:

df = pd.read_excel ('Audit Period.xlsx')
print (df)

I clean up the data:

# Remove all columns except `applieddate` and null rows
time_df = df[df["Order Date Time"].notnull()]

# Ensure the index is still sequential
time_df = time_df[["Order Date Time"]].reset_index(drop=True)

# Select the first 10 rows
time_df.head(10)

I convert to datetime and I look at the month totals:

# Convert applieddate to datetime
time_df = time_df.copy()
time_df["Order Date Time"] = time_df["Order Date Time"].apply(pd.to_datetime)
time_df = time_df.set_index(time_df["Order Date Time"])

# Group by month
grouped = time_df.resample("M").count()
time_df = pd.DataFrame({"count": grouped.values.flatten()}, index=grouped.index)
time_df.head(10)

I try to group by hour but that gives me totals per day/hour rather than totals per hour like every order ever at noon, etc:

# Group by hour
grouped = time_df.resample("2H").count()
time_df = pd.DataFrame({"count": grouped.values.flatten()}, index=grouped.index)
time_df.head(10)

And that is where I'm stuck. I'm trying to integrate the below suggestions but can't quite get a grasp on them yet. Any help would be appreciated.

1 Answers

Not sure if this is the most brilliant solution, but I would start by generating a dataframe at the level of detail I wanted, whether that is 1-hour intervals, 5-minute intervals, etc. Then in your df with all the actual data, you could do your grouping as you currently are doing it above. Once it is grouped, join the two. That way you have one dataframe that includes empty rows associated with time spans with no records. The tricky part will just be making sure you have your date and time formatted in a way that it will match and join properly.

Related