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

user_15109169 0
Viewed 95

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 image

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

Freedomtidy$PR[1]

Output:

[[1]]
[1] "4"
tidyverse r
2 Answers

Try adding values_fn as toString in pivot_wider :

library(tidyverse)

Freedomtidy %>%
  pivot_wider(id_cols = c("Country", "year"), 
              names_from = Type, values_from = Freedom, values_fn = toString) %>%
  arrange(Country, year) -> result

result
user_39629140

Part of your problem is that your grouping row "Years under review" does not strictly have numerical values for the year. There are values that are split across a year, eg. Jan.1981-Aug.1982. You may wish to aggregate such columns beforehand so that they refer to a whole year for consistency.

I've generated an alternate solution that gives a long format dataframe with your data as-is. I've read the Excel file directly: no need to export to csv first.

library(tidyverse)
library(readxl)

# first three rows (header)
hdr <- read_xlsx("2020_Country_and_Territory_Ratings_and_Statuses_FIW1973-2020.xlsx",
                    sheet="Territory Ratings, Statuses",
                    n_max = 3,
                    col_names = FALSE,
                    col_type="text")

# list of column names
cnames <- as.character(hdr[3,])

# 'minimal' will give column names as-is, ie. not unique
df <- read_xlsx("2020_Country_and_Territory_Ratings_and_Statuses_FIW1973-2020.xlsx",
                sheet="Territory Ratings, Statuses",
                skip=3,
                col_names=cnames,
                .name_repair = "minimal")

# merged cells in header
groups1 <- hdr[2,]
unique_groups <- group1[!is.na(group1)][-1]

# explicitly set empty column name
names(df)[1] <- "Country"

# tag all other columns by group (ie. year)
# repeat in groups of 3 for PR, CL, Status
names(df)[-1] <- paste0(names(df)[-1], ".",
                        rep(unique_groups, each=3))

result <- df %>%
  gather(variable,value,-Country) %>%
  mutate(year = substr(variable,nchar(variable)-3,nchar(variable))) %>%
  mutate(variable =  gsub("\\.\\d{4}","",x = variable)) %>%
  arrange(Country,variable,year)
user_133263020
Related