6 ms·

Spreadsheet of San Francisco Bay Area Covid-19 Data and Charts

28 points by andfrob 6y ago

andfrob 6y ago
I developed this for myself but data junkies trying to get a feel for what is happening with the coronavirus spread across the San Francisco Bay Area will appreciate it. I am updating it regularly.
- pkaye 6y ago
  Where are you getting the raw data? I'm extracting it from the New York Times dataset for my own graphing. They have the data for all counties in the US. I've been meaning to automate the graphing but for now doing it manually. I wish you had the new cases per day graphed for all the bay area counties because that is what I monitor.
  - andfrob 6y ago
    Raw data was originally from SF Chronicle, but they removed their timelapse view so I am now getting it direct from county websites. Stanford Open Data project also has a reasonable historical dataset that comes from the county websites. I'll add a new cases graph for each county.
the_crocodile 6y ago
Very helpful. Thank you for sharing! Have you been able to find data on # of tests carried out?
- andfrob 6y ago
  Very, very limited data on the Bay Area. Under the "SF Bay Area Actuals" you can scroll all the way to the right you will see what I have been able to find. California does report them on aggregate, but the purpose of this sheet was to focus on the Bay Area.
norifukuoka 6y ago
Very cool. By the way did you intend for the Y-axis on the "Days since 100 cases" chart to be "Days since 100 cases"? It seems like the Y-axis is "cases" and the X-axis is "Days since 100 cases".
- andfrob 6y ago
  Thanks, fixed!
testfoobar 6y ago
Is anyplace in the Bay Area sharing stats by zipcode? For example, San Diego has zipcode breakdown here: https://www.sandiegocounty.gov/content/sdc/hhsa/programs/phs/community_epidemiology/dc/2019-nCoV/status.html https://www.sandiegocounty.gov/content/sdc/hhsa/programs/phs...
- andfrob 6y ago
  Not that I am aware of. And the counties vary a lot in terms of the data they provide. The best is Napa County.
- jhammons 6y ago
  Austin has a decent breakdown here: http://www.austintexas.gov/covid19 http://www.austintexas.gov/covid19
- bosswipe 6y ago
  That would be so useful to identify hotspots within counties. Also information about new cases such as if they are working from home or are considered "essential workers". How are transmissions occurring despite all the stay at home efforts? That would help us all tighten up our collective defenses. Hopefully the governments are doing at least some rudimentary "contact tracing" efforts and we're just not getting to see the data.
- et-al 6y ago
  Skimming the health department web sites of various counties, it doesn't look like it. Most of them just provide the basic cases and deaths numbers. Whoever compiled this at the Stanford Open Data site (https://opendata.stanforddaily.com/#/datasets/covid19_bayarea_counties https://opendata.stanforddaily.com/#/datasets/covid19_bayare...) might be doing so manually. SF County - https://www.sfdph.org/dph/alerts/coronavirus.asp https://www.sfdph.org/dph/alerts/coronavirus.asp San Mateo County - https://www.smchealth.org/coronavirus https://www.smchealth.org/coronavirus Alameda County excluding Berkeley - http://www.acphd.org/2019-ncov.aspx http://www.acphd.org/2019-ncov.aspx Berkeley - https://www.cityofberkeley.info/coronavirus/ https://www.cityofberkeley.info/coronavirus/ Santa Clara County - https://www.sccgov.org/sites/phd/DiseaseInformation/novel-coronavirus/Pages/dashboard.aspx#cases https://www.sccgov.org/sites/phd/DiseaseInformation/novel-co... Marin County has an (ominously named) dashboard - https://coronavirus.marinhhs.org/surveillance https://coronavirus.marinhhs.org/surveillance
