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

Question

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!

score 1 · Accepted Answer · answered Apr 05 '22 at 23:21

1

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

answered Apr 05 '22 at 23:21

PeterK

1,185
1
9
23

Thank you PeterK! Too new to cast an upvote but this works for me :) – Daya V Apr 06 '22 at 02:05
@DayaV, please do give a check to select a correct answer. – PeterK Apr 06 '22 at 12:59

score 1 · Answer 2 · answered Apr 05 '22 at 23:22

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")

Thank you Sean for the welcome and the heads up! Will definitely include reproducible examples in my next post. This code also works for me :) — Daya V, Apr 06 '22 at 02:06

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

2 Answers2