Import Data using read.table in R

We learnt that we can use read.csv() function to import data from files in comma separated values (CSV) format. A data table can reside in a text file where the cells inside the table are separated by blank characters. If your data uses another character to separate the fields, not a comma, R also has the more general read.table() function.

So if your separator is a tab, for instance, this would work:

mydata <- read.table("filename.txt", sep="\t", header=TRUE)

The command above also indicates there’s a header row in the file with header=TRUE.

If, say, your separator is a character such as | you would change the separator part of the command to sep=”|”.

Categories or values?

Because of R’s roots as a statistical tool, when you import non-numerical data, R may assume that character strings are statistical factors — things like “poor,” “average” and “good” — or “success” and “failure.”

But your text columns may not be categories that you want to group and measure, just names of companies or employees. If you don’t want your text data to be read in as factors, add stringsAsFactor=FALSE to read.table, like this:

mydata <- read.table("filename.txt", sep="\t", header=TRUE, stringsAsFactor=FALSE)

Example

For this example, we will take a text file which contains tab delimited historical price data for Goldman Sachs stock.

We can now import this data into r using the data.table() function.

> setwd("C:/r-programming/data/importing data")
> getwd()
[1] "C:/r-programming/data/importing data"
> dir()
[1] "GS-Stock-Prices.txt" "top-100-stocks.csv" 
> gs_data <- read.table("GS-Stock-Prices.txt", sep="\t", header=TRUE, stringsAsFactor=FALSE);
> gs_data
         Time   Open   High    Low   Last  Volume
1   1/24/2017 231.86 236.06 230.84 233.68 4448100
2   1/23/2017 231.86 233.75 230.75 232.67 3136100
3   1/20/2017 231.62 233.23 230.54 232.20 5211800
4   1/19/2017 234.07 234.75 230.62 231.41 4561800
5   1/18/2017 236.00 237.69 231.52 234.29 7590400
6   1/17/2017 242.94 243.06 235.61 235.74 6277100
7   1/13/2017 245.43 247.77 242.91 244.30 4186000
8   1/12/2017 245.06 245.47 241.57 243.84 4022300
9   1/11/2017 242.77 245.84 242.00 245.76 3532500
10  1/10/2017 240.87 243.44 239.05 242.57 3432900
11   1/9/2017 243.25 244.69 241.47 242.89 3022700
12   1/6/2017 242.29 246.20 241.37 244.90 3591000
13   1/5/2017 242.72 243.23 236.78 241.32 3562600
14   1/4/2017 241.44 243.32 240.03 243.13 2728700
15   1/3/2017 242.70 244.97 237.97 241.57 4384200
16 12/30/2016 238.51 240.50 237.40 239.45 2355500
17 12/29/2016 240.75 241.07 236.64 238.18 2619000
18 12/28/2016 243.69 244.50 240.44 240.65 3052900
19 12/27/2016 241.95 242.59 240.40 241.56 1998100
> summary(gs_data)
     Time                Open            High            Low       
 Length:19          Min.   :231.6   Min.   :233.2   Min.   :230.5  
 Class :character   1st Qu.:237.3   1st Qu.:239.1   1st Qu.:233.6  
 Mode  :character   Median :241.9   Median :243.2   Median :238.0  
                    Mean   :240.0   Mean   :241.7   Mean   :237.3  
                    3rd Qu.:242.9   3rd Qu.:244.8   3rd Qu.:240.9  
                    Max.   :245.4   Max.   :247.8   Max.   :242.9  
      Last           Volume       
 Min.   :231.4   Min.   :1998100  
 1st Qu.:235.0   1st Qu.:3037800  
 Median :241.3   Median :3562600  
 Mean   :239.5   Mean   :3879668  
 3rd Qu.:243.0   3rd Qu.:4416150  
 Max.   :245.8   Max.   :7590400  
>

Point and Click Data Import

If you’d prefer, R allows you to use a series of menu clicks to load data instead of ‘reading’ data from the command line as just described. To do this, go to the Workspace tab of RStudio’s upper-right window, find the menu option to “Import Dataset,” then choose a local text file or URL.

As data are imported via menu clicks, the R command that RStudio generated from your menu clicks will appear in your console. You may want to save that data-reading command into a script file if you’re using this for significant analysis work, so that others — or you — can reproduce that work.

Copying Data Snippets

If you’ve got just a small section of data already in a table, say a spreadsheet, or a Web HTML table, you can control-C copy those data to your Windows clipboard and import them into R.

The command below handles clipboard data with a header row that’s separated by tabs, and stores the data in a data frame (x):

x <- read.table(file = "clipboard", sep="\t", header=TRUE)

On a Mac, the pipe (“pbpaste”) function will access data you’ve copied with command-c, so this will do the equivalent of the previous Windows command:

x <- read.table(pipe("pbpaste"), sep="\t")