Jommi 6y ago
Where is the most important metric? Daily tested vs tested positive stats.
- andfrob 6y ago
  This is not released by most counties, unfortunately. There are some limited stats to the very far right side of the "SF Bay Area Actuals" sheet. Anecdotally, Bay Area is seeing <10% positivity rates
  - andfrob 6y ago
    Another important data point to assess testing is Case Fatality Rate (CFR). This is about 2.5% in the SF Bay Area. In other places with higher testing, such as Australia, the CFR is 0.6% or less. This implies that the true number of cases is 4-5 times higher... probably a lot more.
    - colechristensen 6y ago
      It seems like this disease is so successful because of a significant symptom-free-but-contageous period followed by a small percentage of very serious symptoms. That's what a pandemic needs. If it is very deadly very quickly it kills its transmission vectors before they can transmit. If it is entirely symptom free, it is very evolutionarily successful, but no one cares because there aren't any negative effects. There is an "optimum" of disease characteristics for maximum damage and we seem to be experiencing one. The bottom line is that it seems to be very difficult to prevent a majority of the world population from getting this disease and the result is going to be a global fatality rate of somewhere in the neighborhood of 1%.
      - alteria 6y ago
        What put it into perspective for me is the CDC estimate of up to 25% cases being entirely asymptomatic [1], and data from Iceland shows 50% of those tested were asymptomatic at time of testing [2]. It will be hard to trace and isolate if this is the case. [1] https://sfist.com/2020/04/01/cdc-director-coronavirus-25-percent-no-symptoms/ https://sfist.com/2020/04/01/cdc-director-coronavirus-25-per... [2] https://nationalpost.com/news/world/in-iceland-free-coronavirus-tests-reveal-50-of-cases-show-no-symptoms https://nationalpost.com/news/world/in-iceland-free-coronavi...
pkaye 6y ago
California unfortunately has a huge backlog of pending test results. The cause seems to be the private labs (Quest in particular) accepted test samples and build up a huge backlog of the earlier manually processed test samples. Other labs would push back if their queue got too long. The newer samples are run on the Roche high speed machines.
- grandmczeb 6y ago
  Do you happen to have a source for the cause of the backlog? Not doubting you, just curious to read more information.
  - xivzgrev 6y ago
    here you go: https://covidtracking.com/data/state/california https://covidtracking.com/data/state/california
    - grandmczeb 6y ago
      I’m asking for a source on why the backlog happened. Unless I’m missing something, that just shows the number of pending cases.
      - boyd 6y ago
        They accepted more tests than they had the ability to quickly process. They also appear to have accepted many tests that required use of a lower throughput assay before switching to higher throughput testing on Roche 8800s: https://www.theatlantic.com/health/archive/2020/03/next-covid-19-testing-crisis/609193/ https://www.theatlantic.com/health/archive/2020/03/next-covi...
        grandmczeb 6y ago
        Thanks, googling found this[1] statement by Quest that they have a backlog of 115,000 tests, down from 160,000 on March 25th. It would be interesting to know what percentage of the California backlog is contained within that. [1] https://newsroom.questdiagnostics.com/COVIDTestingUpdates https://newsroom.questdiagnostics.com/COVIDTestingUpdates
Cactus2018 6y ago
Is anyplace in the Bay Area sharing stats by age brackets?
- savagedata 6y ago
  Santa Clara County (South Bay Area) shares a dashboard with cases by age group and deaths by age group: https://www.sccgov.org/sites/phd/DiseaseInformation/novel-coronavirus/Pages/dashboard.aspx https://www.sccgov.org/sites/phd/DiseaseInformation/novel-co...
starpilot 6y ago
Design an evacuation plan for San Francisco. You have 15 minutes.
- kilbuz 6y ago
  Rename it Oakland.
danans 6y ago
The conspicuous lack of realistic infection data from India, coupled with the extreme challenges to containment and control there (just due to the sheer crowding) is frightening, regardless of whether the poor data is intentional or just because India is hard place to coordinate. That the published infection and mortality rates are so low strains credulity in the extreme, especially when much smaller-population countries at similar proximity to the equator but greater distance from China have higher case rates (i.e Brazil, Ecuador, the UAE).
denster 6y ago
@andfrob, just saw your comment about SF Chronicle removing their timelapse view. We made one here from the NYT dataset on MintData [1]: https://nyt-map.covid42.com/ https://nyt-map.covid42.com/ (note: I think we need to update the cumulative counter, we'll be fixing that shortly) @andfrob happy to get you free/unlimited access to MintData if you're interested in making similar visualizations, please DM me if this would be helpful. [1] https://mintdata.com https://mintdata.com

