Repeated columns a into new row

Question

I have repeated values in column a and I want these to become new row with information from column b.

I've tried the tidyr function for gather and spread

library("tidyr")
rearrangeddf<-spread(df,a,b)


#Input
a=c("A","A","A","A","A","B","B","B","B","B")
b=c(1,2,3,4,5,11,12,13214634,14,15432)
df=data.frame(a,b)

#Output
x=c("A",1,2,3,4,5)
y=c("B",11,12,13214634,14,1543)
rearrangeddf=rbind(x,y)

Error: Each row of output must be identified by a unique combination of keys. Keys are shared for 10 rows: * 1, 2, 3, 4, 5 * 6, 7, 8, 9, 10 Do you need to create unique ID with tibble::rowid_to_column()? Call rlang::last_error() to see a backtrace

Try `library(dplyr); library(tidyr); df %>%group_by(a) %>% mutate(new = row_number()) %>% spread(new, b)` — Sotos, Jul 31 '19 at 11:42
`c("A",1,2,3,4,5)` is identical with `c("A", "1", "2", "3", "4", "5")` because of coercion. — jogo, Jul 31 '19 at 13:08

score 2 · Accepted Answer · 2019-07-31T12:09:45.180

2

You can use

aggregate(b~a, df, c)
  a      b.1      b.2      b.3      b.4      b.5
1 A        1        2        3        4        5
2 B       11       12 13214634       14    15432

Not part of answer

Do not use c=c("A",1,2,3,4,5) because this will overwrite the c() function. See here:

c=c("A",1,2,3,4,5)
aggregate(b~a, df, c)
Error in get(as.character(FUN), mode = "function", envir = envir) : 
  object 'FUN' of mode 'function' was not found

edited Jul 31 '19 at 12:09

answered Jul 31 '19 at 11:55

Is that `c` the concatenate? Or `c` your vector. Don't use pre-defined functions to name objects – Sotos Jul 31 '19 at 12:06
@Sotos This is exactly what I am saying. `c=c("A",1,2,3,4,5)` is taken from the question and warn using this assigment – Jul 31 '19 at 12:06
@schwantke I don't understand what does "c" mean here? In my example, it was used to generate the output df (which doesn't exist), I'm unsure do you mean concatenate? It doesn't work with the code. – Sparky Jul 31 '19 at 13:08
1

@Marcosullivan `c()` is a [function in r](https://www.rdocumentation.org/packages/base/versions/3.6.1/topics/c). You shoud not assign objects to pre-defined funtions. So rather don't use `c=c("A",1,2,3,4,5)`. Besides, if the answer(s) answer your question, you can upvote and/ or mark as answered – Jul 31 '19 at 13:11
@Marcosullivan the code `a=c("A","A","A","A","A","B","B","B","B","B"); b=c(1,2,3,4,5,11,12,13214634,14,15432); df=data.frame(a,b);aggregate(b~a, df, c)` works but only as long as you have not run `c=c("A",1,2,3,4,5)` before. Because otherwise you overwrite the function `c()` – Jul 31 '19 at 13:13
1

Great thanks @schwantke I have edited the output file to correct this, and will not do this in future datasets. – Sparky Jul 31 '19 at 13:14

score 1 · Answer 2 · answered Jul 31 '19 at 12:08

1

You can do:

df <- data.frame(a=c("A","A","A","A","A","B","B","B","B","B"),
              b=c(1,2,3,4,5,11,12,13214634,14,15432))
t(unstack(df, b ~ a))
# > t(unstack(df, b ~ a))
#   [,1] [,2]     [,3] [,4]  [,5]
# A    1    2        3    4     5
# B   11   12 13214634   14 15432

answered Jul 31 '19 at 12:08

jogo

12,469
11
37
42

I had something similiar first, using `aggregate(b~a, df, c)[ , -1]` but then I saw that in the expected output `A B` are in first column – Jul 31 '19 at 12:13
Thanks for the explanation @jogo, but "A" and "B" become the row name with this. – Sparky Jul 31 '19 at 13:05

score 0 · Answer 3 · answered Jul 31 '19 at 13:12

0

Thanks @Sotos this worked perfectly

library(dplyr)
library(tidyr)
df %>%group_by(a) %>% mutate(new = row_number()) %>% spread(new, b)

answered Jul 31 '19 at 13:12

Sparky

349
1
12

Repeated columns a into new row

3 Answers3