COVID-19 Data Project

We have assembled executive orders on corona virus policies from all states to create a machine readable data set for the public. Our interns have created a data set of these policies by type and date as detailed in this code book spreadsheet. Our data is updated regularly, and text files are now available for those doing research and plugging them into a data base.

The COVID-19 Data Project: Executive Orders & Policies

 

Background/Introduction

 

The COVID-19 pandemic has unveiled unprecedented challenges for the majority of the countries in the world. In the United States, an estimated 90% of Americans experienced lock-downs and social distancing orders.1 As the novel coronavirus disease 2019 pandemic threatens countries throughout the world, the United States is set to be the new epicenter of the outbreak.2 Systematically tracking and assessing policy responses to the public health crisis in each state is essential  to better understand how to manage each outbreak and slow the growth of positive cases. The current healthcare system is dysfunctional and good care is almost unobtainable for many individuals in the US. Eligibility is limited, policies are too costly, too unequal, and coverage too uncertain resulting in an increasing number of uninsured individuals.3 While researchers and health care professionals are racing to develop a vaccine, officials are implementing social distancing, stay at home, restricting travel, and closing businesses and schools to try and slow the spread of the outbreak. 4 With positive numbers on the rise, what responsibilities do governments have? What are the roles of policy makers? How can we ensure the safety and security of citizens throughout the country?

 

BroadStreet and The COVID-19 Data Project Policy Team have partnered with Temple University’s Center for Public Health Law Research (CPHLR) to look at longitudinal executive orders and policies relating to COVID-19. CPHRL’s process has evolved over time with the COVID-19 Data Project enabling collection of up-to-date proclamations and Executive Orders for each state and conversion into machine readable CVS files with visually quantifiable and qualitative data. On a weekly basis, around 300 Executive Orders and their original sources are gathered, converted, read, and coded for the quality assurance process. Through policy review and coding, and alignment with the scope criteria set in place by the CPHLR, the Broadstreet Policy Team cleans and prepares the data for quantitative analysis and further research projects; creating the first machine readable data set for policies in all 50 states, including the District of Columbia and Puerto Rico. 

 

Methods

 

Collaboration with Temple University 

 

Starting on April 6th, 2020, the CPHLR started collecting, coding and analyzing state statutes, regulations, state emergency declarations and mitigation policies related to the COVID-19 pandemic. The project was divided into phases, with the Phase 1 longitudinal dataset covering legal action from January 20th, 2020 through June 1st, 2020 and including the following states-Alabama, Alaska, Arkansas, California, Colorado, Connecticut, Georgia, Hawaii, Iowa, and Kansas. January 20, 2020 was selected as the start date because it was the day before the Centers for Disease Control and Prevention confirmed the first case of COVID-19 in the United States on January 21, 2020. Data from the following sources; American Enterprise Institute COVID-19 Action Tracker, American Network of Community Options and Resources COVID-19 State Policy Resources, ASTHO Coronavirus Disease 2019 (COVID-19) Responses Hub, Boston University School of Public Health COVID-19 US State Policy Database, Council of State Governments COVID-19 Resources for State Leaders, Kaiser Family Foundation State Data and Policy Actions to Address Coronavirus, National Conference of State Legislatures State Action on Coronavirus (COVID-19), and The National Governors Association (NGA) was used to examine, research and collect relevant state actions.5 The Temple research team, consisting of four legal researchers and one supervisor quickly became inundated with work and needed more help. With help from BroadStreet volunteers, who were also working on the project at Temple University, a partnership was formed to collaborate and work side by side. Phases 2 through 4 ramped up including the rest of the states, the District of Columbia, and Puerto Rico, with the BroadStreet Policy Team helping to identify Executive Orders within scope and assisting in building the longitudinal orders and datasets.

 

The BroadStreet team meticulously researched state laws and legal proceedings for the Phase 2 states- Michigan, Wyoming, and Washington, assisting the Temple Law team in converting the Executive Orders into text files. The BroadStreet team developed a Google sheet to record, analyze, and code the Executive Orders (see complete process below). Comparing the Temple sheets with the BroadStreet document, the teams were able to identify outliers and leave the data clean for the final process document. The BroadStreet team proceeded with the same protocol in collecting the laws, Executive Orders, and legal proceedings for the states in Phase 3 - Delaware, Florida, Idaho, Illinois, Indiana, Maine, Maryland, Mississippi, Ohio, and Wisconsin, as well as the Phase 4 states consisting of the remaining states- Arizona, Missouri, Montana, Nebraska, Nevada, New Hampshire, New Mexico, North Carolina, Oklahoma, and Oregon.

 

 

