<jupyter_start><jupyter_text>Berkeley 311 calls, part II - Lecture notes  Import modulesAs usual, we'll import modules at the top of the notebook. This time, we don't need the `requests` module since we're not going to download the data from the Internet.<jupyter_code>import pandas as pd
import altair as alt<jupyter_output><empty_output><jupyter_text>Import dataWe did a lot of work last week cleaning up the Berkeley 311 calls. We don't need to redo that work since we exported a clean version called `berkeley_311_clean.csv`. Remember that a `csv` file is just a plain-text file. That means that the file, just as it is, cannot retain the **dtype** of a column.So this time when we import the data, we'll want to make sure that we set up the dtypes we do know and parse `datetime` dtypes correctly.I also want to set **Case_ID** to an `object` dtype instead of an `int` dtype. Why would I want to do this? You can't operate on **Case_ID** like it's a number. You aren't going to add up the Case_IDs. So it's better to import that column as an `object`.<jupyter_code>berkeley_311 = pd.read_csv('berkeley_311_clean.csv', 
    dtype={
        'Case_ID': object,
    },
    parse_dates=['Date_Opened', 'Date_Closed', 'Close_Time']
)
berkeley_311.head()
berkeley_311.info()<jupyter_output><class 'pandas.core.frame.DataFrame'>
RangeIndex: 588078 entries, 0 to 588077
Data columns (total 17 columns):
 #   Column               Non-Null Count   Dtype         
---  ------               --------------   -----         
 0   Case_ID              588078 non-null  object        
 1   Date_Opened          588078 non-null  datetime64[ns]
 2   Case_Status          588078 non-null  object        
 3   Date_Closed          561748 non-null  datetime64[ns]
 4   Request_Category     588078 non-null  object        
 5   Request_SubCategory  588078 non-null  object        
 6   Request_Detail       588078 non-null  object        
 7   Object_Type          588078 non-null  object        
 8   APN                  331558 non-null  object        
 9   Street_Address       365469 non-null  object        
 10  City                 588078 non-null  object        
 11  State                588078 non-null  object        
 12  Neighborhood         588078 non-null  object        
 13  Latitude      [...]<jupyter_text>The **Close_Time** column didn't get typed as `timedelta`. It doesn't look like it's possible to do so with `pd.read_csv()`. So we'll just set it this way:<jupyter_code>berkeley_311['Close_Time'] = pd.to_timedelta(berkeley_311['Close_Time']) 
