COVID-19 Good News Day 1

What do Kentucky, Missouri, Montana, Nebraska, North Carolina, Oregon, Washington, and Vermont have in common?

Each of these states have experienced a decline in in the new number of deaths from COVID-19 reported one week earlier.

Here is a chart that shows the change in the number of deaths reported each day from the deaths reported seven days ago, relative to each day on the y-axis, and the total number of deaths reported for each state on the x-axis.  The significance of this, if my understanding is correct, is that epidemiologists can use this to help determine the strength/momentum of an epidemic when the number of deaths from the epidemic are increasing exponentially.

My goal with sharing this information is to highlight states, communities, and countries that seem to be experiencing a change in the statistics for the better.  I understand that epidemiologists normally use confirmed cases, not deaths; however, it is my opinion that COVID-19 has spread far beyond the numbers of the people that have been tested.  However, I will switch, If someone can convince me as to why the number of confirmed COVID-19 cases is a more correct data point to model from than deaths.

I've attached two charts that show these changes, one chart with all the states I mentioned, and one chart without the state of Washington for reasons that you can see in the charts.

Chart 1: All 8 States

As you can see from Chart 1, there is so much more data from the state of Washington that it dwarfs all the other states.  I will try not to speculate to much but it seems that there have been so many more cases of COVID-19 in Washington because it was one of the first and hardest hit when the SARS-CoV2 virus landed on shores of the US.  

To help see the data for the other states, here is the same chart minus the data from the state of Washington.

Chart 2: Just 7 States

OK, this is still confusing so here is the chart for each state starting with Vermont, the state, from these 7 that have reported the most deaths per person. As shown in this Chart 3

Chart 3

Vermont:

Chart 4

Washington:

Chart 5

Montana:

Chart 6

Oregon

Chart 7

Nebraska

Chart 8

NorthCarolina

Chart 9

I sincerely hope that these states really do see a sustained reductions in the number of new deaths reported every day, week over week, from COVID-19, that this information helps people commit to being part of the solution, and that there are real reasons the disease has not progressed in these states as it has in others.

If anyone is wondering if I cherry-picked these states, I have not.  I have the same data Johns Hopkins is using to report on.

Less than a month ago, COVID-19 was something really bad happening somewhere else; however, it was clear that it would touch us soon.  That day has arrived and COVID-19 is in every state, and likely in every country, I think.

How to import COVID-19 From Johns Hopkins University

How to Plot COVID-19 Data from Coronavirus COVID-19 Global Cases by the Center for Systems Science and Engineering (CSSE) at Johns Hopkins University

Navigate to https://gisanddata.maps.arcgis.com/apps/opsdashboard/index.html#/bda7594740fd40299423467b48e9ecf6

Expand the pane on the bottom in the middle

Click on the downloadable database from GitHub here

https://github.com/CSSEGISandData/2019-nCoV

Login to GitHub if you have not already done so

Navigate into the csse_covid_19_data folder

https://github.com/CSSEGISandData/COVID-19/tree/master/csse_covid_19_data

Navigate into the cse_covid_19_time_series

In the csse_covid_19_time_series are several files.  Up until a couple days ago I was using the following files

time_series_19-covid-Confirmed.csv
time_series_19-covid-Deaths.csv
time_series_19-covid-Recovered.csv

As of 3/23/2020 these files are not being updated any more.  The README.md file instructs us to use the new files named:

time_series_covid19_confirmed_global.csv

time_series_covid19_deaths_global.csv

For this example I’ll start with confirmed covid19 cases

Open the file time_series_covid19_confirmed_global.csv in your web browser.   We are going to go to the raw file, copy the URL, then use the URL for the raw file to get the data into Power BI desktop.  The steps are similar for Excel if you have the Power BI plugin for Excel.

For each file, click the “Raw” button to bring up the URL for the raw csv file.  Copy the URLs and save them for when we retrieve the data into Power BI

Copy the URL for the raw data for the confirmed cases and the deaths and recoveries.  I’ve pasted them here so you don’t have to do this through GitHub, but be warned, this data changes and they may introduce new files or change the  names so if you want to make sure you have the latest data you need to get the latest URL for the raw files from GitHub

Confirmed Cases:

https://raw.githubusercontent.com/CSSEGISandData/COVID-19/master/csse_covid_19_data/csse_covid_19_time_series/time_series_covid19_confirmed_global.csv

Deaths:

https://raw.githubusercontent.com/CSSEGISandData/COVID-19/master/csse_covid_19_data/csse_covid_19_time_series/time_series_covid19_deaths_global.csv

As of this moment, there isn’t a new file for Recoveries yet so I’ll leave that to you to find when it’s published.

Next, make sure you have PowerBI desktop installed.  At present, you cannot edit queries in the online version of PowerBI.  Queries can  only be edited with the PowerBI desktop.

You can install the Power BI desktop from here

Follow these steps to import the data into PowerBI

Open PowerBI Desktop

From the “Get data” pull down list select “Web” to import the data from GitHub

Enter the URL for the confirmed cases then click OK, then click Load.  Don’t worry, I’ll show you how to transform the data next

Repeat the same steps for COVID-19 deaths that you did to import the Confirmed data.

Create new views of the imported data

On the left side of your scree you should see two queries/tables.  Right-click on the ellipses “. . .” to bring up “More Options” then select “Edit Query”

Start with time_series_covid19_confirmed_global

In properties, change the name to Confirmed

From Home tab, click “Use First Row as Headers”

Unpivot the table by doing the following

