R - merge/combine columns with same name but some data values equal zero

Question

First of all, I have a matrix of features and a data.frame of features from two separate text sources. On each of those, I have performed different text mining methods. Now, I want to combine them but I know some of them have columns with identical names like the following:

> dtm.matrix[1:10,66:70]
       cough nasal sputum yellow intermitt
    1      1     0      0      0         0
    2      1     0      0      0         0
    3      0     0      0      0         0
    4      0     0      0      0         0
    5      0     0      0      0         0
    6      1     0      0      0         0
    7      0     0      0      0         0
    8      0     0      0      0         0
    9      0     0      0      0         0
    10     0     0      0      0         0

> dim(dtm.matrix) [1] 14300 6543

And the second set looks like this:

    > data1.sub[1:10,c(1,37:40)]
   Data number cough coughing up blood dehydration dental abscess
1            1     0                 0           0              0
2            3     1                 0           0              0
3            6     0                 0           0              0
4            8     0                 0           0              0
5            9     0                 0           0              0
6           11     1                 0           0              0
7           12     0                 0           0              0
8           13     0                 0           0              0
9           15     0                 0           0              0
10          16     1                 0           0              0
> dim(data1.sub)
[1] 14300   168

I got this code from this topic but I'm new to R and I still need some help with it:

    `data1.sub.merged <- dcast.data.table(merge(
    ## melt the first data.frame and set the key as ID and variable
    setkey(melt(as.data.table(data1.sub), id.vars = "Data number"), "Data number", variable), 
  ## melt the second data.frame
  melt(as.data.table(dtm.matrix), id.vars = "Data number"), 
  ## you'll have 2 value columns...
  all = TRUE)[, value := ifelse(
  ## ... combine them into 1 with ifelse
  (value.x == 0), value.y, value.x)], 
  ## This is the reshaping formula
  "Data number" ~ variable, value.var = "value")`

When I run this code, it returns a matrix of 1x6667 and doesn't merge the "cough" (or any other column) from the two data sets together. I'm confused. Could you help me how this works?

Answer 1

There are many ways to do that, f.e. using base R, data.table or dplyr. The choice depends on the volume of your data, and if you, say, work with very large matrices (which is usually the case with natural language processing and bag of words representation), you may need to play with different ways to solve your problem and profile the better (=the quickest) solution. I did what you wanted via dplyr. This is a bit ugly but it works. I just merge two dataframes, then use for cycle for those variables which exist in both dataframes: sum them up (variable.x and variable.y) and then delete em. Note that I changed a bit your column names for reproducibility, but it shouldn't have any impact. Please let me know if that works for you.

df1 <- read.table(text = 
'     cough nasal sputum yellow intermitt
1      1     0      0      0         0
2      1     0      0      0         0
3      0     0      0      0         0
4      0     0      0      0         0
5      0     0      0      0         0
6      1     0      0      0         0
7      0     0      0      0         0
8      0     0      0      0         0
9      0     0      0      0         0
10     0     0      0      0         0')

df2 <- read.table(text = 
'   Data_number cough coughing_up_blood dehydration dental_abscess
1            1     0                 0           0              0
2            3     1                 0           0              0
3            6     0                 0           0              0
4            8     0                 0           0              0
5            9     0                 0           0              0
6           11     1                 0           0              0
7           12     0                 0           0              0
8           13     0                 0           0              0
9           15     0                 0           0              0
10          16     1                 0           0              0')

# Check what variables are common
common <- intersect(names(df1),names(df2))

# Set key IDs for data
df1$ID <- seq(1,nrow(df1))
df2$ID <- seq(1,nrow(df2))

# Merge dataframes
df <- merge(df1, df2,by = "ID")

# Sum and clean common variables left in merged dataframe
library(dplyr)

for (variable in common){
  # Create a summed variable
  df[[variable]] <- df %>% select(starts_with(paste0(variable,"."))) %>% rowSums()
  # Delete columns with .x and .y suffixes
  df <- df %>% select(-one_of(c(paste0(variable,".x"), paste0(variable,".y"))))
}

df
   ID nasal sputum yellow intermitt Data_number coughing_up_blood dehydration dental_abscess cough
1   1     0      0      0         0           1                 0           0              0     1
2   2     0      0      0         0           3                 0           0              0     2
3   3     0      0      0         0           6                 0           0              0     0
4   4     0      0      0         0           8                 0           0              0     0
5   5     0      0      0         0           9                 0           0              0     0
6   6     0      0      0         0          11                 0           0              0     2
7   7     0      0      0         0          12                 0           0              0     0
8   8     0      0      0         0          13                 0           0              0     0
9   9     0      0      0         0          15                 0           0              0     0
10 10     0      0      0         0          16                 0           0              0     1