R: Merge information from 2 columns together

r combine two columns into one
combine two data frames in r with different columns
r combine multiple rows into one
merge in r
combine columns in r dplyr
r combine two data frames vertically
stack two columns in r
r merge data frames by column names

I have created an example dataframe which has 3 different groups with 2 columns for each group.

Group_1 shows the total amount of participants and Group_1_Pos shows how many of the total participants are positive, etc:

df1 <- structure(list(Date = c("2016", "2017", "2018", "2019"), 
                       Group_1 = c("100", "200", "300", "400"), 
                       Group_1_Pos = c("10", "20", "30", "40"),
                       Group_2 = c("500", "600", "700", "800"),
                       Group_2_Pos = c("50", "60", "70", "80"), 
                       Group_3 = c("900", "1000", "1100", "1200"),
                       Group_3_Pos = c("90", "100", "110", "120")), 
                  class = "data.frame", row.names=c("1", "2", "3", "4"))
> df1
  Date Group_1 Group_1_Pos Group_2 Group_2_Pos Group_3 Group_3_Pos
1 2016     100          10     500          50     900          90
2 2017     200          20     600          60    1000         100
3 2018     300          30     700          70    1100         110
4 2019     400          40     800          80    1200         120

I would like to combine the total participant columns together with the positive participant columns in a way that keeps both values still seperated with brackets. As an example:

  Date      Group_1    Group_2     Group_3 
1 2016     100 (10)    500 (50)    900 (90)          
2 2017     200 (20)    600 (60)  1000 (100)        
3 2018     300 (30)    700 (70)  1100 (110)       
4 2019     400 (40)    800 (80)  1200 (120)        

So in this example I add the positive participants in () brackets next to the total participants and only keep 3 columns for the 3 groups.

Any help would be appreciated.

Using dplyr you could go for something like:

library(dplyr)

df1 %>%
  mutate(Group_1 = paste0(Group_1, " (", Group_1_Pos, ")"),
         Group_2 = paste0(Group_2, " (", Group_2_Pos, ")"),
         Group_3 = paste0(Group_3, " (", Group_3_Pos, ")"),) %>% 
  select(-contains("Pos"))

#   Date  Group_1  Group_2    Group_3
# 1 2016 100 (10) 500 (50)   900 (90)
# 2 2017 200 (20) 600 (60) 1000 (100)
# 3 2018 300 (30) 700 (70) 1100 (110)
# 4 2019 400 (40) 800 (80) 1200 (120)

Data Wrangling in R: Combining, Merging and Reshaping Data, Rda") # Sometimes we have multiple data frames we want to combine. other, ( 2) place side-by-side, # or (3) merge together based on common variables. From the help # file: "By default the data frames are merged on the columns with � Part 3. Merging two columns ("first_name" and "last_name") Our goal now is to create a new column "full_name", which will contain the persons first name and last name with the space in between them. We start by identifying the R objects we are going to be merging: in our case it's column "first_name" and column "last_name".

Here is a base R way of doing what the question asks for. Get the columns to be pasted with a regex and grep, then loop through the indices vector and paste them together. Finally, cbind the first column and this result.

inx <- grep("\\d$", names(df1))
tmp <- sapply(inx, function(i) paste(df1[[i]], paste0("(", df1[[i + 1]], ")")))
res <- cbind(df1[1], tmp)
names(res)[-1] <- names(df1)[inx]

res
#  Date  Group_1  Group_2    Group_3
#1 2016 100 (10) 500 (50)   900 (90)
#2 2017 200 (20) 600 (60) 1000 (100)
#3 2018 300 (30) 700 (70) 1100 (110)
#4 2019 400 (40) 800 (80) 1200 (120)

Final clean up.

rm(inx, tmp)

unite: Unite multiple columns into one by pasting strings together in , Convenience function to paste together multiple columns into one. Tidy Messy Data. Description Usage Arguments See Also Examples. View source: R/unite.R data. A data frame. col. The name of the new column, as a string or symbol. See the documentation on ?merge, which states: By default the data frames are merged on the columns with names they both have, but separate specifications of the columns can be given by by.x and by.y. This clearly implies that merge will merge data frames based on more than one column. From the final example given in the documentation:

