Search code examples
rpivot-tabletranspose

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

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!

Solution

  • 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