How do i pivot or transpose specific columns in a table in R?-CodePudding

I have an income dataset that looks like this.

CITY	2014	2015
AA	21	22
BB	21	24

I am trying to find a way to make the dataset look like this.

CITY	Income	Year
AA	21	2014
AA	21	2014
BB	22	2015
BB	24	2015

I tried to pivot this using the tidyr package but I've not been successful so far. Is there any other package or code that would allow for this transformation?

Thank you!

CodePudding user response：

You want pivot_longer. Keep in mind that R doesn't like numbers as column names.

df <- tibble(city=c("AA","BB"), `2014`=c(21,21), `2015`=c(22, 24))
df %>% pivot_longer(c(`2014`, `2015`), names_to="Year", values_to="Income")
# A tibble: 4 x 3
  city  Year  Income
  <chr> <chr>  <dbl>
1 AA    2014      21
2 AA    2015      22
3 BB    2014      21
4 BB    2015      24

CodePudding user response：

Welcome to Stack Overflow Daya! In the future, please try to post a reproducible example. These help respondents better understand and diagnose your issues.

You can accomplish what you are after using the pivot_longer() function in the tidyr package. Below is a reproducible example:

library(tidyr)
city<-c("AA", "BB")
y14<-c(21, 21)
y15<-c(22,24)

DF<-data.frame(city, y14, y15)
colnames(DF)<-c("CITY",2014, 2015)

DF %>% pivot_longer(cols= c(2:3), names_to="Year", values_to="Income")