Lab 2 - Solution
Ken Benoit and Sarah Jewett
Reshaping data in R
In this assignment, we will be working with the tidyr package to reshape (“pivot”) the datasets rhpi.csv and rpdi.csv.
These follow the examples from Chapter 12, “Tidy data”, from Wickham and Grolemund, R for Data Science.
You may find it useful to learn more how to use dataframes in R, read the how-to-manipulate-dataframe.ipynb.
1. Reshaping a simple example.
- From the
cmpdataobject, replicate the reshaped objectcmpdataLongfrom the lecture notes, using the tidyr package, usinggather().
library("tidyverse")
load("cmpdata.Rdata")
# Keyboard shortcuts for the pipe operator %>%
# Ctrl + Shift + M (Windows)
# Cmd + Shift + M (Mac).
# the code from the lecture, this is what you want to replicate using gather()
cmpdata <- cmpdata %>%
select(countryname, date, partyname, starts_with("per")) %>%
select(-pervote)
cmpdataLong <- pivot_longer(cmpdata, cols = starts_with("per"),
names_to = "code", values_to = "value")
# using gather()
cmpdataGather <- cmpdata %>%
gather(key = "code", value = "value", starts_with("per"))
# or you can use indexing
cmpdataGather <- cmpdata %>%
gather(key = "code", value = "value", 4:60)- Try using tidyr on
cmpdataLongto “spread” this back into the wide format, but usingpivot_wider().
cmpdataWide <- cmpdataLong %>%
pivot_wider(names_from = "code", values_from = "value")2. Load the HPI data
click into the rhpi.csv and have a look of the data. Does it have any header? How is each entry of the data separated? by space or comma?
take a look of the
read.csvfunction and read the csv data to r.after the data is loaded into r, you can use
headfunction to take a look of the first few rows of data. Is it in long or wide format?you may find out that the first column name is empty. You may update the first column name to “country” by using
colnamesfunction. And remember R starts counting from 1!
## your code (filled in for you already)
rhpi_wide <- read.csv("rhpi.csv") #if this line does not work, check where you are by getwd()
head(rhpi_wide)## X X1975.Q1 X1975.Q2 X1975.Q3 X1975.Q4 X1976.Q1 X1976.Q2 X1976.Q3
## 1 Australia 39.16 38.50 38.61 37.76 37.95 38.12 38.36
## 2 Belgium 44.51 45.66 47.01 48.67 50.60 52.26 53.74
## 3 Canada 59.42 59.20 59.89 59.54 59.20 59.85 58.89
## 4 Switzerland 93.83 91.83 90.41 88.91 86.83 85.89 84.98
## 5 Germany 108.59 108.46 109.07 109.30 109.90 110.60 111.24
## 6 Denmark 57.37 57.61 59.07 58.07 58.44 57.28 57.34
## X1976.Q4 X1977.Q1 X1977.Q2 X1977.Q3 X1977.Q4 X1978.Q1 X1978.Q2 X1978.Q3
## 1 37.90 37.94 37.73 37.75 37.42 37.75 37.44 37.55
## 2 55.20 56.32 57.98 59.73 61.40 62.63 63.81 64.90
## 3 57.63 55.91 55.30 54.59 56.12 55.83 56.05 56.14
## 4 85.19 84.54 85.10 85.71 85.65 87.21 88.01 88.94
## 5 112.64 113.76 115.29 116.85 118.75 121.49 122.94 124.55
## 6 58.64 57.49 59.28 60.47 62.72 63.83 64.63 65.13
## X1978.Q4 X1979.Q1 X1979.Q2 X1979.Q3 X1979.Q4 X1980.Q1 X1980.Q2 X1980.Q3
## 1 36.98 37.11 36.94 38.73 40.29 41.57 42.70 43.01
## 2 65.83 67.26 67.84 67.98 67.48 67.07 64.86 62.56
## 3 55.28 55.42 56.19 57.45 58.78 60.13 61.72 63.01
## 4 89.68 90.62 91.13 92.41 93.78 95.43 96.11 97.08
## 5 126.14 126.79 126.28 125.73 125.59 123.74 123.27 124.08
## 6 64.61 64.62 65.48 65.18 63.90 61.29 57.46 57.10
## X1980.Q4 X1981.Q1 X1981.Q2 X1981.Q3 X1981.Q4 X1982.Q1 X1982.Q2 X1982.Q3
## 1 42.59 44.97 45.42 43.80 44.56 43.26 43.25 41.37
## 2 60.33 58.21 56.43 54.74 53.15 51.69 50.47 49.50
## 3 65.70 71.76 76.81 82.90 78.33 75.24 71.67 66.99
## 4 97.44 98.74 100.40 100.55 100.77 101.23 99.71 98.52
## 5 124.98 125.96 126.26 126.06 125.92 125.96 125.57 124.44
## 6 55.86 53.91 49.86 46.95 46.76 45.16 44.14 42.44
## X1982.Q4 X1983.Q1 X1983.Q2 X1983.Q3 X1983.Q4 X1984.Q1 X1984.Q2 X1984.Q3
## 1 40.31 40.37 41.00 40.48 40.80 41.98 42.59 43.97
## 2 48.51 47.34 46.41 45.59 44.93 44.57 44.04 43.48
## 3 61.51 61.24 61.27 61.24 60.37 58.90 59.90 59.21
## 4 98.25 99.20 99.82 101.05 101.55 102.30 102.94 103.18
## 5 123.35 122.94 122.15 120.14 118.86 117.13 115.51 114.66
## 6 42.95 46.05 50.66 50.68 51.46 53.34 53.12 53.17
## X1984.Q4 X1985.Q1 X1985.Q2 X1985.Q3 X1985.Q4 X1986.Q1 X1986.Q2 X1986.Q3
## 1 43.09 44.84 44.06 44.11 44.50 44.29 45.19 44.36
## 2 42.98 41.96 42.00 42.31 42.68 43.21 43.63 44.10
## 3 57.28 56.47 57.18 57.40 58.33 58.46 60.45 61.83
## 4 103.29 104.18 104.50 105.42 106.07 108.26 108.63 110.12
## 5 113.29 111.33 110.52 110.61 110.26 110.77 111.97 111.66
## 6 55.18 56.80 58.48 62.55 64.31 68.15 67.69 65.77
## X1986.Q4 X1987.Q1 X1987.Q2 X1987.Q3 X1987.Q4 X1988.Q1 X1988.Q2 X1988.Q3
## 1 43.79 43.59 43.24 43.39 44.68 45.94 47.49 51.33
## 2 44.52 44.74 45.13 45.59 46.13 47.27 47.91 48.52
## 3 63.48 65.42 69.40 71.89 72.39 71.90 74.42 77.17
## 4 111.59 113.46 115.78 117.77 119.86 124.20 128.50 133.32
## 5 111.36 111.14 110.63 110.20 110.35 111.32 111.33 110.78
## 6 66.39 61.14 59.76 58.92 58.05 58.36 57.12 57.72
## X1988.Q4 X1989.Q1 X1989.Q2 X1989.Q3 X1989.Q4 X1990.Q1 X1990.Q2 X1990.Q3
## 1 55.61 59.37 59.07 58.36 57.62 57.14 56.48 55.70
## 2 49.25 50.28 51.44 52.64 53.83 54.85 55.43 55.81
## 3 78.42 81.19 86.46 84.85 85.01 86.51 87.58 82.69
## 4 138.97 142.29 143.82 144.77 143.59 141.45 138.60 134.90
## 5 111.72 110.82 110.66 111.31 111.15 109.54 109.39 109.80
## 6 57.66 56.25 55.26 53.96 53.20 51.47 49.90 48.80
## X1990.Q4 X1991.Q1 X1991.Q2 X1991.Q3 X1991.Q4 X1992.Q1 X1992.Q2 X1992.Q3
## 1 54.66 54.48 54.91 56.13 55.53 54.99 55.03 54.97
## 2 56.13 56.23 56.58 57.21 58.04 59.05 59.70 60.07
## 3 79.14 74.84 74.17 76.45 76.82 75.19 75.18 74.03
## 4 131.16 129.43 128.12 127.00 124.26 121.60 118.84 115.92
## 5 109.92 110.77 110.71 110.10 109.23 110.29 110.20 110.78
## 6 48.58 48.97 48.93 48.86 49.20 48.88 48.27 47.36
## X1992.Q4 X1993.Q1 X1993.Q2 X1993.Q3 X1993.Q4 X1994.Q1 X1994.Q2 X1994.Q3
## 1 55.08 55.34 55.25 55.39 55.57 55.93 56.42 57.26
## 2 60.33 60.68 61.27 61.99 62.81 63.46 64.21 64.79
## 3 73.58 74.02 73.59 74.07 73.31 73.10 73.83 73.86
## 4 113.27 111.39 109.36 107.09 107.13 107.96 108.70 108.48
## 5 111.17 110.56 110.84 111.71 112.25 112.51 113.34 113.89
## 6 46.03 45.49 44.82 46.86 49.48 51.90 51.16 50.26
## X1994.Q4 X1995.Q1 X1995.Q2 X1995.Q3 X1995.Q4 X1996.Q1 X1996.Q2 X1996.Q3
## 1 56.48 56.67 55.59 55.46 54.96 54.52 54.87 55.19
## 2 65.20 65.31 65.92 66.32 67.18 67.14 67.56 67.47
## 3 74.22 73.69 72.16 71.44 71.23 70.53 69.68 69.08
## 4 107.21 105.27 103.94 102.25 100.28 98.19 97.16 96.47
## 5 114.26 114.73 114.59 114.28 113.88 114.02 113.65 112.99
## 6 50.96 51.72 53.10 54.57 56.38 56.88 57.53 59.10
## X1996.Q4 X1997.Q1 X1997.Q2 X1997.Q3 X1997.Q4 X1998.Q1 X1998.Q2 X1998.Q3
## 1 54.93 55.21 55.73 56.69 57.42 58.71 59.57 59.77
## 2 67.51 67.38 68.23 68.49 69.44 70.37 71.00 72.35
## 3 68.38 68.79 69.38 72.27 72.14 72.68 72.58 72.39
## 4 94.72 93.40 92.60 92.22 91.76 91.63 91.48 91.34
## 5 112.18 111.08 110.45 108.99 108.50 108.71 108.09 107.84
## 6 61.36 62.86 63.68 64.90 65.70 66.64 68.66 69.66
## X1998.Q4 X1999.Q1 X1999.Q2 X1999.Q3 X1999.Q4 X2000.Q1 X2000.Q2 X2000.Q3
## 1 60.70 61.69 62.59 63.81 65.42 66.20 66.96 65.54
## 2 74.04 75.46 76.52 77.56 77.81 77.74 77.74 78.03
## 3 72.03 72.28 73.09 72.58 73.52 73.80 74.20 73.94
## 4 91.13 91.10 91.00 90.69 90.54 90.19 90.67 90.60
## 5 107.89 108.58 108.36 108.72 109.04 108.92 109.13 109.16
## 6 71.16 71.83 72.03 72.84 72.56 73.25 74.35 75.82
## X2000.Q4 X2001.Q1 X2001.Q2 X2001.Q3 X2001.Q4 X2002.Q1 X2002.Q2 X2002.Q3
## 1 66.67 67.57 69.36 73.13 74.92 77.99 81.93 84.96
## 2 78.81 79.87 79.56 80.19 81.08 81.75 82.89 84.10
## 3 73.63 74.43 74.35 74.92 75.73 78.30 79.17 79.93
## 4 90.53 90.92 91.31 91.96 93.20 94.58 95.59 96.60
## 5 109.00 108.87 108.28 107.36 105.79 105.15 105.33 104.89
## 6 76.28 77.38 77.19 77.95 77.42 78.32 78.63 79.27
## X2002.Q4 X2003.Q1 X2003.Q2 X2003.Q3 X2003.Q4 X2004.Q1 X2004.Q2 X2004.Q3
## 1 87.14 88.74 93.33 98.84 102.20 101.61 99.98 100.04
## 2 85.40 85.78 86.90 87.60 88.72 89.32 89.81 91.32
## 3 81.87 83.49 85.48 87.55 88.70 90.75 93.73 95.02
## 4 97.53 97.75 98.22 98.58 98.92 99.44 99.62 100.06
## 5 103.84 103.03 104.04 103.37 102.49 102.40 101.13 100.04
## 6 79.09 78.89 80.06 81.17 81.31 83.16 85.33 87.97
## X2004.Q4 X2005.Q1 X2005.Q2 X2005.Q3 X2005.Q4 X2006.Q1 X2006.Q2 X2006.Q3
## 1 100.95 100.16 100.01 99.31 100.52 100.79 103.20 105.29
## 2 93.64 96.43 98.73 100.95 103.88 106.23 107.66 109.33
## 3 95.32 97.09 99.49 100.91 102.50 105.79 111.22 114.36
## 4 99.95 99.88 99.61 100.07 100.44 100.89 101.11 100.99
## 5 99.08 100.45 100.29 100.08 99.17 99.23 99.17 98.26
## 6 89.59 92.85 97.12 102.63 107.39 113.92 119.21 121.16
## X2006.Q4 X2007.Q1 X2007.Q2 X2007.Q3 X2007.Q4 X2008.Q1 X2008.Q2 X2008.Q3
## 1 106.53 107.04 110.04 114.14 117.57 117.48 115.34 111.85
## 2 111.44 112.91 114.35 116.01 116.59 116.98 116.52 116.34
## 3 115.19 116.69 121.50 124.12 124.57 126.04 126.95 123.84
## 4 101.46 101.96 101.78 101.71 101.24 101.16 101.84 103.02
## 5 97.61 95.61 95.25 94.84 94.40 94.83 94.41 93.04
## 6 121.56 123.10 122.63 123.11 120.34 118.32 117.57 113.54
## X2008.Q4 X2009.Q1 X2009.Q2 X2009.Q3 X2009.Q4 X2010.Q1 X2010.Q2 X2010.Q3
## 1 109.41 108.02 111.80 116.39 121.51 125.03 126.41 125.26
## 2 117.28 117.41 117.46 117.78 118.42 119.11 119.64 120.56
## 3 120.77 117.66 119.42 122.76 125.07 128.82 131.49 129.70
## 4 104.42 106.09 107.51 108.77 110.28 111.04 112.11 113.55
## 5 93.42 94.16 94.64 94.53 94.76 94.04 93.85 93.66
## 6 104.72 98.93 98.29 98.72 98.45 98.17 99.25 99.15
## X2010.Q4 X2011.Q1 X2011.Q2 X2011.Q3 X2011.Q4 X2012.Q1 X2012.Q2 X2012.Q3
## 1 125.18 122.59 120.23 117.79 116.27 115.96 115.27 114.40
## 2 121.26 121.37 121.44 121.80 122.08 121.94 122.21 122.00
## 3 128.39 130.18 133.23 133.64 132.97 134.81 138.30 137.98
## 4 114.45 115.20 116.67 118.23 119.68 121.08 122.28 123.76
## 5 93.52 94.16 94.53 94.60 94.70 95.09 96.15 97.02
## 6 98.92 95.80 95.86 93.45 90.58 88.91 88.67 88.61
## X2012.Q4 X2013.Q1 X2013.Q2 X2013.Q3 X2013.Q4 X2014.Q1 X2014.Q2 X2014.Q3
## 1 116.01 116.51 118.57 121.18 125.16 126.12 128.02 130.08
## 2 121.78 122.05 121.64 120.94 120.39 119.98 120.18 120.21
## 3 136.60 136.62 139.92 140.17 140.82 142.06 145.06 145.78
## 4 125.63 127.44 129.06 129.79 130.80 130.93 131.56 131.98
## 5 97.66 97.98 99.01 99.11 99.19 99.67 100.81 101.23
## 6 88.93 89.53 90.77 90.45 90.86 91.45 93.21 93.17
## X2014.Q4 X2015.Q1 X2015.Q2 X2015.Q3 X2015.Q4 X2016.Q1 X2016.Q2 X2016.Q3
## 1 132.02 134.01 139.46 142.59 141.82 142.13 144.49 146.95
## 2 120.57 121.07 120.81 120.89 120.85 121.12 121.21 121.73
## 3 146.32 148.47 152.70 154.65 156.18 160.20 172.16 178.14
## 4 132.82 134.30 135.28 135.78 136.48 137.36 137.54 138.22
## 5 101.83 103.20 104.33 105.36 106.47 108.00 109.95 111.61
## 6 93.25 96.35 98.00 98.17 98.67 100.52 100.72 101.44
## X2016.Q4 X2017.Q1
## 1 153.00 156.41
## 2 122.33 122.12
## 3 179.53 186.32
## 4 139.28 140.06
## 5 112.66 113.97
## 6 101.53 101.73colnames(rhpi_wide)[1] <- "country"
head(rhpi_wide)## country X1975.Q1 X1975.Q2 X1975.Q3 X1975.Q4 X1976.Q1 X1976.Q2 X1976.Q3
## 1 Australia 39.16 38.50 38.61 37.76 37.95 38.12 38.36
## 2 Belgium 44.51 45.66 47.01 48.67 50.60 52.26 53.74
## 3 Canada 59.42 59.20 59.89 59.54 59.20 59.85 58.89
## 4 Switzerland 93.83 91.83 90.41 88.91 86.83 85.89 84.98
## 5 Germany 108.59 108.46 109.07 109.30 109.90 110.60 111.24
## 6 Denmark 57.37 57.61 59.07 58.07 58.44 57.28 57.34
## X1976.Q4 X1977.Q1 X1977.Q2 X1977.Q3 X1977.Q4 X1978.Q1 X1978.Q2 X1978.Q3
## 1 37.90 37.94 37.73 37.75 37.42 37.75 37.44 37.55
## 2 55.20 56.32 57.98 59.73 61.40 62.63 63.81 64.90
## 3 57.63 55.91 55.30 54.59 56.12 55.83 56.05 56.14
## 4 85.19 84.54 85.10 85.71 85.65 87.21 88.01 88.94
## 5 112.64 113.76 115.29 116.85 118.75 121.49 122.94 124.55
## 6 58.64 57.49 59.28 60.47 62.72 63.83 64.63 65.13
## X1978.Q4 X1979.Q1 X1979.Q2 X1979.Q3 X1979.Q4 X1980.Q1 X1980.Q2 X1980.Q3
## 1 36.98 37.11 36.94 38.73 40.29 41.57 42.70 43.01
## 2 65.83 67.26 67.84 67.98 67.48 67.07 64.86 62.56
## 3 55.28 55.42 56.19 57.45 58.78 60.13 61.72 63.01
## 4 89.68 90.62 91.13 92.41 93.78 95.43 96.11 97.08
## 5 126.14 126.79 126.28 125.73 125.59 123.74 123.27 124.08
## 6 64.61 64.62 65.48 65.18 63.90 61.29 57.46 57.10
## X1980.Q4 X1981.Q1 X1981.Q2 X1981.Q3 X1981.Q4 X1982.Q1 X1982.Q2 X1982.Q3
## 1 42.59 44.97 45.42 43.80 44.56 43.26 43.25 41.37
## 2 60.33 58.21 56.43 54.74 53.15 51.69 50.47 49.50
## 3 65.70 71.76 76.81 82.90 78.33 75.24 71.67 66.99
## 4 97.44 98.74 100.40 100.55 100.77 101.23 99.71 98.52
## 5 124.98 125.96 126.26 126.06 125.92 125.96 125.57 124.44
## 6 55.86 53.91 49.86 46.95 46.76 45.16 44.14 42.44
## X1982.Q4 X1983.Q1 X1983.Q2 X1983.Q3 X1983.Q4 X1984.Q1 X1984.Q2 X1984.Q3
## 1 40.31 40.37 41.00 40.48 40.80 41.98 42.59 43.97
## 2 48.51 47.34 46.41 45.59 44.93 44.57 44.04 43.48
## 3 61.51 61.24 61.27 61.24 60.37 58.90 59.90 59.21
## 4 98.25 99.20 99.82 101.05 101.55 102.30 102.94 103.18
## 5 123.35 122.94 122.15 120.14 118.86 117.13 115.51 114.66
## 6 42.95 46.05 50.66 50.68 51.46 53.34 53.12 53.17
## X1984.Q4 X1985.Q1 X1985.Q2 X1985.Q3 X1985.Q4 X1986.Q1 X1986.Q2 X1986.Q3
## 1 43.09 44.84 44.06 44.11 44.50 44.29 45.19 44.36
## 2 42.98 41.96 42.00 42.31 42.68 43.21 43.63 44.10
## 3 57.28 56.47 57.18 57.40 58.33 58.46 60.45 61.83
## 4 103.29 104.18 104.50 105.42 106.07 108.26 108.63 110.12
## 5 113.29 111.33 110.52 110.61 110.26 110.77 111.97 111.66
## 6 55.18 56.80 58.48 62.55 64.31 68.15 67.69 65.77
## X1986.Q4 X1987.Q1 X1987.Q2 X1987.Q3 X1987.Q4 X1988.Q1 X1988.Q2 X1988.Q3
## 1 43.79 43.59 43.24 43.39 44.68 45.94 47.49 51.33
## 2 44.52 44.74 45.13 45.59 46.13 47.27 47.91 48.52
## 3 63.48 65.42 69.40 71.89 72.39 71.90 74.42 77.17
## 4 111.59 113.46 115.78 117.77 119.86 124.20 128.50 133.32
## 5 111.36 111.14 110.63 110.20 110.35 111.32 111.33 110.78
## 6 66.39 61.14 59.76 58.92 58.05 58.36 57.12 57.72
## X1988.Q4 X1989.Q1 X1989.Q2 X1989.Q3 X1989.Q4 X1990.Q1 X1990.Q2 X1990.Q3
## 1 55.61 59.37 59.07 58.36 57.62 57.14 56.48 55.70
## 2 49.25 50.28 51.44 52.64 53.83 54.85 55.43 55.81
## 3 78.42 81.19 86.46 84.85 85.01 86.51 87.58 82.69
## 4 138.97 142.29 143.82 144.77 143.59 141.45 138.60 134.90
## 5 111.72 110.82 110.66 111.31 111.15 109.54 109.39 109.80
## 6 57.66 56.25 55.26 53.96 53.20 51.47 49.90 48.80
## X1990.Q4 X1991.Q1 X1991.Q2 X1991.Q3 X1991.Q4 X1992.Q1 X1992.Q2 X1992.Q3
## 1 54.66 54.48 54.91 56.13 55.53 54.99 55.03 54.97
## 2 56.13 56.23 56.58 57.21 58.04 59.05 59.70 60.07
## 3 79.14 74.84 74.17 76.45 76.82 75.19 75.18 74.03
## 4 131.16 129.43 128.12 127.00 124.26 121.60 118.84 115.92
## 5 109.92 110.77 110.71 110.10 109.23 110.29 110.20 110.78
## 6 48.58 48.97 48.93 48.86 49.20 48.88 48.27 47.36
## X1992.Q4 X1993.Q1 X1993.Q2 X1993.Q3 X1993.Q4 X1994.Q1 X1994.Q2 X1994.Q3
## 1 55.08 55.34 55.25 55.39 55.57 55.93 56.42 57.26
## 2 60.33 60.68 61.27 61.99 62.81 63.46 64.21 64.79
## 3 73.58 74.02 73.59 74.07 73.31 73.10 73.83 73.86
## 4 113.27 111.39 109.36 107.09 107.13 107.96 108.70 108.48
## 5 111.17 110.56 110.84 111.71 112.25 112.51 113.34 113.89
## 6 46.03 45.49 44.82 46.86 49.48 51.90 51.16 50.26
## X1994.Q4 X1995.Q1 X1995.Q2 X1995.Q3 X1995.Q4 X1996.Q1 X1996.Q2 X1996.Q3
## 1 56.48 56.67 55.59 55.46 54.96 54.52 54.87 55.19
## 2 65.20 65.31 65.92 66.32 67.18 67.14 67.56 67.47
## 3 74.22 73.69 72.16 71.44 71.23 70.53 69.68 69.08
## 4 107.21 105.27 103.94 102.25 100.28 98.19 97.16 96.47
## 5 114.26 114.73 114.59 114.28 113.88 114.02 113.65 112.99
## 6 50.96 51.72 53.10 54.57 56.38 56.88 57.53 59.10
## X1996.Q4 X1997.Q1 X1997.Q2 X1997.Q3 X1997.Q4 X1998.Q1 X1998.Q2 X1998.Q3
## 1 54.93 55.21 55.73 56.69 57.42 58.71 59.57 59.77
## 2 67.51 67.38 68.23 68.49 69.44 70.37 71.00 72.35
## 3 68.38 68.79 69.38 72.27 72.14 72.68 72.58 72.39
## 4 94.72 93.40 92.60 92.22 91.76 91.63 91.48 91.34
## 5 112.18 111.08 110.45 108.99 108.50 108.71 108.09 107.84
## 6 61.36 62.86 63.68 64.90 65.70 66.64 68.66 69.66
## X1998.Q4 X1999.Q1 X1999.Q2 X1999.Q3 X1999.Q4 X2000.Q1 X2000.Q2 X2000.Q3
## 1 60.70 61.69 62.59 63.81 65.42 66.20 66.96 65.54
## 2 74.04 75.46 76.52 77.56 77.81 77.74 77.74 78.03
## 3 72.03 72.28 73.09 72.58 73.52 73.80 74.20 73.94
## 4 91.13 91.10 91.00 90.69 90.54 90.19 90.67 90.60
## 5 107.89 108.58 108.36 108.72 109.04 108.92 109.13 109.16
## 6 71.16 71.83 72.03 72.84 72.56 73.25 74.35 75.82
## X2000.Q4 X2001.Q1 X2001.Q2 X2001.Q3 X2001.Q4 X2002.Q1 X2002.Q2 X2002.Q3
## 1 66.67 67.57 69.36 73.13 74.92 77.99 81.93 84.96
## 2 78.81 79.87 79.56 80.19 81.08 81.75 82.89 84.10
## 3 73.63 74.43 74.35 74.92 75.73 78.30 79.17 79.93
## 4 90.53 90.92 91.31 91.96 93.20 94.58 95.59 96.60
## 5 109.00 108.87 108.28 107.36 105.79 105.15 105.33 104.89
## 6 76.28 77.38 77.19 77.95 77.42 78.32 78.63 79.27
## X2002.Q4 X2003.Q1 X2003.Q2 X2003.Q3 X2003.Q4 X2004.Q1 X2004.Q2 X2004.Q3
## 1 87.14 88.74 93.33 98.84 102.20 101.61 99.98 100.04
## 2 85.40 85.78 86.90 87.60 88.72 89.32 89.81 91.32
## 3 81.87 83.49 85.48 87.55 88.70 90.75 93.73 95.02
## 4 97.53 97.75 98.22 98.58 98.92 99.44 99.62 100.06
## 5 103.84 103.03 104.04 103.37 102.49 102.40 101.13 100.04
## 6 79.09 78.89 80.06 81.17 81.31 83.16 85.33 87.97
## X2004.Q4 X2005.Q1 X2005.Q2 X2005.Q3 X2005.Q4 X2006.Q1 X2006.Q2 X2006.Q3
## 1 100.95 100.16 100.01 99.31 100.52 100.79 103.20 105.29
## 2 93.64 96.43 98.73 100.95 103.88 106.23 107.66 109.33
## 3 95.32 97.09 99.49 100.91 102.50 105.79 111.22 114.36
## 4 99.95 99.88 99.61 100.07 100.44 100.89 101.11 100.99
## 5 99.08 100.45 100.29 100.08 99.17 99.23 99.17 98.26
## 6 89.59 92.85 97.12 102.63 107.39 113.92 119.21 121.16
## X2006.Q4 X2007.Q1 X2007.Q2 X2007.Q3 X2007.Q4 X2008.Q1 X2008.Q2 X2008.Q3
## 1 106.53 107.04 110.04 114.14 117.57 117.48 115.34 111.85
## 2 111.44 112.91 114.35 116.01 116.59 116.98 116.52 116.34
## 3 115.19 116.69 121.50 124.12 124.57 126.04 126.95 123.84
## 4 101.46 101.96 101.78 101.71 101.24 101.16 101.84 103.02
## 5 97.61 95.61 95.25 94.84 94.40 94.83 94.41 93.04
## 6 121.56 123.10 122.63 123.11 120.34 118.32 117.57 113.54
## X2008.Q4 X2009.Q1 X2009.Q2 X2009.Q3 X2009.Q4 X2010.Q1 X2010.Q2 X2010.Q3
## 1 109.41 108.02 111.80 116.39 121.51 125.03 126.41 125.26
## 2 117.28 117.41 117.46 117.78 118.42 119.11 119.64 120.56
## 3 120.77 117.66 119.42 122.76 125.07 128.82 131.49 129.70
## 4 104.42 106.09 107.51 108.77 110.28 111.04 112.11 113.55
## 5 93.42 94.16 94.64 94.53 94.76 94.04 93.85 93.66
## 6 104.72 98.93 98.29 98.72 98.45 98.17 99.25 99.15
## X2010.Q4 X2011.Q1 X2011.Q2 X2011.Q3 X2011.Q4 X2012.Q1 X2012.Q2 X2012.Q3
## 1 125.18 122.59 120.23 117.79 116.27 115.96 115.27 114.40
## 2 121.26 121.37 121.44 121.80 122.08 121.94 122.21 122.00
## 3 128.39 130.18 133.23 133.64 132.97 134.81 138.30 137.98
## 4 114.45 115.20 116.67 118.23 119.68 121.08 122.28 123.76
## 5 93.52 94.16 94.53 94.60 94.70 95.09 96.15 97.02
## 6 98.92 95.80 95.86 93.45 90.58 88.91 88.67 88.61
## X2012.Q4 X2013.Q1 X2013.Q2 X2013.Q3 X2013.Q4 X2014.Q1 X2014.Q2 X2014.Q3
## 1 116.01 116.51 118.57 121.18 125.16 126.12 128.02 130.08
## 2 121.78 122.05 121.64 120.94 120.39 119.98 120.18 120.21
## 3 136.60 136.62 139.92 140.17 140.82 142.06 145.06 145.78
## 4 125.63 127.44 129.06 129.79 130.80 130.93 131.56 131.98
## 5 97.66 97.98 99.01 99.11 99.19 99.67 100.81 101.23
## 6 88.93 89.53 90.77 90.45 90.86 91.45 93.21 93.17
## X2014.Q4 X2015.Q1 X2015.Q2 X2015.Q3 X2015.Q4 X2016.Q1 X2016.Q2 X2016.Q3
## 1 132.02 134.01 139.46 142.59 141.82 142.13 144.49 146.95
## 2 120.57 121.07 120.81 120.89 120.85 121.12 121.21 121.73
## 3 146.32 148.47 152.70 154.65 156.18 160.20 172.16 178.14
## 4 132.82 134.30 135.28 135.78 136.48 137.36 137.54 138.22
## 5 101.83 103.20 104.33 105.36 106.47 108.00 109.95 111.61
## 6 93.25 96.35 98.00 98.17 98.67 100.52 100.72 101.44
## X2016.Q4 X2017.Q1
## 1 153.00 156.41
## 2 122.33 122.12
## 3 179.53 186.32
## 4 139.28 140.06
## 5 112.66 113.97
## 6 101.53 101.733. Convert the HPI data from wide to long
The original HPI data is in wide format. Each row represents a country and each column represents a quarter. This means that each row has the HPI values of one country and different quarters.
Here we would like to change it to long format so that each row has the a HPI value of one country and one quarter:
take a look of the
pivot_wider()function and convert the the HPI data into long format..use
head()to take a look of the datayou will notice that the quarter value somehow has the X at the beginning (e.g. X1970.Q3 instead of 1970.Q3). If you want to remove the X from the quarter value, take a look of the
substring()function.
rhpi_long <- rhpi_wide %>%
pivot_longer(cols = colnames(rhpi_wide)[2:length(colnames(rhpi_wide))],
names_to = "quarter", values_to = "rhpi")
rhpi_long$quarter <- substring(rhpi_long$quarter, first = 2)4. Load the PDI data and convert the PDI data from wide to long
rpdi_wide <- read.csv("rpdi.csv")
rpdi_long <- rpdi_wide %>%
pivot_longer(cols = -X, names_to = "quarter", values_to = "rpdi")
rpdi_long$quarter <- substring(rpdi_long$quarter, first = 2)
# use the rename() function from dplyr to rename that first column 'X' to country in order to merge next
rpdi_long <- rpdi_long %>%
rename(country = X)5. Merge the long format HPI and PDI data
take a look of the
merge())function and merge the two dataframes together. You should provide the “by” argument. What should be the unique identifier? Country? Quarter? Or both?take a look of the merged data using
head())to make sure the data are merged properly.
merged <- merge(rhpi_long, rpdi_long, by = c("quarter", "country"))
head(merged)## quarter country rhpi rpdi
## 1 1975.Q1 Australia 39.16 72.13
## 2 1975.Q1 Belgium 44.51 66.15
## 3 1975.Q1 Canada 59.42 70.62
## 4 1975.Q1 Croatia 73.17 8.43
## 5 1975.Q1 Denmark 57.37 70.21
## 6 1975.Q1 Finland 54.20 52.89# in dplyr you can use mutating joins -- see ?join
joined <- full_join(rhpi_long, rpdi_long)6. Filter the data
take a look of the
which())function. This function will return you the indices that a given condition is fulfilled.filter the data so that only Australia data is shown.
use
head())andtail()functions to take a look of the data to make sure you filtered the data correctly.
# which provides you the indices that satisfy the search condition, in this case, Australia:
which(merged$country == "Australia")## [1] 1 24 47 70 93 116 139 162 185 208 231 254 277 300 323
## [16] 346 369 392 415 438 461 484 507 530 553 576 599 622 645 668
## [31] 691 714 737 760 783 806 829 852 875 898 921 944 967 990 1013
## [46] 1036 1059 1082 1105 1128 1151 1174 1197 1220 1243 1266 1289 1312 1335 1358
## [61] 1381 1404 1427 1450 1473 1496 1519 1542 1565 1588 1611 1634 1657 1680 1703
## [76] 1726 1749 1772 1795 1818 1841 1864 1887 1910 1933 1956 1979 2002 2025 2048
## [91] 2071 2094 2117 2140 2163 2186 2209 2232 2255 2278 2301 2324 2347 2370 2393
## [106] 2416 2439 2462 2485 2508 2531 2554 2577 2600 2623 2646 2669 2692 2715 2738
## [121] 2761 2784 2807 2830 2853 2876 2899 2922 2945 2968 2991 3014 3037 3060 3083
## [136] 3106 3129 3152 3175 3198 3221 3244 3267 3290 3313 3336 3359 3382 3405 3428
## [151] 3451 3474 3497 3520 3543 3566 3589 3612 3635 3658 3681 3704 3727 3750 3773
## [166] 3796 3819 3842 3865aus <- merged[which(merged$country == "Australia"), ]
# subset is similar and is easier, but which allows you to explore the indices which might be useful
subset_aus <- subset(merged, country == "Australia")
head(subset_aus)## quarter country rhpi rpdi
## 1 1975.Q1 Australia 39.16 72.13
## 24 1975.Q2 Australia 38.50 71.41
## 47 1975.Q3 Australia 38.61 70.63
## 70 1975.Q4 Australia 37.76 69.63
## 93 1976.Q1 Australia 37.95 69.25
## 116 1976.Q2 Australia 38.12 69.64# or you can use filter() from dplyr
merged %>%
filter(country=="Australia") %>%
head()## quarter country rhpi rpdi
## 1 1975.Q1 Australia 39.16 72.13
## 2 1975.Q2 Australia 38.50 71.41
## 3 1975.Q3 Australia 38.61 70.63
## 4 1975.Q4 Australia 37.76 69.63
## 5 1976.Q1 Australia 37.95 69.25
## 6 1976.Q2 Australia 38.12 69.647. Make the data type into a data column
Try to use the pivot_longer() function again to produce a dataframe that is similar to this object:
melted_df.png
merged_long <- pivot_longer(merged, cols = c("rhpi", "rpdi"),
names_to = "type", values_to = "value")
head(merged_long)## # A tibble: 6 x 4
## quarter country type value
## <chr> <chr> <chr> <dbl>
## 1 1975.Q1 Australia rhpi 39.2
## 2 1975.Q1 Australia rpdi 72.1
## 3 1975.Q1 Belgium rhpi 44.5
## 4 1975.Q1 Belgium rpdi 66.2
## 5 1975.Q1 Canada rhpi 59.4
## 6 1975.Q1 Canada rpdi 70.6tail(merged_long)## # A tibble: 6 x 4
## quarter country type value
## <chr> <chr> <chr> <dbl>
## 1 2017.Q1 Switzerland rhpi 140.
## 2 2017.Q1 Switzerland rpdi 115.
## 3 2017.Q1 UK rhpi 111.
## 4 2017.Q1 UK rpdi 108.
## 5 2017.Q1 US rhpi 92.3
## 6 2017.Q1 US rpdi 111.