1. Data input
2. Data output
3. Data summary

Importing data into R is fairly simple. We can use built-in functions or libraries to read data from the following sources:

• Text file (.txt)
• Comma-separated values (.csv)
• Excel (.xlsx or .xls)
• Database table

Regardless the sources, there are common characteristics about how data are formatted

• There are usually multiple columns
• There is a clear way to separate columns
  • comma (,), semicolon (;), vertical bar (|), tab (\t), etc.
• The first line often contains the names of the columns

• An RStudio Project points to a directory for a project and uses that directory as your default working directory

• If you are in a Project, you do not need to run setwd(...) whenever you restart your RStudio

Step 1: Create a folder BI-Lab on your computer. Remember where it is. For example, if you are using a Windows PC, you can put the folder under C:\User\BI-Lab (or any other place in your folder for this semester).

Step 2: Click the Project icon on the top right corner of RStudio and choose New Project

Step 3: Choose to use an existing directory

Step 4: Click Browse to navigate to the directory you just created in Step 1 and then click the Create Project button

Step 5: Done! You can now put all the data files into that project directory and they are accessible via “relative path” (more on this later).

Your working directory is the folder on your computer in which you are currently working.

# Show your current working directory
getwd() 

# List the files and folders in the current working directory
list.files()

# Set your working directory; make sure the directory exist!!
setwd("/Users/sophiazhang/Library/CloudStorage/Box-Box/Teaching/BI_Lab") #NOTE: / (forward slash) instead of \ (backward slash) 

• On Windows PC:
  • https://www.wikihow.com/Find-a-File%27s-Path-on-Windows
  • Option 1: From File Explorer
  • Option 2: Right click -> File Properties -> Location
• On Mac:
  • Option 1: http://osxdaily.com/2015/11/05/copy-file-path-name-text-mac-os-x-finder/
  • Option 2: http://osxdaily.com/2013/06/19/copy-file-folder-path-mac-os-x/

1. Following the steps to create a project in RStudio

2. Download all data files on slide #5 and move them into the project folder

3. Run list.files() in the R console. Do you see these files?

Important: From this point forward and throughout the rest of the semester, I will assume that you are in the BI-Lab project when you are developing R code in RStudio.

You need to specify the path to the text file.

Read file through an absolute path.

• Analogy: Suppose someone asks you how to get to the instructor’s office. You can say:

    The Foster Building for Business and Innovation, Room 310.36
    1621 S 3rd St
    Waco, TX 76706

df<-read.table(file="C:/Users/zhang/Box/Teaching/Sample Dataset/CSV/HousePrices.csv", sep=",", header=TRUE, stringsAsFactors=FALSE)

class(df) # R will convert the file to a data frame

## [1] "data.frame"

What the data looks like?

head() is a function allows you to see the top few rows of the data frame

head(df, n=5) # n indicates how many rows you'd like to see

##   X  price lot_size waterfront age land_value construction air_cond     fuel
## 1 1 132500     0.09         No  42      50000           No       No Electric
## 2 2 181115     0.92         No   0      22300           No       No      Gas
## 3 3 109000     0.19         No 133       7300           No       No      Gas
## 4 4 155000     0.41         No  13      18700           No       No      Gas
## 5 5  86060     0.11         No   0      15000          Yes      Yes      Gas
##        heat   sewer living_area fireplaces bathrooms rooms
## 1  Electric Private         906          1       1.0     5
## 2 Hot Water Private        1953          0       2.5     6
## 3 Hot Water  Public        1944          1       1.0     8
## 4   Hot Air Private        1944          1       1.5     5
## 5   Hot Air  Public         840          0       1.0     3

Read file through a relative path (relative to the working directory):

• Continue with our previous analogy: Suppose you are inside the Foster Business School building and someone asks you how to get to the instructor’s office, you can just say:

    Room 310.36

