how to concetanate column values of a data frame and converting single letters of snp calls to bi allelic in R?

Question

[blacktomato27]

Dear all good afternoon i have example snp genotyping data like this

LOCUS   POS REF ALLELE  2000    3000
MC10    713          T      C            NA     NA
MC10    760          T      C           NA          NA

now i want to replace MC10 with SNP1, SNP2 and so no down the file and would like to insert chr column with dummy chromosome number let 1, also would like replace A with A/A, T with T/T, G with G/G and C with C/C in both REF and ALLELE columns and also replace NA with REF column values (ex NA of 2000 column to T/T). Finally i want to concatenate LOCUS, CHR and POS columns with _ into like this SNP1_1_713. I wish to like to like have data like this

LOCUS   CHR POS MAR        REF   ALLELE  2000   3000
SNP1    1           713       SNP1_1_713    T/T  C/C             T/T             T/T
SNP2    1          760       SNP2_1_760 T/T  C/C             T/T             T/T

I tried with gsub, mutate etc in tidyverse and dplyr packages and tried within function to achieve my target but unsuccessful. please find my example data here

https://www.dropbox.com/scl/fi/q5mmr48qn1564tukijtm8/New-Microsoft-Excel-Worksheet.xlsx?dl=0&rlkey=ixzyl2e3zyvzf8msu6v7jj0ww

can any one help me to get my expected results with R? anyhelp in this regard will be highly appreciated Thanks in advance

[cpad0112]

Try this code (assuming that there is no special formatting of sheet 1 values)

library(tidyverse)
library(readxl)
library(writexl)
test=read_xlsx("test.xlsx", sheet = 1)
test %>%
    mutate(chr="1", LOCUS=paste0("SNP_",row_number()),MAR = paste(LOCUS,chr,POS, sep = "_")) %>%
    mutate_at(vars(c("REF", "ALLELE")), ~case_when(.=="A" ~ "A/A", .=="T" ~ "T/T",.=="C" ~ "C/C",.=="G" ~ "G/G")) %>%
    mutate_at(vars(-c("LOCUS":"ALLELE","chr","MAR")),~str_replace_all(., "NA", REF)) %>%
    select(LOCUS,chr, POS,MAR, REF:PDPE_021) %>%
    write_xlsx(., "test1.xlsx")

output from test1.xlsx:

LOCUS   chr POS MAR REF ALLELE  2000    3000    4000    5000    6000    7000    PDPE_015    PDPE_016    PDPE_017    PDPE_018    PDPE_019    PDPE_020    PDPE_021
SNP_1   1   713 SNP_1_1_713 T/T C/C T/T T/T T/T T/T T/T C/C T/T T/T T/T T/T T/T T/T T/T
SNP_2   1   760 SNP_2_1_760 T/T C/C T/T T/T T/T T/T T/T C/C T/T T/T T/T T/T T/T T/T T/T
SNP_3   1   914 SNP_3_1_914 A/A T/T A/A A/A A/A A/A A/A T/T A/A A/A A/A A/A A/A A/A A/A
SNP_4   1   946 SNP_4_1_946 G/G A/A G/G G/G G/G G/G G/G A/A G/G G/G G/G G/G G/G G/G G/G
SNP_5   1   1091    SNP_5_1_1091    A/A C/C A/A A/A A/A A/A A/A C/C A/A A/A A/A A/A A/A A/A A/A
SNP_6   1   1187    SNP_6_1_1187    T/T A/A T/T T/T T/T T/T T/T A/A T/T T/T T/T T/T T/T T/T T/T
SNP_7   1   1414    SNP_7_1_1414    G/G T/T G/G G/G G/G G/G G/G T/T G/G G/G G/G G/G G/G G/G G/G
SNP_8   1   1461    SNP_8_1_1461    C/C T/T C/C C/C C/C C/C C/C T/T C/C C/C C/C C/C C/C C/C C/C
SNP_9   1   1462    SNP_9_1_1462    A/A T/T A/A A/A A/A A/A A/A T/T A/A A/A A/A A/A A/A A/A A/A

