Reshaping a dataset

Question

I'm very new to R and I'm not very good at it still. I am working with a dataframe that looks like this:

 ID      ESG var         Y2009     Y2010     Y2011
 A      ESG score         5.1       3.5       4.8       
 A      Emissions         3.0       1.4       1.3 
 B      ESG score         6.5       4.6       2.1 
 B      Emissions         3.6       1.9       1.6

but I would like to reshape it and make it look like:

ID   YEARS    ESG score      Emissions
 A    2009        5.1             3.0
 A    2010        3.5             1.4
 A    2011        4.8             1.3
 B    2009        6.5             3.6
 B    2010        4.6             1.9
 B    2011        2.1             1.6

I need one year variable that takes three values (2009, 2010, 2011) and to go to two ESG variables ( ESG score and Emission) that take the corresponding numerical value.

I tried to use the function reshape() and melt() but I couldn't find any good way. Can someone help me please?

You could do `tidyr::pivot_longer(d, -c(ID, `ESG var`)) %>% tidyr::pivot_wider(names_from = "ESG var", values_from = "value")` — stefan, Oct 03 '20 at 08:54

score 0 · Answer 1 · answered Oct 03 '20 at 09:08

library(reshape)

out <- cast(melt(df, id=c("ID","ESG.var")),ID+variable~ESG.var,value="value")
out[,2] <- as.numeric(gsub("Y","",out[,2]))    
colnames(out)[2] <-"YEARS"
out

gives,

  ID YEARS Emissions ESG score
1  A  2009       3.0       5.1
2  A  2010       1.4       3.5
3  A  2011       1.3       4.8
4  B  2009       3.6       6.5
5  B  2010       1.9       4.6
6  B  2011       1.6       2.1

Data:

df <- read.table(text="ID      'ESG var'         Y2009     Y2010     Y2011
 A      'ESG score'         5.1       3.5       4.8       
 A      'Emissions'         3.0       1.4       1.3 
 B      'ESG score'         6.5       4.6       2.1 
 B      'Emissions'         3.6       1.9       1.6",header=T, stringsAsFactors=FALSE)

Reshaping a dataset

1 Answers1