Merging sets of columns with 0,1 and NA in R

Question

I have a tibble like this.

# A tibble: 45 x 10
   `q2-1` `q2-2` `q2-3` `q2-4` `q2-5` `q3-1` `q3-2` `q3-3` `q3-4` `q3-5`
    <dbl>  <dbl>  <dbl>  <dbl>  <dbl>  <dbl>  <dbl>  <dbl>  <dbl>  <dbl>
 1      0      1      0      1      1     NA     NA     NA     NA     NA
 2      0      0      0      1      0     NA     NA     NA     NA     NA
 3      0      0      0      0      1     NA     NA     NA     NA     NA
 4      NA     NA     NA     NA     NA     1      1      0      0      1
 5      0      0      0      1      0     NA     NA     NA     NA     NA
 6      0      1      0      0      1     NA     NA     NA     NA     NA
 7      0      0      0      0      1     NA     NA     NA     NA     NA
 8      NA     NA     NA     NA     NA     0      1      0      0      0
 9      0      0      0      1      0     NA     NA     NA     NA     NA
10      0      0      0      0      1     NA     NA     NA     NA     NA

I want to merge q2 and q3 while still keeping individual colomns. (like q-1, q-2... q-5) How can I do this?

What do you mean by merge? Can you show your expected output? — Ronak Shah, Sep 18 '21 at 02:46
You should specify the ""merging"" function. Do you want to sum the values rowwise? Or maybe get the max rowwise for every prefix "qx"? — GuedesBF, Sep 18 '21 at 02:49
Please clarify your specific problem or provide additional details to highlight exactly what you need. As it's currently written, it's hard to tell exactly what you're asking. — Community, Sep 18 '21 at 04:21

score 0 · Accepted Answer · answered Sep 18 '21 at 04:00

(If you had offered dput() output wee would not have needed to redo the data entry.)

Appears you want missing values in the first 5 columns replaced by their corresponding values in cols 6-10 and the names of the columns changed to reflect that rearrangement. So use is.na() on the first five columns on both sides of a replacement for an index:

