[Python] How to efficiently filter specific values from a dataset and group those filtered...

I have a large dataset (5m+ records) of NYC crimes. I want to select 5 crimes from the column 'OFNS_DESC' and group the crimes according to the number of occurrences within a month so that I can plot a time series, in a more efficient way.

sample dataset

I currently have the below. The issue is that it takes quite a while to compute because, for each value, it goes through the entire dataset and it does the same for the grouping. I want to make this process more efficient

# convert ARREST_DATE format to YYYY/MM
arrest_ym = df["ARREST_DATE"].dt.to_period('M')

# filter out crime types
dngr_drug = df[df["OFNS_DESC"] == "DANGEROUS DRUGS"]
aslt3 = df[df["OFNS_DESC"] == "ASSAULT 3 & RELATED OFFENSES"]
pt_lrcy = df[df["OFNS_DESC"] == "PETIT LARCENY"]
flny_aslt = df[df["OFNS_DESC"] == "FELONY ASSAULT"]
dngr_wpns = df[df["OFNS_DESC"] == "DANGEROUS WEAPONS"]

# group and count
dngr_drug_gc = dngr_drug.groupby(arrest_ym)["OFNS_DESC"].count()
aslt3_gc = aslt3.groupby(arrest_ym)["OFNS_DESC"].count()
pt_lrcy_gc = pt_lrcy.groupby(arrest_ym)["OFNS_DESC"].count()
flny_aslt_gc = flny_aslt.groupby(arrest_ym)["OFNS_DESC"].count()
dngr_wpns_gc = dngr_wpns.groupby(arrest_ym)["OFNS_DESC"].count()


Below is the code for my plot. I believe it would be affected based on the changes made to the above.

# multi-line plot
x_labels = dngr_drug_gc.index.astype(str)

plt.figure(figsize = (20, 10))
plt.plot(dngr_drug_gc.index.astype(str), dngr_drug_gc.values, 'b-', label = "Dangerous Drugs")
plt.plot(aslt3_gc.index.astype(str), aslt3_gc.values, 'g-', label = "Assault 3 & Related Offenses")
plt.plot(pt_lrcy_gc.index.astype(str), pt_lrcy_gc.values, 'r-', label = "Petit Larceny")
plt.plot(flny_aslt_gc.index.astype(str), flny_aslt_gc.values, 'c-', label = "Felony Assault")
plt.plot(dngr_wpns_gc.index.astype(str), dngr_wpns_gc.values, 'm-', label = "Dangerous Weapons")

plt.xticks(x_labels[::6], rotation=45)

plt.xlabel("Month")
plt.ylabel("Crime Count")
plt.title("Monthly Crime Frequency")
plt.legend(loc = "best")


My results are fine but I just want to optimise the code. One way I saw was to do the code below but I don't know how to proceed from there.

crime_select = ["DANGEROUS DRUGS", "ASSAULT 3 & RELATED OFFENSES", "PETIT LARCENY", "FELONY ASSAULT", "DANGEROUS WEAPONS"]
filtered_crime = df[df["OFNS_DESC"].isin(crime_select)]


Please bear in mind that I am absolutely new to coding so any explanations would be very helpful. Thanks.

result plot

Continue reading...