CODEWITHSUNDEEP
rpivottidyverse
Lea7885
Lea7885

Reputation: 97

R code pivot column to rows and rows to columns

I'm new to RStudio and I've been struggling on this issue for 2 days and came to the conclusion that I need help.

I have a data frame looking like this : 

ISO  Indicator 2009  2010 2011 
ARB  Use of electricity 0.5 0.5 0.4
CSS  Population  2.5 3.5 0.5

What I want to do get is 

ISO Year Use of Electricity Population
ARB 2009 0.5 na
ARB 2010 0.5 na
ARB 2011 0.4 na
CSS 2009 na 2.5
CSS 2010 na 3.5
CSS 2011 na 0.5

I started of with a "gather" function to create the Year column and then I transformed Year to numeric and created a result column for the values. I then tried "spread" I get an error on the type of my data frame. Is there a way to make this pivoting in one step, if so I'd be happy to hear it. I thought about adding a row up top and name it Year to use a pivot function but I'm not sure how to proceed. Is the problem coming from the fact that my data frame should be converted into a matrix?

Any help is greatly appreciated!

Upvotes: 2

Views: 4614

Answers (1)

Martin Gal
Martin Gal

Reputation: 16978

Assuming your data is

df <- read_table2("ISO  Indicator 2009  2010 2011
ARB  Use_of_electricity 0.5 0.5 0.4
CSS  Population  2.5 3.5 0.5")

Using dplyr/tidyr:

df %>%
  pivot_longer(cols=as.character(2009:2011), names_to="Year") %>%
  pivot_wider(names_from="Indicator")

yields

# A tibble: 6 x 4
  ISO   year  Use_of_electricity Population
  <chr> <chr>              <dbl>      <dbl>
1 ARB   2009                 0.5       NA  
2 ARB   2010                 0.5       NA  
3 ARB   2011                 0.4       NA  
4 CSS   2009                NA          2.5
5 CSS   2010                NA          3.5
6 CSS   2011                NA          0.5

Upvotes: 1

Related Questions