Transform reorder timestamps in dataframe - R

user_17817552 0
Viewed 46

I have the following table in R:

S <- c("A","A","A","B","B","B","C","C","C")
TS <- c(1,1,1,2,2,2,3,3,3)
f1 <- c(10,20,30,15,25,35,17,27,37)
p <- c(100,200,300,150,250,350,170,270,370)

df <- data.frame(S, TS, f1, p)

So it looks like that:

S TS f1 p
A 1 10 100
A 2 20 200
A 3 30 300
B 1 15 150
B 2 25 250
B 3 35 350
C 1 17 170
C 2 27 270
C 3 37 370

Now I want to transform my dataframe so that I have unique values for TS (timestamps) for every row and binded my variables right to it, like this:

TS SA_f1 pA SB_f1 pB SC_f1 pC
1 10 100 15 150 17 170
2 20 200 25 250 27 270
3 30 300 35 250 37 370

What is the most elegant way doing this?

r
2 Answers

This can be done for any number of strings using the following:

library(tidyr)
res1 <- df %>% group_by(TS) %>% mutate(colnames = paste0("SA_f",row_number())) %>% select(-p) %>% 
  pivot_wider(names_from = colnames, values_from = f1)

res2 <- df %>% group_by(TS) %>% mutate(colnames = paste0("p_",row_number())) %>% select(-f1) %>% 
  pivot_wider(names_from = colnames, values_from = p)

result <- merge(res1, res2)

  S TS SA_f1 SA_f2 SA_f3 p_1 p_2 p_3
1 A  1    10    20    30 100 200 300
2 B  2    15    25    35 150 250 350
3 C  3    17    27    37 170 270 370

If you have 100+ variables I would include this in a for loop and perform the merge iteratively.

library(tidyr)

reslist <- list()
varlist <- c("f1", "p")
for(i in varlist) {
thisres <- res1 <- df %>% group_by(TS) %>% mutate(colnames = paste(i,row_number(), sep="_")) %>% select(S, TS, i, colnames) %>% 
  pivot_wider(names_from = colnames, values_from = i)
reslist[[i]] <- thisres
}
res <- reslist[[1]]
for(i in 2:length(reslist)) {
  res <- merge(res, reslist[[i]])
}

Original, simplified answer:

You could use:

library(dplyr)
df %>% group_by(TS) %>% summarise(SA_f1=f1[1], SB_f1=f1[2],SC_f1=f1[3], pA=p[1], pB=p[2], pC=p[3])

# A tibble: 3 x 7
     TS SA_f1 SB_f1 SC_f1    pA    pB    pC
  <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>
1     1    10    20    30   100   200   300
2     2    15    25    35   150   250   350
3     3    17    27    37   170   270   370

Differences in result come I think from the fact that the original input differs from what is shown.

user_52242360

There is an error in your R code, it should be

TS <- rep(1:3,3)

anyway, a very elegant solution

library(reshape2)

dcast(
  melt(df,id.vars=c("S","TS")),
  TS~S+variable,
  value.var="value",
  fun.aggregate=mean
)

  TS A_f1 A_p B_f1 B_p C_f1 C_p
1  1   10 100   15 150   17 170
2  2   20 200   25 250   27 270
3  3   30 300   35 350   37 370
user_29749510
Related