Flatten rows into one single row where there is only one value per column and vertically combine with similar resulting data frames

user_7871804 0
Viewed 702

I have two data frames:

Harry <- c(1, NA, NA, NA)
Tom <- c(NA, 2, NA, NA)
Sally <- c(NA, NA, 3, NA)
Jane <- c(NA, NA, NA, 4)

df <- data.frame(Harry, Tom, Sally, Jane)

Harry <- c(1, NA, NA, NA)
Tom <- c(1, NA, NA, NA)
Mary <- c(NA, NA, 3, NA)
Sarah <- c(NA, NA, NA, 4)

df2 <- data.frame(Harry, Tom, Mary, Sarah)

... where there's only one value per column. I'd like to flatten the data frames into single rows and then vertically concatenate such that each data frame becomes an observation in the new frame. There may be different columns, in which case these columns would be added and hence why I can't use rbind.

In addition and since these are numeric, the NAs should be zeroes and the resulting frame would look as below:

Harry <- c(1, 1)
Tom <- c(2, 1)
Sally <- c(3, 0)
Jane <- c(4, 0)
Mary <- c(0, 3)
Sarah <- c(0, 4)

df <- data.frame(Harry, Tom, Sally, Jane, Mary, Sarah)

I realise I could make everything numeric and total to get each row, but my issue is to get this into a single object.

dataframe r
2 Answers

We can use the gather and spread approach from dplyr and tidyr.

library(dplyr)
library(tidyr)

df_2 <- df %>% gather(Col, Val, na.rm = TRUE) 

df2_2 <- df2 %>% gather(Col, Val, na.rm = TRUE) 

df3 <- bind_rows(df_2, df2_2, .id = "ID") %>%
  spread(Col, Val, fill = 0) %>%
  select(-ID)

df3
#   Harry Jane Mary Sally Sarah Tom
# 1     1    4    0     3     0   2
# 2     1    0    3     0     4   1
user_76698090

We can get the dataset into a single one with bind_rows, create a grouping column using .id, grouped by 'grp', then get the sum of columns with summarise_all

library(dplyr)
bind_rows(df, df2, .id = 'grp') %>% 
       group_by(grp) %>% 
       summarise_all(funs(sum(., na.rm = TRUE))) %>%
       ungroup %>%
       select(-grp)
# A tibble: 2 x 6
#  Harry   Tom Sally  Jane  Mary Sarah
#  <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>
#1     1     2     3     4     0     0
#2     1     1     0     0     3     4
user_37322710
Related