mysql in R - using a text file of IDs to subset a table

Question

I have a list text file of IDs:

JJ-58390234
JJ-45983252
JJ-90590485025825
JJ-0123343
etc... roughly 20,000 long

I have a table called genomics:

ID      gene    sex age height diagnosis pc1 pc2 pc3
JJ-1234 CFTR     M   43  123    ihd      0.1 0.54 0.543
etc.... roughly 100,000 lines long

These are stored using labkey (https://www.labkey.com/) and we use a mysql script to query the tables via R.

I would like to pull out the diagnosis and pc1, pc2 and pc3 for a select number of IDs.

I know how to do this for a specific "thing" in the table e.g. for the diagnosis

lab.setDefaults(baseURL = "#code for labkey")

query <- "SELECT g.diagnosis, g.pc1, pc2, g.pc3
FROM genomic as g
WHERE g.diagnosis = 'ihd'"

mysql <- labkey.executeSql(
schemaName="lists", 
colNameOpt = "rname", 
maxROWS = 100000000,
folderPath="/main-programme_v10",
sql= query)

However, I don't know how to subset the table by the IDs in the text file. I don't think the labkey API allows for the creation of temporary tables which I understand would have made things easier. Is there a method to loop over each ID and run a separate query?

Many thanks

Greg · Accepted Answer · 2020-09-17 16:06:52Z

1

I don't know anything about labkey, but I guess the mysql object is a dataframe or coercible to a dataframe?

If I read your question right, I'd keep your query as is, with the addition of returning the gene ID ("ID gene"?). Then, just a left join/merge (in R) of the id file with the query results.

I find the data.table syntax easiest for simple merges (other methods are available)

Something like the code below might work

library(data.table)
myids <- fread("id.file")
setnames(myids,"ID Gene")
### YOUR CODE HERE ###
setDT(mysql) # may or may not work depending on class of mysql
results <- mysql[myids,on="ID Gene"]

answered Sep 17, 2020 at 16:06

Greg

2091 silver badge4 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

tacrolimus Over a year ago

Thanks for taking the time to answer Greg. The issue is the initial table is up to 3 million lines long. I'll give this approach a go though and see if it is quick enough!

Collectives™ on Stack Overflow

mysql in R - using a text file of IDs to subset a table

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related