Reshaping data in R

In this part of the assignment, we will again reshape rhpi.csv and rpdi.csv, but this time we will use R to do it!

Take a look of the assignment-2-complex-example-python.ipynb and try to do the same using R. You only need to fill in the part 1-6 here. You do not need to do the pivot table.

You may find it useful to learn more how to use dataframe in R, read the how-to-manipulate-dataframe.ipynb.

If you get stuck somewhere, feel free to contact us! As this is a formative assignment, we are happy to give you a few lines of codes to help you to start.

1. Reshaping a simple example.

  1. From the cmpdata object, replicate the reshaped object cmpdataLong from the lecture notes, using the reshape2 package. (Just use the same syntax as in the lecture notes.)
library("reshape2")
load("cmpdata.Rdata")

cmpdataLong <- melt(cmpdata,
                    id.vars = c("countryname", "party", "date"),
                    measure.vars = names(cmpdata)[21:76],
                    variable.name = "category",
                    value.name = "catcount")
  1. Try using tidyr on cmpdataLong to spread() this back into the wide format.
library("tidyr")
## 
## Attaching package: 'tidyr'
## The following object is masked from 'package:reshape2':
## 
##     smiths
cmpdata <- spread(cmpdataLong, key = category, value = catcount)
head(cmpdata)
##   countryname party   date per101 per102 per103 per104 per105 per106
## 1     Austria 42110 199010      0      0      2      0      3      0
## 2     Austria 42110 199410      0      0      0      0      0      3
## 3     Austria 42110 199512      0      0      0      0      1      1
## 4     Austria 42110 199910      0      0      0      0     12      2
## 5     Austria 42110 200211      0      0      0      0      9      4
## 6     Austria 42110 200610      0      0      0      3      9      2
##   per107 per108 per109 per110 per201 per202 per203 per204 per301 per302
## 1     17      0      0      3     79    230     24      0     14      2
## 2      2      2      0      1     20     67     21      0      0      0
## 3      1      6      2      6     12      7      1      0      0      0
## 4      2      0      0      1     15      3      0      0      0      0
## 5      7     13      0      0     37     17      0      0      0      0
## 6     52     36     11      0     36     17      1      0      0      0
##   per303 per304 per305 per401 per402 per403 per404 per405 per406 per407
## 1     67     39     14      5      8      3      2      0      2      0
## 2     19     27     83      1      0      4      0      0      0      0
## 3      1      0     17      0      0      1      0      0      0      0
## 4      0      1     20      0      0      0      0      0      0      0
## 5      0      0     14      0      6      2      0      0      0      0
## 6      3      0      1      2      6     20      0      0      0      0
##   per408 per409 per410 per411 per412 per413 per414 per415 per416 per501
## 1     24      2      5     11      5      0      0      0     38    317
## 2     21      0     32     68      0      0      0      0      1    239
## 3      9      0      5      2      0      0      5      0      0      9
## 4      1      0      0      0      0      0      0      0      4     25
## 5      3      4      1     22      0      0      0      0     34     42
## 6      1      1      0     38     17      1      0      0     13     62
##   per502 per503 per504 per505 per506 per507 per601 per602 per603 per604
## 1     45    105     73      0     17      0      0     14     20     20
## 2      0      3      3      0      1      0      0      0      0      0
## 3      0     14      3      0      2      0      0      4      1      0
## 4      0     20     19      0      0      0      0      0      0      4
## 5     18     45     35      0     33      0      0      2      0      1
## 6     48     83     24      0     46      0     14      0      0     20
##   per605 per606 per607 per608 per701 per702 per703 per704 per705 per706
## 1     29     23     14      0     36      0     33      2    100     49
## 2      0      6      0      0      0      0     51      0     90      1
## 3      0      3      0      0      0      0      0      0      4      0
## 4      1      1      0      0      1      0      0      0      7     17
## 5      8      0     12      0     35      0      6      0     15     35
## 6      9     10     15      5     33      0     13      9      0     32

