If you want to make sure time is represented in the data you upload into mySidewalk, there are a couple of formatting things you need to ensure you do. This will help you to make time series components as well as show time in the visualizations you build.
To understand more about geography (a vital part of data upload), check out other articles in the Upload section of our knowledge base.
Goal of this Example
In this article we are going to walk through an example of how to create a time series component with your own data.
1. Get and Clean the Data
We can use the data publicly available here at Florida Health Charts. I used the Age-adjusted deaths from all external causes dataset and picked one county, Clay. I exported it so I could change the columns around to what I needed.
We can remove all of the rows that we do not want to upload. For example, anything listed as ‘not calculated’ will not be helpful to us
You can remove extra data columns if you want (for this example, we will not be using “Total Tested” but it’s fine to leave it in the spreadsheet)
Transforming the data to represent Time and Space
The most important step we have to do is transform the data so that the geography is listed in the rows and the time is in the column headers. We must do this to ensure that geography is appropriately represented when you upload.
First, make a row for each Clay county and Florida as a whole. Then we will take each date and turn that into a column header. We will follow the pattern "date_variable" so the first column headers will be "2020_Count", "2020_Rate", "2019_Count", "2019_Rate" and so on. You will then need to fill in this new table you made with the correct data.
If you have monthly or quarterly time periods, you can use those too! Check out the section below (More information about leveraging Time) to make sure you have the right formatting.
You can check out the clean file I made here to ensure yours will work!
Save the cleaned file as a .csv
2. Upload and Assign the geographies
We will now take that cleaned file and upload it into mySidewalk. Since the shapes we want to use (Clay county and the state of Florida) are already loaded into mySidewalk, we are going to leverage what is already there!
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 “Multiple Geographies” button
Using the dropdowns, assign the county and state to the corresponding rows. You must select a corresponding geography for each line.
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.
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)
Look for your time groupings in the dropdown under the map.
All the column header names load into the “Name” list. We want to ensure that the data we need is the data we are getting.
The geographies are 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 basically the name of the geographic region that will appear when you aren’t using a map (see callout example)
Change the aliases if desired
3. Use the layer in a Time Series
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”
Select “Your Data” since you are adding the data you just uploaded and not something that mySidewalk provides for everyone
Select the layer you just uploaded
Click Manage Geographies
Select Florida to include both geographies
To change to the rate, click Data->Data Source and pick Rate instead of Count.
You will want to make adjustments to your chart at this point to fully represent what you want to share. Check out our articles on Time Series for more information.
More information about leveraging Time
Specifying the time period (year, month, day) in the column name allows the mySidewalk data upload system to recognize it as time period. This will not only lead to a cleaner data selection experience but will also enable you to make a time series chart!
It works best if you put the time period at the beginning or end of a column name (i.e. ‘2010_population’ or ‘population_April_01_2020’)
Be sure to be consistent in how the time period is formatted. Changing between 'Jan 2010' and 'January 2010' will result in the system creating 2 different months
The data upload system currently only recognizes dates between January 1, 1900 and January 1, 2100.
Year - Use a 4 digit year
i.e. 2010, 2020, 2050, 1961
Quarter - include the year and quarter
The following formats all work to specify the second quarter of 2019.
2019_q2 | 2019 q2 | 2019_Q2 | 2019 Q2
Month - include month and year
The following formats all work to specify the month January 2019.
January_2019 | 2019_January | January 2019 | Jan 2019 | 2019_01 | 01 2019
Day - include the day, month, and year
The following formats all work to specify the day June 30, 2019.
2019_06_30 | 2019_June_30 | 06_30_2019 | June 30 2019