Check out this article if you need more generic information about georeferencing in mySidewalk.
This example utilizes time as the first column. If you don't have a time you want to reference, it is recommended to keep the first column blank during upload (don't delete it, keep a column of blank rows).
Goal of this Example
In this article we are going to walk through an example of how to use the georeference tool. The goal will be to upload some daily covid data over the course of 2020 for a single city using the following steps:
1. Get the Data
We can use the data publicly available here at the data.kcmo.org. I used the Open Data KC Overall Trends dataset (“COVID-19 Case & Death Trends by Date”) which should not actually require any cleaning before uploading the daily data.
Keep the first column as a date, that date will be incorporated into the column headers as metadata for this upload.
Most common date formats are acceptable here (mm/dd/yy, Month Day, Quarter, Year, etc.)
Each row should be a unique date and the entire spreadsheet should be for a single geography
Note: If you are using another file that does NOT have date in the first column, it is best practice to add a date column and either put the correct date in or leave the rows under the header blank.
access file here
Save the cleaned file as a .csv
2. Upload and Assign the geographies
We will now take the cleaned file and upload it into mySidewalk.
Log In
Click Upload -> Upload and Georeference
Upload the file we created
At this point, you will see a sample of the file.
Select the “Single Geography” button
Using the dropdowns, assign “Kansas City, MO” because that is the area represented by this data
You can also change the name of your file by updating the “Layer File name” at the top of the page
Click “Submit” to upload the layer with the geographies assigned
A successful layer upload will look like the above image. You may have to wait up to 5 minutes for the layer to process, especially for a large one like this.
Things to note:
The column headers that contained numbers say “number” in the Type column and not “text”. This means that the format can be changed within mySidewalk (to percent, for example, and used in calculations such as normalization).
All the Column headers load into the “Name” list. We want to ensure that the data we need is the data we are getting.
You have a time element dropdown that shows all the dates loaded. You can confirm the data values for the different months by choosing from the dropdown and watching the data values change to match the spreadsheet.
The geography is outlined on the map image. This confirms that you agree with our representation of the geography.
Things to Do:
Choose a label for your layer with the radio buttons in the “Use as Label?” column (typically the name of the geographic region). In this example, choose the row name “label”. The label is typically the name of the geographic region that will appear when you aren’t using a map (see callout example)
Change the aliases if desired, especially useful to edit out the “12:00:00 AM” part that is not useful here.
3. Use the layer
To start using this layer, you will need to navigate to a report. You can choose “New Report” at the top of the page or select “Reports” from the blue quick start menu button on the left.
Create the Time Series
Hover to view then select the add content blue “+” button on the page and select “Time Series” then “Your Data”
Select the layer you just uploaded
Change which data value is charted
Select “Data” and under “Data source” choose a different line to be charted (like Total Cases)
You can also adjust the label here to remove the unwanted 12:00:00 AM that was part of the upload. Pro Tip: You could have also removed that from the spreadsheet before uploading.
Normalize Data by Total Tested
Underneath “Data,” make sure “New Cases” is selected and find the selection for “Normalize By”
Choose the desired variable (Total Tested)
Change the format to “Ratio as Percent”
Edit the Label
What does this chart tell me?
This chart is an easy way to follow a trend over time. You can see that the new cases were trending upward but made a big jump up in November. Even when normalized for number of tests given, the trend is still moving upward.