CODEWITHSUNDEEP
regexrdataframebioinformatics
user1631306
user1631306

Reputation: 4470

gsub in columns value in dataframe

I have a file with multiple columns. I am showing two columns in which I am interested two columns

 Probe.Set.ID         Entrez.Gene
A01157cds_s_at               50682
A03913cds_s_at               29366
A04674cds_s_at 24860 /// 100909612
A07543cds_s_at               24867
A09811cds_s_at               25662
----                          ----
A16585cds_s_at               25616

I need to replace /// with "\t"(tab) and the output should be like

A01157cds_s_at;50682
A03913cds_s_at;29366
A04674cds_s_at;24860      100909612

Also, I need to avoid the ones with "---"

Upvotes: 1

Views: 617

Answers (3)

narendra-choudhary
narendra-choudhary

Reputation: 4818

We can use dplyr and tidyr here.

library(dplyr)
library(tidyr)

> df <- data.frame(
    col1 = c('A01157cds_s_at', 'A03913cds_s_at', 'A04674cds_s_at', 'A07543cds_s_at', '----'), 
    col2 = c('50682', '29366', '24860 /// 100909612', '24867', '----'))


> df %>% filter(col1 != '----') %>% 
    separate(col2, c('col2_first', 'col2_second'), '///', remove = T) %>%
    unite(col1_new, c(col1, col2_first), sep = ';', remove = T)

> df

##                col1_new col2_second
## 1  A01157cds_s_at;50682        <NA>
## 2  A03913cds_s_at;29366        <NA>
## 3  A04674cds_s_at;24860    100909612
## 4  A07543cds_s_at;24867        <NA>
  • filter removes the observations with col1 == '----'.
  • separate splits col2 into two columns, namely col2_first and col2_second
  • unite concatenates col1 and col2_first with ; as separator.

Upvotes: 1

user2280549
user2280549

Reputation: 1234

Here is slightly more different approach using dplyr:

data <- data.frame(Probe.Set.ID = c("A01157cds_s_at",
                                "A03913cds_s_at",
                                "A04674cds_s_at",
                                "A07543cds_s_at",
                                "A09811cds_s_at",
                                "----",
                                "A16585cds_s_at"),
               Entrez.Gene = c("50682",
                               "29366",
                               "24860 /// 100909612",
                               "24867",
                               "25662",
                               "----",
                               "25616")
)

if(!require(dplyr)) install.packages("dplyr")
library(dplyr)

data %>% 
  filter(Entrez.Gene != "----") %>%
  mutate(new_column = paste(Probe.Set.ID,
                        gsub("///", "\t", Entrez.Gene),
                        sep = ";"
                        )
     ) %>% select(new_column)

Upvotes: 3

Willie D
Willie D

Reputation: 145

Looks like you will want to subset the data, then paste the two columns together, then use gsub to make the replace the '///'. Here is what I came up with, with dat being the dataframe containing the two columns. 

dat = dat[dat$Probe.Set.ID != "----",] # removes the rows with "---"
dat = paste0(dat$Probe.Set.ID, ";", dat$Entrez.Gene) # pastes the columns together and adds the ";"
dat = gsub("///","\t",dat) # replaces the "///" with a tab

Also, use cat() to view the tab as opposed to "\t". I got that from here: How to replace specific characters of a string with tab in R. This will output a list as opposed to a data.frame. You can convert back with data.frame(), but then you cannot use cat() to view.

Upvotes: 2

Related Questions