Dr. Mark Gardener


Providing training for:

  • Ecology
  • Data analysis
  • Statistics
  • R The statistical programming language
  • Data management
  • Data mining

Tips and Tricks - for R and Excel

On these pages you can find tips, tricks and hints for using both R and Excel. At the end of each tip there are links forwards and backwards as appropriate. There is also an index of R tips and an index of Excel tips.

For most analytical purposes the combination of Excel and R is unbeatable! Excel is great as a data management tool and for preparing data for analysis. You can also use it to get an overview of your data or to make simple (and not so simple) graphs. R is an analytical "swiss army knife" and can carry out a mind-boggling array of analytical routines as well as producing great graphics.

Tips & Tricks for R | Tips & Tricks for Excel | An Introduction to R | MonogRaphs | Writer's Bloc

Use multiple columns as row names


Use multiple columns as row names

Sometimes your data contains more than one column that you want to use as labels. You may want "combination" labels for graphs or perhaps to use as row names.

This is where the paste() command comes in useful, as it allows you to combine items into a new item.

Use paste() command to combine objects into one.

The result can be used for labels or as row names.


Use paste() to combine elements

The paste() command allows you to combine several items and reform them into a single item. There are many uses for this command such as making labels for plots or row names for a data object. Look at these example data for example (you can get/view the datafile here):

 Colour    Coat Obs Ratio
Green Smooth 116 9
Green Wrinkle 40 3
Yellow Smooth 31 3
Yellow Wrinkle 13 1

The data shows some phenotype data. There are four varieties of pea plants with two main characteristics, the colour and the smoothness. The Obs column shows the observed frequency of plants in an experiment. The Ratio column shows the expected ratio of the four phenotypes under standard genetic theory.

It would be helpful to have the Colour and Coat combined into one and used as the row names. You can use the paste() command to achieve this.

The paste() command in its simplest form requires the names of the objects to combine and the separator (a text character).

Start by getting the data from the .txt file:

> peas <- read.table(file.choose(), header = TRUE, sep = "\t")
> peas
Colour Coat Obs Ratio
1 Green Smooth 116 9
2 Green Wrinkle 40 3
3 Yellow Smooth 31 3
4 Yellow Wrinkle 13 1

Then you need to combine the Colour and Coat columns, the separator will be a colon ":".

> rownames(peas) = with(peas, paste(Colour, Coat, sep = ":"))

Now you've got the rownames sorted you can remove the original Coat and Colour columns:

> peas = peas[, -1:-2]
> peas
Obs Ratio
Green:Smooth 116 9
Green:Wrinkle 40 3
Yellow:Smooth 31 3
Yellow:Wrinkle 13 1

Now you have a combination of coat and colour as a single element.

If you want to download the data directly into R try the following:

peas <- read.table("http://www.dataanalytics.org.uk/Publications/S4E2e Support/data/peas.txt", header = TRUE, sep = "\t")

Then you can have a go for yourself.

Top << Previous tip: Read column names as number when importing data  
Follow me...
Facebook Twitter Google+ Linkedin Amazon
Top Tips & Tricks Home Index of R Tips Index of Excel Tips  
More links:

An introduction to R

See my Publications about Excel, R, statistics and data analysis Courses in R, data analysis, data management and statistics Visit the R Project website

See my Publications about statistics and data analysis.

MonogRaphs: random topics in R

Writer's Bloc – my latest writing project includes R scripts

Courses in data analysis, data management and statistics.

Top Home
Data Analysis
Contact GardenersOwn Homepage