May 10, 2020

This is the first update using Google Sheets. One nice thing about Sheets is that it lets you publish a read-only version of the entire spreadsheet, so those of you who are real data nerds can go here to look at the raw data.

First, a bit about the data.

The state of California publishes a dataset here.

This downloadable dataset contains everything you see in the charts, EXCEPT testing data. That, it turns out, requires going to each separate county public health department. On top of that, the only counties that have accessible testing stats are Los Angeles, Orange, San Diego, San Francisco, and Santa Clara. If you know where to find testing stats by day for Alameda, Riverside, Sacramento, Kern, and Ventura, please let me know!

If you do look at the full spreadsheet, I apologize in advance for the lack of navigation. There are nearly 800 columns, and I realize that, even though I’ve organized everything in logical categories, it may be difficult to get around. On my copy of the spreadsheet, I have full indexed navigation, but this is a little “feature” of the published version.

On the charts, I’ve tried to implement a standardized theme, along with colors and specific shapes for each locality. For the “dashboard” charts, I eliminated point shapes, but kept consistent color schemes.

I’m trying out a new approach with the “Dashboard”, consisting of smaller charts. These charts will always show SD County, along with the highest and lowest localities. Hopefully that will replace a lot of my commentary as well!

The value of charts to me is that it helps focus on trends. All to often I see headlines like “______ shows big increase in COVID cases!” Then I look at the data and find that its one day, often a Monday, and the jump just represents noise. It’s really, really important to focus on the trends over time to understand what’s happening.

Some of the charts have a logarithmic scale on the Y axis to make things more readable.

The hospital data are for THAT DAY ONLY. They are not cumulative, and I haven’t found any readily accessible data that shows that. I’ve lumped together totals for confirmed and suspected, mainly because I believe most of the suspected numbers will end up falling in the confirmed column.

As always, I really welcome your feedback. This is a first time for me using Google Sheets for a project like this. I’m sure I’ve goofed up somewhere, and I’m equally sure one of you will find out where!



All these data are for counties, so when I refer to “LA” or “SF” or “SD”, that means the county, not the city.

  1. Last Sunday, I commented how LA had replaced the Bay Area as the state hotspot, and this week is no exception. LA is the highest in normalize cumulative cases, daily new cases, normalized cumulative fatalities, daily fatalities, case fatality rate, and normalized current day’s hospitalizations. SD, however, is now the lowest in doubling days, but the trend lines for LA and SD are nearly identical.
  2. Scan all 11 charts on the dashboard and one thing stands out — for the most part, the lines are either rising or plateauing. While that’s a good thing for doubling days and testing, it’s a real concern for everything else. Remember the CDC criteria for opening up that the White House has tried to bury? It calls for 2 weeks of improvements. Not exactly what you see here, unfortunately.
  3. Tests per week in LA have flattened. That’s sort of strange, because anyone can get a test there – with or without symptoms. The rate for San Diego is rising steadily.
  4. I don’t know exactly how to interpret the charts on hospitalizations. These are normalized numbers, so the population size doesn’t explain the differences. Why does LA have more than 3 times the hospitalization rate of Sacramento, or almost double the rate of SD? Why are Kern’s ICU rates up to 60%, while LA’s rates are less than 30%? Maybe some of you who are medical professionals or researchers can answer those questions.

Finally, I want to wish all the mothers out there a very happy Mother’s Day. I know this is a bittersweet day for many of you, looking at your kids or grand kids in a zoom/facetime window.

Leave a Reply

Your email address will not be published. Required fields are marked *


This site uses Akismet to reduce spam. Learn how your comment data is processed.