Question: HELP: Excel Index & Match in R - How to do it?
0
gravatar for Pepitostar
14 days ago by
Pepitostar0
EMoonX
Pepitostar0 wrote:

Hi Biostar users! Im a bit new to R and I want someone to help me with this problem. Im trying to solve it using loop function in R, unfortunately, I cant do it. Please help, I'm solving this almost a day. In end I want to have an output file that is similar to the given example below. Thanks in advance.

df1 <- data.frame(stringsAsFactors = FALSE,
                         id = c('13B1', '13B2', '13B3', '13B4', '13B5', '13B6', '13B7', '13B8', '13B9', '13B10'),
                         A1 = c('ABB',  'CD',     'NA',   'CD',   'CD',   'CD',   'ABB',    'CD',     'CD',   'CD'),
                         A2 = c('ASD',  'ASD',  'ASD',  'ASD',  'ASD',  'ASD',  'ADD',  'ASD',  'ASD',  'ASD'),
                         A3 = c('WWW',  'WWW',  'NA',     'WWW',    'NA',     'WWW',    'NA',     'NA',   'XX',   'NA'),
                         A4 = c('QRS',  'QRS',  'QRS',  'QRS',  'QRS',  'QRS',  'QRS',  'QRS',  'QRS',  'QRS'),
                         A5 = c('HMM',  'HMM',  'HMM',  'HMM',  'HMM',  'HMM',  'HMM',  'HMM',  'HMM',  'ABB')
 )

 df2 <- data.frame(stringsAsFactors = FALSE,
                       Remark = c('Good',   'Bad'), 
                       A1 = c('ABB','CD'),
                       A4 = c('QRS','FST'),
                       A3 = c('WWW','XX'),
                       A5 = c('HMM','ABB'),
                       A2 = c('ADD','ASD')
 )                        

 output <- data.frame(stringsAsFactors = FALSE,
                   id = c('13B1',   '13B2', '13B3', '13B4', '13B5', '13B6', '13B7', '13B8', '13B9', '13B10'),
                   A1 = c('Good',   'Bad',  'NA',   'Bad',  'Bad',  'Bad',  'Good', 'Bad',  'Bad',  'Bad'),
                   A2 = c('Bad',    'Bad',  'Bad',  'Bad',  'Bad',  'Bad',  'Good', 'Bad',  'Bad',  'Bad'),
                   A3 = c('Good',   'Good', 'NA',   'Good', 'NA',   'Good', 'NA',   'NA',   'Bad',  'NA'),
                   A4 = c('Good',   'Good', 'Good', 'Good', 'Good', 'Good', 'Good', 'Good', 'Good', 'Good'),
                   A5 = c('Good',   'Good', 'Good', 'Good', 'Good', 'Good', 'Good', 'Good', 'Good', 'Bad')
)
snp R • 117 views
ADD COMMENTlink modified 14 days ago by rpolicastro720 • written 14 days ago by Pepitostar0
3
gravatar for rpolicastro
14 days ago by
rpolicastro720
rpolicastro720 wrote:

Here's a tidyverse solution. The trick is to go from wide to long data, join the data.frames, and then go back to wide data.

library("dplyr")
library("tidyr")

df1 <- pivot_longer(df1, starts_with("A"), names_to="col", values_to="sample")
df2 <- pivot_longer(df2, starts_with("A"), names_to="col", values_to="sample")

output <- df1 %>%
  left_join(df2, by=c("col", "sample")) %>%
  select(-sample) %>%
  pivot_wider(names_from="col", values_from="Remark")

>output
# A tibble: 10 x 6
   id    A1    A2    A3    A4    A5   
   <chr> <chr> <chr> <chr> <chr> <chr>
  1 13B1  Good  Bad   Good  Good  Good 
  2 13B2  Bad   Bad   Good  Good  Good 
  3 13B3  NA    Bad   NA    Good  Good 
  4 13B4  Bad   Bad   Good  Good  Good 
  5 13B5  Bad   Bad   NA    Good  Good 
  6 13B6  Bad   Bad   Good  Good  Good 
  7 13B7  Good  Good  NA    Good  Good 
  8 13B8  Bad   Bad   NA    Good  Good 
  9 13B9  Bad   Bad   Bad   Good  Good 
 10 13B10 Bad   Bad   NA    Good  Bad
ADD COMMENTlink modified 14 days ago • written 14 days ago by rpolicastro720

OMG! I tried and it works. You are heaven-sent. Thanks a lot man for this clean and beautiful codes.

Note to the viewers: I have a difficulty of making this work due to the "pivot_longer" or tidyr function. You have to install the latest version of R to make this work. Mine was 3.5.3 :)

ADD REPLYlink written 14 days ago by Pepitostar0

Glad it worked! pivot_longer and pivot_wider are from tidyr version 1.1.0 released this May 2020. These functions are replacements for gather and spread respectively.

ADD REPLYlink modified 14 days ago • written 14 days ago by rpolicastro720
Please log in to add an answer.

Help
Access

Use of this site constitutes acceptance of our User Agreement and Privacy Policy.
Powered by Biostar version 2.3.0
Traffic: 1518 users visited in the last hour