Merging data from different files using R

A reader asked yesterday how you would merge data from two different files. For example, let's say you have a ped file with genotypes for individuals, and another file that had some other data for some of the individuals in the pedfile, like clinical or environmental covariates. How would you go about automatically putting the clinical data from file 2 into the appropriate rows in file 1? Without using a database, the easiest way is probably using the "merge" function in R that will do the trick with a one-line command. Here's a short tutorial to get you started.

First, start up R on the cheeses simply by typing in the uppercase letter R and hit enter. Alternatively you could use R if it's installed on your computer. Now type in this command to view a small dataset I made up. It's not a real pedfile, but it will do the trick. Make sure to put it all on one line:

read.table("http://people.vanderbilt.edu/~stephen.turner/ggd/ped.txt")

You should see the dataset displayed:

indiv_id SNP1 SNP2
1 1 1 1
2 2 1 1
3 3 0 0
4 4 1 0
5 5 1 1
6 6 1 0
7 7 1 1
8 8 0 1
9 9 1 1
10 10 1 1

We have 10 individuals numbered 1-10 in a column called "indiv_id" that have genotype information at 2 loci. Now let's view another dataset that has fake clinical covariates for a few of those individuals:

read.table("http://people.vanderbilt.edu/~stephen.turner/ggd/meta.txt")

Again, you will see the dataset displayed.

indiv_id cov1 cov2
1 1 1.14 74.6
2 3 4.50 79.4
3 4 0.80 48.2
4 6 1.39 68.1
5 7 3.89 84.8
6 8 0.42 86.1

In this dataset, we have clinical information on the individuals in the ped file above, but only on some of the individuals. Now, let's merge what information we have here with the ped file above.

But first, since all we did above was view the datasets, let's run those commands again, but this time storing the tables in a "data frame" (R parlance for dataset). Type in these two commands. The " <- " is an assignment. It means create a data frame called ped from the following table from the internet, and the same with a data frame called meta.

ped <- read.table("http://people.vanderbilt.edu/~stephen.turner/ped.txt")

meta <- read.table("http://people.vanderbilt.edu/~stephen.turner/meta.txt")

Now if you just type in the name of the data frame, e.g. ped, and hit enter, you will see the data frame displayed. Now it's time for the real magic. Type the following command, and I'll explain what it does below:

merge(ped,meta,by="indiv_id",all=TRUE)

The output should look like this:

indiv_id SNP1 SNP2 cov1 cov2
1 1 1 1 1.14 74.6
2 2 1 1 NA NA
3 3 0 0 4.50 79.4
4 4 1 0 0.80 48.2
5 5 1 1 NA NA
6 6 1 0 1.39 68.1
7 7 1 1 3.89 84.8
8 8 0 1 0.42 86.1
9 9 1 1 NA NA
10 10 1 1 NA NA

This command uses R's "merge" function to combine the data frames you created above, ped and meta, into a single data frame. It does this by joining the two data frames on a common variable called "indiv_id". The all=TRUE option tells R to join the two data frames, even if one has fewer entries than another. You'll see the NA's listed for a few individuals. NA is R's way of coding missing data. If you didn't include the all=TRUE, observations with any missing data for any variable would be deleted!

Finally, if you want to save this data to a file, re-run the command above, assigning it to a variable, then use the write.table command to write it to your home directory.

superpedfile <- merge(ped,meta,by="indiv_id",all=TRUE)

write.table(superpedfile,file="~/superped.txt")

To exit R, type quit(). To do this on your own data, see the help on read.table by typing ?read.table at the R command line. If anybody knows of a better way to do this post it in the comments!

X-cess of variants in XLMR

From a News and Views article in Nature Genetics, following up the recently mentioned sequencing effort for X-linked mental retardation:

"This study echoes old lessons from mendelian genetics that should be heard by other groups currently engaged in such large-scale sequencing surveys for discovering genes associated with human diseases. It is clear from the current work that a major challenge for this era of resequencing studies is discerning causative variation, as these will be accompanied by many other changes that each look as if they might perturb gene function. In the absence of clear functional criteria, compelling evidence of association is required to make the case that alleles are causative. As it is unlikely that there will be any simple single solution to providing functional data for all the discovered variation, the process of assigning phenotypes to newly discovered genotypes will get harder before it gets easier."

Nature Genetics: X-cess of variants in XLMR

Use plain text when you need to just write

