Question

Python question: the time is from 2016-01-01 ~ 2018-12-31 such as the columns "Month", "Day", "Year"

I need to count how many rows data for each day, and each Borough, such as the column "Borough". The Borough includes: QUEENS, BROOKLYN, BRONX, MANHATTAN, STATEN ISLAND, all together is 5 different Boroughs.

Many Thanks!

Unnamed: 0 Created Date Incident Zip Complaint Type Descriptor Community Board Borough Latitude Longitude Month Day Year 0 19661 01/01/2016 01:00:00 PM 10309.0 Missed Collection (All Materials) 2R Bulk-Missed Recy Collection 03 STATEN ISLAND STATEN ISLAND 40.546680 -74.217776 1 1 2016 1 19662 01/01/2016 01:00:44 AM 10016.0 Noise - Residential Loud Music/Party 06 MANHATTAN MANHATTAN 40.744731 -73.975788 1 1 2016 2 19663 01/01/2016 01:00:52 AM 10017.0 Noise - Commercial Loud Music/Party 06 MANHATTAN MANHATTAN 40.752467 -73.970263 1 1 2016 3 19664 01/01/2016 01:01:00 PM 10307.0 Missed Collection (All Materials) 1RG Missed Recycling Paper 03 STATEN ISLAND STATEN ISLAND 40.499313 -74.239750 1 1 2016 4 19665 01/01/2016 01:01:14 AM 11365.0 Noise - Residential Banging/Pounding 08 QUEENS QUEENS 40.730643 -73.810888 1 1 2016 5 19666 01/01/2016 01:01:18 PM 11213.0 HEAT/HOT WATER ENTIRE BUILDING 08 BROOKLYN BROOKLYN 40.670351 -73.926591 1 1 2016 6 19667 01/01/2016 01:01:24 PM 11423.0 For Hire Vehicle Complaint Driver Complaint 12 QUEENS QUEENS 40.711030 -73.757487 1 1 2016

Answer 1

my sample data set

import pandas as pd df=pd. read_cs ("Records.csv") df Unnamed: Created Date Incident Zip Complaint Type Description Community Board Borough Latitude Longitude Month Day Year 0 19661 01/01/2016 01:00:00 PM 10309.0 2R Bulk-Missed Recy Collection 03 STATEN ISLAND STATEN ISLAND 40.546680 -74.217776 1 1 2016 Missed Collection All Materials) Noise Residential 1 19662 01/01/2016 01:00:44 AM 10016.0 Loud Music/Party 06 MANHATTAN MANHATTAN 40.744731 -73.975788 1 1 2016 2 19663 01/01/2016 01:00:52 AM 10017.0 Noise Residential Loud Music/Party 06 MANHATTAN MANHATTAN 40.752467 -73.970263 1 1 2016 3 19664 01/01/2016 01:01:00 PM 10307.0 Missed Collection All Materials) 1R Missed Recycling Paper 03 STATEN ISLAND STATEN ISLAND 40.40.499313 -74.239750 1 1 2016 4 19665 01/01/2016 01:01:14 AM 11365.0 Noise Residential Banging/Pounding 08 QUEENS QUEENS 40.730643 -73.810888 1 1 2016 un 19666 01/01/2016 01:01:18 AM 11213.0 HEAT/HOT WATER ENTIRE BUILDING 08 BROOKLYN BROOKLYN 40.670351 -73.926591 1 2016 6 19667 01/01/2016 01:01:24 AM 11423.0 Driver Complaint 12 QUEENS QUEENS 40.711030 -73.757487 1 1 2016 For Hire Vehicle Complaint Missed Collection(All Materials) 7 19668 01/02/2016 01:00:00 PM 10309.0 2R Bulk-Missed Recy Collection 03 STATEN ISLAND STATEN ISLAND 40.546680 -74.217776 1 2 2016

code and output select appropriate one as you need

Records.csv х Untitled.ipynb a + X O ► с Code Download GitHub Binder Python 3 [33]: complaintcount=df.groupby(['Month', 'Day', 'Year', 'Borough']['Complaint Type'].count() print(complaintCount) 2016 Month Day Year Borough 1 1 BROOKLYN MANHATTAN QUEENS STATEN ISLAND 2 2016 BROOKLYN MANHATTAN QUEENS STATEN ISLAND Name: Complaint Type, dtype: int64 1 2 2 2 1 2 2 2 [34]: complaintCountdf.groupby ( ["Month', 'Day', 'Year', 'Borough' ][ 'Complaint Type'].count().reset_index() complaintCount.rename (columns={'Complaint Type':'Complaint Count'}, inplace='True') print(complaintCount) Month Day Year Borough Complaint Count 1 1 2016 BROOKLYN 1 1 1 1 2016 MANHATTAN 2 2 1 1 2016 QUEENS 2 1 1 2016 STATEN ISLAND 2 1 2 2016 BROOKLYN 1 5 1 2 2016 MANHATTAN 2 6 1 2 2016 QUEENS 2 7 1 2 2016 STATEN ISLAND 2 3 4 [ ]:

Code

import pandas as pd
#reading my sample dataset
df=pd.read_csv("Records.csv")

#simple group by with count 
complaintCount=df.groupby(['Month','Day','Year','Borough'])['Complaint Type'].count()
print(complaintCount)
#Group by with changing count column name to 'Complaint Count'
complaintCount=df.groupby(['Month','Day','Year','Borough'])['Complaint Type'].count().reset_index()
complaintCount.rename(columns={'Complaint Type':'Complaint Count'},inplace='True')
print(complaintCount)