How to use Pivot_wider or Pivot_longer on a dataset with multiple values and names

user_12968928 0
Viewed 44

Given the table below, I want to transpose the vale from Indicator:last column such that the values in Indicator will be new columns and columns after Indicator will be in values.

  `Country ISO3` `Country Name` Indicator                       `1995` `1996` `1997`
  <chr>          <chr>          <chr>                            <dbl>  <dbl>  <dbl>
1 AGO            Angola         Index of economic freedom score   27.4   24.4   24.2
2 AGO            Angola         Property rights score             30     30     30  
3 AGO            Angola         Fiscal freedom score              NA     NA     NA  
4 ALB            Albania        Index of economic freedom score   49.7   53.8   54.8
5 ALB            Albania        Property rights score             50     50     50  
6 ALB            Albania        Fiscal freedom score              NA     NA     NA

Using simply pivot wider doesnt help

HeritageIndex %>% 
  pivot_wider(names_from = Indicator, values_from = colnames(HeritageIndex)[6:length(colnames(HeritageIndex))])


Sample dataset HeritageIndex

structure(list(`Country ISO3` = c("AGO", "AGO", "AGO", "ALB", 
"ALB", "ALB", "ARE", "ARE", "ARE"), `Country Name` = c("Angola", 
"Angola", "Angola", "Albania", "Albania", "Albania", "United Arab Emirates", 
"United Arab Emirates", "United Arab Emirates"), Indicator = c("Index of economic freedom score", 
"Property rights score", "Fiscal freedom score", "Index of economic freedom score", 
"Property rights score", "Fiscal freedom score", "Index of economic freedom score", 
"Property rights score", "Fiscal freedom score"), `1995` = c(27.4, 
30, NA, 49.7, 50, NA, NA, NA, NA), `1996` = c(24.4, 30, NA, 53.8, 
50, NA, 71.6, 90, NA), `1997` = c(24.2, 30, NA, 54.8, 50, NA, 
71.9, 90, NA)), row.names = c(NA, -9L), class = c("tbl_df", "tbl", 
"data.frame"))

Desired Output

  `Country ISO3` `Country Name`       Indicator       `Index of economic freedom score` `Property rights score` `Fiscal freedom score `
  <chr>          <chr>                <chr>           <dbl>                     <dbl>           <dbl>
1 AGO            Angola               `1995`          27.4                      30              NA
2 AGO            Angola               `1996`          24.4                      30              NA  
3 AGO            Angola               `1997`          24.2                      30              NA  
4 ALB            Albania              `1995`          49.7                      50              NA
5 ALB            Albania              `1996`          53.8                      50              NA  
6 ALB            Albania              `1997`          54.8                      50              NA  
7 ARE            United Arab Emirates `1995`          NA                        NA              NA
8 ARE            United Arab Emirates `1996`          71.6                      90              NA  
9 ARE            United Arab Emirates `1997`          71.9                      90              NA
tidyverse r
1 Answers

You could use the following melt/dcast approach with data.table

library(data.table)

dcast(
  melt(setDT(df1)[,Indicator:=str_remove(Indicator," score")],measure.vars = 4:6,variable.name = "year"),
  ...~Indicator,
  value.var="value"
)

Or the equivalent approach using pivot_wider/longer from tidyr

library(tidyr)

pivot_wider(
  pivot_longer(df1,`1995`:`1997`, names_to="Year"),
  names_from = Indicator,
  values_from = value
)

Output:

   Country ISO3         Country Name year Fiscal freedom Index of economic freedom Property rights
1:          AGO               Angola 1995             NA                      27.4              30
2:          AGO               Angola 1996             NA                      24.4              30
3:          AGO               Angola 1997             NA                      24.2              30
4:          ALB              Albania 1995             NA                      49.7              50
5:          ALB              Albania 1996             NA                      53.8              50
6:          ALB              Albania 1997             NA                      54.8              50
7:          ARE United Arab Emirates 1995             NA                        NA              NA
8:          ARE United Arab Emirates 1996             NA                      71.6              90
9:          ARE United Arab Emirates 1997             NA                      71.9              90

If you want one row per country, you could take this approach:

dcast(setDT(df1)[,Indicator:=str_remove(Indicator," score")],
      ...~Indicator,
      value.var = c("1995", "1996","1997")
)

Output:

   Country ISO3         Country Name 1995_Fiscal freedom 1995_Index of economic freedom 1995_Property rights 1996_Fiscal freedom 1996_Index of economic freedom
1:          AGO               Angola                  NA                           27.4                   30                  NA                           24.4
2:          ALB              Albania                  NA                           49.7                   50                  NA                           53.8
3:          ARE United Arab Emirates                  NA                             NA                   NA                  NA                           71.6
   1996_Property rights 1997_Fiscal freedom 1997_Index of economic freedom 1997_Property rights
1:                   30                  NA                           24.2                   30
2:                   50                  NA                           54.8                   50
3:                   90                  NA                           71.9                   90
user_44475400
Related