2. Load the HPI data

  1. 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?

  2. take a look of the read.csv function and read the csv data to R.

  3. after the data is loaded into R, you can use head function to take a look of the first few rows of data. Is it in long or wide format?

  4. you may find out that the first column name is empty. You may update the first column name to “country” by using colnames function. And remember R starts counting from 1!

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
## 1   Australia    39.16    38.50    38.61    37.76    37.95    38.12
## 2     Belgium    44.51    45.66    47.01    48.67    50.60    52.26
## 3      Canada    59.42    59.20    59.89    59.54    59.20    59.85
## 4 Switzerland    93.83    91.83    90.41    88.91    86.83    85.89
## 5     Germany   108.59   108.46   109.07   109.30   109.90   110.60
## 6     Denmark    57.37    57.61    59.07    58.07    58.44    57.28
##   X1976.Q3 X1976.Q4 X1977.Q1 X1977.Q2 X1977.Q3 X1977.Q4 X1978.Q1 X1978.Q2
## 1    38.36    37.90    37.94    37.73    37.75    37.42    37.75    37.44
## 2    53.74    55.20    56.32    57.98    59.73    61.40    62.63    63.81
## 3    58.89    57.63    55.91    55.30    54.59    56.12    55.83    56.05
## 4    84.98    85.19    84.54    85.10    85.71    85.65    87.21    88.01
## 5   111.24   112.64   113.76   115.29   116.85   118.75   121.49   122.94
## 6    57.34    58.64    57.49    59.28    60.47    62.72    63.83    64.63
##   X1978.Q3 X1978.Q4 X1979.Q1 X1979.Q2 X1979.Q3 X1979.Q4 X1980.Q1 X1980.Q2
## 1    37.55    36.98    37.11    36.94    38.73    40.29    41.57    42.70
## 2    64.90    65.83    67.26    67.84    67.98    67.48    67.07    64.86
## 3    56.14    55.28    55.42    56.19    57.45    58.78    60.13    61.72
## 4    88.94    89.68    90.62    91.13    92.41    93.78    95.43    96.11
## 5   124.55   126.14   126.79   126.28   125.73   125.59   123.74   123.27
## 6    65.13    64.61    64.62    65.48    65.18    63.90    61.29    57.46
##   X1980.Q3 X1980.Q4 X1981.Q1 X1981.Q2 X1981.Q3 X1981.Q4 X1982.Q1 X1982.Q2
## 1    43.01    42.59    44.97    45.42    43.80    44.56    43.26    43.25
## 2    62.56    60.33    58.21    56.43    54.74    53.15    51.69    50.47
## 3    63.01    65.70    71.76    76.81    82.90    78.33    75.24    71.67
## 4    97.08    97.44    98.74   100.40   100.55   100.77   101.23    99.71
## 5   124.08   124.98   125.96   126.26   126.06   125.92   125.96   125.57
## 6    57.10    55.86    53.91    49.86    46.95    46.76    45.16    44.14
##   X1982.Q3 X1982.Q4 X1983.Q1 X1983.Q2 X1983.Q3 X1983.Q4 X1984.Q1 X1984.Q2
## 1    41.37    40.31    40.37    41.00    40.48    40.80    41.98    42.59
## 2    49.50    48.51    47.34    46.41    45.59    44.93    44.57    44.04
## 3    66.99    61.51    61.24    61.27    61.24    60.37    58.90    59.90
## 4    98.52    98.25    99.20    99.82   101.05   101.55   102.30   102.94
## 5   124.44   123.35   122.94   122.15   120.14   118.86   117.13   115.51
## 6    42.44    42.95    46.05    50.66    50.68    51.46    53.34    53.12
##   X1984.Q3 X1984.Q4 X1985.Q1 X1985.Q2 X1985.Q3 X1985.Q4 X1986.Q1 X1986.Q2
## 1    43.97    43.09    44.84    44.06    44.11    44.50    44.29    45.19
## 2    43.48    42.98    41.96    42.00    42.31    42.68    43.21    43.63
## 3    59.21    57.28    56.47    57.18    57.40    58.33    58.46    60.45
## 4   103.18   103.29   104.18   104.50   105.42   106.07   108.26   108.63
## 5   114.66   113.29   111.33   110.52   110.61   110.26   110.77   111.97
## 6    53.17    55.18    56.80    58.48    62.55    64.31    68.15    67.69
##   X1986.Q3 X1986.Q4 X1987.Q1 X1987.Q2 X1987.Q3 X1987.Q4 X1988.Q1 X1988.Q2
## 1    44.36    43.79    43.59    43.24    43.39    44.68    45.94    47.49
## 2    44.10    44.52    44.74    45.13    45.59    46.13    47.27    47.91
## 3    61.83    63.48    65.42    69.40    71.89    72.39    71.90    74.42
## 4   110.12   111.59   113.46   115.78   117.77   119.86   124.20   128.50
## 5   111.66   111.36   111.14   110.63   110.20   110.35   111.32   111.33
## 6    65.77    66.39    61.14    59.76    58.92    58.05    58.36    57.12
##   X1988.Q3 X1988.Q4 X1989.Q1 X1989.Q2 X1989.Q3 X1989.Q4 X1990.Q1 X1990.Q2
## 1    51.33    55.61    59.37    59.07    58.36    57.62    57.14    56.48
## 2    48.52    49.25    50.28    51.44    52.64    53.83    54.85    55.43
## 3    77.17    78.42    81.19    86.46    84.85    85.01    86.51    87.58
## 4   133.32   138.97   142.29   143.82   144.77   143.59   141.45   138.60
## 5   110.78   111.72   110.82   110.66   111.31   111.15   109.54   109.39
## 6    57.72    57.66    56.25    55.26    53.96    53.20    51.47    49.90
##   X1990.Q3 X1990.Q4 X1991.Q1 X1991.Q2 X1991.Q3 X1991.Q4 X1992.Q1 X1992.Q2
## 1    55.70    54.66    54.48    54.91    56.13    55.53    54.99    55.03
## 2    55.81    56.13    56.23    56.58    57.21    58.04    59.05    59.70
## 3    82.69    79.14    74.84    74.17    76.45    76.82    75.19    75.18
## 4   134.90   131.16   129.43   128.12   127.00   124.26   121.60   118.84
## 5   109.80   109.92   110.77   110.71   110.10   109.23   110.29   110.20
## 6    48.80    48.58    48.97    48.93    48.86    49.20    48.88    48.27
##   X1992.Q3 X1992.Q4 X1993.Q1 X1993.Q2 X1993.Q3 X1993.Q4 X1994.Q1 X1994.Q2
## 1    54.97    55.08    55.34    55.25    55.39    55.57    55.93    56.42
## 2    60.07    60.33    60.68    61.27    61.99    62.81    63.46    64.21
## 3    74.03    73.58    74.02    73.59    74.07    73.31    73.10    73.83
## 4   115.92   113.27   111.39   109.36   107.09   107.13   107.96   108.70
## 5   110.78   111.17   110.56   110.84   111.71   112.25   112.51   113.34
## 6    47.36    46.03    45.49    44.82    46.86    49.48    51.90    51.16
##   X1994.Q3 X1994.Q4 X1995.Q1 X1995.Q2 X1995.Q3 X1995.Q4 X1996.Q1 X1996.Q2
## 1    57.26    56.48    56.67    55.59    55.46    54.96    54.52    54.87
## 2    64.79    65.20    65.31    65.92    66.32    67.18    67.14    67.56
## 3    73.86    74.22    73.69    72.16    71.44    71.23    70.53    69.68
## 4   108.48   107.21   105.27   103.94   102.25   100.28    98.19    97.16
## 5   113.89   114.26   114.73   114.59   114.28   113.88   114.02   113.65
## 6    50.26    50.96    51.72    53.10    54.57    56.38    56.88    57.53
##   X1996.Q3 X1996.Q4 X1997.Q1 X1997.Q2 X1997.Q3 X1997.Q4 X1998.Q1 X1998.Q2
## 1    55.19    54.93    55.21    55.73    56.69    57.42    58.71    59.57
## 2    67.47    67.51    67.38    68.23    68.49    69.44    70.37    71.00
## 3    69.08    68.38    68.79    69.38    72.27    72.14    72.68    72.58
## 4    96.47    94.72    93.40    92.60    92.22    91.76    91.63    91.48
## 5   112.99   112.18   111.08   110.45   108.99   108.50   108.71   108.09
## 6    59.10    61.36    62.86    63.68    64.90    65.70    66.64    68.66
##   X1998.Q3 X1998.Q4 X1999.Q1 X1999.Q2 X1999.Q3 X1999.Q4 X2000.Q1 X2000.Q2
## 1    59.77    60.70    61.69    62.59    63.81    65.42    66.20    66.96
## 2    72.35    74.04    75.46    76.52    77.56    77.81    77.74    77.74
## 3    72.39    72.03    72.28    73.09    72.58    73.52    73.80    74.20
## 4    91.34    91.13    91.10    91.00    90.69    90.54    90.19    90.67
## 5   107.84   107.89   108.58   108.36   108.72   109.04   108.92   109.13
## 6    69.66    71.16    71.83    72.03    72.84    72.56    73.25    74.35
##   X2000.Q3 X2000.Q4 X2001.Q1 X2001.Q2 X2001.Q3 X2001.Q4 X2002.Q1 X2002.Q2
## 1    65.54    66.67    67.57    69.36    73.13    74.92    77.99    81.93
## 2    78.03    78.81    79.87    79.56    80.19    81.08    81.75    82.89
## 3    73.94    73.63    74.43    74.35    74.92    75.73    78.30    79.17
## 4    90.60    90.53    90.92    91.31    91.96    93.20    94.58    95.59
## 5   109.16   109.00   108.87   108.28   107.36   105.79   105.15   105.33
## 6    75.82    76.28    77.38    77.19    77.95    77.42    78.32    78.63
##   X2002.Q3 X2002.Q4 X2003.Q1 X2003.Q2 X2003.Q3 X2003.Q4 X2004.Q1 X2004.Q2
## 1    84.96    87.14    88.74    93.33    98.84   102.20   101.61    99.98
## 2    84.10    85.40    85.78    86.90    87.60    88.72    89.32    89.81
## 3    79.93    81.87    83.49    85.48    87.55    88.70    90.75    93.73
## 4    96.60    97.53    97.75    98.22    98.58    98.92    99.44    99.62
## 5   104.89   103.84   103.03   104.04   103.37   102.49   102.40   101.13
## 6    79.27    79.09    78.89    80.06    81.17    81.31    83.16    85.33
##   X2004.Q3 X2004.Q4 X2005.Q1 X2005.Q2 X2005.Q3 X2005.Q4 X2006.Q1 X2006.Q2
## 1   100.04   100.95   100.16   100.01    99.31   100.52   100.79   103.20
## 2    91.32    93.64    96.43    98.73   100.95   103.88   106.23   107.66
## 3    95.02    95.32    97.09    99.49   100.91   102.50   105.79   111.22
## 4   100.06    99.95    99.88    99.61   100.07   100.44   100.89   101.11
## 5   100.04    99.08   100.45   100.29   100.08    99.17    99.23    99.17
## 6    87.97    89.59    92.85    97.12   102.63   107.39   113.92   119.21
##   X2006.Q3 X2006.Q4 X2007.Q1 X2007.Q2 X2007.Q3 X2007.Q4 X2008.Q1 X2008.Q2
## 1   105.29   106.53   107.04   110.04   114.14   117.57   117.48   115.34
## 2   109.33   111.44   112.91   114.35   116.01   116.59   116.98   116.52
## 3   114.36   115.19   116.69   121.50   124.12   124.57   126.04   126.95
## 4   100.99   101.46   101.96   101.78   101.71   101.24   101.16   101.84
## 5    98.26    97.61    95.61    95.25    94.84    94.40    94.83    94.41
## 6   121.16   121.56   123.10   122.63   123.11   120.34   118.32   117.57
##   X2008.Q3 X2008.Q4 X2009.Q1 X2009.Q2 X2009.Q3 X2009.Q4 X2010.Q1 X2010.Q2
## 1   111.85   109.41   108.02   111.80   116.39   121.51   125.03   126.41
## 2   116.34   117.28   117.41   117.46   117.78   118.42   119.11   119.64
## 3   123.84   120.77   117.66   119.42   122.76   125.07   128.82   131.49
## 4   103.02   104.42   106.09   107.51   108.77   110.28   111.04   112.11
## 5    93.04    93.42    94.16    94.64    94.53    94.76    94.04    93.85
## 6   113.54   104.72    98.93    98.29    98.72    98.45    98.17    99.25
##   X2010.Q3 X2010.Q4 X2011.Q1 X2011.Q2 X2011.Q3 X2011.Q4 X2012.Q1 X2012.Q2
## 1   125.26   125.18   122.59   120.23   117.79   116.27   115.96   115.27
## 2   120.56   121.26   121.37   121.44   121.80   122.08   121.94   122.21
## 3   129.70   128.39   130.18   133.23   133.64   132.97   134.81   138.30
## 4   113.55   114.45   115.20   116.67   118.23   119.68   121.08   122.28
## 5    93.66    93.52    94.16    94.53    94.60    94.70    95.09    96.15
## 6    99.15    98.92    95.80    95.86    93.45    90.58    88.91    88.67
##   X2012.Q3 X2012.Q4 X2013.Q1 X2013.Q2 X2013.Q3 X2013.Q4 X2014.Q1 X2014.Q2
## 1   114.40   116.01   116.51   118.57   121.18   125.16   126.12   128.02
## 2   122.00   121.78   122.05   121.64   120.94   120.39   119.98   120.18
## 3   137.98   136.60   136.62   139.92   140.17   140.82   142.06   145.06
## 4   123.76   125.63   127.44   129.06   129.79   130.80   130.93   131.56
## 5    97.02    97.66    97.98    99.01    99.11    99.19    99.67   100.81
## 6    88.61    88.93    89.53    90.77    90.45    90.86    91.45    93.21
##   X2014.Q3 X2014.Q4 X2015.Q1 X2015.Q2 X2015.Q3 X2015.Q4 X2016.Q1 X2016.Q2
## 1   130.08   132.02   134.01   139.46   142.59   141.82   142.13   144.49
## 2   120.21   120.57   121.07   120.81   120.89   120.85   121.12   121.21
## 3   145.78   146.32   148.47   152.70   154.65   156.18   160.20   172.16
## 4   131.98   132.82   134.30   135.28   135.78   136.48   137.36   137.54
## 5   101.23   101.83   103.20   104.33   105.36   106.47   108.00   109.95
## 6    93.17    93.25    96.35    98.00    98.17    98.67   100.52   100.72
##   X2016.Q3 X2016.Q4 X2017.Q1
## 1   146.95   153.00   156.41
## 2   121.73   122.33   122.12
## 3   178.14   179.53   186.32
## 4   138.22   139.28   140.06
## 5   111.61   112.66   113.97
## 6   101.44   101.53   101.73
colnames(rhpi_wide)[1] <- "country"
head(rhpi_wide)
##       country X1975.Q1 X1975.Q2 X1975.Q3 X1975.Q4 X1976.Q1 X1976.Q2
## 1   Australia    39.16    38.50    38.61    37.76    37.95    38.12
## 2     Belgium    44.51    45.66    47.01    48.67    50.60    52.26
## 3      Canada    59.42    59.20    59.89    59.54    59.20    59.85
## 4 Switzerland    93.83    91.83    90.41    88.91    86.83    85.89
## 5     Germany   108.59   108.46   109.07   109.30   109.90   110.60
## 6     Denmark    57.37    57.61    59.07    58.07    58.44    57.28
##   X1976.Q3 X1976.Q4 X1977.Q1 X1977.Q2 X1977.Q3 X1977.Q4 X1978.Q1 X1978.Q2
## 1    38.36    37.90    37.94    37.73    37.75    37.42    37.75    37.44
## 2    53.74    55.20    56.32    57.98    59.73    61.40    62.63    63.81
## 3    58.89    57.63    55.91    55.30    54.59    56.12    55.83    56.05
## 4    84.98    85.19    84.54    85.10    85.71    85.65    87.21    88.01
## 5   111.24   112.64   113.76   115.29   116.85   118.75   121.49   122.94
## 6    57.34    58.64    57.49    59.28    60.47    62.72    63.83    64.63
##   X1978.Q3 X1978.Q4 X1979.Q1 X1979.Q2 X1979.Q3 X1979.Q4 X1980.Q1 X1980.Q2
## 1    37.55    36.98    37.11    36.94    38.73    40.29    41.57    42.70
## 2    64.90    65.83    67.26    67.84    67.98    67.48    67.07    64.86
## 3    56.14    55.28    55.42    56.19    57.45    58.78    60.13    61.72
## 4    88.94    89.68    90.62    91.13    92.41    93.78    95.43    96.11
## 5   124.55   126.14   126.79   126.28   125.73   125.59   123.74   123.27
## 6    65.13    64.61    64.62    65.48    65.18    63.90    61.29    57.46
##   X1980.Q3 X1980.Q4 X1981.Q1 X1981.Q2 X1981.Q3 X1981.Q4 X1982.Q1 X1982.Q2
## 1    43.01    42.59    44.97    45.42    43.80    44.56    43.26    43.25
## 2    62.56    60.33    58.21    56.43    54.74    53.15    51.69    50.47
## 3    63.01    65.70    71.76    76.81    82.90    78.33    75.24    71.67
## 4    97.08    97.44    98.74   100.40   100.55   100.77   101.23    99.71
## 5   124.08   124.98   125.96   126.26   126.06   125.92   125.96   125.57
## 6    57.10    55.86    53.91    49.86    46.95    46.76    45.16    44.14
##   X1982.Q3 X1982.Q4 X1983.Q1 X1983.Q2 X1983.Q3 X1983.Q4 X1984.Q1 X1984.Q2
## 1    41.37    40.31    40.37    41.00    40.48    40.80    41.98    42.59
## 2    49.50    48.51    47.34    46.41    45.59    44.93    44.57    44.04
## 3    66.99    61.51    61.24    61.27    61.24    60.37    58.90    59.90
## 4    98.52    98.25    99.20    99.82   101.05   101.55   102.30   102.94
## 5   124.44   123.35   122.94   122.15   120.14   118.86   117.13   115.51
## 6    42.44    42.95    46.05    50.66    50.68    51.46    53.34    53.12
##   X1984.Q3 X1984.Q4 X1985.Q1 X1985.Q2 X1985.Q3 X1985.Q4 X1986.Q1 X1986.Q2
## 1    43.97    43.09    44.84    44.06    44.11    44.50    44.29    45.19
## 2    43.48    42.98    41.96    42.00    42.31    42.68    43.21    43.63
## 3    59.21    57.28    56.47    57.18    57.40    58.33    58.46    60.45
## 4   103.18   103.29   104.18   104.50   105.42   106.07   108.26   108.63
## 5   114.66   113.29   111.33   110.52   110.61   110.26   110.77   111.97
## 6    53.17    55.18    56.80    58.48    62.55    64.31    68.15    67.69
##   X1986.Q3 X1986.Q4 X1987.Q1 X1987.Q2 X1987.Q3 X1987.Q4 X1988.Q1 X1988.Q2
## 1    44.36    43.79    43.59    43.24    43.39    44.68    45.94    47.49
## 2    44.10    44.52    44.74    45.13    45.59    46.13    47.27    47.91
## 3    61.83    63.48    65.42    69.40    71.89    72.39    71.90    74.42
## 4   110.12   111.59   113.46   115.78   117.77   119.86   124.20   128.50
## 5   111.66   111.36   111.14   110.63   110.20   110.35   111.32   111.33
## 6    65.77    66.39    61.14    59.76    58.92    58.05    58.36    57.12
##   X1988.Q3 X1988.Q4 X1989.Q1 X1989.Q2 X1989.Q3 X1989.Q4 X1990.Q1 X1990.Q2
## 1    51.33    55.61    59.37    59.07    58.36    57.62    57.14    56.48
## 2    48.52    49.25    50.28    51.44    52.64    53.83    54.85    55.43
## 3    77.17    78.42    81.19    86.46    84.85    85.01    86.51    87.58
## 4   133.32   138.97   142.29   143.82   144.77   143.59   141.45   138.60
## 5   110.78   111.72   110.82   110.66   111.31   111.15   109.54   109.39
## 6    57.72    57.66    56.25    55.26    53.96    53.20    51.47    49.90
##   X1990.Q3 X1990.Q4 X1991.Q1 X1991.Q2 X1991.Q3 X1991.Q4 X1992.Q1 X1992.Q2
## 1    55.70    54.66    54.48    54.91    56.13    55.53    54.99    55.03
## 2    55.81    56.13    56.23    56.58    57.21    58.04    59.05    59.70
## 3    82.69    79.14    74.84    74.17    76.45    76.82    75.19    75.18
## 4   134.90   131.16   129.43   128.12   127.00   124.26   121.60   118.84
## 5   109.80   109.92   110.77   110.71   110.10   109.23   110.29   110.20
## 6    48.80    48.58    48.97    48.93    48.86    49.20    48.88    48.27
##   X1992.Q3 X1992.Q4 X1993.Q1 X1993.Q2 X1993.Q3 X1993.Q4 X1994.Q1 X1994.Q2
## 1    54.97    55.08    55.34    55.25    55.39    55.57    55.93    56.42
## 2    60.07    60.33    60.68    61.27    61.99    62.81    63.46    64.21
## 3    74.03    73.58    74.02    73.59    74.07    73.31    73.10    73.83
## 4   115.92   113.27   111.39   109.36   107.09   107.13   107.96   108.70
## 5   110.78   111.17   110.56   110.84   111.71   112.25   112.51   113.34
## 6    47.36    46.03    45.49    44.82    46.86    49.48    51.90    51.16
##   X1994.Q3 X1994.Q4 X1995.Q1 X1995.Q2 X1995.Q3 X1995.Q4 X1996.Q1 X1996.Q2
## 1    57.26    56.48    56.67    55.59    55.46    54.96    54.52    54.87
## 2    64.79    65.20    65.31    65.92    66.32    67.18    67.14    67.56
## 3    73.86    74.22    73.69    72.16    71.44    71.23    70.53    69.68
## 4   108.48   107.21   105.27   103.94   102.25   100.28    98.19    97.16
## 5   113.89   114.26   114.73   114.59   114.28   113.88   114.02   113.65
## 6    50.26    50.96    51.72    53.10    54.57    56.38    56.88    57.53
##   X1996.Q3 X1996.Q4 X1997.Q1 X1997.Q2 X1997.Q3 X1997.Q4 X1998.Q1 X1998.Q2
## 1    55.19    54.93    55.21    55.73    56.69    57.42    58.71    59.57
## 2    67.47    67.51    67.38    68.23    68.49    69.44    70.37    71.00
## 3    69.08    68.38    68.79    69.38    72.27    72.14    72.68    72.58
## 4    96.47    94.72    93.40    92.60    92.22    91.76    91.63    91.48
## 5   112.99   112.18   111.08   110.45   108.99   108.50   108.71   108.09
## 6    59.10    61.36    62.86    63.68    64.90    65.70    66.64    68.66
##   X1998.Q3 X1998.Q4 X1999.Q1 X1999.Q2 X1999.Q3 X1999.Q4 X2000.Q1 X2000.Q2
## 1    59.77    60.70    61.69    62.59    63.81    65.42    66.20    66.96
## 2    72.35    74.04    75.46    76.52    77.56    77.81    77.74    77.74
## 3    72.39    72.03    72.28    73.09    72.58    73.52    73.80    74.20
## 4    91.34    91.13    91.10    91.00    90.69    90.54    90.19    90.67
## 5   107.84   107.89   108.58   108.36   108.72   109.04   108.92   109.13
## 6    69.66    71.16    71.83    72.03    72.84    72.56    73.25    74.35
##   X2000.Q3 X2000.Q4 X2001.Q1 X2001.Q2 X2001.Q3 X2001.Q4 X2002.Q1 X2002.Q2
## 1    65.54    66.67    67.57    69.36    73.13    74.92    77.99    81.93
## 2    78.03    78.81    79.87    79.56    80.19    81.08    81.75    82.89
## 3    73.94    73.63    74.43    74.35    74.92    75.73    78.30    79.17
## 4    90.60    90.53    90.92    91.31    91.96    93.20    94.58    95.59
## 5   109.16   109.00   108.87   108.28   107.36   105.79   105.15   105.33
## 6    75.82    76.28    77.38    77.19    77.95    77.42    78.32    78.63
##   X2002.Q3 X2002.Q4 X2003.Q1 X2003.Q2 X2003.Q3 X2003.Q4 X2004.Q1 X2004.Q2
## 1    84.96    87.14    88.74    93.33    98.84   102.20   101.61    99.98
## 2    84.10    85.40    85.78    86.90    87.60    88.72    89.32    89.81
## 3    79.93    81.87    83.49    85.48    87.55    88.70    90.75    93.73
## 4    96.60    97.53    97.75    98.22    98.58    98.92    99.44    99.62
## 5   104.89   103.84   103.03   104.04   103.37   102.49   102.40   101.13
## 6    79.27    79.09    78.89    80.06    81.17    81.31    83.16    85.33
##   X2004.Q3 X2004.Q4 X2005.Q1 X2005.Q2 X2005.Q3 X2005.Q4 X2006.Q1 X2006.Q2
## 1   100.04   100.95   100.16   100.01    99.31   100.52   100.79   103.20
## 2    91.32    93.64    96.43    98.73   100.95   103.88   106.23   107.66
## 3    95.02    95.32    97.09    99.49   100.91   102.50   105.79   111.22
## 4   100.06    99.95    99.88    99.61   100.07   100.44   100.89   101.11
## 5   100.04    99.08   100.45   100.29   100.08    99.17    99.23    99.17
## 6    87.97    89.59    92.85    97.12   102.63   107.39   113.92   119.21
##   X2006.Q3 X2006.Q4 X2007.Q1 X2007.Q2 X2007.Q3 X2007.Q4 X2008.Q1 X2008.Q2
## 1   105.29   106.53   107.04   110.04   114.14   117.57   117.48   115.34
## 2   109.33   111.44   112.91   114.35   116.01   116.59   116.98   116.52
## 3   114.36   115.19   116.69   121.50   124.12   124.57   126.04   126.95
## 4   100.99   101.46   101.96   101.78   101.71   101.24   101.16   101.84
## 5    98.26    97.61    95.61    95.25    94.84    94.40    94.83    94.41
## 6   121.16   121.56   123.10   122.63   123.11   120.34   118.32   117.57
##   X2008.Q3 X2008.Q4 X2009.Q1 X2009.Q2 X2009.Q3 X2009.Q4 X2010.Q1 X2010.Q2
## 1   111.85   109.41   108.02   111.80   116.39   121.51   125.03   126.41
## 2   116.34   117.28   117.41   117.46   117.78   118.42   119.11   119.64
## 3   123.84   120.77   117.66   119.42   122.76   125.07   128.82   131.49
## 4   103.02   104.42   106.09   107.51   108.77   110.28   111.04   112.11
## 5    93.04    93.42    94.16    94.64    94.53    94.76    94.04    93.85
## 6   113.54   104.72    98.93    98.29    98.72    98.45    98.17    99.25
##   X2010.Q3 X2010.Q4 X2011.Q1 X2011.Q2 X2011.Q3 X2011.Q4 X2012.Q1 X2012.Q2
## 1   125.26   125.18   122.59   120.23   117.79   116.27   115.96   115.27
## 2   120.56   121.26   121.37   121.44   121.80   122.08   121.94   122.21
## 3   129.70   128.39   130.18   133.23   133.64   132.97   134.81   138.30
## 4   113.55   114.45   115.20   116.67   118.23   119.68   121.08   122.28
## 5    93.66    93.52    94.16    94.53    94.60    94.70    95.09    96.15
## 6    99.15    98.92    95.80    95.86    93.45    90.58    88.91    88.67
##   X2012.Q3 X2012.Q4 X2013.Q1 X2013.Q2 X2013.Q3 X2013.Q4 X2014.Q1 X2014.Q2
## 1   114.40   116.01   116.51   118.57   121.18   125.16   126.12   128.02
## 2   122.00   121.78   122.05   121.64   120.94   120.39   119.98   120.18
## 3   137.98   136.60   136.62   139.92   140.17   140.82   142.06   145.06
## 4   123.76   125.63   127.44   129.06   129.79   130.80   130.93   131.56
## 5    97.02    97.66    97.98    99.01    99.11    99.19    99.67   100.81
## 6    88.61    88.93    89.53    90.77    90.45    90.86    91.45    93.21
##   X2014.Q3 X2014.Q4 X2015.Q1 X2015.Q2 X2015.Q3 X2015.Q4 X2016.Q1 X2016.Q2
## 1   130.08   132.02   134.01   139.46   142.59   141.82   142.13   144.49
## 2   120.21   120.57   121.07   120.81   120.89   120.85   121.12   121.21
## 3   145.78   146.32   148.47   152.70   154.65   156.18   160.20   172.16
## 4   131.98   132.82   134.30   135.28   135.78   136.48   137.36   137.54
## 5   101.23   101.83   103.20   104.33   105.36   106.47   108.00   109.95
## 6    93.17    93.25    96.35    98.00    98.17    98.67   100.52   100.72
##   X2016.Q3 X2016.Q4 X2017.Q1
## 1   146.95   153.00   156.41
## 2   121.73   122.33   122.12
## 3   178.14   179.53   186.32
## 4   138.22   139.28   140.06
## 5   111.61   112.66   113.97
## 6   101.44   101.53   101.73

