<jupyter_start><jupyter_text>Oakland 311 callsThis notebook is part of Assignment 6.<jupyter_code>import pandas as pd
import altair as alt
import requests<jupyter_output>/Users/derekschwabe/.pyenv/versions/3.8.5/envs/lecture_06-3.8.5/lib/python3.8/site-packages/pandas/compat/__init__.py:124: UserWarning: Could not import the lzma module. Your installed Python is incomplete. Attempting to use lzma compression will result in a RuntimeError.
  warnings.warn(msg)<jupyter_text>Step 1: Downloading the dataHere I am downloading the[311 call data](https://data.oaklandca.gov/Infrastructure/Service-requests-received-by-the-Oakland-Call-Cent/quth-gb8e) from the City of Oakland's Open Data Portal.<jupyter_code># url = 'https://data.oaklandca.gov/api/views/quth-gb8e/rows.csv?accessType=DOWNLOAD'
# r = requests.get(url, allow_redirects=False)

# # write the content of the request into a file called `oakland_311.csv`
# open('oakland_311.csv', 'wb').write(r.content)<jupyter_output><empty_output><jupyter_text>Step 2: Importing the Oakland 311 CSV into a Pandas DataframeI'm using the same method we used on the Berkeley 311 data, called `pd.read_csv()` to import the Oakland 311 csv file into a dataframe.I'm making sure to assign the dataframe into a variable. I'll name the dataframe `oakland_311_original`. I will continue using df to refer generically to a dataframe, as we did in lecture.<jupyter_code>oakland_311_original = pd.read_csv('oakland_311.csv')<jupyter_output><empty_output><jupyter_text>Step 3: Viewing the dataHere I am using the df.head() df.and df.tail() to view the first and last five rows of the Oakland 311 dataframe. The purpose of this step is just to familiarize myself with the data structure by taking a first glance. This is especially important for seeing how columns are named, compared to what we saw in the Berkeley 311 dataset.<jupyter_code>oakland_311_original.head()
oakland_311_original.tail()<jupyter_output><empty_output><jupyter_text>Specifying the number of first or last rows I want to seeIf I want to see more rows than the standard 5, I could specify that in the parentheses after df.head() or df.tail(). Here's an example using df.tail() to look at the last 10 rows of the Oakland 311 data:<jupyter_code>oakland_311_original.tail(10)<jupyter_output><empty_output><jupyter_text>Calling the dataframe to view both the first and last five rowsI can also call the dataframe on its own to see both the first 5 rows and the last 5 rows in one command. The elipses in between the first and last 5 rows indicate that all the remaining rows are in between. Here's what that looks like:<jupyter_code>oakland_311_original<jupyter_output><empty_output><jupyter_text>Using df.info()Lastly, I can use df.info() to get additional key information on the dataframe. Perhaps most useful is the column names and what dtype each column is.  Refresher on dtypesAs a refresher of what we learned in class, > **dtype** stands for **data type** and comes from the `numpy` module, which was automatically imporated as part of the pandas module. dt.info() will display `int64` instead of `int`, and `float64` instead of `float`. We also learned in class that `object` is pretty close to a `string`, but not exactly the same, as `object` is a sequence of bytes (machine readable), while `string` is a sequence of characters.<jupyter_code>oakland_311_original.info()<jupyter_output><class 'pandas.core.frame.DataFrame'>
RangeIndex: 852541 entries, 0 to 852540
Data columns (total 16 columns):
 #   Column           Non-Null Count   Dtype  
---  ------           --------------   -----  
 0   REQUESTID        852541 non-null  int64  
 1   DATETIMEINIT     852541 non-null  object 
 2   SOURCE           852541 non-null  object 
 3   DESCRIPTION      852541 non-null  object 
 4   REQCATEGORY      852460 non-null  object 
 5   REQADDRESS       836654 non-null  object 
 6   STATUS           852540 non-null  object 
 7   REFERREDTO       70168 non-null   object 
 8   DATETIMECLOSED   610875 non-null  object 
 9   SRX              836654 non-null  float64
 10  SRY              836654 non-null  float64
 11  COUNCILDISTRICT  809790 non-null  object 
 12  BEAT             812245 non-null  object 
 13  PROBADDRESS      852064 non-null  object 
 14  City             852541 non-null  object 
 15  State            852541 non-null  object 
dtypes: float64(2), int64(1), object(13)
me[...]<jupyter_text>Checking to see if we need to convert any columns for analysis purposesAs was the case with the Berkeley 311 data, the Oakland 311 data has columns for the date that each case opened (DATETIMEINIT, column 1 and DATETIMECLOSED, column 8). Here I will use df.min() and df.max() to verify that these columns are not in fact sorted by actual date and instead by string. Attempting to execute this command will return wrong answers, sorted by string instead of actual date. This is because their Dtype is object. This means I'll need to convert these columns to the datetime format, which I'll do in the next section.<jupyter_code>oakland_311_original['DATETIMEINIT'].min()
oakland_311_original['DATETIMEINIT'].max()<jupyter_output><empty_output><jupyter_text>Step 4: Properly type the Oakland 311 data Copy the original dataframeBefore I convert the 2 columns, I'm going to copy the original dataframe into a new dataframe using `df.copy()`. This way I can still have the original dataframe saved in case I run into any problems later. I'll also be able to compare the edited dataframe with the original dataframe to check for errors. I'll name the copy dataframe simply `oakland_311`.<jupyter_code>oakland_311_copy = oakland_311_original.copy()<jupyter_output><empty_output><jupyter_text>Converting the date opened and closed columns to `datetime`Now that I have the copy, I can convert the two columns from `object` dtype to `datetime` Dtype. I'll convert **DATETIMEINIT** first so I can compare the two columns. The syntax for this conversion is:```pythondf['column_name'] = pd.to_datetime(df['column_name'])```<jupyter_code>oakland_311_copy['DATETIMEINIT'] = pd.to_datetime(oakland_311_copy['DATETIMEINIT'])<jupyter_output><empty_output><jupyter_text>As noted in class, `oakland_311['Date_Opened']` is called a pandas **series**. Now I can use df.head() and df.info() to compare the converted DATETIMEINIT column to the DATETIMECLOSED column that I have not yet converted:<jupyter_code>oakland_311_copy.head()
oakland_311_copy.info()<jupyter_output><class 'pandas.core.frame.DataFrame'>
RangeIndex: 852541 entries, 0 to 852540
Data columns (total 16 columns):
 #   Column           Non-Null Count   Dtype         
---  ------           --------------   -----         
 0   REQUESTID        852541 non-null  int64         
 1   DATETIMEINIT     852541 non-null  datetime64[ns]
 2   SOURCE           852541 non-null  object        
 3   DESCRIPTION      852541 non-null  object        
 4   REQCATEGORY      852460 non-null  object        
 5   REQADDRESS       836654 non-null  object        
 6   STATUS           852540 non-null  object        
 7   REFERREDTO       70168 non-null   object        
 8   DATETIMECLOSED   610875 non-null  datetime64[ns]
 9   SRX              836654 non-null  float64       
 10  SRY              836654 non-null  float64       
 11  COUNCILDISTRICT  809790 non-null  object        
 12  BEAT             812245 non-null  object        
 13  PROBADDRESS      852064 non-null  object        
 14  City             852[...]<jupyter_text>As we can see above, the format of the DATETIMEINIT column has changed, as has its Dtype. It is now a datetime64 Dtype. Now I can convert the DATETIMECLOSED row:<jupyter_code>oakland_311_copy['DATETIMECLOSED'] = pd.to_datetime(oakland_311_copy['DATETIMECLOSED'])<jupyter_output><empty_output><jupyter_text>I'll also take a look again to make sure the change has occurred:<jupyter_code>oakland_311_copy.head()<jupyter_output><empty_output><jupyter_text>We can notice here (and indeed may have noticed before in the original oakland 311 file) that some number of the cells under the DATETIMECLOSED say 'NaN', which indicates that there is no data available. A quick glance across the table shows that this is in the case of cases that have a status of OPEN. Because of this missing data, we will not be able to find the difference between the DATETIMEINIT and DATETIMECLOSED columns for every case, but only for closed cases that have data for both the DATETIMEINIT and DATETIMECLOSED columns.<jupyter_code>oakland_311_copy.info()<jupyter_output><class 'pandas.core.frame.DataFrame'>
RangeIndex: 852541 entries, 0 to 852540
Data columns (total 16 columns):
 #   Column           Non-Null Count   Dtype         
---  ------           --------------   -----         
 0   REQUESTID        852541 non-null  int64         
 1   DATETIMEINIT     852541 non-null  datetime64[ns]
 2   SOURCE           852541 non-null  object        
 3   DESCRIPTION      852541 non-null  object        
 4   REQCATEGORY      852460 non-null  object        
 5   REQADDRESS       836654 non-null  object        
 6   STATUS           852540 non-null  object        
 7   REFERREDTO       70168 non-null   object        
 8   DATETIMECLOSED   610875 non-null  datetime64[ns]
 9   SRX              836654 non-null  float64       
 10  SRY              836654 non-null  float64       
 11  COUNCILDISTRICT  809790 non-null  object        
 12  BEAT             812245 non-null  object        
 13  PROBADDRESS      852064 non-null  object        
 14  City             852[...]<jupyter_text>Using .min() and .max() to now find the correct earliest and latest entriesNow I can find the earliest date and the latest date of both columns, by performing `series.min()` and `series.max()` on these series.<jupyter_code>oakland_311_copy['DATETIMEINIT'].min()
oakland_311_copy['DATETIMEINIT'].max()
oakland_311_copy['DATETIMECLOSED'].min()
oakland_311_copy['DATETIMECLOSED'].max()<jupyter_output><empty_output><jupyter_text>The min and max tell us that the year 2009 is not complete (as of course is also true of 2021 because it's still happening). If I analyze the data by year later, I might consider excluding these years.  Getting the difference of the two date columnsUsing Pandas, I can find the difference of two dates by subtracting one datetime column from another. I'll now create a new column called **CLOSETIME** that shows how long it took for a case to be closed:<jupyter_code>oakland_311_copy['CLOSETIME'] = oakland_311_copy['DATETIMECLOSED'] - oakland_311_copy['DATETIMEINIT']<jupyter_output><empty_output><jupyter_text>The resulting column should not be a `datetime` dtype, but rather a `timedelta` dtype. As a reminder from class, the term "delta" is often used to mean "change." I'll now use df.head() again to make sure the new column has been added:<jupyter_code>oakland_311_copy.head(40)<jupyter_output><empty_output><jupyter_text>I used df.head(40) above to see more rows of the data, as I wanted to make sure that the operation worked for the rows for which there is data available for both DATETIMEINIT and DATETIMECLOSED. We see that the new CLOSETIME column has been created and that the operation seems to have worked for rows with complete data, such as rows 7 and 27.  Step 5: Conducting some basic data analysisNow that I've converted the columns, I can do some interesting operations on them, just like we did with the Berkeley 311 dataset in class.  MeanHere I'll the mean of a column by calling series.mean():<jupyter_code>oakland_311_copy['CLOSETIME'].mean()<jupyter_output><empty_output><jupyter_text>We can see here that the average time the city of Oakland took to close a case was nearly 50 days. This is for the whole dataset, but only includes cases that have closed. We know that more than 200,000 cases are still open, so this finding may not be representative.   Median<jupyter_code>oakland_311_copy['CLOSETIME'].median()<jupyter_output><empty_output><jupyter_text>It looks like the median time was around 5 days, which seems consistent with what we saw in the Berkeley 311 data as well.  Minimum<jupyter_code>oakland_311_copy['CLOSETIME'].min()<jupyter_output><empty_output><jupyter_text>It appears that the minimum amount of time it took to close a case is appearing as a negative number here, which is obviously a mistake that would need to be looked into.  Maximum<jupyter_code>oakland_311_copy['CLOSETIME'].max()<jupyter_output><empty_output><jupyter_text>It appears that the maximum amount of time it took to close a case was more than 4,293 days, or more than 11 years! This may have also been an error.  Sorting the data Now I'm going to sort the data to look at the top 5 cases that took the longest time to resolve. Just like in calss, I'll use the `df.sort_values()` method.Next, I don't want to see the entire dataframe, just the first 10 rows. So I'm going to call `df.head(10)`.<jupyter_code>oakland_311_copy.sort_values(by=['CLOSETIME'], ascending=False).head(5)<jupyter_output><empty_output><jupyter_text>These 5 cases all took thousands of days to resolve. This could be an error...or the beginning of an interesting story idea. Further investigation would be needed to determine which.  Step 6: Cleaning the Data by Checking and Vetting it Checking to see if there is a unique identifier for every row.Just like in class with the Berkeley 311 dataset, I will check to see if `REQUESTID` has a unique ID for every row, which is important for running most anlaysis.I will get a count of the unique values by calling `.nunique()` on that column.<jupyter_code>oakland_311_copy['REQUESTID'].nunique()<jupyter_output><empty_output><jupyter_text>Now I can compare that number with the total number of rows to see if they match. I'll call `len(df)` to find out, which we learned to use earlier to find the length of strings and datastructures:<jupyter_code>len(oakland_311_copy)<jupyter_output><empty_output><jupyter_text>Unlike in the Berkeley 311 dataset, this Oakland 311 data seems to have the a request ID for every row, which is good news! This means we won't need to create new request IDs. Just for practice though, I'll still check to see if there are any duplicates.<jupyter_code>oakland_311_copy[oakland_311.duplicated()]<jupyter_output><empty_output><jupyter_text>As expected, there are no exact duplicates. Now we'll double check to see if there are any duplicates in the REQUEST ID column:<jupyter_code>oakland_311_copy[oakland_311['REQUESTID'].duplicated()]<jupyter_output><empty_output><jupyter_text>Also as expected, there are no duplicates in the REQUESTID column either. Thus, there is no need to create a list of duplicates in the REQUESTID column by subsetting, since that list would be empty.  How many `NaN`s do we have?As mentioned above, this dataset has a lot of `NaN`s in the `DATETIMEINIT` and `DATETIMECLOSED` columns, and now in the new `CLOSETIME` column we created. Let's use df.info() to find out how many:<jupyter_code>oakland_311_copy.info()<jupyter_output><class 'pandas.core.frame.DataFrame'>
RangeIndex: 852541 entries, 0 to 852540
Data columns (total 17 columns):
 #   Column           Non-Null Count   Dtype          
---  ------           --------------   -----          
 0   REQUESTID        852541 non-null  int64          
 1   DATETIMEINIT     852541 non-null  datetime64[ns] 
 2   SOURCE           852541 non-null  object         
 3   DESCRIPTION      852541 non-null  object         
 4   REQCATEGORY      852460 non-null  object         
 5   REQADDRESS       836654 non-null  object         
 6   STATUS           852540 non-null  object         
 7   REFERREDTO       70168 non-null   object         
 8   DATETIMECLOSED   610875 non-null  datetime64[ns] 
 9   SRX              836654 non-null  float64        
 10  SRY              836654 non-null  float64        
 11  COUNCILDISTRICT  809790 non-null  object         
 12  BEAT             812245 non-null  object         
 13  PROBADDRESS      852064 non-null  object         
 14  City[...]<jupyter_text>According to this, it looks like the `DATETIMECLOSE` column is the one that has the most `NaN`s. We could no doubt subset the cases that are missing a `DATETIMECLOSE` date and run some analyses to find out if, for example, most of those cases were opened recently.   AssertWe learned in class that the keyword `assert` is a way to check if the length of the dataframe now matches the number of unique IDs. Although I don't really need to do this, I will do it anyway here for practice.<jupyter_code>assert len(oakland_311_copy) == oakland_311['REQUESTID'].nunique()<jupyter_output><empty_output><jupyter_text>Step 7: Exporting a clean version of the data to a csvI will now use the `df.to_csv()` method to export a clean copy of the csv, which will allow me to instantly import the clean data in a new notebook instead of rerunning the code in this notebook. This is still valuable for me to do, since I did make some important changes, namely changing the dType for the columns 2 and 7 and adding a new column.<jupyter_code>oakland_311_copy.to_csv('oakland_311_clean.csv', index=False)<jupyter_output><empty_output><jupyter_text>Lastly, I'll try removing index=False to see what happens:<jupyter_code># oakland_311_copy.to_csv('oakland_311_clean.csv')<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.06960342236096659, 'fraction_numerical': 0.047173083593479014, 'mean_word_length': 3.7072108843537417, 'pattern_counts': {'":': 0, '<': 120, '<?xml version=': 0, '>': 121, 'https://': 2, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 2, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '30', 'dir': 'jupyter-structured-clean-dedup', 'id': '108750', 'license': 'CC0-1.0', 'n_tokens_mistral': 5775, 'n_tokens_neox': 5092, 'n_words': 2078, 'path': 'oakland_311_calls.ipynb', 'repo_name': 'd-schwabe/oakland-311', 'size': '17298'}