Dr. Mark Gardener

Home

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.

Use multiple columns as row names

Top

### 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.

Top

#### 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 Ratio1  Green  Smooth 116     92  Green Wrinkle  40     33 Yellow  Smooth  31     34 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 RatioGreen:Smooth   116     9Green:Wrinkle   40     3Yellow:Smooth   31     3Yellow: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:

Then you can have a go for yourself.

Top << Previous tip: Read column names as number when importing data

Top Tips & Tricks Home Index of R Tips Index of Excel Tips

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.

My Publications about statistics and data analysis

See my personal pages at GardenersOwn

Top Home
Data Analysis
Contact GardenersOwn Homepage