Data Gathering & Collection Process

 

To gather and collect data each week, the BroadStreet policy team organizes themselves into 5 groups. Four of the groups perform “Data Bursts”- a team meeting where team members collectively work on the data collection process while exchanging ideas about how to resolve issues that arise. Each burst team works on a unique preformatted spreadsheet created for a specific week. The Flight Team, the fifth team, contributes to the data gathering process throughout the week. To ensure a smooth and manageable data gathering process for each team, recently released State Executive Orders are collected and converted into Google Doc format, enabling the teams to easily edit the documents and efficiently collaborate with one-another during the data gathering process.

 

Before the Data Burst processes can begin, several tasks must be performed to equip the teams with the necessary documents and data. First, the Executive Orders and proclamations for each state need to be found on The Council of State Governments website. Specific dates are listed on a BroadStreet document identifying the states to be scanned for a given week; orders are gathered once a week.

 

Following this, a BroadStreet team member organizes the converted Executive Orders on a shared spreadsheet. The individual gathering the data enters their name, source, and the date each order is pulled. The individual also enters the data for which the gathering takes place, and documents it for the given state. 

 

One of the team leads creates a new tab on the spreadsheet weekly, enabling policy team members access to the converted EO’s during the Data Burst. The completed tabs are organized and sent to the Quality Assurance team for further review. A count is made of the total number of Executive Orders scanned each week, and the number is logged for future reference on the Status Tracker spreadsheet.

 

The second major step in the data gathering process is creating the Google document for a deep read. The deep read is an essential part of the Data Burst as this is when the policy team members correct any spelling and formatting errors and highlight important data in the document. The marked-up document provides the team leads and Quality Assurance team with important information and creates a track record of the logged values.

 

Orders are documented in either HTML or PDF format. The conversion of the Executive Orders is just as important as the deep read itself, as the documents need to be in the Google Doc format for the data burst teams. Likewise, both processes are relatively time-consuming tasks. 

 

The HTML formatted documents are converted to Google Docs by copying and pasting the executive order into a blank document. The more common method is to download the original source as a PDF document, convert it to Microsoft word format using Adobe Acrobat software, and then convert it into a google doc. Once the Executive Orders have been converted into the Google Doc format, the links are uploaded to the appropriate columns in the spreadsheet. When PDF conversion issues occur, alternative solutions include hand typing the orders or converting the document to word format, and copy/pasting the text into a new Google Doc. This rarely occurs, as Adobe Acrobat and HTML formats have proved to be quite efficient.

 

The final step in preparing for the Data Burst is to format the documents and fix any spelling errors. Each state has its own protocol as to how it presents its Executive Orders. This makes each Executive Order unique and cleaning and formatting is often necessary. Formatting and spelling errors typically arise after the document has been converted into a Google Doc, due to the inability of the conversion software to accurately understand what to do with borders, lines, images, and stamps on the original order. Policy team members scrub the documents each week by deleting unnecessary images, applying a single-column format for easy viewing, deleting converted signatures, and removing the headers and footers between each paragraph. Cleansed documents are verified with a check mark in the appropriate column on the spreadsheet and left ready to be reviewed in the upcoming burst. A value is then added to the total number of “clean PDFs” on the Status Tracker spreadsheet.

 

Gathering data is a crucial step in ensuring proper Data Bursts. Performing the proper work of collecting accurate data, converting it into a usable file, and editing the final product presents the researcher with the most accurate information possible. It is just one of the Policy Team’s tasks, but it allows for an efficient and workable field of data.

 

The following steps are performed in reviewing the Executive Order (EO):

  • Category Blue
    • The Executive Order or Proclamation number must be documented.
  • Category Purple
    • The reviewer determines if the EO is an amendment to a previous order.
    • The effective date and end date are recorded.
  • Category Green
    • The reviewer searches the document for state of emergency declarations and state reopening information. Any information regarding a state’s reopening is crucial.
    • The reviewer needs to determine if the Executive Order is declaring a new state of emergency or is declaring a continuation of an emergency.
    • As the pandemic endures, a continuation of emergency is becoming more common among the states.
    • The reviewer needs to search for stay at home and curfew information.
    • Mask requirements, gathering bans, and social distancing are also noted.
  • Category Yellow
    • The reviewer searches the document for any information regarding business closures, restaurant restrictions, and school closures. These restrictions have varied greatly between the states throughout the pandemic, as many states initially closed during the early months, but have now reopened. During the summer months, however, many businesses, restaurants, and schools have altered their stance, with some pledging to re-close entirely, while others have implemented strict guidelines. It is important for the reviewer to determine whether an organization is following strict safety guidelines or has opted to re- open or close their business entirely.
  • Category Pink
    • In this category the reviewer determines if the Executive Order contains information regarding medical procedures. This information often involves restrictions to elective procedures and out-of-state physician license guidelines.
  • Category Orange
    • Any travel restrictions or changes with the correctional facilities are noted here. Travel inter and intra state and U.S border travel restrictions are identified. Restrictions and changes in adult and juvenile correctional facilities transportation and visitations are identified and noted. Restrictions have varied between the states throughout the course of the pandemic, with travel restrictions in particular appearing in several Executive Orders. 

