I have 2 sets of data, for 2 indicators, over time. Format shown below (of course, the data sets are much bigger)
Data set 1 (indicator A)
<tbody>
</tbody>
Data set 2 (indicator B)
<tbody>
</tbody>
I would like to run a few simple analyses on these:
Is there a simple and direct way of doing this, short of manually manipulating data (e.g. copying columns for each year below each other in each data set for analysis #1 , etc)?
Greatly appreciate any help!
****** id="cke_pastebin" style="position: absolute; top: 0px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">
<tbody>
</tbody>
</body>
Data set 1 (indicator A)
City | 2009 | 2010 | 2011 | 2012 | 2013 | 2014 | 2015 | 2016 | 2017 |
Airdrie | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 |
Brooks | 9 | 8 | 7 | 8 | 4 | 2 | 1 | 2 | 5 |
Calgary | 4 | 7 | 4 | 3 | 4 | 8 | 7 | 6 | 1 |
Duncan | 7 | 5 | 6 | 1 | 2 | 8 | 7 | 9 | 9 |
Edmonton | 3 | 5 | 2 | 8 | 9 | 7 | 9 | 9 | 4 |
Fernie | 1 | 2 | 8 | 2 | 3 | 8 | 7 | 9 | 4 |
Greenwood | 6 | 7 | 2 | 9 | 6 | 7 | 9 | 3 | 8 |
Hamilton | 2 | 5 | 6 | 5 | 5 | 8 | 7 | 4 | 4 |
Iqaluit | 6 | 3 | 5 | 7 | 3 | 6 | 4 | 1 | 2 |
<tbody>
</tbody>
Data set 2 (indicator B)
City | 2009 | 2010 | 2011 | 2012 | 2013 | 2014 | 2015 | 2016 | 2017 |
Airdrie | 1 | 9 | 3 | 8 | 9 | 4 | 5 | 8 | 9 |
Brooks | 5 | 3 | 8 | 7 | 9 | 5 | 4 | ||
Calgary | 1 | 6 | 3 | 1 | 3 | 5 | 8 | 7 | 5 |
Duncan | 8 | 1 | 4 | 3 | 9 | 3 | 9 | 5 | 7 |
Edmonton | 8 | 1 | 5 | 2 | 5 | 3 | 9 | 8 | |
Fernie | 9 | 1 | 6 | 1 | 1 | 3 | 8 | ||
Greenwood | 5 | 5 | 8 | 7 | 1 | 4 | 9 | 9 | 3 |
Hamilton | 9 | 8 | 1 | 5 | 8 | 5 | |||
Iqaluit | 8 | 9 | 8 | 7 | 5 | 9 | 1 | 6 | 7 |
<tbody>
</tbody>
I would like to run a few simple analyses on these:
- Scatter plot Indicator A v Indicator B (across countries and across years), just to see whether there is a correlation (or some relationship) between the 2 indicators.
- Scatter plot Indicator A v % change in Indicator B (across countries and across years), to see whether there is a correlation between indicator A and the speed of change in B.
- As you can see in the table above, Indicator B starts getting values for some cities after a few columns. I would like to plot the first x years of indicator B in a line chart or something, BUT with an offset when looking at years. So that rather than the plot starting in column 2 for every single city, the first value for each city should be the first cell for the city that has a number. And so on...
Is there a simple and direct way of doing this, short of manually manipulating data (e.g. copying columns for each year below each other in each data set for analysis #1 , etc)?
Greatly appreciate any help!
****** id="cke_pastebin" style="position: absolute; top: 0px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">
City | 2009 | 2010 | 2011 | 2012 | 2013 | 2014 | 2015 | 2016 | 2017 |
Airdrie | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 |
Brooks | 9 | 8 | 7 | 8 | 4 | 2 | 1 | 2 | 5 |
Calgary | |||||||||
Duncan | |||||||||
Edmonton | |||||||||
Fernie | |||||||||
Greenwood | |||||||||
Hamilton | |||||||||
Iqaluit |
<tbody>
</tbody>
</body>