CODEWITHSUNDEEP
r
Eliel Epelbaum
Eliel Epelbaum

Reputation: 69

Convert information from rows to new columns

Is there a way in R to place every three values in the column "V" (below) to new columns? In others words, I need to reshape the data from long to wide, but only to three columns and where the values are what appears in column V. Below is a demonstration.

Thank you in advance!

enter image description here

enter image description here

data = structure(list(Key = c(200, 200, 200, 200, 200, 200, 300, 300, 
                300, 300, 300, 300, 400, 400, 400, 400, 400, 400), 
                V = c("a", "b", "c", "b", "d", "c", "d", "b", "c", "a", "f", "c", "d", "b", 
                "c", "a", "b", "c")), 
                row.names = c(NA, 18L), 
                class = "data.frame")

Upvotes: 1

Views: 269

Answers (1)

Maurits Evers
Maurits Evers

Reputation: 50728

Here is one option

data %>%
    group_by(Key) %>%
    mutate(
        grp = gl(n() / 3, 3),
        col = c("x", "y", "z")[(row_number() + 2) %% 3 + 1]) %>%
    group_by(Key, grp) %>%
    spread(col, V) %>%
    ungroup() %>%
    select(-grp)
## A tibble: 6 x 4
#    Key x     y     z
#  <dbl> <chr> <chr> <chr>
#1   200 a     b     c
#2   200 b     d     c
#3   300 d     b     c
#4   300 a     f     c
#5   400 d     b     c
#6   400 a     b     c

Note: This assumes that the number of entries per Key is divisible by 3.

Instead of grp = gl(n() / 3, 3) you can also use grp = rep(1:(n() / 3), each = 3).

Update

In response to your comments, let's create sample data by removing some rows from data such that for Key = 200 and Key = 300 we don't have a multiple of 3 V entries.

data2 <- data %>% slice(-c(1, 8))

Then we can do

data2 %>%
    group_by(Key) %>%
    mutate(grp = gl(ceiling(n() / 3), 3)[1:n()]) %>%
    group_by(Key, grp) %>%
    mutate(col = c("x", "y", "z")[1:n()]) %>%
    spread(col, V) %>%
    ungroup() %>%
    select(-grp)
## A tibble: 6 x 4
#    Key x     y     z
#  <dbl> <chr> <chr> <chr>
#1   200 b     c     b
#2   200 d     c     NA
#3   300 d     c     a
#4   300 f     c     NA
#5   400 d     b     c
#6   400 a     b     c

Note how "missing" values are filled with NA.

Upvotes: 1

Related Questions