[blacktomato27]

Dear CPAD good Morning

i have some more columns after PDPE_021 and they are not identifying in R and not getting in final output i.e. test1. i am getting error like this Error in is_string(y) : object 'POLYMORPHISM' not found, this is the last and i need it in out for further processing. some times i am not getting error but NA is not converting with REF column alleles. if i run your code on above example data with out extra columns i am not getting any error. Can please let me know where it went wrong? Thanks in advance

[cpad0112]

can you update the dropbox excel sheet with exact columns, column names and dummy data? @ blacktomato27

[blacktomato27]

Dear cpad0112 good morning Thanks lot for your willingness to help me. Please find below link with updated excel sheet.

https://www.dropbox.com/scl/fi/mxxixvw4rq1t88r3tvsho/New-Microsoft-Excel-Worksheet.xlsx?dl=0&rlkey=fj078mygejeh1l6g38onb09el Thanking you very much. With Kind Regards

[cpad0112]

@ blacktomato27 There is no column named "POLYMORPHISM" and I could not find such text in the excel sheet. WIth the data given in excel sheet 1, please try the following code:

test %>%
    mutate(chr="1", LOCUS=paste0("SNP_",row_number()),MAR = paste(LOCUS,chr,POS, sep = "_")) %>%
    mutate_at(vars(c("REF", "ALLELE")), ~case_when(.=="A" ~ "A/A", .=="T" ~ "T/T",.=="C" ~ "C/C",.=="G" ~ "G/G")) %>%
    mutate_at(vars(-c("LOCUS":"ALLELE","chr","MAR","MAF":"TOPsegsite[A/B]")),~str_replace_all(., "NA", REF)) %>%
    select(LOCUS,chr, POS,MAR, REF:"TOPsegsite[A/B]") %>%
    write_xlsx(., "test1.xlsx")

[cpad0112]

Output from test1.xlsx is:

LOCUS   chr POS MAR REF ALLELE  2000    3000    4000    5000    6000    7000    PDPE_015    PDPE_016    PDPE_017    PDPE_018    PDPE_019    PDPE_020    PDPE_021    MAF MAF²    Fvalue  TOPseq(FWDifTOPnotfound)    %GCflankingsequence TOPallA TOPallB TOPsegsite[A/B]
    SNP_1   1   713 SNP_1_1_713 T/T C/C T/T T/T T/T T/T T/T C/C T/T T/T T/T T/T T/T T/T T/T 0.076923076923077   0.005917159763314   0.142011834319526   AAGCACGACATTTCAGTAATTATTATTTTAATTAAAAATTAAAATAAAATTTTTAATTGATTAGGATTAGGATATGAAATGAGTATATTGATAATTATACTTTATCTGATTCTTATTTTTAATGTATGCAAACAATATGAATAAACCAAC[A/G]AAATGAAAGAAATTAATAAAATATCTTTTAACTAGAATCCAACCTAAAGAAAGAGTCCAACTAAAAGTACCTTAATATCTACCGGAACTAAAATTCTAAAAAAGTAAAATTAGAAAATAAAGCAAAGCAAATCTTTAGGGTTTAGGGTTT   0.222591362126246   A   G   [A/G]
    SNP_2   1   760 SNP_2_1_760 T/T C/C T/T T/T T/T T/T T/T C/C T/T T/T T/T T/T T/T T/T T/T 0.076923076923077   0.005917159763314   0.142011834319526   AAGCACGACATTTCAGTAATTATTATTTTAATTAAAAATTAAAATAAAATTTTTAATTGATTAGGATTAGGATATGAAATGAGTATATTGATAATTATACTTTATCTGATTCTTATTTTTAATGTATGCAAACAATATGAATAAACCAAC[A/G]AAATGAAAGAAATTAATAAAATATCTTTTAACTAGAATCCAACCTAAAGAAAGAGTCCAACTAAAAGTACCTTAATATCTACCGGAACTAAAATTCTAAAAAAGTAAAATTAGAAAATAAAGCAAAGCAAATCTTTAGGGTTTAGGGTTT   0.245847176079734   A   G   [A/G]
    SNP_3   1   914 SNP_3_1_914 A/A T/T A/A A/A A/A A/A A/A T/T A/A A/A A/A A/A A/A A/A A/A 0.076923076923077   0.005917159763314   0.142011834319526   AAGCACGACATTTCAGTAATTATTATTTTAATTAAAAATTAAAATAAAATTTTTAATTGATTAGGATTAGGATATGAAATGAGTATATTGATAATTATACTTTATCTGATTCTTATTTTTAATGTATGCAAACAATATGAATAAACCAAC[A/G]AAATGAAAGAAATTAATAAAATATCTTTTAACTAGAATCCAACCTAAAGAAAGAGTCCAACTAAAAGTACCTTAATATCTACCGGAACTAAAATTCTAAAAAAGTAAAATTAGAAAATAAAGCAAAGCAAATCTTTAGGGTTTAGGGTTT   0.325581395348837   A   T   [A/T]
    SNP_4   1   946 SNP_4_1_946 G/G A/A G/G G/G G/G G/G G/G A/A G/G G/G G/G G/G G/G G/G G/G 0.076923076923077   0.005917159763314   0.142011834319526   AAGCACGACATTTCAGTAATTATTATTTTAATTAAAAATTAAAATAAAATTTTTAATTGATTAGGATTAGGATATGAAATGAGTATATTGATAATTATACTTTATCTGATTCTTATTTTTAATGTATGCAAACAATATGAATAAACCAAC[A/G]AAATGAAAGAAATTAATAAAATATCTTTTAACTAGAATCCAACCTAAAGAAAGAGTCCAACTAAAAGTACCTTAATATCTACCGGAACTAAAATTCTAAAAAAGTAAAATTAGAAAATAAAGCAAAGCAAATCTTTAGGGTTTAGGGTTT   0.332225913621262   A   G   [A/G]
    SNP_5   1   1091    SNP_5_1_1091    A/A C/C A/A A/A A/A A/A A/A C/C A/A A/A A/A A/A A/A A/A A/A 0.076923076923077   0.005917159763314   0.142011834319526   AAGCACGACATTTCAGTAATTATTATTTTAATTAAAAATTAAAATAAAATTTTTAATTGATTAGGATTAGGATATGAAATGAGTATATTGATAATTATACTTTATCTGATTCTTATTTTTAATGTATGCAAACAATATGAATAAACCAAC[A/G]AAATGAAAGAAATTAATAAAATATCTTTTAACTAGAATCCAACCTAAAGAAAGAGTCCAACTAAAAGTACCTTAATATCTACCGGAACTAAAATTCTAAAAAAGTAAAATTAGAAAATAAAGCAAAGCAAATCTTTAGGGTTTAGGGTTT   0.365448504983389   A   C   [A/C]
    SNP_6   1   1187    SNP_6_1_1187    T/T A/A T/T T/T T/T T/T T/T A/A T/T T/T T/T T/T T/T T/T T/T 0.076923076923077   0.005917159763314   0.142011834319526   AAGCACGACATTTCAGTAATTATTATTTTAATTAAAAATTAAAATAAAATTTTTAATTGATTAGGATTAGGATATGAAATGAGTATATTGATAATTATACTTTATCTGATTCTTATTTTTAATGTATGCAAACAATATGAATAAACCAAC[A/G]AAATGAAAGAAATTAATAAAATATCTTTTAACTAGAATCCAACCTAAAGAAAGAGTCCAACTAAAAGTACCTTAATATCTACCGGAACTAAAATTCTAAAAAAGTAAAATTAGAAAATAAAGCAAAGCAAATCTTTAGGGTTTAGGGTTT   0.388704318936877   A   T   [A/T]
    SNP_7   1   1414    SNP_7_1_1414    G/G T/T G/G G/G G/G G/G G/G T/T G/G G/G G/G G/G G/G G/G G/G 0.076923076923077   0.005917159763314   0.142011834319526   AAGCACGACATTTCAGTAATTATTATTTTAATTAAAAATTAAAATAAAATTTTTAATTGATTAGGATTAGGATATGAAATGAGTATATTGATAATTATACTTTATCTGATTCTTATTTTTAATGTATGCAAACAATATGAATAAACCAAC[A/G]AAATGAAAGAAATTAATAAAATATCTTTTAACTAGAATCCAACCTAAAGAAAGAGTCCAACTAAAAGTACCTTAATATCTACCGGAACTAAAATTCTAAAAAAGTAAAATTAGAAAATAAAGCAAAGCAAATCTTTAGGGTTTAGGGTTT   0.322259136212625   A   C   [A/C]
    SNP_8   1   1461    SNP_8_1_1461    C/C T/T C/C C/C C/C C/C C/C T/T C/C C/C C/C C/C C/C C/C C/C 0.076923076923077   0.005917159763314   0.142011834319526   AAGCACGACATTTCAGTAATTATTATTTTAATTAAAAATTAAAATAAAATTTTTAATTGATTAGGATTAGGATATGAAATGAGTATATTGATAATTATACTTTATCTGATTCTTATTTTTAATGTATGCAAACAATATGAATAAACCAAC[A/G]AAATGAAAGAAATTAATAAAATATCTTTTAACTAGAATCCAACCTAAAGAAAGAGTCCAACTAAAAGTACCTTAATATCTACCGGAACTAAAATTCTAAAAAAGTAAAATTAGAAAATAAAGCAAAGCAAATCTTTAGGGTTTAGGGTTT   0.255813953488372   A   G   [A/G]
    SNP_9   1   1462    SNP_9_1_1462    A/A T/T A/A A/A A/A A/A A/A T/T A/A A/A A/A A/A A/A A/A A/A 0.076923076923077   0.005917159763314   0.142011834319526   AAGCACGACATTTCAGTAATTATTATTTTAATTAAAAATTAAAATAAAATTTTTAATTGATTAGGATTAGGATATGAAATGAGTATATTGATAATTATACTTTATCTGATTCTTATTTTTAATGTATGCAAACAATATGAATAAACCAAC[A/G]AAATGAAAGAAATTAATAAAATATCTTTTAACTAGAATCCAACCTAAAGAAAGAGTCCAACTAAAAGTACCTTAATATCTACCGGAACTAAAATTCTAAAAAAGTAAAATTAGAAAATAAAGCAAAGCAAATCTTTAGGGTTTAGGGTTT   0.252491694352159   A   T   [A/T]

[blacktomato27]

Dear cpad0112 good afternoon Thanks for your kind and prompt reply to my request. I do not know why this below step is not working on my data but it is working on example data i provided in dropbox mutate_at(vars(-c("LOCUS":"ALLELE","chr","MAR","MAF":"TOPsegsite[A/B]")),~str_replace_all(., "NA", REF)) %>% NA is not replaced by ref allele. do you think my file may have formatting problem i.e. hidden formats? is there any way to remove such format problems? anyway thanks lot for the help you provided to me. With Kind Regards.

[cpad0112]

@ blacktomato27 I cannot say any thing about the file you have. If you don't mind, please join biostars slack channel and share the file with me. My educated guess is presence of white spaces or special characters in the column names.

[Alewa]

How did you use the gsub? the example data in the dropbox doesn't reflect the examples in your question. would you be able to edit your question so we could offer some insight?

[blacktomato27]

Dear cpad0112 Good Morning

Thanks lot for your help and valuable time spent to help me. Your solution is working perfectly as per my expectation. Once again thank you very much With Kind Regards