# Reshaping data in R

In this assignment, we will be working with the **tidyr** package to reshape ("pivot") the dataset [rhpi.csv](rhpi.csv) and [rpdi.csv](rpdi.csv).

These follow the examples from Chapter 12, "[Tidy data](https://r4ds.had.co.nz/tidy-data.html)", from Wickham and Grolemund, [_R for Data Science_](https://r4ds.had.co.nz/index.html).

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


## 1.  **Reshaping a simple example.**

a.  From the `cmpdata` object, replicate the reshaped object `cmpdataLong` from the lecture notes, using the **tidyr** package, using `gather`.

```{r message=FALSE}
library("tidyr")
library(tidyverse)

load("cmpdata.Rdata")

# Keyboard shortcut 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 %>% 
  dplyr::select(countryname, date, partyname, starts_with("per")) %>% 
  dplyr::select(-pervote)
# we need to do this with pervote because it starts with "per" but is not a variable we want to keep
    
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)
```

b.  Try using **tidyr** on `cmpdataLong` to "spread" this back into the wide format, but using `pivot_wider()`.

```{r}

cmpdataWide <- cmpdataLong %>%
  pivot_wider(names_from = "code", values_from = "value")

```


## 2. Load the HPI data

a. click into the [rhpi.csv](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?

b. take a look of the [```read.csv```](https://stat.ethz.ch/R-manual/R-devel/library/utils/html/read.table.html) function and read the csv data to r.

c. after the data is loaded into r, you can use [```head```](https://stat.ethz.ch/R-manual/R-devel/library/utils/html/head.html) function to take a look of the first few rows of data. Is it in long or wide format?

d. you may find out that the first column name is empty. You may update the first column name to "country" by using [```colnames```](https://stat.ethz.ch/R-manual/R-devel/library/base/html/colnames.html) function. And remember R starts counting from 1!

```{r}
## your code (filled in for you already)
library(ISLR)
rhpi_wide <- read.csv("rhpi.csv") #if this line does not work, check where you are by getwd()
head(rhpi_wide)

colnames(rhpi_wide)[1]
colnames(rhpi_wide)[1] <- "country"

head(rhpi_wide)
```

## 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:

a. take a look of the [`pivot_wider()`](https://tidyr.tidyverse.org/reference/pivot_wider.html) function and convert the the HPI data into long format..

b. use [`head()`](https://stat.ethz.ch/R-manual/R-devel/library/utils/html/head.html) to take a look of the data

c. 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()`](https://stat.ethz.ch/R-manual/R-devel/library/base/html/substr.html) function.

```{r}
rhpi_long <- rhpi_wide %>% 
   pivot_longer(cols = colnames(rhpi_wide)[2:length(colnames(rhpi_wide))],
                               names_to = "quarter", values_to = "rhpi")
# here we are indicating columns 2 to the rest of the columns, as we don't want to change the first renamed 'country' column

rhpi_long$quarter <- substring(rhpi_long$quarter, first = 2)
# this is essentially just dropping the X, telling it to pull out the values in the column
# starting with the second character, which is why we specify 2 with the 'first=' arg

head(rhpi_long)
```

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

```{r}
rpdi_wide <- read.csv("rpdi.csv")

rpdi_long <- rpdi_wide %>% 
   pivot_longer(cols = !X, names_to = "quarter", values_to = "rpdi")
# selecting all columns except the first X column (country) 
# remember, the function is being applied only to the columns you want to reshape
                               
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

# compare this to the way we did this earlier... which do you prefer?
rpdi_long <-rpdi_long %>% 
   rename(country = X)
```

## 5. Merge the long format HPI and PDI data

a. take a look of the [`merge())`](https://stat.ethz.ch/R-manual/R-devel/library/base/html/merge.html) function and merge the two dataframes together.  You should provide the "```by```" argument. What should be the unique identifier? Country? Quarter? Or both?

b. take a look of the merged data using [`head())`](https://stat.ethz.ch/R-manual/R-devel/library/utils/html/head.html) to make sure the data are merged properly.

```{r message=FALSE}
merged <- merge(rhpi_long, rpdi_long, by = c("quarter", "country"))
# both data have 3 columns, 2 of which are the same columns. We don't want to merge only 
# on one variable b/c it will do it in order and not necessarily correctly if there were different numbers of rows in one. 

head(merged)

# in dplyr you can use mutating joins
?full_join

library(dplyr)
joined <- full_join(rhpi_long, rpdi_long)

# however, merge() will give you a df, join() a tibble
head(merged)
head(joined)
```

## 6. Filter the data

a. take a look of the [`which())`](https://stat.ethz.ch/R-manual/R-devel/library/base/html/which.html) function. This function will return you the indices that a given condition is fulfilled.

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

c. use [`head())`](https://stat.ethz.ch/R-manual/R-devel/library/utils/html/head.html) and [`tail()`](https://stat.ethz.ch/R-manual/R-devel/library/utils/html/head.html) functions to take a look of the data to make sure you filtered the data correctly.

```{r}
# which provides you the indices that satisfy the search condition, in this case, Australia:

which(merged$country =="Australia")
aus <- 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)

# or you can use filter() from dplyr

# RUN THIS BIT IN THE CONSOLE (copp-paste) NOT IN RMARKDOWN
# COMPARE THE OUTPUT
merged %>% 
   filter(country=="Australia") %>% 
   head()

joined %>% 
   filter(country=="Australia") %>% 
   head()
```

## 7. Make the data the data

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

![melted_df.png](melted_df.png)
```{r}
merged_long <- pivot_longer(merged, cols = c("rhpi", "rpdi"),
                             names_to = "type", values_to = "value")

# this removes the designated rhpi and rpdi columns and instead turns these into a singular
# column 'type', and then another column # value. 

head(merged_long)
tail(merged_long)
```

