Is there a way to move every other row in a column into a new column in R?

Challenge: I have a column in which there are several rows. For eg., the first row is "Fruit name" and second row is "Fruit Color" and it repeats for another fruit. I want to grab the every second row (Fruit color) and create a new column. In the original column only the fruit names remain.

I would like to go from df_before to df_after.
I'm sure there is a easier way to do this using functions from tidyverse (dplyr?) but couldn't find any info with a good deal of search. Would appreciate any pointers. Thanks in advance!

library(tidyverse)
df_before <- tribble(~Singlecolumn,"Apple","Red","Banana","Yellow","Kiwi","Grey","Grapes","Green")
df_before

Singlecolumn

Apple
Red
Banana
Yellow
Kiwi
Grey
Grapes
Green

df_after <- tribble(~Column1, ~Column2, "Apple","Red","Banana","Yellow","Kiwi","Grey","Grapes","Green")
df_after

Column1 Column2

Apple Red
Banana Yellow
Kiwi Grey
Grapes Green

One way to do it

library(tidyverse)

df_before <- tribble(~Singlecolumn,"Apple","Red","Banana","Yellow","Kiwi","Grey","Grapes","Green")

df_before %>% 
    mutate(variable = rep(c("Fruit", "Color"), nrow(df_before) / 2), 
           key = rep(1:(nrow(df_before) / 2), each = 2)) %>%
    pivot_wider(id_cols = key, names_from = variable, values_from = Singlecolumn) %>% 
    select(-key)
#> # A tibble: 4 x 2
#>   Fruit  Color 
#>   <chr>  <chr> 
#> 1 Apple  Red   
#> 2 Banana Yellow
#> 3 Kiwi   Grey  
#> 4 Grapes Green

Created on 2020-04-16 by the reprex package (v0.3.0.9001)

5 Likes

Is using odd and even indices too trivial?

library(tidyverse)
df_before <- tribble(~Singlecolumn,"Apple","Red","Banana","Yellow","Kiwi","Grey","Grapes","Green")
df_before
filter <- seq(1 ,nrow(df_before), 2)  # odd indices
filter
df_after <- tibble(df_before[filter, 1], df_before[filter+1, 1], )
names(df_after) <- c("Fruit",  "Color")
df_after

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.

library(tidyverse)
df_after<-df_before %>%
mutate(second_col=Singlecolumn) %>%
mutate_at(c("second_col"), list(lead), n = 1 ) %>%
dplyr::filter(row_number() %% 2 == 1) %>%
rename(fruit=Singlecolumn,
color=second_col)
df_after

Fruit Color
1 Apple Red
2 Banana Yellow
3 Kiwi Grey
4 Grapes Green

The logic is simple:
step-1: copy the original column as the second column
step-2: shift the second column up by one with the function "lead"
step-3: delete the odd-rows using dplyr command
step-4: rename the first two columns

The world needs the diversity of thoughts.