How to count frequencies per datetime and category in python

September 6, 2022

I have a following problem.

df_dict = {"uziv_id" : [1, 1, 2, 3], "datetime" : ["2022-09-05 07:25:12", "2022-09-05 07:25:52", "2022-09-05 07:42:12", "2022-09-05 07:43:12"],
           "expedice" : ["A", "A", "B", "A"]}

df = pd.DataFrame(df_dict)

I need to count uziv_id per 10 minute interval and per expedice. I try this:


df["time"] = pd.to_datetime(df["datetime"])

df = (
    df.groupby(pd.Grouper(freq="10Min", key="time"), "exp")["uziv_id"]
    .nunique()
    .reset_index(name="count")
)
df = df.rename(columns={"time": "interval start"})
df.insert(1, "interval end", df["interval start"] + pd.Timedelta("10Min"))

But I got an error ValueError: No axis named exp for object type DataFrame. What do I do wrong please?

>Solution :

Use list [] in groupby:

df["time"] = pd.to_datetime(df["datetime"])

df = (
    df.groupby([pd.Grouper(freq="10Min", key="time"), "expedice"])["uziv_id"]
    .nunique()
    .reset_index(name="count")
)
df = df.rename(columns={"time": "interval start"})
df.insert(1, "interval end", df["interval start"] + pd.Timedelta("10Min"))
print (df)
       interval start        interval end expedice  count
0 2022-09-05 07:20:00 2022-09-05 07:30:00        A      1
1 2022-09-05 07:40:00 2022-09-05 07:50:00        A      1
2 2022-09-05 07:40:00 2022-09-05 07:50:00        B      1