How to reshaping an excel sheet with a header in three rows to tidy data format?

I'm trying to convert the sheet "Country Ratings, Statuses" from the excel file "2020_Country_and_Territory_Ratings_and_Statuses_FIW1973-2020.xlsx" to tidy data format. file Link

In this process, I downloaded the workbook and saved that sheet to a CSV file called "Freedom.csv".

The CSV file looks like: Data Input

The information I'm organizing and collecting from this CSV is:

1. Country
2. Year(s) under Review
3. PR
4. CL
5. Status

How would you go about converting the file to this format?

The code I tried to use on the file was

library(tidyverse)  filelocation<- "./Data/"  FreedomIndex <- read.csv(file = paste0(filelocation,'Freedom.csv'),skip=2)    col2 = paste(rep(c("PR", "CL", "Status"), 47),rep(1973:2019, each=3), sep = "-")  RenamesCol = c("Country", col2)  colnames(FreedomIndex) = RenamesCol    Freedomtidy<-FreedomIndex %>%    pivot_longer(cols = c(col2), names_to = "Type", values_to = "Freedom") %>%    arrange("Country")    Freedomtidy = separate(data = Freedomtidy, col = Type, into = c("Type", "year"), sep = "-")    Freedomtidy %>%    pivot_wider(names_from = "Type", values_from = "Freedom") %>%    arrange("Country", "year")    Freedomtidy %>%    pivot_wider(id_cols = c("Country", "year"), names_from = "Type", values_from = "Freedom") %>%    arrange("Country", "year")  

This code did not convert the data into the required format. I got "<chr 1>" in the data returned. Data Output

Also, an important note is the lists in the dataframe look like this:

Freedomtidy$PR[1]  

Output:

[[1]]  [1] "4"  

https://stackoverflow.com/questions/65963102/how-to-reshaping-an-excel-sheet-with-a-header-in-three-rows-to-tidy-data-format January 30, 2021 at 07:33AM