We will be using data from the San Francisco crime department to understand the relation between civilian-reported incidents of crime and police-reported incidents of crime. To store and readily access a large amount of data, we will be using GridDB as our database platform.<\/p>\n
Exporting and Import dataset using GridDB:<\/h2>\n
Because GridDB is a highly scalable and in-memory database, it allows parallel processing for higher performance and efficiency. Using GridDB’s python client, we can easily connect GridDB to python and use it to import or export data in real-time.<\/p>\n
Libraries:<\/p>\n
We will be using some python libraries to preprocess and analyze the data visually.<\/p>\n
- \n
- Pandas: Vastly used python library, especially when dealing with data frames.<\/li>\n
- Matplotlib: Primary library to present data visually using basic plots<\/li>\n
- Numpy: Python library to handle data involving mathematical calculations<\/li>\n<\/ol>\n
Preprocessing:<\/p>\n
We have two datasets available for crime data, one containing information about police-reported crimes while the other contains information about the calls received by the police department by people to report those crimes.<\/p>\n
\nincidents = pd.read_csv(\"datasets\/downsample_police-department-incidents.csv\")\ncalls = pd.read_csv(\"datasets\/downsample_police-department-calls-for-service.csv\")<\/code><\/pre>\n<\/div>\nThe datasets are now saved in the form of a data frame into the variables “incidents” and “calls”.<\/p>\n
The datasets contain a few columns that are not significant in our analysis so we would be removing them from the data frames to minimize the memory consumption and maximize the time efficiency of our analysis.<\/p>\n
\nincidents = incidents.drop(['IncidntNum', 'Time', 'X', 'Y', 'Location', 'PdId'], axis = 1)\ncalls = calls.drop(['Crime Id', 'Report Date', 'Offense Date', 'Call Time', 'Call Date Time', \n 'Disposition', 'Address', 'City', 'State', 'Agency Id','Address Type', \n 'Common Location'], axis = 1)<\/code><\/pre>\n<\/div>\nThese are the columns remaining in the data frames that we would be using in our analysis:<\/p>\n
Incident dataframe:<\/p>\n
- \n
- Category: The type of crime<\/li>\n
- Descript: Description of the crime incident that occurred<\/li>\n
- DayOfWeek: The day on which the crime took place<\/li>\n
- Date: The date on which the crime took place<\/li>\n
- PdDistrict: The district of San Francisco where the crime took place<\/li>\n
- Resolution: The action taken against the crime<\/li>\n
- Address: Exact location where the crime took place<\/li>\n<\/ol>\n
Calls dataframe:<\/p>\n
- \n
- Descript: Description of the crime as described by the reporter<\/li>\n
- Date: The date of receiving the reporting call.<\/li>\n<\/ol>\n
We will also need to introduce a primary key column in both of the dataframes so that we can track each row individually. We will reset the index and rename it to ‘ID’.<\/p>\n
\nincidents.reset_index(drop=True, inplace=True)\nincidents.index.name = 'ID'\nincidents.dropna(inplace = True)\n \n \ncalls.reset_index(drop=True, inplace=True)\ncalls.index.name = 'ID'\ncalls.dropna(inplace = True)<\/code><\/pre>\n<\/div>\nAfter completing our cleaning process, we will save the dataframes as CSV files on our local drive that would be then uploaded to GridDB.<\/p>\n
\n#save it into csv\nincidents.to_csv(\"preprocessed_incidents.csv\")\ncalls.to_csv(\"preprocessed_calls.csv\")<\/code><\/pre>\n<\/div>\nExporting Dataset into GridDB:<\/p>\n
The next step is to upload the data to GridDB. For that, we will read the preprocessed CSV file using pandas and save it to individual dataframes.<\/p>\n
\n#read the cleaned data from csv\nincident_processed = pd.read_csv(\"preprocessed_incidents.csv\")\ncalls_processed = pd.read_csv(\"preprocessed_calls.csv\")<\/code><\/pre>\n<\/div>\nWe will create two different containers to pass our column info to the GridDB to be able to generate the design of the two databases before inserting the row information.<\/p>\n
\n#Create container \nincident_container = \"incident_container\"\n\n# Create containerInfo\nincident_containerInfo = griddb.ContainerInfo(incident_container,\n [[\"ID\", griddb.Type.INTEGER],\n [\"Category\", griddb.Type.STRING],\n [\"Descript\", griddb.Type.STRING],\n [\"DayOfWeek\", griddb.Type.STRING],\n [\"Date\", griddb.Type.TIMESTAMP],\n [\"PdDistrict\", griddb.Type.STRING],\n [\"Resolution\", griddb.Type.STRING]],\n griddb.ContainerType.COLLECTION, True)\n \nincident_columns = gridstore.put_container(incident_containerInfo)<\/code><\/pre>\n<\/div>\n\n#Create container \ncalls_container = \"calls_container\"\n\n# Create containerInfo\ncalls_containerInfo = griddb.ContainerInfo(calls_container,\n [[\"ID\", griddb.Type.INTEGER],\n [\"Descript\", griddb.Type.STRING],\n [\"Date\", griddb.Type.TIMESTAMP]],\n griddb.ContainerType.COLLECTION, True)\n \ncalls_columns = gridstore.put_container(calls_containerInfo)<\/code><\/pre>\n<\/div>\nFinally, we will insert the rows into the schema created.<\/p>\n
\n# Put rows\nincident_columns.put_rows(incident_processed)\n \nprint(\"Data Inserted using the DataFrame\")<\/code><\/pre>\n<\/div>\n\n# Put rows\ncalls_columns.put_rows(calls_processed)\n \nprint(\"Data Inserted using the DataFrame\")<\/code><\/pre>\n<\/div>\nImporting Dataset from GridDB:<\/p>\n
We will use TQL to query the data from the GridDB database that is similar to SQL commands. Before fetching the data, we would create the containers to extract rows of data before saving it into dataframes.<\/p>\n
\n# Define the container names\n incident_container = \"incident_container\"\n\n # Get the containers\n obtained_data = gridstore.get_container(incident_container)\n \n # Fetch all rows - language_tag_container\n query = obtained_data.query(\"select *\")\n \n rs = query.fetch(False)\n print(f\"{incident_container} Data\")<\/code><\/pre>\n<\/div>\n\n# Define the container names\n call_container = \"call_container\"\n\n # Get the containers\n obtained_data = gridstore.get_container(call_container)\n \n # Fetch all rows - language_tag_container\n query = obtained_data.query(\"select *\")\n \n rs = query.fetch(False)\n print(f\"{call_container} Data\")<\/code><\/pre>\n<\/div>\nThe last step for our extraction of data would be to query the rows in order of the column info and save it into the dataframes to use for data visualization and analysis.<\/p>\n
\n# Iterate and create a list\n retrieved_data= []\n while rs.has_next():\n data = rs.next()\n retrieved_data.append(data)\n\n# Convert the list to a pandas data frame\n incidents = pd.DataFrame(retrieved_data,\n columns=['ID', 'Category', 'Descript', 'DayOfWeek', 'Date', \n 'PdDistrict', 'Resolution','Address'])<\/code><\/pre>\n<\/div>\n\n# Iterate and create a list\n retrieved_data= []\n while rs.has_next():\n data = rs.next()\n retrieved_data.append(data)\n\n # Convert the list to a pandas data frame\n calls = pd.DataFrame(retrieved_data,\n columns=['ID', 'Descript', 'Date'])<\/code><\/pre>\n<\/div>\nOur cleaned data is now available for data analysis saved as two dataframes, “incidents” and “calls”.<\/p>\n
Data Analysis and Visualization<\/h2>\n
We will begin our analysis by introducing a column consisting of Null or zero values that will help us in keeping a count of our grouping data.<\/p>\n
\nincidents['NumOfIncidents'] = np.zeros(len(incidents))\ncalls['NumOfCalls'] = np.zeros(len(calls))<\/code><\/pre>\n<\/div>\nThe crimes reported belong to different categories while reported by the police themselves. Let’s dive into the data and take a look at some major crimes that take place in San Francisco.<\/p>\n
\nincident_categories = incidents.groupby([\"Category\"]).count()\nn_largest = incident_categories['NumOfIncidents'].nlargest(n=10)\nincident_categories.reset_index(inplace = True)\nincident_categories = incident_categories[[\"Category\",\"NumOfIncidents\"]]\nn_largest.plot(kind = 'barh')\nplt.show()<\/code><\/pre>\n<\/div>\n![\"\"](\"https:\/\/griddb.net\/wp-content\/uploads\/2022\/04\/plot1.png\")
<\/a><\/p>\nWe can see that most of the crimes that take place in San Francisco are related to Theft. Taking this investigation further, we can also explore the areas where this particular crime takes place mostly.<\/p>\n
\nTheft_address = incidents[incidents['Category']==\"LARCENY\/THEFT\"]\nTheft_address = Theft_address.groupby([\"Address\"]).count()\nn_largest = Theft_address['NumOfIncidents'].nlargest(n=10)\nTheft_address.reset_index(inplace = True)\nTheft_address = Theft_address[[\"Address\",\"NumOfIncidents\"]]\nn_largest.plot(kind = 'barh')\nplt.show()<\/code><\/pre>\n<\/div>\n![\"\"](\"https:\/\/griddb.net\/wp-content\/uploads\/2022\/04\/plot2.png\")
<\/a><\/p>\nWe will now explore the daily calls and daily incidents reported by the police individually to get an insight into how the values of daily incidents and daily calls are changing over time. By removing the Date column from the key, the result is a long and narrow data frame with multiple rows for each date observation in both cases.<\/p>\n
\ndaily_incidents = incidents.groupby([\"Date\"]).count()\ndaily_incidents.reset_index(inplace = True)\ndaily_incidents = daily_incidents[[\"Date\",\"NumOfIncidents\"]]\n\n\ndaily_calls = calls.groupby([\"Date\"]).count()\ndaily_calls.reset_index(inplace = True)\ndaily_calls = daily_calls[[\"Date\",\"NumOfCalls\"]]<\/code><\/pre>\n<\/div>\nNext, we will merge both dataframes on the Date column to get the values of calls and incidents over a particular date in a single dataframe.<\/p>\n
\nshared_dates = pd.merge(daily_incidents, daily_calls, on='Date', how = 'inner')<\/code><\/pre>\n<\/div>\nLet’s plot both the columns on a scatterplot and apply a linear regression model to get a line of the pattern shown by our data. For that we will fit or data to the regression model first, plot a scatter plot using the data points, and then plot a line using the data points obtained by our model and saved as “Treg1”.<\/p>\n
\nd1 = np.polyfit(shared_dates.index,shared_dates['NumOfCalls'],1)\nf1 = np.poly1d(d1)\nshared_dates.insert(3,'Treg1',f1(shared_dates.index))\nax = shared_dates.plot.scatter(x = 'Day', y='NumOfCalls')\nshared_dates.plot(y='Treg1',color='Red',ax=ax)<\/code><\/pre>\n<\/div>\n
![\"\"](\"https:\/\/griddb.net\/wp-content\/uploads\/2022\/04\/plot3.png\")
<\/a><\/p>\n![\"\"](\"https:\/\/griddb.net\/wp-content\/uploads\/2022\/04\/plot4.png\")
<\/a><\/p>\n