Breaking a wide dataframe to two uneven column dataframe and change it to long formate

user_13676462 0
Viewed 42

I have a wide table with more than 22 columns. This table is the result of fuzzymatch and that's why it's in wide format. The column names are shown below (in order) (I will try to create a sample data frame for better demonstration):

 [1] "shift_date.x"  "shift"   "ageyrs"   "site"   "level"            
 [6] "crowded_shift"    "time"    "dd"     "AE"    "ageyrs_start"    
[11] "ageyrs_end"    "time_start"     "time_end"     "shift_date.y"     "shift_n"   
[16] "ageyrs_n"     "site_n"   "level_n"   "crowded_shift_n"  "los_n"           
[21] "dd_n" "AE_n"

What I want to do is to break this data frame starting from column 14 to the end ("shift_date.y" to "AE_n") and add it as new rows to the bottom of first section of table (change it to long format). The problem is that the first section has 13 columns but the second part has 8 and I am not sure how I can combine them (that's why probably subsetting and rbind don't work).

As an example, imagine we have the following data frame:

shift <- c (2,1,0)
ageyrs <- c(12.2,13,14)
site <- c(0,1,3)
level <- c (1,5,6)
ageyrs_s <- c (2,4,5)
ageyrs_n <- c (4,6,8)
shift2 <- c (2,1,0)
ageyrs2 <- c(12.2,13,14)
site2 <- c(0,1,3)
level2 <- c (1,5,6)

a <- data.frame(shift, ageyrs, site, level, ageyrs_s, ageyrs2, shift2, ageyrs2, site2, level2)


  shift ageyrs site level ageyrs_s ageyrs_n shift_n ageyrs_n site_n level_n
1     2   12.2    0     1        2        4      2    12.2     0      1
2     1   13.0    1     5        4        6      1    13.0     1      5
3     0   14.0    3     6        5        8      0    14.0     3      6

No I want to break this dataframe at "shift2" column and create a dataframe line shown below:

 shift ageyrs site level ageyrs_s ageyrs_n
1     2   12.2    0     1        2        4
2     1   13.0    1     5        4        6
3     0   14.0    3     6        5        8
4     2   12.2    0     1       NA       NA
5     1   13.0    1     5       NA       NA
6     0   14.0    3     6       NA       NA

Any suggestions on how to resolve this?

tidyr dataframe pivot r
2 Answers

Try this. You can use bind_rows() and setNames() to define common names so that the values can be joined properly:

library(dplyr)
#Code
newa <- a %>% select(shift:ageyrs_n) %>%
  bind_rows(a %>% select(shift2:level2) %>% setNames(gsub('2','',names(.))))

Output:

  shift ageyrs site level ageyrs_s ageyrs_n
1     2   12.2    0     1        2        4
2     1   13.0    1     5        4        6
3     0   14.0    3     6        5        8
4     2   12.2    0     1       NA       NA
5     1   13.0    1     5       NA       NA
6     0   14.0    3     6       NA       NA
user_20808480

We can use split.default from base R to split the data into list of data.frames and then convert to a single data.frame after unlisting the list elements

nm1 <- sub("\\d+$", "", names(a))
lst1 <-  lapply(split.default(a, nm1),
      unlist, use.names = FALSE)
out <- data.frame(lapply(lst1, `length<-`, max(lengths(lst1))))[unique(nm1)]

-output

out
#  shift ageyrs site level ageyrs_s ageyrs_n
#1     2   12.2    0     1        2        4
#2     1   13.0    1     5        4        6
#3     0   14.0    3     6        5        8
#4     2   12.2    0     1       NA       NA
#5     1   13.0    1     5       NA       NA
#6     0   14.0    3     6       NA       NA

Or using tidyverse

library(dplyr)
library(tidyr)
library(stringr)

a %>% 
  rename_at(vars(shift:level), ~ str_c(., '1')) %>%
  pivot_longer(cols = -c(ageyrs_s, ageyrs_n), names_to = c(".value", 'grp'), 
     names_sep = "(?<=[a-z])(?=[0-9])")
user_37322710
Related