Manipulating data

In statistics, data is the starting point for any analysis. It is therefore essential to know how to master operations such as importing and exporting data, changing types, identifying individuals with missing values, concatenating factor levels, etc. These different notions are therefore presented in this sheet, which aims to be both concise and satisfactory in practice. We focus on the dplyr package, an up-to-date tool to manipulate data.

1. Reading data from files

Functions read.table and read.csv allows to import data from .txt or .csv files.

path <- file.path("../DATA", "piscines.csv") #first: directory, second: file
piscines <- read.csv(path)
class(piscines)
summary(piscines)

The are many important options in read.csv:

• sep: the field separation character
• dec: the character used for decimal points
• header: a logical value indicating whether the file contains the names of the variables as its first line
• row.names: a vector of row names (to identify indivuals if needed)
• na.strings: a character vector of strings which are to be interpreted as NA values.
• …

Exercise 1

1. Import the dataset mydata.csv

2. Import correctly the dataset mydata.csv (use sep, dec and row.names)

3. Import the dataset mydata2.csv

4. This dataset contains missing data (collected with a dot). We need to use na.strings to take into account of this situation. Import correctly this dataset.

5. Call the levels of sex: women and man (use levels)

readr package makes it easy to read many types of rectangular data, including csv, tsv and fixed width files. It uses read_csv and read_table instead of read.csv and read.table. We can also use this package with the menu Import Dataset of Rstudio.

Exercise 2

Read the piscine.csv file with readr (use read_csv or click on Import Dataset)

2. dplyr package

dplyr is a powerful R-package to transform and summarize tabular data with rows and columns. We can find information here or in the cheatsheet.

We can manipulate data with classical matrix operation. For instance, we can obtain Longitude and Latitude for swimming pool with Longitude more than 153 with

piscines[piscines$Longitude>153,c("Longitude","Latitude")]

dplyr offers a more friendly syntax

library(dplyr)
select(piscines,Longitude,Latitude) %>% filter(Longitude>153)

Code is more efficient and easier to read.

dplyr contains a grammar with the following verbs:

• select() select columns (variables)
• filter() filter rows (individuals)
• arrange() re-order or arrange rows
• mutate() create new columns (new variables)
• summarise() summarise values (compute statistics summaries)
• group_by() allows for group operations in the “split-apply-combine” concept

2.1 select() verb

It allows to select variables (columns):

select(df, VAR1, VAR2, ...)

For instance

coord <- select(piscines, Latitude, Longitude)
head(piscines, n=2)
head(coord, n=2)

We can use helper functions (begins_with, end_with, contains, matches) for more complex selections based on the name of the variables

coord <- select(piscines, ends_with("tude"))
head(coord, n=2)

2.2 mutate() verb

It allows to create new variables in the dataset:

mutate(df, NEW.VAR = expression(VAR1, VAR2, ...))

For instance

df <- mutate(piscines, phrase=paste("Swimming pool", Name, "is located at the address", Address))
select(df,phrase)

We can create many variables

mutate(piscines,
       phrase = paste("Swimming pool", Name, "is located at the address", Address),
       unused = Longitude + Latitude
)

2.3 filter() verb

It allows to select individuals (rows):

filter(df, TEST)

For instance

p1 <- filter(piscines, Longitude>153.02)
select(p1,Longitude)

or (we select swimming pool without “Pool” in the name)

df <- filter(piscines, !grepl("Pool", Name))
select(df,Name)

or

p2 <- filter(piscines, Longitude>153.02 | Latitude < -27.488)
p2 <- select(p2, Longitude, Latitude)
p2

We use slice to select individuals with index:

piscines %>% slice(5:8)

2.4 arrange() verb

It allows to sort a dataset according to a variable:

arrange(df, VAR) #increasing sort

or

arrange(df, desc(VAR)) #decreasing sort

For instance

arrange(piscines, Longitude)

or

arrange(piscines, desc(Longitude))

2.5 summarise() verb

More complex… It allows to define new datasets from the original dataset. New dataset often includes statistical summaries for the original dataset

1. mean() ;
2. median() ;
3. IQR() ;
4. var().

For instance

