Transpose specific columns while leaving first columns intact (with duplicates) in R

Question

I'm new at stackoverflow, so I hope I tagged the topic correctly. Also, I'm new at programming in general, so maybe my steps are not too logical.

I have the following dataframe (but then larger):

df <- data.frame(location = c("a", "a", "a", "b", "b"),
                   date = c("2017-05-01", "2017-05-02", "2017-05-03", "2017-05-01", "2017-05-02"),
                   average1 = c(246, 233, 257, 290, 307),
                   average2 = c(134, 256, 315, 346, 312),
                   average3 = c(214, 315, 233, 249, 216))

I would like to transpose the averages but also keep them linked to their location and date. The first two lines of the dataframe would end up like this:

location date   ave_interval   average
a   2017-05-01  average1    246
a   2017-05-01  average2    134 
a   2017-05-01  average3    214
a   2017-05-02  average1    233
a   2017-05-02  average2    256
a   2017-05-02  average3    315
etc

Does someone know how to code this?

I've tried to following: I made a separate data table with only the location, date and average1, because I didn't know how to do it for all the averages.

Try 1:

> recast(df, date + average1 ~ am, id.var = c("location", "date"))

Gives me the following error:

Error in FUN(X[[i]], ...) : object 'average1' not found

names(df) gives me (mind the strange spacing): 
 "location" "datum"    "average1"

is.numeric(df$average1)
[1] TRUE
exists("df$average1")
[1] FALSE

Try2:

df.new < -reshape(df,dir='w',idvar=c('location','date'),timevar='average1');
Error in Ops.data.frame(df.new, -reshape(df, dir = "w", idvar = c("location",  : 
  ‘<’ only defined for equally-sized data frames
In addition: Warning messages:
1: In Ops.factor(left) : ‘-’ not meaningful for factors
2: In Ops.factor(left) : ‘-’ not meaningful for factors

Summarized: How to code the transposing of certain columns and transfer the heading name to column 'ave_interval"? Is there maybe something wrong with my header names?

Thanks in advance.

score 0 · Accepted Answer · answered Apr 08 '20 at 12:44

This is a job for pivot_longer

library(dplyr)
df %>% 
    pivot_longer(cols = c("average1", "average2", "average3"),
                 names_to = "average_interval", values_to = "average")
# A tibble: 15 x 4
   location date       average_interval average
   <fct>    <fct>      <chr>              <dbl>
 1 a        2017-05-01 average1             246
 2 a        2017-05-01 average2             134
 3 a        2017-05-01 average3             214
 4 a        2017-05-02 average1             233
 5 a        2017-05-02 average2             256
 6 a        2017-05-02 average3             315
 7 a        2017-05-03 average1             257
 8 a        2017-05-03 average2             315
 9 a        2017-05-03 average3             233
10 b        2017-05-01 average1             290
11 b        2017-05-01 average2             346
12 b        2017-05-01 average3             249
13 b        2017-05-02 average1             307
14 b        2017-05-02 average2             312
15 b        2017-05-02 average3             216

Transpose specific columns while leaving first columns intact (with duplicates) in R

1 Answers1