5 Input and Output

Download and load packages

The most important aspect when working with data is knowing how to import data into the software, and exporting your data out of the software.

if (!require("pacman")) install.packages("pacman")
pacman::p_load(tidyverse, # All purpose wrangling for dataframes
               openxlsx) # writing excel documents

5.1 Dealing with “Cannot Open File” in Windows

You are running R on Windows, and you are using filenames such as C:\data\sample.xlsx. R says it cannot open the file, but you know the file does exist. You see the file with your own eyes.

The backslashes, \, in the filepath are causing trouble. You can solve this problem in one of two ways:

• Change the backslashes, \, to forward, /, slashes: "C:/data/sample.txt".

• Double the backslashes: "C:\\data\\sample.txt".

When you open a file in R, you give the filename as a character string. Problems arise when the name contains backslashes (\) because backslashes have a special meaning inside strings. You’ll probably get something like this:

dat_fms <- read.xlsx ("data\groupFMS.xlsx")

## Error: '\g' is an unrecognized escape in character string (<text>:1:29)

R escapes every character that follows a backslash and then removes the backslashes. The simple solution is to use forward slashes instead of backslashes. R leaves the forward slashes alone, and Windows treats them just like backslashes. Problem solved:

dat_fms <- read.xlsx ("data/groupFMS.xlsx")

An alternative solution is to double the backslashes, since R replaces two consecutive backslashes with a single backslash:

dat_fms <- read.xlsx ("data\\groupFMS.xlsx")

5.2 Reading in Excel “.xlsx” data

If you have a .xlsx file that you want to read into R, use the read.xlsx() function in the openxlsx package.

Argument	Description
xlsxFile	The document’s file path relative to the working directory unless specified otherwise. For example xlsxFile = "SubjectData.xlsx" looks for the text file directly in the working directory, while xlsxFile = "data/SubjectData.xlsx" will look for the file in an existing folder called data inside the working directory. If the file is outside of your working directory, you can also specify a full file path (xlsxFile = "C:/Users/bl19622/Box/myBox/Documents/teaching/se747_ResearchMeth/sample_book/data/SubjectData.xlsx").
sheet	The name of the excel sheet or the numerical index. This is useful if you have many sheets in one Excel workbook. If this is not specified, the function automatically reads in the first sheet in the Excel workbook.

In my projects folder, I tend tend to have a habit of create a folder named data. This contains all my original data, that I do not want to touch!!! Let’s test this function out by reading in an Excel file titled groupFMS.xlsx. Since the file is located in a folder called data in my working directory, I’ll use the file path xlsxFile = "groupFMS.xlsx", and since the sheet I have is named "FMS", I will use sheet = "FMS":

dat_fms <-  read.xlsx (xlsxFile = "data/groupFMS.xlsx",
                    sheet = "FMS")

5.3 Writing a Data Frame to Excel

After you have done some work on the data, you want to write (i.e. export) an R data frame to an Excel file.

Argument	Description
x	The object you are trying to export and write into an Excel file, commonly a dataframe you modified. For example x = fms.
sheetName	If you want to name the sheet of the Excel workbook.
file	Specifying where you want to export the Excel sheet relative to the working directory, and how you want to name the sheet you exported.

write.xlsx(x = dat_fms,
           sheetName = "FMS",
           file = "data/groupFMS_write.xlsx")

5.4 Learning check

You can download the 3 files for this learning check below:

1. In your se201/practice folder, create another folder called data.

2. Place the files downloaded inside the se201/practice/data folder.

3. From your learning check in 4.4, open RStudio via the .Rproj symbol. In the Files tab on the bottom right, you should see the script you created practice_script.R. Click on it to open.

4. Import the Excel file data/groupFMS.xlsx and assign it to an object dat_fms. See Recipe 5.2. Remember to add some comments to remind yourself what this line of code is trying to do. Save it.

5. Import the Excel file data/rightleg_imu.xlsx and assign it to an object bad_leg_accl. See Recipe 5.2. Remember to add some comments to remind yourself what this line of code is trying to do. Save it.

6. Import the Excel file data/leftleg_imu.xlsx and assign it to an object good_leg_accl. See Recipe 5.2. Remember to add some comments to remind yourself what this line of code is trying to do. Save it.

7. Download the solution to this learning check below.