Does anyone know how i can group this dataframe by month?

user_18338223 0
Viewed 21

Im looking to group this dataframe by month and then find the month with the most accidents, any idea how i would do this?

Tried a few things using the group_by function but think i need to sort the dates into months first

small part of dataframe;

   Accidents    Longitude   Latitude    Date        Day_of_Week  Time   
0   124         -0.153842   51.508057   18/02/2019        2     17:50   
1   234         -0.127949   51.436208   15/02/2019        3     21:45   
2   222         -0.124193   51.526795   01/03/2019        3     01:50   
3   188         -0.191044   51.546387   01/07/2019        3     01:20   
4   300         -0.200064   51.541121   01/11/2019        3     00:40
python
3 Answers

Try this:

df['Date'] = pd.to_datetime(df['Date'], dayfirst=True)
df.groupby(df['Date'].dt.strftime('%B'))['Accidents'].sum().sort_values(ascending=False)
user_154001630

I think this could work:

df.groupby(df.Date.str.split("/", expand=True)[1]).Accidents.sum()
user_117284880

You will first need to extract the month from your Date column, there are various ways to do that but if you consider it as string, you can do it by:

df["month"] = df.Date.apply(lambda s: int(s.split("/")[1]))

   Accidents  Longitude   Latitude        Date  Day_of_Week   Time  month
0        124  -0.153842  51.508057  18/02/2019            2  17:50      2
1        234  -0.127949  51.436208  15/02/2019            3  21:45      2
2        222  -0.124193  51.526795  01/03/2019            3  01:50      3
3        188  -0.191044  51.546387  01/07/2019            3  01:20      7
4        300  -0.200064  51.541121  01/11/2019            3  00:40     11

Then you can use that column for groupby and aggregation:

df.groupby("month").sum()[["Accidents"]].sort_values("Accidents", ascending=False)

       Accidents
month
2            358
11           300
3            222
7            188
user_32169800
Related