df <- read.table(file="HousePrices.csv", sep=",", 
                 header=TRUE, stringsAsFactors=FALSE)

• Important: If you are not in a RStudio Project, always make sure that you are using the right working directory (getwd() and setwd()) before you try to access a file.

Because csv files are very common, there is another function in R that makes it easier to read csv files:

df3 <- read.csv(file="HousePrices.csv", header=TRUE, 
                stringsAsFactors=FALSE)
head(df, n=4)

##   X  price lot_size waterfront age land_value construction air_cond     fuel
## 1 1 132500     0.09         No  42      50000           No       No Electric
## 2 2 181115     0.92         No   0      22300           No       No      Gas
## 3 3 109000     0.19         No 133       7300           No       No      Gas
## 4 4 155000     0.41         No  13      18700           No       No      Gas
##        heat   sewer living_area fireplaces bathrooms rooms
## 1  Electric Private         906          1       1.0     5
## 2 Hot Water Private        1953          0       2.5     6
## 3 Hot Water  Public        1944          1       1.0     8
## 4   Hot Air Private        1944          1       1.5     5

The key is to specify the correct column separator character. When there is missing values in your data, you need to tell R how to recognize these missing values.

df_semicolon <- read.table(file="HousePrices_semicolon.txt", sep=";", 
                           header=TRUE, stringsAsFactors=FALSE)

df_tab <- read.table(file="HousePrices_tab.txt", sep="\t", 
                     header=TRUE, stringsAsFactors=FALSE)

df_csv_na <- read.table(file="HousePrices_with_missing_values.csv", sep=",", 
                    header=TRUE, stringsAsFactors=FALSE, 
                    na.strings = c("","NA", "Not Available"))

sep

• specify the character/string that seperates columns

stringsAsFactors

• logical: should character vectors be converted to factors? Usually, set to FALSE.

na.strings

• a vector of strings which are to be interpreted as NA values.

Try if you can read HousePrices_semicolon.txt, HousePrices_tab.txt, HousePrices_with_missing_values.csv into R, and save them to different data frames df_semicolon, df_tab, and df_csv_na.

You can then verify if you have these data in R by typing:

head(df_semicolon)
head(df_tab)
head(df_csv_na)

url <- "https://stats.idre.ucla.edu/wp-content/uploads/2016/02/test-1.csv"
df <- read.csv(file=url, header=TRUE, 
               stringsAsFactors=FALSE)
head(df)

##    make   model mpg weight price
## 1   amc concord  22   2930  4099
## 2   amc   oacer  17   3350  4749
## 3   amc  spirit  22   2640  3799
## 4 buick century  20   3250  4816
## 5 buick electra  15   4080  7827

To read Excel data, we need to use a library: readxl

First, you need to install the package on your computer.

install.packages("readxl") # R will then go online and donwload the libray

library("readxl") # You need to load the library into R before you can use it.
df <- read_excel("HousePrices.xlsx", col_names = TRUE)
head(df, n=3)

## # A tibble: 3 × 15
##    ...1  price lot_size waterfront   age land_value construction air_cond fuel  
##   <dbl>  <dbl>    <dbl> <chr>      <dbl>      <dbl> <chr>        <chr>    <chr> 
## 1     1 132500     0.09 No            42      50000 No           No       Elect…
## 2     2 181115     0.92 No             0      22300 No           No       Gas   
## 3     3 109000     0.19 No           133       7300 No           No       Gas   
## # ℹ 6 more variables: heat <chr>, sewer <chr>, living_area <dbl>,
## #   fireplaces <dbl>, bathrooms <dbl>, rooms <dbl>

• The RODBC package provides access to Microsoft Access and Microsoft SQL Server

• The RMySQL package provides an interface to MySQL.

• The ROracle package provides an interface for Oracle.

You just need to install these libraries and follow their reference manuals.

install.packages("RODBC")
install.packages("RMySQL") 
install.packages("ROracle") 