A purrr-dplyr-stringr:

    other_values <- df1[,seq(1,ncol(df1),2)]
       df1 %>% 
   select(-contains("Pos")) %>% 
   purrr::map2_df(.,other_values, 
                  function(x,y) paste0(x," (",y,")")) %>% 

   mutate(Date=stringr::str_remove_all(Date,"\\s.*"))
# A tibble: 4 x 4
  Date  Group_1  Group_2  Group_3   
  <chr> <chr>    <chr>    <chr>     
1 2016  100 (10) 500 (50) 900 (90)  
2 2017  200 (20) 600 (60) 1000 (100)
3 2018  300 (30) 700 (70) 1100 (110)
4 2019  400 (40) 800 (80) 1200 (120)

Merging Data, Merging Data. Adding Columns. To merge two data frames (datasets) horizontally, use the merge function. In most cases, you join two data frames by one or� It is used to concatenate two or more strings together. These strings can be in cells. So let’s see an example. Combine Two Columns In Excel Excel. For this example, we have this sample data. See below image. We need to combine column A and Column B into one to get full name. To combine First Name and Last Name we will use a helping column.

Given 3 groups, here is a base R solution that can give you the desired output

n <- 3
dfout <- cbind(df1[1],
               `colnames<-`(sapply(seq(n), function(k) paste0(df[[x <- paste0("Group_",k)]]," (", df[[paste0(x,"_Pos")]],")")),
                            paste0("Group",seq(n))))

such that

> dfout
  Date   Group1   Group2     Group3
1 2016 100 (10) 500 (50)   900 (90)
2 2017 200 (20) 600 (60) 1000 (100)
3 2018 300 (30) 700 (70) 1100 (110)
4 2019 400 (40) 800 (80) 1200 (120)

How to merge data in R using R merge, dplyr, or data.table, Merges with base R It doesn't matter the order of data frame 1 and data frame 2, but whichever one is first is considered x and the second one is y. If the columns you want to join by don't have the same name, you need to tell merge which columns you want to join by: by. x for the x data frame column name, and by. You may want to combine data from different sources in your analysis. Generally speaking, you can use R to combine different sets of data in three ways: By adding columns: If the two sets of data have an equal set of rows, and the order of the rows is identical, then adding columns makes sense. […]

Here is a more general tidyverse solution

library(tidyverse)
df1 %>%
      rename_at(
        vars(contains("Pos")),
        ~ str_remove(., "_Pos") %>% str_remove("Group_") %>% str_c("Pos", ., sep = "_")
      ) %>%
      pivot_longer(Group_1:Pos_3,
                   names_to = c(".value", "set"),
                   names_sep = "_") %>%
      mutate(Pos = Pos %>% str_c("(", ., ")")) %>%
      unite("result", Group:Pos, sep = "") %>%
      pivot_wider(names_from = set, values_from = result)

R: Merge Two Data Frames, names" or the number 0 specifies the row names. The rows in the two data frames that match on the specified columns are extracted, and joined together. If there is� In R you use the merge() function to combine data frames. This powerful function tries to identify columns or rows that are common between the two different data frames. How to use merge to find the intersection of data The simplest form of merge() finds the intersection between two different sets of data. In other […]

Concatenate two columns of dataframe in R, Concatenate or join of two string column in R & integer columns in R is 2. 3. 4. 5. 6. ### Create Data Frame. df1 = data.frame (State = c ( 'Arizona' , 'Georgia'� cbind() – combining the columns of two data frames side-by-side; rbind() – stacking two data frames on top of each other, appending one to the other; merge() – joining two data frames using a common column; Using cbind() to merge two R data frames. We will start with the cbind() R function. This a simple way to join datasets in R where

Use R to Combine Multiple Columns of Data into a Single Column , The last argument consists of the columns you want to be combined. You can also select instead the columns you don't want to combine using� In Excel, you can combine or merge text from two or more cells, as well as columns and rows,into one cell. If you have a lot of rows of data where you want to combine text, you can simply start typing the combined text in an adjacent column and Excel will fill in the rest for you.

Unite multiple columns into one by pasting strings together — unite , Source: R/unite.R. unite.Rd. Convenience function to paste together multiple columns into one. unite(data, col, , sep = "_", remove = TRUE, na.rm = FALSE)� This function allows you to combine text from different cells into one cell. For example, we have a worksheet containing names and contact information. We want to combine the Last Name and First Name columns in each row into the Full Name column. To begin, select the first cell that will contain the combined, or concatenated, text.