berkeley_311.info()
berkeley_311<jupyter_output><empty_output><jupyter_text>Explore dataWhat do I do if I don't have a question yet? I'm not really sure what to look into with this 311 data. So I'm going to explore it a little bit. I might do some analysis, I might not.  Categories of incidents in 2019I'm curious about the different categories of incidents in the year 2019.First, I'll create a new dataframe `berkeley_311_2019` that limits the `berkeley_311` data to just the cases that were open in 2019.<jupyter_code>berkeley_311_2019 = berkeley_311[
    (berkeley_311['Date_Opened'] >= '2019-01-01') &
    (berkeley_311['Date_Opened'] < '2020-01-01')
].copy()
berkeley_311_2019<jupyter_output><empty_output><jupyter_text>One thing I'm seeing immediately is that the index of this new dataframe `berkeley_311_2019` looks kind of weird. It's no longer sequential. I can reset the index to make it sequential by using `df.reset_index(drop=True)`. (We learned this last lecture.)```pythonberkeley_311_2019 = berkeley_311_2019.reset_index(drop=True)```Alternatively, instead of copying the original dataframe with df.copy(), we can reset the index at the same time we subset the data:<jupyter_code>berkeley_311_2019 = berkeley_311[
    (berkeley_311['Date_Opened'] >= '2019-01-01') &
    (berkeley_311['Date_Opened'] < '2020-01-01')
].reset_index(drop=True)
berkeley_311_2019<jupyter_output><empty_output><jupyter_text>Let's see all the unique values of **Request_Category**You can call `series.unique()` on a column:<jupyter_code>berkeley_311_2019['Request_Category'].unique()<jupyter_output><empty_output><jupyter_text>I'm interested in getting a count of categories for 2019. How can I achieve this? Aggregate with `df.groupby()`To aggregate the data, we're going to use a method called `df.groupby()`. Normally, when we group data, we'll group them by columns, like so:```pythondf.groupby(['Column 1', 'Column 2'])```You can also just group by a single column, like we're doing below:<jupyter_code>berkeley_311_2019.groupby(['Request_Category'])<jupyter_output><empty_output><jupyter_text>Running a `df.groupby()` doesn't do anything on its own, it just creates a pandas DataFrameGroupBy object. You have to follow it up with some kind of other method. Below, we're calling `df.count()` on the DataFrameGroupBy object.<jupyter_code>berkeley_311_2019.groupby(['Request_Category']).count()<jupyter_output><empty_output><jupyter_text>OK! So that's a new dataframe, with a little too much info. The "counts" are also different across columns. For "count" to make sense, you need to limit the dataframe to a column with zero null cells. Since we know **Case_ID** has unique values, we just need that one. Let's also set the results of the groupby/count to a new dataframe called `category_counts_2019`. You won't need to use `df.copy()` here because pandas will create a new dataframe instance with `df.groupby().count()`.<jupyter_code>category_counts_2019 = berkeley_311_2019.groupby(['Request_Category']).count()<jupyter_output><empty_output><jupyter_text>Now let's subset just the one column, **Case_ID**, from `category_counts_2019`:<jupyter_code>category_counts_2019 = category_counts_2019[['Case_ID']].copy()
category_counts_2019<jupyter_output><empty_output><jupyter_text>Let's look at that code one more time.```pythoncategory_counts_2019 = category_counts_2019[['Case_ID']].copy()```The outer set of brackets is used to select the data. Within the outer set of brackets is `['Case_ID']`. That is a list of the columns. If we wanted to select two columns, we might have written:```pythoncategory_counts_2019 = category_counts_2019[['Case_ID', 'Date_Opened']].copy()```  Resetting the indexIn this dataframe, the index is no longer a series of sequential integers like we've seen before. We want **Request_Category** to be a column, not an index, because that makes the dataframe easier to use.We're going to use `df.reset_index()`. This time, we're not going to use the `drop=True` argument because we want to create a wholly new index.<jupyter_code>category_counts_2019 = category_counts_2019.reset_index()
category_counts_2019<jupyter_output><empty_output><jupyter_text>Rename columnsLet's change the column names, while we're at it. I want to change the name of the **Case_ID** column to represent an actual count, like **Case_Count**.You can replace _all_ the columns in a dataframe with the following code:```pythoncategory_counts_2019.columns = ['Request_Category', 'Case_Count']```If you have a lot of columns, that's going to be a long list. But if you have a lot of columns to rename, the method above might be easier. If you have only one column to rename out of many columns, you'll want to use the following code:```pythoncategory_counts_2019.rename(columns={'Case_ID': 'Case_Count'}, inplace=True)```The first argument in the `df.rename()` method is `columns`. And what do we set columns to? We set it to a Python dictionary where the "key" is the original column name and the "value" is the new column name: `{'Case_ID': 'Case_Count'}`. The second argument is `inplace=True`. That tells us to change the `category_counts_2019` "in place" or without having to reset the dataframe variable. A lot of the methods in pandas return a new dataframe instead of altering the original dataframe. An alternative to using `inplace` is the following code:```pythoncategory_counts_2019 = category_counts_2019.rename(columns={'Case_ID': 'Count'})```<jupyter_code>category_counts_2019.rename(columns={'Case_ID': 'Case_Count'}, inplace=True)<jupyter_output><empty_output><jupyter_text>Let's take a look at our nicely named summary table:<jupyter_code>category_counts_2019<jupyter_output><empty_output><jupyter_text>Now, I'll sort the dataframe by **Case_Count**:<jupyter_code>category_counts_2019 = category_counts_2019.sort_values(by=['Case_Count'], ascending=False).copy()
category_counts_2019<jupyter_output><empty_output><jupyter_text>Looks like Trash & Recycling, along with Streets/Utilities/Transportation were among the top issues in 2019. Might be worth looking into some of the sub-categories later.At this point, you can see the index is out of order now. You can use `df.reset_index(drop=True)` if you like. You could have also replaced `df.copy()` in the previous cell with `df.reset_index(drop=True)`.<jupyter_code>category_counts_2019 = category_counts_2019.reset_index(drop=True)
category_counts_2019<jupyter_output><empty_output><jupyter_text>Let's visualize this summary table!Before we run the Altair code below, let's take a closer look:```pythonalt.Chart(category_counts_2019).mark_bar().encode(    x='Case_Count',    y='Request_Category').properties(    title='Berkeley 311 cases in 2019')```The first part of the code `alt.Chart()` requires you to fill the first argument with a dataframe, in this case `category_counts_2019`.The next part of the code `mark_bar()` specifies a bar chart. (If you want a line chart, you'd use `mark_line()`.)After that, `.encode()` tells Altair which columns to use for the `x` and `y` axes.If you want to add a title, you'd use Altair's `.properties()` method.<jupyter_code>alt.Chart(category_counts_2019).mark_bar().encode(
    x='Case_Count',
    y='Request_Category'
).properties(
    title='Berkeley 311 cases in 2019'
)<jupyter_output><empty_output><jupyter_text>Annoyingly, this doesn't sort the chart in descending order, which I prefer. This is the code to do, it's a little more complicated:```pythonalt.Chart(category_counts_2019).mark_bar().encode(    x='Case_Count',    y=alt.Y('Request_Category', sort='-x')).properties(    title='Berkeley 311 cases in 2019')```Basically, you have to create a custom Y encoding with the format: `alt.Y('column_name', sort='-x')`. `-x` means the inverse of the x-axis, in this case. This is not intuitive, I think — it's just something you'd have to look up in the documentation.<jupyter_code>alt.Chart(category_counts_2019).mark_bar().encode(
    x='Case_Count',
    y=alt.Y('Request_Category', sort='-x')
).properties(
    title='Berkeley 311 cases in 2019'
)<jupyter_output><empty_output><jupyter_text>Count how many incidents per yearThe next thing I'd like to do is get a count of all the incidents by year. However, I know from the last notebook that the data for 2010 and 2021 are not complete. So I need to subset.Below, I'm creating a new dataframe called `berkeley_311_complete` that limits the `berkeley_311` dataframe to ones in which the **Date_Opened** value starts on or after January 1, 2011 and is before January 1, 2021.<jupyter_code>berkeley_311_complete = berkeley_311[
    (berkeley_311['Date_Opened'] >= '2011-01-01') &
    (berkeley_311['Date_Opened'] < '2021-01-01')
].reset_index(drop=True)<jupyter_output><empty_output><jupyter_text>Use df.groupby() with datetimesIt's a little tricky to group by datetimes. Instead of grouping by just a column name, we're going to have to use a method called `pd.Grouper`. Before we run the code below, let's look at the different arguments within the method:```pythonpd.Grouper(key='Date_Opened', axis=0, freq='A')```The `key` argument lists the column. The `axis` argument is `0`. In pandas, axis 0 is rows and axis 1 means columns. That means you can do column-wise calculations if your data is shaped differently. The `freq` argument is `A`, which stands for "annual" or year (`Y` also works, but isn't documented). You can see other [frequency arguments](https://pandas.pydata.org/pandas-docs/stable/user_guide/timeseries.htmloffset-aliases) in the official pandas documentation.<jupyter_code>berkeley_311_complete.groupby([pd.Grouper(key='Date_Opened', axis=0, freq='A')])<jupyter_output><empty_output><jupyter_text>Remember that running a `df.groupby()` doesn't do anything on its own; you have to chain that command with some kind of other method. Below, we're calling `df.count()` on the DataFrameGroupBy object. Finally, we're calling our new dataframe `annual_cases`.<jupyter_code>annual_cases = berkeley_311_complete.groupby([pd.Grouper(key='Date_Opened', axis=0, freq='A')]).count()
annual_cases<jupyter_output><empty_output><jupyter_text>Now let's subset just the one column, **Case_ID**, from annual cases, then reset the index so that `Date_Opened` becomes a new column:<jupyter_code>annual_cases = annual_cases[['Case_ID']].reset_index()
annual_cases<jupyter_output><empty_output><jupyter_text>Rename columns<jupyter_code>annual_cases.rename(columns={'Case_ID': 'Case_Count'}, inplace=True)<jupyter_output><empty_output><jupyter_text>Let's take a look at our nicely named summary table:<jupyter_code>annual_cases<jupyter_output><empty_output><jupyter_text>Let's create a new column in `annual_cases` called **Year**.<jupyter_code>annual_cases['Year'] = annual_cases['Date_Opened'].dt.year
annual_cases<jupyter_output><empty_output><jupyter_text>At this point, I don't need the **Date_Opened** column anymore. So I can subset the dataframe with just the two columns I need.<jupyter_code>annual_cases = annual_cases[['Year', 'Case_Count']].copy()
annual_cases<jupyter_output><empty_output><jupyter_text>Visualize<jupyter_code>alt.Chart(annual_cases).mark_bar().encode(
    x='Year',
    y='Case_Count'
)<jupyter_output><empty_output><jupyter_text>That's pretty cool, but **Year** shows up kind of weird. Let's make a very small alteration to the code.Before you run the code below, notice that after `Year` there's a colon and an `O`. The `O` is shorthand for "ordinal," and tells Altair to treat `Year` as if it's a discrete quantity (a.k.a. integers), not a continuous quantity (e.g. a number with decimals).<jupyter_code>alt.Chart(annual_cases).mark_bar().encode(
    x='Year:O',
    y='Case_Count'
).properties(
    title='Berkeley 311 calls: Number of cases'
)<jupyter_output><empty_output><jupyter_text>You can read about more [Altair encoding types](https://altair-viz.github.io/user_guide/encoding.htmlencoding-data-types) in the documentation. It's helpful to get familiar with those encoding types in the event your chart doesn't look quite right. Try adjusting the encoding types on your own to see what happens.  Median Close_Time by yearNow I'd like to try to get the median length of time it takes to close a case by year. I'm going to try something I think will work...<jupyter_code>median_close_time = berkeley_311_complete.groupby([pd.Grouper(key='Date_Opened', axis=0, freq='A') ]).median()
median_close_time<jupyter_output><empty_output><jupyter_text>It looks like that didn't work! Sometimes pandas doesn't work the way you want it to. Getting the median of a `timedelta` dtype is a feature that hasn't yet been built.I'm instead going to create a new column called **Close_Time_Seconds** that converts **Close_Time** to seconds. Then I can calculate the median number of seconds.<jupyter_code>berkeley_311_complete['Close_Time_Seconds'] = berkeley_311_complete['Close_Time'].dt.total_seconds()<jupyter_output><empty_output><jupyter_text>Let's make sure the new column looks OK:<jupyter_code>berkeley_311_complete.head()<jupyter_output><empty_output><jupyter_text>Let's run the groupby/median code we used before. Hopefully pandas will calculate **Close_Time_Seconds** this time around:<jupyter_code>median_close_time = berkeley_311_complete.groupby([pd.Grouper(key='Date_Opened', axis=0, freq='A'), ]).median()
median_close_time<jupyter_output><empty_output><jupyter_text>That worked! Now we can subset for the columns we want, and set **Date_Opened** to its own column.<jupyter_code>median_close_time = median_close_time[['Close_Time_Seconds']].reset_index()
median_close_time<jupyter_output><empty_output><jupyter_text>Below, I'm creating a new column called **Year**, as we did before. I'm also going to convert seconds to days:<jupyter_code>median_close_time['Year'] = median_close_time['Date_Opened'].dt.year
median_close_time['Close_Time_Days'] = median_close_time['Close_Time_Seconds'] / 60 / 60 / 24<jupyter_output><empty_output><jupyter_text>Let's take another look at our dataframe before we subset:<jupyter_code>median_close_time
median_close_time = median_close_time[['Year', 'Close_Time_Days']].copy()
median_close_time
alt.Chart(median_close_time[['Year', 'Close_Time_Days']]).mark_bar().encode(
    x='Year:O',
    y='Close_Time_Days',
).properties(
    title='Berkeley 311 calls: Median resolution time'
)<jupyter_output><empty_output><jupyter_text>Merge two dataframesNow I'd like to merge `median_close_time` and `annual_cases`. Why? Mostly because I'd like to teach you how to merge dataframes. But you can get a neat summary table this way. Let's look at both dataframes again:<jupyter_code>annual_cases
median_close_time<jupyter_output><empty_output><jupyter_text>Let's look at the arguments in `pd.merge()` before we run it:```pythonpd.merge(    df1,    df2,    how='outer',  other options: 'inner', 'left', 'right'    on='Year',    validate='1:1'  options: '1:m', 'm:m', 'm:1')```1. The first argument is the left-hand dataframe. The second argument is the right-hand dataframe. Why is it important that there's an order to dataframes? 2. The `how` argument tells pandas how we'll merge the two dataframes. In this case, we'll use `outer`. But we could also use `left`, `right`, or `inner`. What does this mean? Let's take a look at the lecture slides to view a visual. In this case, it doesn't matter which value we use for `how` because both dataframes have 10 rows with matching years.3. The `on` argument tells pandas which column key we're going to match on. In this case, we want the years to match up.4. The `validate` argument is optional, but I recommend you learn how to use it. The value we used, `'1:1'` means that 1 row in the left-hand dataframe will match up to exactly 1 row in the right-hand dataframe. The option `1:m` means that 1 row in the left-hand dataframe could match up to **many** rows in the right-hand dataframe. (Any time you use `m`, you're telling pandas that there _might_ be multiple matches.)<jupyter_code>annual_summary = pd.merge(
    annual_cases,
    median_close_time,
    on='Year',
    how='outer',
    validate='1:1'
)
annual_summary<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.10158424320274032, 'fraction_numerical': 0.030774994647827018, 'mean_word_length': 5.438662991040662, 'pattern_counts': {'":': 0, '<': 148, '<?xml version=': 0, '>': 148, 'https://': 2, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 1, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '36', 'dir': 'jupyter-structured-clean-dedup', 'id': '529214', 'license': 'MIT', 'n_tokens_mistral': 6296, 'n_tokens_neox': 5683, 'n_words': 2036, 'path': 'berkeley_311_calls_pt2_lecture.ipynb', 'repo_name': 'soooh/lecture_06', 'size': '18684'}