Average COVID-like symptoms vs average mask-wearing by state scatter plot for 21-Oct-2020
Scatter-plot for 21-Oct-2020
The dots on this plot represent "mask_wearing_pct" on the x-axis with "symptoms_pct" on the y-axis from this query:
select
round(mask_wearing_pct) as "% wearing mask",
round(symptoms_pct) as "% with symptoms",
state
from covidcast_fb_survey_results_v
where survey_date = to_date('2020-10-21', 'yyyy-mm-dd')
order by 1;
The plot would be too cluttered if each of the 51 points were labeled with its two-letter state abbreviation.
The plot was created simply by pasting a comma-separated list of "mask_wearing_pct"-"symptoms_pct" pairs into a spreadsheet and by using the app's built-in functionality to create a scatter plot from such pairs values. The values were produced with this query:
select
round(mask_wearing_pct)::text||','||round(symptoms_pct)::text
from covidcast_fb_survey_results_v
where survey_date = to_date('2020-10-21', 'yyyy-mm-dd')
order by 1;
Then the plot was printed and the line was drawn in by hand using the slope and y-axis intercept from this query:
with a as (
select
max(survey_date) as survey_date,
regr_slope (symptoms_pct, mask_wearing_pct) as s,
regr_intercept(symptoms_pct, mask_wearing_pct) as i
from covidcast_fb_survey_results_v
where survey_date = to_date('2020-10-21', 'yyyy-mm-dd'))
select
to_char(survey_date, 'mm/dd') as survey_date,
to_char(s, '90.9') as s,
to_char(i, '990.9') as i
from a;
This is the result:
survey_date | s | i
-------------+-------+--------
10/21 | -1.2 | 131.4
And here is the plot:
Scatter-plot for synthetic data
For comparison, the same technique was used to create a scatter-plot and to draw in the best-fit straight line using synthetic data by running the procedure "populate_t()" described in the section Create the test table within the section that introduces the built-in aggregate functions for linear regression analysis.
create table t(
k int primary key,
x double precision,
y double precision,
delta double precision);
create procedure populate_t(
no_of_rows in int,
slope in double precision,
intercept in double precision,
mean in double precision,
stddev in double precision)
language plpgsql
as $body$
...
This code, and the remaining code below, needed to make the scatter-plot for synthetic data is included in synthetic-data.sql.
It uses the function normal_rand(), brought by the tablefunc extension to add pseudorandomly generated normally distributed noise the y-axis values produced by the "y = m*x + c" formula for the straight line.
It was then invoked like this:
call populate_t(
no_of_rows => 100,
mean => 0.0,
stddev => 5.0,
slope => -1.2,
intercept => 131.4);
using the values for slope and intercept from the regression analysis of the COVIDcast data for 21-Oct-2020 and by choosing a value for the "stddev" actual argument arbitrarily.
The comma-separated pairs for the spreadsheet were produced by this query:
select
round(x)::text||','||round(y + delta)::text
from t
where
x > 60 and
x < 95 and
x is not null and
y is not null
order by x;
And the values for the slope and y-axis intercept were produced by this query:
with a as (
select
regr_r2 ((y + delta), x) as r2,
regr_slope ((y + delta), x) as s,
regr_intercept((y + delta), x) as i
from t)
select
to_char(r2, '0.99') as r2,
to_char(s, '90.9') as s,
to_char(i, '990.9') as i
from a;
This is the result:
r2 | s | i
-------+-------+--------
0.98 | -1.2 | 130.8
The emergent values for the slope and intercept are very close to the values (-1.2 and 131.4) that were used for the invocation of "populate_t()".
Here is the resulting plot
:
Note: The normal_rand() function produces a different set of pseudorandomly distributed values each time that synthetic-data.sql is run. But the overall shape of the scatter-plot will remain the same.