Quick recap

The data frame

The format in which R holds data is called a data frame. A data frame is a rectangular collection of variables (in the columns) and observations (in the rows) Wickham & Grolemund, 2016. In a dataframe, each column is a variable, and each row is an observation.

So for example, in this data set each row is a recorded crime in Greater Manchester.

Each column is a variable

And this column for example is the month variable. Each observation (response) will have a value for this variable (the month that it was recorded).

Each column is a variable

So that is essentially what a dataframe looks like. All the data analysis and visualisation we will cover in the course will require the data to be in this format.

Now the aim for importing data into R, is to turn our data into a data frame object, in R. So to create objects, remember we use the <- notation to assign a value to an object. So if we think back to pseudo code, to read in our data we need something like this:

name_for_dataframe <- afunction_to_read_in_data(source_from_which_to_read_data)

For the rest of this tutorial we will go thourgh importing data from various sources, into data frame objects.

Importing data from .csv files

CSV stands for comma separated value, and it refers to a data set where columns are separated by commas, and rows by line breaks. You might have come across this data format before. It is very simple, and the method for reading these csv files into R are very straightforward, because these formats tend to be flat, and look like a dataframe would.

So for example, a .csv file would look something like this if opened in a text editor:

name, gender, height
bob, male, 178
bobbie, male, 154
bobette, female, 164

and when read into a dataframe, it will look like this:

##      name gender height
## 1     bob   male    178
## 2  bobbie   male    154
## 3 bobette female    164

You might recognise this as the dataframe we created manually. Perhaps for something with only 3 observations, this is a sustainable mode of creation. However you are most likely to be working with a data that contains lot more rows than that!

So if you have a .csv file, you can use the read.csv() file to import it into your working directory. There are a variety of arguments you can pass to this function, and to see a full list you can use the help function (by typing question mark followed by the function name, so in this case ?read.csv()). But usually you can leave many to the default, and specify only the file path where it should read from, and whether your file has headers or not.

To get the file path, there are a variety of tricks you can do. The simplest thing is just to save your file into your working directory. In this case, you only have to specify the file name, and R will know to look in your working directory. So if I have a file in my working directory called my_csv.csv, then I can make the path the file name, like so:

my_csv_path <- "my_csv.csv"

However this is not always the case, and sometimes you will want to read in files that you have saved in another folder, not in your working directory. In this case you have to specify the full file path.

If you know your file path that is fantastic, and you can just type that and assign it to the my_path variable as we did with just the file name above. So, for example, if I know that my file is in the data folder in the course-incubator folder in the Desktop folder in the Reka folder in the Users folder, then I can just type this out like so (remembering that this is a Mac laptop):

my_csv_path <- "/Users/reka/Desktop/course-incubator/data/my_csv.csv"

However, if you are like me, and nest everything in so many folders (see above…!) and do not remember where things are, there is a handy function that lets you just navigate to the file using good old pop-up window. This handy function in R is called file.choose(). This funtion opens up a popup window, which you can use to navigate to your file, and when you double click it, the value of the file path gets recorded:

my_csv_path <-  file.choose()

You can see that the value of this has now become the same as the one I entered manually:

my_csv_path

## [1] "/Users/reka/Desktop/course-incubator/data/my_csv.csv"

Now, however you got it, you have this variable called my_path which holds the path to your csv file. You can now use the read.csv() function to load your file, while giving it the parameter of the path, within the brackets. Remember that for R to remember this, you need to assign it to a variable. What I mean is, you need some name to call your dataframe by! So you can, for example, be very creative and read the csv file into a dataframe called my_dataframe, like so:

my_dataframe <- read.csv(file=my_csv_path)

Now you should see this appear in your global environment:

And you can also check by printing out the first 6 lines of the dataframe using the head() function:

head(my_dataframe)

(of course my sample data here only has 3 rows, so we get all of them)

Now you have this data set all ready to work with! That was easy!