3. 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:

  1. take a look of the melt() function and convert the the HPI data into long format. You may want to provide the following additional arguments (take a look of [http://seananderson.ca/2013/10/19/reshape.html] if you do not know how to set the arguments):
  • id
  • value.name
  • variable.name
  1. use head() to take a look of the data

  2. you 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 <- melt(rhpi_wide, id.vars = "country", variable.name = "quarter",
                  value.name = "hpi")

head(rhpi_long)
##       country  quarter    hpi
## 1   Australia X1975.Q1  39.16
## 2     Belgium X1975.Q1  44.51
## 3      Canada X1975.Q1  59.42
## 4 Switzerland X1975.Q1  93.83
## 5     Germany X1975.Q1 108.59
## 6     Denmark X1975.Q1  57.37
# Remove the X at the beginning of the variable label
rhpi_long$quarter <- substring(rhpi_long$quarter, first = 2)

head(rhpi_long)
##       country quarter    hpi
## 1   Australia 1975.Q1  39.16
## 2     Belgium 1975.Q1  44.51
## 3      Canada 1975.Q1  59.42
## 4 Switzerland 1975.Q1  93.83
## 5     Germany 1975.Q1 108.59
## 6     Denmark 1975.Q1  57.37

You can optionally turn year and quarter into separate variables.

rhpi_long_opt <- rhpi_long
rhpi_long_opt$year <- as.numeric(substring(rhpi_long_opt$quarter, first = 1, last = 4))
rhpi_long_opt$quarter <- as.numeric(substring(rhpi_long_opt$quarter, first = 7))
rhpi_long_opt <- data.frame(rhpi_long_opt$country, rhpi_long_opt$year,
                            rhpi_long_opt$quarter, rhpi_long_opt$hpi)

This last optional part is substantially simpler with the dplyr package.

library("dplyr")
## 
## Attaching package: 'dplyr'
## The following objects are masked from 'package:stats':
## 
##     filter, lag
## The following objects are masked from 'package:base':
## 
##     intersect, setdiff, setequal, union
rhpi_long_alt <- mutate(
   .data = rhpi_long,
   year = as.numeric(substring(quarter, first = 1, last = 4)),
   quarter = as.numeric(substring(quarter, first = 7))
)
rhpi_long_alt <- select(rhpi_long_alt, country, year, quarter, hpi)

4. Load the PDI data and convert the PDI data from wide to long

# This code is the same as above for 3c with changed file and object name
rpdi_wide <- read.csv("rpdi.csv")
colnames(rpdi_wide)[1] <- "country"
rpdi_long <- melt(rpdi_wide, id.vars = "country", variable.name = "quarter",
                  value.name = "pdi")
rpdi_long$quarter <- substring(rpdi_long$quarter, first = 2)
head(rpdi_long)
##       country quarter   pdi
## 1   Australia 1975.Q1 72.13
## 2     Belgium 1975.Q1 66.15
## 3      Canada 1975.Q1 70.62
## 4 Switzerland 1975.Q1 73.36
## 5     Germany 1975.Q1 65.20
## 6     Denmark 1975.Q1 70.21

5. Merge the long format HPI and PDI data

  1. 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?

  2. 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("country", "quarter"))

head(merged)
##     country quarter   hpi   pdi
## 1 Australia 1975.Q1 39.16 72.13
## 2 Australia 1975.Q2 38.50 71.41
## 3 Australia 1975.Q3 38.61 70.63
## 4 Australia 1975.Q4 37.76 69.63
## 5 Australia 1976.Q1 37.95 69.25
## 6 Australia 1976.Q2 38.12 69.64

6. Filter the data

  1. take a look of the which() function. This function will return you the indices that a given condition is fulfilled.

  2. filter the data so that only Australia data is shown.

  3. use head()) and tail() functions to take a look of the data to make sure you filtered the data correctly.

# It is much easier to use the subset() function instead of which() here
merged_aus <- subset(merged, country == "Australia")
head(merged_aus)
##     country quarter   hpi   pdi
## 1 Australia 1975.Q1 39.16 72.13
## 2 Australia 1975.Q2 38.50 71.41
## 3 Australia 1975.Q3 38.61 70.63
## 4 Australia 1975.Q4 37.76 69.63
## 5 Australia 1976.Q1 37.95 69.25
## 6 Australia 1976.Q2 38.12 69.64

7. Melt the data

Try to use the melt() function again to produce a dataframe that is similar to this object: