# Reading/writing data from/to files   

---

One of the first things you have to do in any data analysis project is to read data from a file.  Data can come in myraid forms, but the most common and convenient form is the spreadsheet. In this note we will learn to read and write data in spreadsheet format.  We will also assume that your data is in a text file.  If it is not in a plain text format, find a way to convert it into that form.


## Absolute and relative path

In general, a path of a file or a directory defines a unique location in a file system. The path of a data file is usually the principal argument passed to a reading or writing function. A wrong path will prevent you from importing your data, or it will export your data in a different location. Either the absolute or relative path can specify the location of a data file.

The absolute path indicates a location in a file system, regardless of the current working directory. It has to contain the root directory.
By contrast, the relative path includes the full path of a given working directory implicitly. Thus, it not necessary to provide the full absolute path starting with the root directory. A relative path only contains a portion of the entire path; therefore, it requires less typing and facilitates sharing documents with another user.

Example of absolute path versus relative path, to read a data file named `mri.csv`:

| Operating system | Working directory                      | Absolute path                                            | Relative path          |
|:-----------------|:---------------------------------------|:---------------------------------------------------------|:-----------------------|
| Windows          | `C:\Users\userName\R\Projects\Neuro\`  | `C:\Users\userName\R\Projects\Neuro\Data\mri.csv`  | `Data\mri.csv`   |
| Mac              |  `/Users/username/R/Projects/Neuro/`   | `/Users/userName/R/Projects/Neuro/Data/mri.csv`    | `Data/mri.csv`   |
| Unix/Linux       |  `/home/username/R/Projects/Neuro/`    | `/home/userName/R/Projects/Neuro/Data/mri.csv`     | `Data/mri.csv`   |     

- Note that the relative path looks the same across operating systems (and across different computers/accounts using the same operating system), but the absolute path can look quite different.
- Windows and Mac is case-insensitive, i.e. the `uthsc.csv` and `UTHSC.csv` refer to the same file, but in Unix/Linux, they are considered different.  Please keep this in mind when you name your files.
- In Windows, directories (folders) are indicated with a backslash (`\`), while in Mac/Linux, they are indicated by a forwardslash (`/`).  To make your scripts fully portable in R, use `/` to indicate directories, as R will interpret it accordingly in across operating systems.


### Finding and setting the working directory

To obtain the absolute path of the current working directory, we can use the function `getwd()`. To change the working directory, we use the function `setwd(dir)` where `dir` is the path of the new working directory written as a string. 

In the case the data file is localized in a website, R can also use an URL link as the path for reading a data file.  

```{r}
getwd()
```

```{r}
setwd("/Users/sen/GIT/bioe805/assets/hw")
```

```{r}
getwd()
```

```{r}
setwd("../lectures")
```

```{r}
getwd()
```

### Relative paths make your scripts portable

Consider the following setup for two users in two different systems.  Notice that the file structure from `BIOE805` downwards is the same (this would be the case if we have unzipped the same ZIP archive in two different computers).

    Computer -- /Users/sen -- Desktop
                           -- Documents
                           -- Downloads
                           -- BIOE805 -- hw1
                                      -- hw2
                                      -- hw3 -- data.csv
      C: -- \Users\gregory -- Desktop
                           -- Downloads
                           -- Music
                           -- Documents -- BIOE805 -- hw1
                                                   -- hw2
                                                   -- hw3 -- data.csv                                

If the working directory is `hw3`, both of us can read using the same command `dt <- read.csv("data.csv")`.


### Going up one level

What if the working directory was `hw2`?  In this case, we can use the special character "`..`" to go up one level in the hierarchy.  So we can use: `dt <- read.csv("../hw3/data.csv")`.


### The home directory

The special character `~` denotes the home directory, i.e. the top level for an individual user in the system.  So the user `sen` above could use `dt <- read.csv("~/BIOE805/hw3/data.csv")` regardless of what their working directory is.  The user `gregory` would use `dt <- read.csv("~/Documents/BIOE805/hw3/data.csv")`.  This is an example of using an absolute path, and is helpful when you don't want to change the working directory and know exactly where a file is located. 

```{r}
agren <- read.csv("~/Downloads/2020-08-27/smalldata/arabidopsis/agren2013.csv")
```

```{r}
head(agren)
```

```{r}
getwd()
```

```{r}
setwd("~/Downloads/2020-08-27/smalldata/")
getwd()
```

```{r}
agren <- read.csv("arabidopsis/agren2013.csv")
head(agren)
```

<!-- #region -->
## Delimited text file formats

Plain text files contain a pure sequence of character codes that can be read by users.  They represent only characters of readable material, and they are free of any graphical representation, style and structural information (e.g., paragraphs and font format specification). Their principal advantages are their simplicity, stability, and their portability to multiple operating systems.

The use of plain text files rather than any other format files (e.g., binary files, propreitary formatted files) make them more distributable mainly due to their computer architecture compatibilities. Any programming language can read a plain text file.  Plain text files are also temporally stable. You will be able to read a plain text file fifty years from now, but you may not have software to read a WordPerfect file from twenty years ago.

Rectangular data written in text file requires that columns are separated by a delimiter (a specific character) and rows are separated by line breaks.
   
For example, consider these two datasets.

### Arabidopsis average number of seeds

```
it09,it10,it11,sw09,sw10,sw11,id,flc
19.761589,24.176136,15.677778,5.664430,21.48045,4.477778,1,1
6.288462,1.774011,3.238889,9.543046,22.12222,7.766667,2,2
```
This is an example of a traditional CSV (comma separated value) file.  The columns are separated by commas, and rows by line breaks.  The first row contains character data (variable names).

### Pancake recipe
     
```
# pancake recipe

ingredient;units;amount
whole wheat flour;g;60.000
brown rice flour;g;60.000
salt;t;0.125
```
This is also a delimited text file, with a few twists.
- The first line is a comment, indicated by `#`.  The comments can be used in data files for including metadata such as a data dictionary.
- The second line is a blank, without a comment.  It clearly needs to be skipped.
- The fields are delimited by a semicolon.

### Functions to read files

R programming provides a set of reading function to turn text file into dataframe according to a delimiter (or field separator).  


| Functions     |                   Delimiter |
|---------------|----------------------------:|
| `read.csv()`  |                       comma |
| `read.csv2()` |                   semicolon |
| `read.fwf()`  |       arbitrary fixed width |
| `read.delim()`|                         tab |
| `read.table()`|      customizable delimiter |

The `read.table` function is the main function, the other being aliases with particular defaults for the different options.  See the help menu for details.

<!-- #endregion -->

## Reading a CSV file

One of the best formats for storing and sharing spreadsheet data is the CSV (comma-separated values) format, which is a plain text format. In CSV files, columns are separated by commas and rows are separated by line breaks. You can easily share across file systems and statistical analysis programs.  You can also look at it with your eyes in the raw form (not usually recommended, but useful to find stubborn issues with the data).

In a CSV file data fields are separated by commas.  Traditionally, the column names are put in quotes, but this is not always necessary.  If you have text fields, it is recommended that you quote them, otherwise any commas in your text fields will be interpreted as a delimiter.

If you have a file in some other format such as in Excel, you can use the "save as" option to save it in CSV format.

Let's read the Agren data again.

```{r}
# filename in string
agrenURL <- "https://raw.githubusercontent.com/sens/smalldata/master/arabidopsis/agren2013.csv"
agren <- read.csv(agrenURL)
# print top of data frame
head(agren,4)
```

The columns of the data frame are in an odd order, though.

```{r}
names(agren)
```

```{r}
names(agren) <- c("italy2009","italy2010","italy2011","sw09","sw10","sw11","id","FLC")
head(agren)
```

```{r}
agren[1,1:2]
```

We might want the `id` column to go first.  To do that we reorder the columns as follows.

```{r}
# reorder columns in data frame
agren <- agren[,c(7,1:6,8)]
# print column names
names(agren)
head(agren)
```

We now print the first 10 rows (and round off to two decimals).

```{r}
# print first 10 rows
round(agren[1:10,],2)
```

We can do the same using a pipe syntax using the `tidyr` package.  Some folks might find this to be easier to understand that the previous syntax.

```{r}
# load the tidyr package (called "library")
library(tidyr)
# take agren data frame, send it to head function, send it to round function
agren %>% head(10) %>% round(2)
```

In summary:

| Piping syntax                      |   Standard syntax               |
|:-----------------------------------|:--------------------------------|
| `agren %>% head(10) %>% round(2)`  |     `round(head(agren,10),2)`   |


We can select rows and columns of a data frame as follows.

```{r}
# select odd rows and columns
round(agren[c(1,3,5,7),c(1,3,5,7)],2)
```

We can select a column by name and print out select entries.

```{r}
# print odd entries from it09 column
agren$it09[c(1,3,5,7)] %>% round(2)
```

Using ther `cbind` function we can "bind" two data columns.

```{r}
# bind two data columns using cbind
cbind(agren$it09,agren$sw09) %>% round(2) %>% head
```

Finally, after manipulating a data frame we can write it out as a CSV file using the `write.csv()` function.


## Reading any delimiter files

The function `read.table()` reads in file with any delimiter.


Now we read the same data, but using a different function, `read.table`, which is more general.  In this case, we have to specify that we are using a comma delimeter and that we want the first line to be variable names (header).

```{r}
# read data using read.table function and comma separator
agrenAlt <- read.table(agrenURL,sep=",",header=T)
# print out top of file and round for readability
round(head(agrenAlt),2)
```

It looks exactly like what we got from `read.csv`, which was the intention.


## Reading fixed width format files

Fixed width format (FWF) files are less common that comma, or tab-delimited files.  The are found when it is important to view the file in text form as well as store it.  They are also found when the datasets are very large, as you can access any element by calculating where it is in the file, instead of having to search for it.

### Coin flip data

We will read in some coin flip data that is stored in that format.  For a fixed width format file, we have to specfy the widths (in characters) of each data column/field. In this case, we can count them by hand, and we can specify the widths.  Here is what we would use to read in the last names, and the first two flips.  Notice that I have skipped the first line.

```{r}
# url for the data
coinflipURL <- "https://raw.githubusercontent.com/sens/smalldata/master/coinspins/spins.txt"
# read in first three fields, skip first line
flips <- read.fwf(coinflipURL,widths=c(14,1,1,1,1),skip=1)
```

```{r}
flips
```

```{r}
flips[,c(1,4,5)]
```

I have to give the columns of this data frame by hand as follows.

```{r}
# assign names to columns
names(flips) <- c("id","flip1","flip2")
# show top of data
flips %>% head
```

It is also possible to extract only the first line that conatins the header (i.e., columns names) buy using the function `scan()`.

```{r}
# get the header and save it in a variable named header
header <- scan(coinflipURL, nlines = 1, what = character())
header
# rename the column
names(flips) <- header
# show top of data
flips %>% head
```

## Writing files

In a similar way to reading functions, R programming offers a set of writing function to write data frames to a disk in-memory plain text file according to a delimiter (or field separator).

| Functions      |                   Delimiter |
|------- --------|----------------------------:|
| `write.csv()`  |                       comma |
| `write.csv2()` |                   semicolon |
| `write.table()`|      customizable delimiter |

```{r}
# generate a dataframe
pancakes10 <- data.frame(ingredient = c("whole wheat flour", "brown rice flour", "salt", "sugar", "baking powder", "whole milk", "yogurt", "egg, beaten", "vegetable oil or butter"),
                         units = c("g", "g", "t", "t", "t", "g", "g", "g", "g"),
                         amount = c(60,60,1/8,1,2,200,20,50,50))
# print data.frame
pancakes10
```

```{r}
# save the pancake dataframe by writing in to delimiter file
# write the data with a delimiter of your choice; do not print out rownames (the default)
write.table(pancakes10, 
            file= "pancake_recipe.txt",
            sep = "@",
            quote = FALSE,
            row.names = FALSE)
```

Writing function offers multiple options to save files. It is recommended to explore some of these options to understand which results to expect.