28 points by andfrob 6y ago

I developed this for myself but data junkies trying to get a feel for what is happening with the coronavirus spread across the San Francisco Bay Area will appreciate it. I am updating it regularly.

pkaye 6y ago
Where are you getting the raw data? I'm extracting it from the New York Times dataset for my own graphing. They have the data for all counties in the US. I've been meaning to automate the graphing but for now doing it manually. I wish you had the new cases per day graphed for all the bay area counties because that is what I monitor.
- andfrob 6y ago
  Raw data was originally from SF Chronicle, but they removed their timelapse view so I am now getting it direct from county websites. Stanford Open Data project also has a reasonable historical dataset that comes from the county websites. I'll add a new cases graph for each county.

the_crocodile 6y ago

Very helpful. Thank you for sharing! Have you been able to find data on # of tests carried out?

andfrob 6y ago
Very, very limited data on the Bay Area. Under the "SF Bay Area Actuals" you can scroll all the way to the right you will see what I have been able to find. California does report them on aggregate, but the purpose of this sheet was to focus on the Bay Area.

norifukuoka 6y ago

Very cool. By the way did you intend for the Y-axis on the "Days since 100 cases" chart to be "Days since 100 cases"? It seems like the Y-axis is "cases" and the X-axis is "Days since 100 cases".

andfrob 6y ago
Thanks, fixed!

testfoobar 6y ago

Is anyplace in the Bay Area sharing stats by zipcode? For example, San Diego has zipcode breakdown here: https://www.sandiegocounty.gov/content/sdc/hhsa/programs/phs/community_epidemiology/dc/2019-nCoV/status.html https://www.sandiegocounty.gov/content/sdc/hhsa/programs/phs...

andfrob 6y ago
Not that I am aware of. And the counties vary a lot in terms of the data they provide. The best is Napa County.
jhammons 6y ago
Austin has a decent breakdown here: http://www.austintexas.gov/covid19 http://www.austintexas.gov/covid19
bosswipe 6y ago
That would be so useful to identify hotspots within counties. Also information about new cases such as if they are working from home or are considered "essential workers". How are transmissions occurring despite all the stay at home efforts? That would help us all tighten up our collective defenses. Hopefully the governments are doing at least some rudimentary "contact tracing" efforts and we're just not getting to see the data.
et-al 6y ago
Skimming the health department web sites of various counties, it doesn't look like it. Most of them just provide the basic cases and deaths numbers. Whoever compiled this at the Stanford Open Data site (https://opendata.stanforddaily.com/#/datasets/covid19_bayarea_counties https://opendata.stanforddaily.com/#/datasets/covid19_bayare...) might be doing so manually. SF County - https://www.sfdph.org/dph/alerts/coronavirus.asp https://www.sfdph.org/dph/alerts/coronavirus.asp San Mateo County - https://www.smchealth.org/coronavirus https://www.smchealth.org/coronavirus Alameda County excluding Berkeley - http://www.acphd.org/2019-ncov.aspx http://www.acphd.org/2019-ncov.aspx Berkeley - https://www.cityofberkeley.info/coronavirus/ https://www.cityofberkeley.info/coronavirus/ Santa Clara County - https://www.sccgov.org/sites/phd/DiseaseInformation/novel-coronavirus/Pages/dashboard.aspx#cases https://www.sccgov.org/sites/phd/DiseaseInformation/novel-co... Marin County has an (ominously named) dashboard - https://coronavirus.marinhhs.org/surveillance https://coronavirus.marinhhs.org/surveillance

Jommi 6y ago

Where is the most important metric? Daily tested vs tested positive stats.

