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.

Read column names as numbers when importing a data file.

Top

### Read column names as numbers when importing a data file

When you read in a data file from CSV or some other text format, R will examine the column headings and ensure that they are in correct "R-format". If your headings are all numbers, for example years, then R will convert them to character strings and prepend "X" to each heading.

You can overcome this behaviour and force R to read the headings as they come. Your headings are still converted to character strings but these are easier to coerce to a numeric value.

Use check.names = FALSE as a parameter in read.table() to read column names "as is".

Top

#### Use check.names = FALSE

Look at these data (you can view/get the datafile here):

`Spp     1996 1997 1998 1999 2000M.bro     88   47   13   33   86Or.tip    90   14   36   24   47Paint.l   50    0    0    0    4Pea       48  110   85   54   65Red.ad     6    3    8   10   15Ring     190   80   96  179  145`

The first column contains species names and the rest are numbers. The column names are mostly years (the sampling year). If you read these data into R the column names will be prepended with "X", which is not helpful.

If you add check.names = FALSE as a parameter to read.xxxx() you'll force R to accept the names of the columns as they are.

```> bfy <- read.table(file.choose(), header = TRUE, sep = "\t", check.names = FALSE, row.names = 1)
> bfy        1996 1997 1998 1999 2000M.bro     88   47   13   33   86Or.tip    90   14   36   24   47Paint.l   50    0    0    0    4Pea       48  110   85   54   65Red.ad     6    3    8   10   15Ring     190   80   96  179  145
> as.numeric(colnames(bfy))[1] 1996 1997 1998 1999 2000```

The data object will contain colnames that are character but it is easy to coerce them to numeric with the as.numeric() command.

If you want to get these data direct from the website then use the following command:

bfy <- read.table("http://www.dataanalytics.org.uk/Publications/S4E2e Support/data/butterfly table.txt", header = TRUE, sep = "\t", check.names = FALSE, row.names = 1)

Of course this is a bit of a cheat... the column names are not real numbers. You cannot make the column names "properly" numeric but in this (character) form you can easily coerce them to be numeric when you need with the as.numeric() command.

Top << Previous tip: Use locator() to place labels interactively >> Next tip: Use multiple columns as row names

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.

Top Home
Data Analysis
Contact GardenersOwn Homepage