ARPA-CLE: Review data processing script

[Weihua4455]

Hi hi,

I just finished reviewed the scripts. The code makes sense, however, I have some questions about how we compiled in-direct funds that local government received from the state.

Here's Ana's readme/instruction for this part:

Finally, indirect allocations from the state. 

They were hand input by Rachel Disell here:
https://docs.google.com/spreadsheets/d/1VaZU-rJdtvhGJMLtLYfqqiAcBRrswdM_N1iCRubEXF8/edit#gid=125400899

Tabs: Ohio_CC_courts and Ohio_CC_Violent crime reduction.

Ana copied them over to a clean table here:
https://docs.google.com/spreadsheets/d/1nq0AwnOEi61XXPcbFUaI_aHfBl4lHWKphSqp3f9B0Jc/edit#gid=1493443383

Then, exported as xlsx file, cleaned here 
clean_indirect_multiplesheets.py
and saved as cle_indirect_multiple.csv

From Rachel's source documentation in the hand-compiled Google Sheet, here're a couple of tabs that I understand are CJ-related and need to be included in the analysis, and I have a few questions about those:

First, some overarching questions about the analysis:

• Question: There are multiple sheets in the Google Sheet (Ohio_CC_ARPA_Wellness, Ohio_CC_ARPA_ViolentCrimeReduction, Ohio_ACC_ARPA_BodyCam, Ohio_CC_Courts), why did we choose to only export the crime (which I assume is Ohio_CC_ARPA_ViolentCrimeReduction) and court tabs?

• Question: In the story, we said About a third of Cuyahoga County’s ARPA money – $66 million – was divided up between county council districts to be used as “community grants.”. But I don't see any scripts that pulls in or analyzes that data. Where are we doing that?

• Tab Ohio_CC_ARPA_Wellness

  • Source data: Ohio First Responder Recruitment, Retention, and Resilience Program Grant
  • Question: Looking under the Cuyahoga County list, there are two spendings (highlighted in the spreadsheet) that we did not include in the initial tally. Did we exclude them on purpose?
  • Question: There were also a couple of cases where the departments who benefited from the grant are police and fire department, but we only included police in the dataset. Would love to learn more.

• Tab Ohio_CC_ARPA_ViolentCrimeReduction

  • Source data: Ohio Violent Crime Reduction Program Grant
  • Question: There were 5 grants under Cuyahoga County that were not included in our spreadsheet (see highlighted in the tab). Did we exclude them on purpose?
  • Question: I could not find the last two projects ($1.8 million to Westlake Police Department and ~$250,000 to Southeast Area Law Enforcement Task Force) in the source data. Did we find them somewhere else?

• Tab Ohio_ACC_ARPA_BodyCam

  • Source data: Body Worn Camera Grant
  • Question: This seems to come from a different source, or at least the format of the source document looks different. Would love to learn more about why that's the case.

• Tab Ohio_CC_Courts

  • Question: Ana included this in the final analysis, but I'm not sure where this came from.

[Weihua4455]

I talked to Rachel today, who answered most of my questions. See notes here.

However, during fact-checking, Rachel flagged another issue: In the CJ-related spendings, there are a lot of cases where police spending is wrapped with other things. I.e. Cleveland is spending more than $109 million on salaries, but Rachel found out that only half of those went to the police.

I think there was a discussion on how we need to take some of those numbers out of the total, which, I assume, is why we have the $96 million as the total, not $151 million that I got. That said, I don't see documentations about this, and can't really include the analysis in the review. We can either use the $151 million number and say "no more than," or document which projects we're taking out of the totals.