dat <- read.table(text="`q2-1` `q2-2` `q2-3` `q2-4` `q2-5` `q3-1` `q3-2` `q3-3` `q3-4` `q3-5`
     1      0      1      0      1      1     NA     NA     NA     NA     NA
 2      0      0      0      1      0     NA     NA     NA     NA     NA
 3      0      0      0      0      1     NA     NA     NA     NA     NA
 4      NA     NA     NA     NA     NA     1      1      0      0      1
 5      0      0      0      1      0     NA     NA     NA     NA     NA
 6      0      1      0      0      1     NA     NA     NA     NA     NA
 7      0      0      0      0      1     NA     NA     NA     NA     NA
 8      NA     NA     NA     NA     NA     0      1      0      0      0
 9      0      0      0      1      0     NA     NA     NA     NA     NA
 10      0      0      0      0      1     NA     NA     NA     NA     NA", head=T)
dat
   X.q2.1. X.q2.2. X.q2.3. X.q2.4. X.q2.5. X.q3.1. X.q3.2. X.q3.3. X.q3.4. X.q3.5.
1        0       1       0       1       1      NA      NA      NA      NA      NA
2        0       0       0       1       0      NA      NA      NA      NA      NA
3        0       0       0       0       1      NA      NA      NA      NA      NA
4       NA      NA      NA      NA      NA       1       1       0       0       1
5        0       0       0       1       0      NA      NA      NA      NA      NA
6        0       1       0       0       1      NA      NA      NA      NA      NA
7        0       0       0       0       1      NA      NA      NA      NA      NA
8       NA      NA      NA      NA      NA       0       1       0       0       0
9        0       0       0       1       0      NA      NA      NA      NA      NA
10       0       0       0       0       1      NA      NA      NA      NA      NA
 dat1 <- dat[1:5]
 dat2<- dat[6:10]
 dat1[is.na(dat1)] <- dat2[is.na(dat1)]

The name stuff can be done with sub or gsub.

 names(dat1) <- gsub("X[.]","", names(dat1))
 names(dat1) <- gsub("^q\\d[.]","q-", names(dat1))
 dat1
#---------------------
   q-1. q-2. q-3. q-4. q-5.
1     0    1    0    1    1
2     0    0    0    1    0
3     0    0    0    0    1
4     1    1    0    0    1
5     0    0    0    1    0
6     0    1    0    0    1
7     0    0    0    0    1
8     0    1    0    0    0
9     0    0    0    1    0
10    0    0    0    0    1

Thank you very much for this, it worked! Also, sorry for the data, I'm new to here/programming in general. I will mind next time! — r_noobie, Sep 18 '21 at 05:21
There a lot of good information and worked examples about how to deliver unambiguous data here: https://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example?r=SearchResults&s=1|1747.9037 In the pre-tibble era there was often ambiguity in the print output from the console, so we could never tell whether a column was a factor or character. Tibbles are unambiguous because they display their classes but they don't lend themselves to use as immediate text input. You need to strip out the class lines. — IRTFM, Sep 18 '21 at 05:40

GuedesBF · Answer 2 · 2021-09-18T14:39:00.380

I think you are looking for coalesce:

library(dplyr)
library(purrr)

  dat %>% mutate(map2_dfc(across(starts_with('q2')),
                         across(starts_with('q3')),
                         coalesce),
                .keep = 'unused') %>%
         rename_with(~str_remove_all(.x, "(?<=q)\\d+"))

  q.1 q.2 q.3 q.4 q.5
1    0   1   0   1   1
2    0   0   0   1   0
3    0   0   0   0   1
4    1   1   0   0   1
5    0   0   0   1   0
6    0   1   0   0   1
7    0   0   0   0   1
8    0   1   0   0   0
9    0   0   0   1   0
10   0   0   0   0   1

data

structure(list(q2.1 = c(0L, 0L, 0L, NA, 0L, 0L, 0L, NA, 0L, 0L
), q2.2 = c(1L, 0L, 0L, NA, 0L, 1L, 0L, NA, 0L, 0L), q2.3 = c(0L, 
0L, 0L, NA, 0L, 0L, 0L, NA, 0L, 0L), q2.4 = c(1L, 1L, 0L, NA, 
1L, 0L, 0L, NA, 1L, 0L), q2.5 = c(1L, 0L, 1L, NA, 0L, 1L, 1L, 
NA, 0L, 1L), q3.1 = c(NA, NA, NA, 1L, NA, NA, NA, 0L, NA, NA), 
    q3.2 = c(NA, NA, NA, 1L, NA, NA, NA, 1L, NA, NA), q3.3 = c(NA, 
    NA, NA, 0L, NA, NA, NA, 0L, NA, NA), q3.4 = c(NA, NA, NA, 
    0L, NA, NA, NA, 0L, NA, NA), q3.5 = c(NA, NA, NA, 1L, NA, 
    NA, NA, 0L, NA, NA)), class = "data.frame", row.names = c("1", 
"2", "3", "4", "5", "6", "7", "8", "9", "10"))

  q2.1 q2.2 q2.3 q2.4 q2.5 q3.1 q3.2 q3.3 q3.4 q3.5
1     0    1    0    1    1   NA   NA   NA   NA   NA
2     0    0    0    1    0   NA   NA   NA   NA   NA
3     0    0    0    0    1   NA   NA   NA   NA   NA
4    NA   NA   NA   NA   NA    1    1    0    0    1
5     0    0    0    1    0   NA   NA   NA   NA   NA
6     0    1    0    0    1   NA   NA   NA   NA   NA
7     0    0    0    0    1   NA   NA   NA   NA   NA
8    NA   NA   NA   NA   NA    0    1    0    0    0
9     0    0    0    1    0   NA   NA   NA   NA   NA
10    0    0    0    0    1   NA   NA   NA   NA   NA

Merging sets of columns with 0,1 and NA in R

2 Answers2