Following the final scoping questions, the reviewer makes any miscellaneous notes or questions on the spreadsheet. The team lead performs a quality assurance check assuring that there is no missing information, dates, or empty cells. The team lead then converts the Google Doc to a plain text file and enters the total number of Executive Orders processed on the Status Tracker spreadsheet. The documents and spreadsheets are then forwarded to the primary quality assurance team.

Quality Assurance Process

 

State Proclamations and Executive Orders (EOs) are received in various formats; PDF, HTML, ect, and need to be converted to a workable document. A process has been developed to scrub the documents and to deliver the final product in a uniform manner. While some team members convert the EOs using Adobe Pro or Adobe DC, other team members begin scraping the converted EOs.

The EOs are listed on a formatted spreadsheet and located in their respective State sections. Each EO has its own row with columns denoting the in-scope data that needs to be identified in the EO by the data scraper. The QA process begins here.

The first step is to confirm the link spelling, ensuring that the team member accesses the correct EO. A checkmark is placed in the Check Link Spelling Column on the spreadsheet. The document is then opened, and the spelling is checked inside the converted EO. Any spelling errors in the converted document are corrected by the data scraper. One hundred percent conversions are difficult as images, borders, elaborate fonts, and stamps in the original documents cause conversion problems.

In scope items are highlighted in the converted EOs, copied, and inserted as a note into specified data column cell in the Review Spreadsheet. A number “1” (one) is added to the cell to represent an in-scope item. Out of scope items that relate to a column may also be copied and inserted as a note in the Review Spreadsheet. A number “0” (zero) is added to the cell to represent an out-of-scope item. Information inserted as a note is highlighted in the converted EO and color coded to represent the type of data being captured.

Executive Orders cover various topics. To help identify the specific topic of an EO a short phrase describing the topic is inserted into the Review Spreadsheet at the end of the row for that EO. Sometimes it is difficult to precisely identify the topic of an EO as it may contain numerous topics or simply state that a previous EO number has been extended, amended, or closed.

When the team member has finished scraping the converted document, all cells with a number “1” (one) are double checked to ensure that a note has been added. A checkmark is placed in the column confirming that the document has been highlighted and another checkmark is placed in the column confirming that the document is ready for review. Should a team member have questions about a document, and the question is not immediately resolved by another team member or by the Team Lead, the EO is highlighted on the Review Spreadsheet and escalated for further review.

Authors

 

Robert Birds

Bachelor’s Degree in English l University of California, Irvine

 

Samin Charepoo

Bachelor's Degree in Data Science and Neuroscience l Simmons University 

 

Alessandro Malave

Bachelor's Degree in Global Health l New York University

References

 

  1.  Chan AT, Brownstein JS. Putting the Public Back in Public Health — Surveying Symptoms of Covid-19. N Engl J Med. 2020;0(0):null. doi:10.1056/NEJMp201625

  2.  Monroe M. Florida emerges as world’s new epicenter for COVID-19. TheHill. Published July 8, 2020. Accessed July 20, 2020. https://thehill.com/homenews/state-watch/506492-florida-emerges-as-worlds-new-epicenter-for-covid-1

  3.  Fuchs VR. Health Care Policy After the COVID-19 Pandemic. JAMA. Published online June 12, 2020. doi:10.1001/jama.2020.10777

  4.  Gostin LO, Wiley LF. Governmental Public Health Powers During the COVID-19 Pandemic: Stay-at-home Orders, Business Closures, and Travel Restrictions. JAMA. 2020;323(21):2137-2138. doi:10.1001/jama.2020.5460

  5.  Coronavirus Resources. National Governors Association. Accessed August 6, 2020. https://www.nga.org/coronavirus-resources/