One note I would want to make is that here we did not specify that we have headers in the data. The default for read.csv() is to assume that this is true. However for consistency I would recommend that you specify this each time, by setting the parameter header to equal TRUE:

my_dataframe <- read.csv(file=my_csv_path, header = TRUE)

my_tab_path <- "/Users/reka/Desktop/course-incubator/data/uscrime.txt"

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

head(my_dataframe_2)

my_url_tab_path <- "http://www.statsci.org/data/general/uscrime.txt"

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

identical(my_dataframe_2, my_dataframe_3)

my_dataframe_4 <- read.table(file=my_csv_path, sep = ",", header = TRUE)

install.packages(foreign)

library(foreign)

my_spss_path = "/Users/reka/Desktop/course-incubator/data/csew1112.sav"

my_dataframe_5 <- read.spss(file = my_spss_path, to.data.frame = TRUE )

Excel

The last common file type we’ll cover here are files that come from Excel. No doubt you will work with data that has come form Excel. The common file extension for this is .xlsx. There are a few ways of reading in these data. For one, you could convert the workbook you need to a .csv file, by selecting “Save as…” and choosing comma separated value (csv) as one of the options. Then you can use the instructions for .csv to read in your data (with the read.csv() function).

You could also resort to good old copy and paste. You could select all the content which you want to import, and then just copy. This will attach everything you selected to the clipboard. To read in data from your clipboard you can use the following command: (I think this only works on Windows machines though…!)

my_dataframe_6 <- read.table("clipboard")

But of course this isn’t very good solution, instead, let’s read in some data from excel correctly. For this we can use the readxl package. Once again, install if you don’t already have it, and then just load it up:

library(readxl)

And now we can use the function read_excel() from this package. As with previous functions, you have to specify the file path.

my_xlsx_path <- "/Users/reka/Desktop/course-incubator/data/myXlsx.xlsx"

But there’s another important parameter, which is to specify which sheet to read the data from. Remember that a dataframe is 2 dimensional, it has rows and columns. So each sheet in an excel workbook will have to be imported separately, into an individual dataframe. So to import “Sheet1” from this excel workbook you would need to specify this like so:

my_data_frame_7 <- read_excel(my_xlsx_path, sheet = "Sheet1")

And this reads in Sheet 1 of this excel file!

head(my_data_frame_7)

Now we’ve discussed reading data in from most traditional static file sources. We’ve even used read.tables() to get data directly from a website. But there are also ways to retreive data from active data sources. We will cover APIs and Web Scraping in the next section, for example. However there are many other possibilities. I’ll quickly touch here on reading data from database connections.

Getting data from database connections

It’s possible that your data will be stored in databases. We do not really cover how to set up a connection with databases in R, but I’ll briefly touch on this. For example, in a previous role I set up my R session to connect with an SQL Server. To do this I used the RODBC package. Firstly, you will also have to install the appropriate drivers for the appropriate databases. There should be lots of tutorials for doing this online. Then you can use the RODBC package to connect to the database, and retreive data.

I will just show the example below, which was used to connect to an SQL database:

#load the package
library(RODBC) 

#enter username and password
t_User <- "my_username"
t_password <- "my_password"

#use the odbcConnect() function to open a connection object, which here we name 'ch'
ch <- odbcConnect("name_of_database", uid=t_User, pwd=t_password, believeNRows=FALSE)

#then use the sqlQuery function to pass an SQL query to the database to retreive data into a dataframe
my_dataframe_8 <- sqlQuery(ch, " SELECT ROW_1, ROW_2, ROW_3 FROM TABLE_1 WHERE ROW_4='some_value';")

#finally close the connection object
close(ch)

Anyway I just put this in here in case this is something that you will need to set up to retreive data form a database to do your analysis on. It’s not a comprehensive guide, but is meant to illustrate that this is also an option.

Resources

• Foreign package
• Leetaru et al., 2013.
• Kamath et al., 2013.
• R Core Team, “R Data Import/Export.” (2000).
• Wickham, Hadley, and Garrett Grolemund. “R for data science.” (2016).