Dr. Mark Gardener

Home
About

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


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 2000
M.bro 88 47 13 33 86
Or.tip 90 14 36 24 47
Paint.l 50 0 0 0 4
Pea 48 110 85 54 65
Red.ad 6 3 8 10 15
Ring 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 2000
M.bro 88 47 13 33 86
Or.tip 90 14 36 24 47
Paint.l 50 0 0 0 4
Pea 48 110 85 54 65
Red.ad 6 3 8 10 15
Ring 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
 
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