Transposing a tibble with dates as headers and multiple observations per date

Question

I have a tibble that looks like this:

   X  Form  Jan_92 Feb_92 Mar_92 (....)
<int> <fct>  <dbl> <dbl>  <dbl>
   1  var1   2.02   2.97  0.12 
   2  var2   0.23   0.28  0.33
   3  var3   0.08   0.28  0.12

I want to tidy it so that all of the month/year combos (there are 10 years worth) are in a single column, and the var1/var2/var3 measures are column names with the respective values (corresponding to the dates).

So far I've tried transposing with t(data) but that turns it into a dataframe and I get weird row/column naming problems that I can't fix when I put it back into tibble format. Help!

Answer 1

We can convert to 'long' format with pivot_longer and then reshape to 'wide' with pivot_wider

library(dplyr)
library(tidyr)
df1 %>% 
   pivot_longer(cols = -c(X, Form)) %>% 
   select(-X) %>%
   group_by(Form) %>%
   mutate(rn = row_number()) %>%       
   pivot_wider(names_from = Form, values_from = value)
# A tibble: 3 x 5
#      rn name    var1  var2  var3
#  <int> <chr>  <dbl> <dbl> <dbl>
#1     1 Jan_92  2.02  0.23  0.08
#2     2 Feb_92  2.97  0.28  0.28
#3     3 Mar_92  0.12  0.33  0.12

data

df1 <- structure(list(X = 1:3, Form = c("var1", "var2", "var3"), Jan_92 = c(2.02, 
0.23, 0.08), Feb_92 = c(2.97, 0.28, 0.28), Mar_92 = c(0.12, 0.33, 
0.12)), class = "data.frame", row.names = c(NA, -3L))

Answer 2

If you don't care X column (and it is just a counter), then the following works in data.table:

library(data.table)

dcast(melt(setDT(df1), id=c("X","Form"), variable.name = "datetime"), datetime ~ Form)

#>    datetime var1 var2 var3
#> 1:   Jan_92 2.02 0.23 0.08
#> 2:   Feb_92 2.97 0.28 0.28
#> 3:   Mar_92 0.12 0.33 0.12

Or another tidyverse solution:

library(dplyr)
library(tidyr)

df1 %>%
  select(-X) %>% 
  gather(datetime, value, -Form) %>%
  spread(Form, value)

# >   datetime var1 var2 var3 
# > 1   Feb_92 2.97 0.28 0.28   
# > 2   Jan_92 2.02 0.23 0.08 
# > 3   Mar_92 0.12 0.33 0.12

_{Using akrun's data.}

Answer 3

You can do:

as_tibble(cbind(Dates = names(df), t(df))) %>% .[-c(1:2),]

# A tibble: 3 x 4
#  Dates  V2    V3    V4   
#  <chr>  <chr> <chr> <chr>
#1 Jan_92 2.02  0.23  0.08 
#2 Feb_92 2.97  0.28  0.28 
#3 Mar_92 0.12  0.33  0.12 

Data

df <- as_tibble(read.table(header = TRUE, text = "X Form Jan_92 Feb_92 Mar_92
1 var1 2.02 2.97 0.12
2 var2 0.23 0.28 0.33
3 var3 0.08 0.28 0.12"))