When you need to focus and get some serious writing done, it may be a good idea to ditch your word processor and go with plain text instead. Save all your formatting and spell-checking to do later in one step. I just tried this out on a review article I needed to write and found it much easier to concentrate without all of MS Word's squiggly underlining, autocorrecting, autoformatting, and fancy toolbar buttons begging to be clicked. If you're using windows, Notepad works fine, but try out a tiny free program called Q10. It's a full screen text editor that by default uses easy-on-the-eyes light text on a black background. You can also run it directly from a USB stick. When you're done, just open the .txt file using Word, and from there out save it as .doc. By default it comes enabled with some silly typewriter sound when you type, but it's easy enough to turn that off.

Q10 - Full screen distraction-free text editor

Genes2Networks

I saw a demonstration of this tool at the workshop on network analysis I announced last week. Genes2Networks draws from a large background network consisting of several experimentally verified mammalian protein interaction databases. It will take a list of genes you provide as seed genes and identify all interacting genes that fall on paths through the background network between them. It then calculates a z-score for each intermediate node based on the number of links to the seed nodes and the number of total links it has in the background network, and color-codes the significant links.

It's free, and it took about 30 seconds to paste in and analyze a list of a few dozen genes I submitted. It looks like a great way to identify potential interaction partners for some genes you've already found. You can also export the networks you find to a text file that can be loaded with other software developed by this lab for more extensive statistical network analysis and visualization.

Genes2Networks

Publication in BMC Bioinformatics

Avi Ma'ayan's Lab Website

UPDATE 2009-04-27: I have slides from three one-hour lectures that Avi gave on network analysis following his seminar. Email me if you want them.

Sequencing is Not (Yet) the Silver Bullet

Amidst the fallout of an academic discussion over the worth of GWA studies followed by several gloomy and scathing articles in the popular press, came this paper in Nature Genetics. In summary, the investigators sequenced all the coding DNA on the X-chromosome in families affected with an evidently X-linked mental retardation phenotype. They found nearly 1000 changes that would either alter the amino acid sequence, introduce a frameshift or stop codon, or interfere with splicing, all illustrating the huge heterogeneity problem and the analytical conundrum to face when there are too many potential susceptibility/causal changes. What's more, nearly all of the protein-truncating variants they found were unique to individual families, and many of these were found in both affected and unaffected males! I believe this highlights the fact that an argument pushing for whole-genome sequencing should not omit a discussion of the analytical and interpretation issues we will have to deal with when the time comes.

Nature Genetics: A systematic, large-scale resequencing screen of X-chromosome coding exons in mental retardation

Abstract: Large-scale systematic resequencing has been proposed as the key future strategy for the discovery of rare, disease-causing sequence variants across the spectrum of human complex disease. We have sequenced the coding exons of the X chromosome in 208 families with X-linked mental retardation (XLMR), the largest direct screen for constitutional disease-causing mutations thus far reported. The screen has discovered nine genes implicated in XLMR, including SYP, ZNF711 and CASK reported here, confirming the power of this strategy. The study has, however, also highlighted issues confronting whole-genome sequencing screens, including the observation that loss of function of 1% or more of X-chromosome genes is compatible with apparently normal existence.

GettingGeneticsDone.com

Save yourself a few keystrokes the next time you visit GGD. Going to gettinggeneticsdone.com will now automatically redirect you to the blogspot address. If you've bookmarked the site or subscribed to the site's feed with RSS, everything will still work.

Network analysis in systems biology workshop

Avi Ma'ayan, Ph.D., Assistant Professor in the Department of Pharmacology and Systems Therapeutics of Mount Sinai School of Medicine, will present "Network Analysis in Systems Biology", at 10:00 am on Wednesday, April 22 in Room 206 PRB.

Following the lecture, Dr. Ma’ayan will hold a series of one-hour interactive workshops on "Computational Methods for Analyzing Lists of Genes/Proteins and Building Networks in Systems Biology," which will highlight the software tools and databases he has helped to develop in the Systems Biology Center of New York. These computational tools will focus on modeling cellular pathways and using large-scale data in these models.

The workshop is being offered over 10 different time slots Wednesday and Thursday this week. See this link for more details about the schedule and information about registration.

Five articles on the success/failure of GWAS

Here are four interesting and provocative articles in New England Journal of Medicine:

David Goldstein: Common Genetic Variation and Human Traits

Joel Hirschhorn: Genomewide Association Studies — Illuminating Biologic Pathways

Peter Kraft and David Hunter: Genetic Risk Prediction — Are We There Yet?

John Hardy & Andrew Singleton: Genomewide Association Studies and Human Disease

And after all their hype over the last year or so, a rather pessimistic article in the NYT.

Share your thoughts in the comments!