file_path <- "my_test.csv"
write.csv(df, file=file_path, row.names = FALSE) 
# row.names are usually not very useful. So I like to exclude them.

file_path <- "my_test2.csv"
write.table(df, file_path, sep=",", row.names = FALSE)

file_path <- "my_test_semicolon.txt"
write.table(df, file_path, sep=";", row.names = FALSE)

file_path <- "my_test_tab.txt"
write.table(df, file_path, sep="\t", row.names = FALSE)

str(df)

## tibble [1,728 × 15] (S3: tbl_df/tbl/data.frame)
##  $ ...1        : num [1:1728] 1 2 3 4 5 6 7 8 9 10 ...
##  $ price       : num [1:1728] 132500 181115 109000 155000 86060 ...
##  $ lot_size    : num [1:1728] 0.09 0.92 0.19 0.41 0.11 0.68 0.4 1.21 0.83 1.94 ...
##  $ waterfront  : chr [1:1728] "No" "No" "No" "No" ...
##  $ age         : num [1:1728] 42 0 133 13 0 31 33 23 36 4 ...
##  $ land_value  : num [1:1728] 50000 22300 7300 18700 15000 14000 23300 14600 22200 21200 ...
##  $ construction: chr [1:1728] "No" "No" "No" "No" ...
##  $ air_cond    : chr [1:1728] "No" "No" "No" "No" ...
##  $ fuel        : chr [1:1728] "Electric" "Gas" "Gas" "Gas" ...
##  $ heat        : chr [1:1728] "Electric" "Hot Water" "Hot Water" "Hot Air" ...
##  $ sewer       : chr [1:1728] "Private" "Private" "Public" "Private" ...
##  $ living_area : num [1:1728] 906 1953 1944 1944 840 ...
##  $ fireplaces  : num [1:1728] 1 0 1 1 0 1 1 1 0 0 ...
##  $ bathrooms   : num [1:1728] 1 2.5 1 1.5 1 1 1.5 1.5 1.5 1.5 ...
##  $ rooms       : num [1:1728] 5 6 8 5 3 8 8 9 8 6 ...

head(df, n=2)

## # A tibble: 2 × 15
##    ...1  price lot_size waterfront   age land_value construction air_cond fuel  
##   <dbl>  <dbl>    <dbl> <chr>      <dbl>      <dbl> <chr>        <chr>    <chr> 
## 1     1 132500     0.09 No            42      50000 No           No       Elect…
## 2     2 181115     0.92 No             0      22300 No           No       Gas   
## # ℹ 6 more variables: heat <chr>, sewer <chr>, living_area <dbl>,
## #   fireplaces <dbl>, bathrooms <dbl>, rooms <dbl>

df[1:2,] # df[ rows_you_want, columns_you_want ]

## # A tibble: 2 × 15
##    ...1  price lot_size waterfront   age land_value construction air_cond fuel  
##   <dbl>  <dbl>    <dbl> <chr>      <dbl>      <dbl> <chr>        <chr>    <chr> 
## 1     1 132500     0.09 No            42      50000 No           No       Elect…
## 2     2 181115     0.92 No             0      22300 No           No       Gas   
## # ℹ 6 more variables: heat <chr>, sewer <chr>, living_area <dbl>,
## #   fireplaces <dbl>, bathrooms <dbl>, rooms <dbl>

tail(df, n=2)

## # A tibble: 2 × 15
##    ...1  price lot_size waterfront   age land_value construction air_cond fuel 
##   <dbl>  <dbl>    <dbl> <chr>      <dbl>      <dbl> <chr>        <chr>    <chr>
## 1  1727 125000     0.24 No            48      16800 No           No       Gas  
## 2  1728 111300     0.59 No            86      26000 No           No       Gas  
## # ℹ 6 more variables: heat <chr>, sewer <chr>, living_area <dbl>,
## #   fireplaces <dbl>, bathrooms <dbl>, rooms <dbl>