R pivot_wider so repeating rows become headers

user_13300333 0
Viewed 196

I am trying to transform long data so that repeating row values become headers. The data looks like this:

# A tibble: 12 x 2
   x1       x2           
   <chr>    <chr>        
 1 Position 1            
 2 Name     Jon Ellis    
 3 Sex      m            
 4 Year     2017         
 5 Category Open         
 6 Time     06:37:27     
 7 Position 2            
 8 Name     Craig Holgate
 9 Sex      m            
10 Year     2015         
11 Category Open         
12 Time     06:43:45

I would like my repeating row values ("Position", "Name", "Sex", "Year", "Category", "Time") to become headers but despite many attempts haven't yet figured out how to spread/pivot the data to make this happen. Grateful for pointers, thank you.

structure(list(x1 = c("Position", "Name", "Sex", "Year", "Category", 
"Time", "Position", "Name", "Sex", "Year", "Category", "Time", 
"Position", "Name", "Sex", "Year", "Category", "Time", "Position", 
"Name", "Sex", "Year", "Category", "Time"), x2 = c("1", "Jon Ellis", 
"m", "2017", "Open", "06:37:27", "2", "Craig Holgate", "m", "2015", 
"Open", "06:43:45", "3", "Stuart Leaney", "m", "2018", "Open", 
"06:46:03", "4", "Craig Holgate", "m", "2013", "Open", "06:47:19"
)), row.names = c(NA, -24L), class = c("tbl_df", "tbl", "data.frame"
))
dplyr r
3 Answers

1) dplyr/tidyr Add a grouping column, row, convert from long to wide form, remove row and convert the column types.

library(dplyr)
library(tidyr)

DF %>%
  mutate(row = cumsum(x1 == "Position")) %>%
  pivot_wider(names_from = x1, values_from = x2) %>%
  select(-row) %>%
  type.convert(as.is = TRUE)

giving:

# A tibble: 2 x 6
  Position Name          Sex    Year Category Time    
     <int> <chr>         <chr> <int> <chr>    <chr>   
1        1 Jon Ellis     m      2017 Open     06:37:27
2        2 Craig Holgate m      2015 Open     06:43:45

2) Base R Convert to Debian Control File format using string manipulation and read that with read.dcf creating a character matrix, convert to data frame and fix up the types.

txt <- with(DF, sub("Position", "\nPosition", sprintf("%s: %s", x1, x2)))
type.convert(as.data.frame(read.dcf(textConnection(txt))), as.is = TRUE)

giving:

  Position          Name Sex Year Category     Time
1        1     Jon Ellis   m 2017     Open 06:37:27
2        2 Craig Holgate   m 2015     Open 06:43:45

or expressed in terms of the Bizarro pipe which only needs base R:

DF ->.;
  with(., sub("Position", "\nPosition", sprintf("%s: %s", x1, x2))) ->.;
  textConnection(.) ->.;
  read.dcf(.) ->.;
  as.data.frame(.) ->.;
  type.convert(., as.is = TRUE)

Note

DF <- structure(list(x1 = c("Position", "Name", "Sex", "Year", "Category", 
"Time", "Position", "Name", "Sex", "Year", "Category", "Time"
), x2 = c("1", "Jon Ellis", "m", "2017", "Open", "06:37:27", 
"2", "Craig Holgate", "m", "2015", "Open", "06:43:45")), class = "data.frame", row.names = c(NA, 
-12L))
user_5165480

This will do assuming no attribute in between is missing

library(tidyverse)
df %>% pivot_wider(names_from = x1, values_from = x2, values_fn = list) %>%
  unnest(cols = everything())

A tibble: 4 x 6
  Position Name          Sex   Year  Category Time    
  <chr>    <chr>         <chr> <chr> <chr>    <chr>   
1 1        Jon Ellis     m     2017  Open     06:37:27
2 2        Craig Holgate m     2015  Open     06:43:45
3 3        Stuart Leaney m     2018  Open     06:46:03
4 4        Craig Holgate m     2013  Open     06:47:19
user_28848590

Mine is a bit more like dear AnilGoyal interpretation with two slight modifications:

library(dplyr)
library(tidyr)

df %>%
  group_by(x1) %>%
  mutate(id = row_number()) %>%      
  pivot_wider(id_cols = id, names_from = x1, values_from = x2)

# A tibble: 4 x 7
     id Position Name          Sex   Year  Category Time    
  <int> <chr>    <chr>         <chr> <chr> <chr>    <chr>   
1     1 1        Jon Ellis     m     2017  Open     06:37:27
2     2 2        Craig Holgate m     2015  Open     06:43:45
3     3 3        Stuart Leaney m     2018  Open     06:46:03
4     4 4        Craig Holgate m     2013  Open     06:47:19

Data:

df <- structure(list(x1 = c("Position", "Name", "Sex", "Year", "Category", 
                      "Time", "Position", "Name", "Sex", "Year", "Category", "Time", 
                      "Position", "Name", "Sex", "Year", "Category", "Time", "Position", 
                      "Name", "Sex", "Year", "Category", "Time"), x2 = c("1", "Jon Ellis", 
                                                                         "m", "2017", "Open", "06:37:27", "2", "Craig Holgate", "m", "2015", 
                                                                         "Open", "06:43:45", "3", "Stuart Leaney", "m", "2018", "Open", 
                                                                         "06:46:03", "4", "Craig Holgate", "m", "2013", "Open", "06:47:19"
                      )), row.names = c(NA, -24L), class = c("tbl_df", "tbl", "data.frame"
                      ))
user_143145200
Related