andfrob 6y ago
This is not released by most counties, unfortunately. There are some limited stats to the very far right side of the "SF Bay Area Actuals" sheet. Anecdotally, Bay Area is seeing <10% positivity rates
- andfrob 6y ago
  Another important data point to assess testing is Case Fatality Rate (CFR). This is about 2.5% in the SF Bay Area. In other places with higher testing, such as Australia, the CFR is 0.6% or less. This implies that the true number of cases is 4-5 times higher... probably a lot more.
  - colechristensen 6y ago
    It seems like this disease is so successful because of a significant symptom-free-but-contageous period followed by a small percentage of very serious symptoms. That's what a pandemic needs. If it is very deadly very quickly it kills its transmission vectors before they can transmit. If it is entirely symptom free, it is very evolutionarily successful, but no one cares because there aren't any negative effects. There is an "optimum" of disease characteristics for maximum damage and we seem to be experiencing one. The bottom line is that it seems to be very difficult to prevent a majority of the world population from getting this disease and the result is going to be a global fatality rate of somewhere in the neighborhood of 1%.
    - alteria 6y ago
      What put it into perspective for me is the CDC estimate of up to 25% cases being entirely asymptomatic [1], and data from Iceland shows 50% of those tested were asymptomatic at time of testing [2]. It will be hard to trace and isolate if this is the case. [1] https://sfist.com/2020/04/01/cdc-director-coronavirus-25-percent-no-symptoms/ https://sfist.com/2020/04/01/cdc-director-coronavirus-25-per... [2] https://nationalpost.com/news/world/in-iceland-free-coronavirus-tests-reveal-50-of-cases-show-no-symptoms https://nationalpost.com/news/world/in-iceland-free-coronavi...

pkaye 6y ago

California unfortunately has a huge backlog of pending test results. The cause seems to be the private labs (Quest in particular) accepted test samples and build up a huge backlog of the earlier manually processed test samples. Other labs would push back if their queue got too long. The newer samples are run on the Roche high speed machines.

grandmczeb 6y ago
Do you happen to have a source for the cause of the backlog? Not doubting you, just curious to read more information.
- xivzgrev 6y ago
  here you go: https://covidtracking.com/data/state/california https://covidtracking.com/data/state/california
  - grandmczeb 6y ago
    I’m asking for a source on why the backlog happened. Unless I’m missing something, that just shows the number of pending cases.
    - boyd 6y ago
      They accepted more tests than they had the ability to quickly process. They also appear to have accepted many tests that required use of a lower throughput assay before switching to higher throughput testing on Roche 8800s: https://www.theatlantic.com/health/archive/2020/03/next-covid-19-testing-crisis/609193/ https://www.theatlantic.com/health/archive/2020/03/next-covi...
      - grandmczeb 6y ago
        Thanks, googling found this[1] statement by Quest that they have a backlog of 115,000 tests, down from 160,000 on March 25th. It would be interesting to know what percentage of the California backlog is contained within that. [1] https://newsroom.questdiagnostics.com/COVIDTestingUpdates https://newsroom.questdiagnostics.com/COVIDTestingUpdates

Cactus2018 6y ago

Is anyplace in the Bay Area sharing stats by age brackets?

savagedata 6y ago
Santa Clara County (South Bay Area) shares a dashboard with cases by age group and deaths by age group: https://www.sccgov.org/sites/phd/DiseaseInformation/novel-coronavirus/Pages/dashboard.aspx https://www.sccgov.org/sites/phd/DiseaseInformation/novel-co...

starpilot 6y ago

Design an evacuation plan for San Francisco. You have 15 minutes.

kilbuz 6y ago
Rename it Oakland.

danans 6y ago

The conspicuous lack of realistic infection data from India, coupled with the extreme challenges to containment and control there (just due to the sheer crowding) is frightening, regardless of whether the poor data is intentional or just because India is hard place to coordinate. That the published infection and mortality rates are so low strains credulity in the extreme, especially when much smaller-population countries at similar proximity to the equator but greater distance from China have higher case rates (i.e Brazil, Ecuador, the UAE).

denster 6y ago

@andfrob, just saw your comment about SF Chronicle removing their timelapse view. We made one here from the NYT dataset on MintData [1]: https://nyt-map.covid42.com/ https://nyt-map.covid42.com/ (note: I think we need to update the cumulative counter, we'll be fixing that shortly) @andfrob happy to get you free/unlimited access to MintData if you're interested in making similar visualizations, please DM me if this would be helpful. [1] https://mintdata.com https://mintdata.com