Click on the column named “1/22/20” then select all of the columns to the right

From the Transform tab, click “Unpivot Columns”

Change the column named “Attribute” to “Date” and the column name “Value” to “Confirmed”

On the column you renamed to “Date”, right click on the column and mouse over “Change Data” then click on “Date”

Repeat these same steps for the time_series_covid19_deaths_global query/table except name the “Values” column “Deaths”

Save your work

I’ll now show you how to create new queries from the queries we imported from the Web

From the list of Queries on the left side, right click on “Conformed” and select “Reference”

There is now a new query named “Confirmed (2)”, rename the query to “DayCountryConfirmed”

Again, right-click on the “Confirmed” query on the left side, then right click on “Confirmed” and select “Reference”

Again, there is a new query named “Confirmed (2)”, rename the query to “DayUSStateConfirmed”

Let’s modify the “DayCountryConfirmed” query to remove unneeded data, and group the data by date and Country with a sum of the deaths in each country on each day

Click on the query name “DayCountryConfirmed”

Drag the date column all the way to the left so it’s the first column

Remove the Lat and Long columns because we won’t use them but we can get them back from the original query.

Group and sort the data by Date First, Country Second, and sum the Confirmed by

In the Transform tab, Ctrl-click Group By

The Group By dialog box is displayed

Change from “Basic” to “Advanced” so we can add multiple group-by conditions

The first condition needs to be Date

Click “Add Grouping”

From the pull-down list select “Country/Region”

Under “New column name” type “Confirmed”, then from the pull-down list under “Operation” select “Sum”, then from the pull-down list under “Column” select Confirmed

Click OK

Now let’s sort things.  First by Date, then by Country by

Next to the column name for “Date” click the box just to the right of the column and select “Sort Ascending”

Next to the column name for “Country/Region” click the box just to the right of the column and select “Sort Ascending”

Check for any data quality problems by selecting a specific country (like US) and make sure the data is correct and matches what is being reported.

Compare data to

JorJordan River Park Trail Intro

Installed New Toilet

In November our basement flooded.  The first room we decided to repair was the bathroom.

Here is what it looked like before repainting.  

Here it is repainted and with the new trim installed.

Since we pulled the original toilet out we decided to install a new toilet.  Interestingly, the old toilet had a nonstandard flange.  It was tiny so I removed it as well as some of the old concrete to install a new flange.

I added an extension that would allow me to install a 3 inch pipe flange.

It's an ABS pipe extension so I used the ABS pipe glue then poured a small batch of fresh cement.

After letting things set I installed the new flange.

Then drilled the holes for the screws then secured the new flange with concrete screws.

Here is the new toilet.

One room done.  Three more to go (plus the hallway)

Six Months Later . . .

Sorry to take so long to post; however, the results of the kitchen are wonderful.  I still have under-cabinet lighting and back splashes to install; however, we have been using the kitchen and it's wonderful.

More Leveling

The most time consuming aspect of making our countertops is making sure they are flat and level.  This wasn't a problem I had with the samples I made because the samples were so small that imperfections in flatness or level were not detectible.  Imperfections in flatness and level do show up on the larger surface areas, so this is something I've had to figure out.

At first, I tried to eye-ball the level, that didn't work out.  The human eye can only see so much at once.  The brain fills in details that just aren't there and something that looks level isn't.  

The next method I tried was to use my T-square to find all the valleys, mark them then grind down to them.  The problem with this method is that not all valleys are the same height, nor are they all level.

At present, I'm finding the highest spot, marking it, and grinding it down.  This seems to be the most accurate method.  It's also very time consuming.  I'm using my longest level, marking the high spots on the X-axis, and marking the high spots on the Y-axis.  The high spots are simply the parts of the countertop that touch the bottom of the level.  I'm shining a bright light across the counter top on the side opposite to where I am so that as I look under the level I'll see the light anywhere light passes under it.  I'm also using my putty knifes to lift the low end of the level to make the level . . . well . . . level.  I'm marking all the places the light does not pass under the level using the level like a ruler.

Notice the hash-marks I've drawn on the countertop in the photo above.  the places where the hash-marks intersect are the only places I grind down.  Eventually, when the counter is flat and level, no matter where I put the level, the entire level will touch the surface of the counter top and no light will pass under it.

At the moment, there is a 2 mm difference between the highest and lowest points on the kitchen island counter top which are just over 2-feet away from each other.  Grinding down these peaks requires a very light touch.  I've already turned a couple peaks into new valleys so I've put the 15-Amp Makita high-speed grinder away and switched to my variable speed polisher with the large 7-inch grinding wheel on a low speed.  

Polishing New Countertops

I think I'm on the final stretch polishing our counter tops.  This is the most time consuming part of the whole fabrication process.  

The polishing process started with grinding the surface of the concrete down to expose the aggregate and to remove any blotches.  Grinding the surface also exposes small air bubbles and pin holes so they need to be filled with a slury mixture.  Repeat the slurry and grinding process until counters are smooth, level, and flat.

When the counter tops were reasonably flat I began polishing them with 50 grit diamond polishing pads and worked my way up to 6000 grit pads.  

Here is one of the counter tops polished up to 3000 grit.

The photo above shows the exposed aggregate (rocks), glass, and quartz sand that I used in the concrete mix.

Just to show the difference between un-polished vs polished.  Here is the area the kitchen faucet and skink will be installed.  It's only polished to 400 grit.  The picture just after shows the same area polished up to 3000 grit.

It's slow work but we're getting there