summarise(piscines,
          mean_long = mean(Longitude),
          med_lat = median(Latitude),
          min_lat = min(Latitude),
          sum_long = sum(Longitude)
)

You can also look at summarise_all, summarise_at (help(summarise_all)). dplyr also proposes the following functions (very useful in statistics):

1. n(): number of lines (individuals of the dataset)
2. n_distinct(): number of distinct elements of a vector
3. fisrt() and last(): first and last element of a vector

summarise(piscines,n())
summarise(piscines,last(Longitude))

2.6 Operations on the verbs

We can of course concatenate verbs. For instance, we obtain the names of the swimming pool with the higher Longitude with:

p1 <- arrange(piscines,desc(Longitude)) #decreasing sort
summarise(p1,first(Name)) #extract the first

The pipe operator %>% makes the code more readable:

piscines %>% arrange(desc(Longitude)) %>% summarise(first(Name))

2.7 Group data with ‘Group_by’

group_by allows to apply operation for group of data. For instance, we want to compute mean longitudes for swimming pools of High and Low latitude (it does not make sense!). We first add a variable lat_disc which allows to discern high and low latitudes.

lat_mean <- summarise(piscines,mean(Latitude))
pisc1 <- mutate(piscines,lat_dis=factor(Latitude>as.numeric(lat_mean)))
levels(pisc1$lat_dis) <- c("Low","High")

We can now compute with group_by the mean longitudes for the 2 groups

group_by(pisc1,lat_dis) %>% summarise(mean_long=mean(Longitude))

Exercise 3

We consider the iris dataset

data(iris)

Answer to the following questions with dplyr

1. Select the variables Petal.Width and Species

2. Select individuals from Versicolor and Virginica groups (you can use symbol | for condition or)

3. Calculate the number of setosa with summarise

4. Calculate the mean of Petal Width for the versicolor specie

5. Add in the dataset the variable Sum_Petal which contains the sum of Petal.Width and Sepal.Width

6. Calcuate the mean and variance of Sepal Length for each group of Species (use group_by)

Exercise 4 (optional)

We consider the hflights dataset which contains informations about flights departing from Houston airports IAH (George Bush Intercontinental) and HOU (Houston Hobby):

library(hflights)
hflights <- tbl_df(hflights)

Variable Unique Carrier provides a code which identify the carrier:

lut1 <- c("AA" = "American", "AS" = "Alaska", "B6" = "JetBlue", "CO" = "Continental", 
         "DL" = "Delta", "OO" = "SkyWest", "UA" = "United", "US" = "US_Airways", 
         "WN" = "Southwest", "EV" = "Atlantic_Southeast", "F9" = "Frontier", 
         "FL" = "AirTran", "MQ" = "American_Eagle", "XE" = "ExpressJet", "YV" = "Mesa")

We can also specify the variable CancellationCode as follows:

lut2 <- c("A" = "carrier", "B" = "weather", "C" = "FFA", "D" = "security", "E" = "not cancelled")

1. Modify the table hflights to better explain variables Unique Carrier et CancellationCode.

hflights$UniqueCarrier <- lut1[hflights$UniqueCarrier]
hflights$CancellationCode[hflights$CancellationCode==""] <- "Z"
hflights$CancellationCode <- lut2[hflights$CancellationCode]

2. Use several different ways to select variables from Origin to Cancelled

3. Select variables DepTime, ArrTime, ActualElapsedTime, AirTime, ArrDelay and DepDelay. Use helper function contains().

4. Add a variable ActualGroundTime which corresponds to ActualElapsedTime minus AirTime

5. Add a variable AverageSpeed (=Distance/AirTime) and order the dataset according to this variable

6. Select flights to JFK

7. Calculate the number of fligths to JFK

8. Create a summary of hflights which contains:
   • n : the total number of flights;
   • n_dest: the total number of destinations;
   • n_carrier : the total numbers of carriers.
9. Create a summary of flights from the carrier American which contains
   • the total number of flights ;
   • the total number of cancelled flights ;
   • the mean of the variable ArrDelay (be carefull with the NA).
10. Calculate for each carrier
    • the total number of flights
    • the mean of the variable AirTime

11. Order the carriers according to the mean of departure delays