Modification of one row of data to get an nice looking output (summary), reshape - split rows to new multiple columns

user_5082294 0
Viewed 50

I need your help again. Usually I have some idea how to resolve my problem but in this case I have no clue. Here is the sample of my data:

df <- t(data.frame(b = list(c('C:1874 N:3493','C:2642 A:7 M:1 N:2717','A:5298 N:69','C:5366 Y:1','A:5359 G:8'))))
row.names(df) <- 'x'
colnames(df) <- c('V1','V2','V3','V4','V5')
df

  V1              V2                      V3            V4           V5          
x "C:1874 N:3493" "C:2642 A:7 M:1 N:2717" "A:5298 N:69" "C:5366 Y:1" "A:5359 G:8"

I'm looking for a nice summary stored in df like this NA can be replaced by blanks or zeros:

    V1    V2    V3    V4    V5
A   NA    7     5298  NA    5359
C   1874  2642  NA    5366  NA
N   3493  2717  69    NA    NA
M   NA    1     NA    NA    NA
Y   NA    NA    NA    1     NA
G   NA    NA    NA    NA    8

So far I know how to easly split element from one column to new rows:

library(tidyr)
separate_rows(as.data.frame(df), V1, sep = " ")

  V1     V2                    V3          V4         V5        
  <chr>  <chr>                 <chr>       <chr>      <chr>     
1 C:1874 C:2642 A:7 M:1 N:2717 A:5298 N:69 C:5366 Y:1 A:5359 G:8
2 N:3493 C:2642 A:7 M:1 N:2717 A:5298 N:69 C:5366 Y:1 A:5359 G:8

The output isn't nice beacuse all remaining columns are filled with result coming from 1st column but I was thinking to do this maybe in sapply() and with cbind() to get one data frame with resultin data frames coming from each input column. But dunno what next. How to put every letter to first column and make nice summary.

Thank you in advance!

dplyr reshape dataframe r split
2 Answers

This does what you want:

library(tidyverse)

df %>% 
  t() %>% 
  as_tibble(rownames = "colname") %>% # two columns: 'colname' (V1...V5) and 'x' (values)
  separate_rows(x, sep = " ") %>% # spread 'x' over rows
  separate(x, c("rowname", "value"), sep = ":") %>% # separate letter and value into two columns 'rowname' and 'value' 
  pivot_wider(names_from = "colname", values_from = "value") %>% # pivot into the wide form you want
  arrange(rowname) # sort by rowname

which gives

# A tibble: 6 x 6
  rowname V1    V2    V3    V4    V5   
  <chr>   <chr> <chr> <chr> <chr> <chr>
1 A       NA    7     5298  NA    5359 
2 C       1874  2642  NA    5366  NA   
3 G       NA    NA    NA    NA    8    
4 M       NA    1     NA    NA    NA   
5 N       3493  2717  69    NA    NA   
6 Y       NA    NA    NA    1     NA
user_10622250

In base R you can do

FUN <- function(x) {
  s <- strsplit(x, split=":")
  d <- as.double(sapply(s, `[`, 2))
  setNames(as.data.frame(t(d)), lapply(s, `[`, 1))
}


l <- lapply(lapply(unlist(df), function(x) el(strsplit(x, " "))), FUN)
res <- as.data.frame(t(Reduce(function(...) merge(..., all=TRUE, sort=FALSE), l)))
res
#     V1   V2   V3   V4   V5
# A   NA    7 5298   NA 5359
# C 1874 2642   NA 5366   NA
# N 3493 2717   69   NA   NA
# M   NA    1   NA   NA   NA
# Y   NA   NA   NA    1   NA
# G   NA   NA   NA   NA    8
user_65740380
Related