<jupyter_start><jupyter_text>OverviewThis is an auxiliary notebook for the [main analyses](Main.ipynb), meant for documenting the data access and cleaning processes. Both streams of data (prisoner counts and overall populations, respectively) require additional  steps before they are ready for analysis. Some of these steps can be automated in a Python environment; certain minor adjustments must be done manually, and are also explained here.*__Note__*: The data access steps here are documented for transparency; it is not necessary to do anything other than use in-repository, cleaned files to replicate the main analyses. Table of ContentsI. [Prison Populations](data1)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;[Description](d1)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;[Access](a1)   &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;[Cleaning](a2)III. [Overall Populations](data3)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;[Description](d3)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;[Access](a5)   &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;[Cleaning](a6)  Prison Populations   Description Recall that the [Communicating With Prisoners](https://www.acrosswalls.org) project compiled counts of [Prisoners By State And Sex](https://www.acrosswalls.org/datasets/prisoners-us-state-sex-panel/) from 1880 to 2010. This is a count from federal and state correctional facilities, itself coming from the [US Bureau of Justice Statistics Prisoner Series](https://www.bjs.gov/index.cfm?ty=pbse&tid=0&dcid=0&sid=40&iid=0&sortby=&page=paging&curpg=4).  Access To completely automate the data cleaning process (without using the Google Sheets API for Python) would require several steps. The URL of the public sheet hosted by the project would have to include the right tab information, range information (to exclude the extra mis-formatted parts of the CSV), and a specifier to return the result as a CSV file. HTML parsers would be needed to scrape together a CSV from the raw response of a GET request. The Google API involves OAuth setup, the Google Console, and several copy/paste operations. It is simpler to document the download of the file and additional steps (programmed or manual) to ready the data. Inclusion of the raw and clean files means future users need not perform data processing unless interested.1. We start by locating the Google Sheet hosted by the project, and visiting the relevant [panel tab](https://docs.google.com/spreadsheets/d/1V4_0T_lJPVBhKuMirhXncjWMI3C6crHgcD84qv1Y2JY/editgid=1100768042).![img1](./figures/data1_snippet1.png) 2. It suffices to download this sheet only, as a CSV file, as highlighted in the picture.![img2](./figures/data1_snippet2.png)  Cleaning The result is the file store in [data/raw/prisoner_counts.csv](./data/raw). From here, we can automate data processing steps. Run all these steps in order, exactly once (it is best to restart from __*1*__ if errors are made).1. We load the file into a `pandas.DataFrame` object, taking care to handle the ill-formatted raw CSV file.<jupyter_code>import pandas as pd
df_pp_raw = pd.read_csv('./data/raw/prisoner_counts.csv', skiprows=3)
df_pp_raw.head()<jupyter_output><empty_output><jupyter_text>2. Drop the right-most non-tabular columns, and the unneeded ratio and aglev columns.<jupyter_code>df_pp_raw = df_pp_raw.drop(['Unnamed: 8', 'source and notes', 'sexratio', 'aglev'], axis=1)
df_pp_raw.head()<jupyter_output><empty_output><jupyter_text>3. Regardless of other filters we apply, we need a column totalling male and female prisoner counts.<jupyter_code>df_pp_raw['total'] = df_pp_raw.males + df_pp_raw.females
df_pp_raw.head()<jupyter_output><empty_output><jupyter_text>4. To preserve the table when transformation are applied, we replace certain missing `NaN` values. Regions are `NaN` when the row is an aggregate (e.g., an extraneous row for total federal count rather than for one state). We can impute this description in place of `NaN`.<jupyter_code>df_pp_raw['region'].fillna('aggregate', inplace=True)
df_pp_raw.head()<jupyter_output><empty_output><jupyter_text>Observe that in some years, multiple totals are available from the `fedstate` source (US Bureeau of Justice Statistics) and the `census-total` source (Decennial Census). There are often discrepancies. While it is ideal to use the same source consistently, not all sources are available for all years, and the discrepancies imply that one source or the other is missing counts. To resolve years with multiple sources available, we can take the higher count, noting that there is a compromise in experimental consistency.5. Drop the class column. Group by year, region, and geography, then perform a maximum aggregation of the count columns.<jupyter_code>df_pp_raw = df_pp_raw.drop(['class'], axis=1)
df_pp_grouped = df_pp_raw.groupby(['year', 'region', 'geography']).max().reset_index()
df_pp_grouped.head()<jupyter_output><empty_output><jupyter_text>6. At this point the data can be used in the main analyses. We will save this cleaned version as a CSV at [data/clean/prisoner_counts.csv](./data/clean).<jupyter_code>df_pp_grouped.to_csv('./data/clean/prisoner_counts.csv')<jupyter_output><empty_output><jupyter_text>Overall Populations   Description US population data comes primarily from the US Census Bureau: via the decennial Census, which filled decades with constant-growth estimates until 1960, and the yearly American Community Survey thereafter. The aggregator here is the international [World Bank Group](https://www.worldbank.org/). Specifically, its [World Development Indicators](https://datacatalog.worldbank.org/dataset/world-development-indicators) project collects vast development data for several countries. Its [aggregation of population over time in the US](https://data.worldbank.org/indicator/SP.POP.TOTL?locations=US) is cited as a direct combination of the Census Bureau data. State-level granularity is thus far not offered by any known aggregator for sufficient years, and impractical to self-compile.  Access Since only a national-level series is required, there are several ways to access the World Development Indicators data. Third-party APIs can wrap the World Bank HTTP GET request support, but the Group's [online database](https://databank.worldbank.org/source/world-development-indicators) interface is a simpler approach. It allows us to select only the small slice of data we need, and store it in-repository so future users need not re-acquire it.1. Visit the [interface](https://databank.worldbank.org/source/world-development-indicators) and select "World Development Indicators" for "Database".&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ![img](./figures/data3_snippet1.png)2. Select "United States" for "Country".&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ![img](./figures/data3_snippet2.png)3. Select "Population, total" for "Series".&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ![img](./figures/data3_snippet3.png)4. For "Time", click the "select all" icon.&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ![img](./figures/data3_snippet4.png)5. Download the slice in its current form. ![img](./figures/data3_snippet5.png)        Cleaning The result of the __Access__ step is the raw file stored in [data/raw/overall_population.xlsx](./data/raw/), ready for cleaning. Execute in order, once (restart if errors made).1. Load into `pandas.DataFrame` object, with attention to any mis-formatting.<jupyter_code>df_op_raw = pd.read_excel('./data/raw/overall_population.xlsx', nrows=1, thousands=',')
df_op_raw.iloc[:,:10]<jupyter_output><empty_output><jupyter_text>2. Drop three erroneous columns (two invalid, one for the blank 2020 count).<jupyter_code>df_op_raw = df_op_raw.drop([' ', 'Unnamed: 7', '2020'], axis=1)
df_op_raw.iloc[:, :10]<jupyter_output><empty_output><jupyter_text>3. Pivot the table and make the values numeric.<jupyter_code>df_pivot = df_op_raw.transpose().reset_index()
df_pivot.columns = ['year', 'total_population']
df_pivot.year = df_pivot.year.astype(int)
df_pivot.total_population = df_pivot.total_population.astype(int)
df_pivot.head()<jupyter_output><empty_output><jupyter_text>4. We save this result as a clean CSV file, ready for main analyses, in [/data/clean/overall_population.csv](./data/clean).<jupyter_code>df_pivot.to_csv('./data/clean/overall_population.csv')<jupyter_output><empty_output>
StarcoderdataJupyterStructuredCleanDedup