A recent USA Today investigation found that as the COVID-19 outbreak began to spread throughout the United States, many hospitals didn’t have enough bed capacity to respond to a surge in cases. The story relied on data from the American Hospital Association, and both organizations have partnered with Big Local News to share data on available hospital beds by state and metropolitan area.

The story USA Today found

The report by Jayme Fraser and Matt Wynn identified states and metropolitan areas likely to encounter more difficulties treating patients, based on factors like the size of vulnerable populations and number of occupied beds. Some states could need 20 times more hospital beds than were available, according to the analysis, and even states in the best shape would need 8–12 times as many if infection rates peaked.

Projections have changed in recent weeks in response to social-distancing and other measures, with some states now announcing that they expect containment efforts will prevent their hospitals from being overwhelmed. So how can you use AHA data to determine what capacity looks like in your community?

How you can analyze the data

You can download a cleaned version of the American Hospital Association dataset, combined with additional calculated columns from USA Today, on the Big Local News platform for free. After logging in with your Google account, you’ll find the data in an open project called “COVID_AHA_Hospital_beds.” Download all of the project files, including the README put together by Jayme, which talks through common pitfalls with this data, and how it was tested. Then:

1. Determine the state or metro area you’ll be using for your analysis. For the next several steps, we’ll use Arkansas as our example, one of the states cited in the USA Today story.

2. If you’re selecting a state, open the file CoronavirusHospitalCapacity_states.csv. If you’re selecting a metro area, open the file CoronavirusHospitalCapacity_metros.csv. Since we’re focusing on Arkansas, we’ll open the state file.

   NOTE: Keep an eye on metro areas near borders, because this data breaks up cities into chunks for each side of the border. The data includes three metros for which state data was combined, and most of the 24 incomplete rows are for cities that sit on state borders.

Get population figures

1. Look up the total population in your selected area in the Population_allages column. In Arkansas, the total population is 2,990,671. This column is available because USA Today gathered and cleaned census data for these population numbers, and included it in the data we downloaded.

2. Look up the percentage of people who could get infected by COVID-19 (based on USA Today’s analysis), inside the Infected_allages column. For Arkansas, that column should read 221,310, which means the infected population could be up to 221,310.

   NOTE: The USA Today analysis chose 7.4% as a baseline infection rate by looking at five years’ worth of CDC flu data—you can read a lot more in their excellent reporting guide on this data. Their analysis was published earlier in the outbreak, and due to social distancing and other protective measures, your local health experts may have a more current infection estimate for you to use. We’d encourage you to seek out their expertise when reporting for your community. If you choose a different infection rate, here’s how you’d recalculate the rest of the numbers for your analysis.

3. Look up the percentage of people who may be in severe or critical condition (meaning they will need hospitalization) due to COVID-19, in the Severe_allages and Critical_allages columns, respectively. For Arkansas, the dataset projects 30,541 people in severe condition and 13,500 in critical condition.

Now that you have your general population figures, you can do some more analysis.

Get available bed figures

1. To calculate the number of patients who would need care per hospital bed, add severe and critical case numbers, then divide the total by the amount of total beds. For example, in Arkansas, adding 30,541 and 13,500, we find there are 44,041 total severe and critical cases. We divide that by the column Total_Beds, which is 9,517, and find that there would be 4.6 patients per bed—lower than the national figure, but still enough to overwhelm hospitals. Nationwide the United States would have about 6.0 COVID-19 patients per bed, a number we can calculate by:

   (sum of Severe_allages for all states + sum of Critical_allages for all states) / sum of Total_Beds for all states
   

   You can use the PatientsPerBed_allages column to see how your state or metro area measures up.

2. Those rates are based on the total number of beds in hospitals, which we found in the Total_Beds column. However, the AHA calculates that 64% of hospital beds are in use at any given point, so the dataset also includes an Estimated_Available_Beds column representing the remaining 36%. Arkansas has 9,517 total beds, but only 3,426 would be available for COVID-19 care based on AHA estimates—likely a more accurate picture of hospital capacity during an outbreak. Recalculating based on that figure shows about 12.9 COVID-19 patients per available bed in Arkansas. You can find those numbers for your state or metro area in the PatientsPerAvailableBed_allages column.

   Note: The USA Today analysis calculated the Estimated_Available_Beds column based on AHA estimates about bed usage. Local policies and response to the outbreak may have changed bed availability in your area. We’d encourage you to talk with local health experts, who may have a more current estimate for you to use.

To cite this data for your readers, you can use the following language: The figures come from a USA Today analysis of data from the American Hospital Association, U.S. Census, CDC, and World Health Organization that is shared publicly at biglocalnews.stanford.edu.

Where to look for your story

Knowing your community’s figure for expected COVID-19 patients per available bed gives you important background knowledge when asking local officials about their plans to address potential shortages in beds. And ranking your metro area or state against others can provide context for your readers. For example, when the Monterey County Weekly wrote a local story based on this data, they were able to explain to readers that their region’s hospital bed capacity ranks 337th out of nearly 450 metropolitan regions across the country.

Understanding possible shortfalls in bed capacity can also point you toward questions about staffing: Are there enough health-care workers to properly care for patients if COVID-19 infection rates increase? Are those workers being given the necessary personal protective equipment? Talking to hospital staff themselves, or to patients and families trying to access care, can help you illuminate important aspects of the story.

BONUS: Using your own rates

If you choose a different infection rate for your analysis, here’s how you can recalculate the different population numbers:

new infection rate * current Population_allages value = new Infected_allages value

(For example, currently: 0.074 * Population_allages = Infected_allages)

The USA Today analysis used WHO estimates of 13.8% to determine severe cases and 6.1% to determine critical cases, so you’d calculate new Severe_allages and Critical_allages values by:

new Infected_allages value * 0.138 = new Severe_allages value

new Infected_allages value * 0.061 = new Critical_allages value

Here’s how you can then recalculate how many patients would need care per bed:

(new Severe_allages value + new Critical_allages value) / current Total_Beds

(new Severe_allages value + new Critical_allages value) / current Estimated_Available_Beds

Find more step-by-step COVID-19 data story recipes like this one. If you have questions about a story you’re working on, our free peer data review program is here to help.

Programs like these are part of the OpenNews COVID-19 community care package. If you’re using this story recipe, please let us know — we’d love to promote your work! If you’ve got a story recipe idea, we’d love to hear about it. Drop us a line at source@opennews.org.

• Share on Twitter
• Share on Facebook