Flow of data manipulation in R using dplyr

We know that the main verbs for the dplyr package are -
1. select
2. filter
3. arrange
4. summarise
5. group_by
6. mutate

Let us take a simple question and try to find it's solution. While writing the code for this solution, I will demonstrate how R transforms our data. This step by step procedure will make it crystal clear on how to use these different functions together for powerful analyis.

Taking the in-built 'hflights' package for the data analysis.
Let's say we want to find the favourite or most frequented destination for each Carrier.

Code:

> b <- hflights %>%
       group_by(UniqueCarrier, Dest) %>%
 
       summarise(n = n()) %>%
 
       mutate(rank = rank(desc(n))) %>%
       filter(rank == 1)
> b
Source: local data frame [15 x 4]
Groups: UniqueCarrier
 
        UniqueCarrier Dest    n rank
1             AirTran  ATL 2029    1
2              Alaska  SEA  365    1
3            American  DFW 2105    1
4      American_Eagle  DFW 2424    1
5  Atlantic_Southeast  DTW  851    1
6         Continental  EWR 3924    1
7               Delta  ATL 2396    1
8          ExpressJet  CRP 3175    1
9            Frontier  DEN  837    1
10            JetBlue  JFK  695    1
11               Mesa  CLT   71    1
12            SkyWest  COS 1335    1
13          Southwest  DAL 8243    1
14         US_Airways  CLT 2212    1
15             United  SFO  643    1

Voila ! We have the list of Air Carriers and the most frequented destination by them. But, for a more intuitive understanding -

Let's split the code at each instance and see the kind of tables that R creates.

Code 1.1

b <- hflights %>%
      group_by(UniqueCarrier, Dest) %>%
      select(UniqueCarrier,Dest)
View(b)

Code 1.2

b <- hflights %>%
      group_by(UniqueCarrier, Dest) %>%
      summarise(n = n()) %>%
      mutate(rank = rank(desc(n)))
  View(b)

Code 1.3: Final Result

b <- hflights %>%
+       group_by(UniqueCarrier, Dest) %>%
+       summarise(n = n()) %>%
+       mutate(rank = rank(desc(n))) %>%
+       filter(rank == 1)
> b
Source: local data frame [15 x 4]
Groups: UniqueCarrier
 
        UniqueCarrier Dest    n rank
1             AirTran  ATL 2029    1
2              Alaska  SEA  365    1
3            American  DFW 2105    1
4      American_Eagle  DFW 2424    1
5  Atlantic_Southeast  DTW  851    1
6         Continental  EWR 3924    1
7               Delta  ATL 2396    1
8          ExpressJet  CRP 3175    1
9            Frontier  DEN  837    1
10            JetBlue  JFK  695    1
11               Mesa  CLT   71    1
12            SkyWest  COS 1335    1
13          Southwest  DAL 8243    1
14         US_Airways  CLT 2212    1
15             United  SFO  643    1

Data Manipulation in R: Piping Operator

The piping operator '%>%' makes working with the code very easy and intuitive.
When we write:

x %>% f(y)

it essentially means f(x,y)

R Code

#Method 1: Normal method
group_by(filter(hflights, !is.na(TailNum),UniqueCarrier)
 
#Method 2 : Using the piping operator
hflights %>%
        filter(!is.na(TailNum)) %>%
        group_by(UniqueCarrier)

Let's say we want to find out which airline by TailNum has the highest flights outs of Houston ?

Code

> b <- hflights %>%
       filter(!is.na(TailNum)) %>%
       group_by(TailNum) %>%
       summarise(n_flights = n()) %>%
       filter(n_flights == max(n_flights))
> b
Source: local data frame [1 x 2]
 
  TailNum n_flights
1  N14945       971

Data manipulation in R - summarise & group_by

Summarise and group by are 2 very powerful functions. Together they can be used to provide an over arching view of our data.

Summarise Function

Syntax 

Let us understand the summarise function through a sample question.

Question

Find out the average Taxi In & Taxi Out time for all American Airlines flights.

> s2 <- hflights %>%
 filter(UniqueCarrier == "American",!is.na(TaxiIn), !is.na(TaxiOut)) %>%
 summarise(avg_In = mean(TaxiIn), avg_out = mean(TaxiOut))
> s2
Source: local data frame [1 x 2]
 
    avg_In  avg_out
1 8.032349 15.08229

is.na() is used to filter out the NA values, otherwise we won't get the right result.

Aggregate functions in dplyr

Let us understand on how to use these functions in summarise by solving a problem statement.

Question

1. Write a code to return
a. count of distinct values of UniqueCarriers
b. the min and max TaxiIn of Continental Airlines
c. Summarising statistics of Continental Airlines

Solution

Part a

a <- n_distinct(hflights$UniqueCarrier)
a

Part b

a <-  hflights %>%
 filter(UniqueCarrier == "Continental",!is.na(ArrTime), !is.na(DepTime)) %>%
 summarise(max_Taxi = max(TaxiIn), min_Taxi = min(TaxiIn))
> a
Source: local data frame [1 x 2]
 
  max_Taxi min_Taxi
1      140        1

Part c

> a <- hflights %>%
                 filter(UniqueCarrier == "Continental") %>%
               summarise(n_obs = n(),
                           distinct_flightnum = n_distinct(FlightNum),
                           avg_Airtime = mean(AirTime, na.rm = TRUE),
                           n_canc = sum(Cancelled == 1),
                           p_canc = (n_canc/n_obs)*100)

> a
Source: local data frame [1 x 5]
 
  n_obs distinct_flightnum avg_Airtime n_canc    p_canc
1 70032                927    145.4579    475 0.6782614

Group By Function

Group by lets you define groups within your data set. In conjunction with summarise, we can perform powerful analysis on our data.

Question

Find out which Airline had the highest average  Taxi Time (Taxi In + Taxi out) and use the percentage of cancelled flights as a deciding parameter in case of a tie.

Solution

> a <- hflights %>%
         mutate(TotalTaxiTime = TaxiIn + TaxiOut) %>%
           group_by(UniqueCarrier) %>%
                 summarise(n_obs = n(),
                   n_canc = sum(Cancelled == 1),
                   n_per = (n_canc/n_obs)*100,
                  avg_taxi = round(mean(TotalTaxiTime, na.rm= TRUE),2)) %>%
         arrange(desc(avg_taxi),n_per)
> a
Source: local data frame [15 x 5]
 
        UniqueCarrier n_obs n_canc     n_per avg_taxi
1                Mesa    79      1 1.2658228    29.45
2               Delta  2641     42 1.5903067    28.70
3              United  2072     34 1.6409266    26.04
4         Continental 70032    475 0.6782614    25.48
5          US_Airways  4082     46 1.1268986    24.15
6             SkyWest 16061    224 1.3946828    23.6

Is this the most optimized way to write the code ?
Notice that, we created a new column "TotalTaxiTime" especially for this calculation. What if, mutate function was to be avoided ?

Thankfully, summarise does allow us to do small mathematical operations within it. Please see below how we can succinctly write the same code below  -

Optimized Code

> a <- hflights %>%
               group_by(UniqueCarrier) %>%
                 summarise(n_obs = n(),
                   n_canc = sum(Cancelled == 1),
                   n_per = (n_canc/n_obs)*100,
                  avg_taxi = round(TotalTaxiTime = mean(TaxiIn + TaxiOut, na.rm= TRUE),2)) %>%
         arrange(desc(avg_taxi),n_per)
> a

Source: local data frame [15 x 5]

        UniqueCarrier n_obs n_canc     n_per avg_taxi
1                Mesa    79      1 1.2658228    29.45
2               Delta  2641     42 1.5903067    28.70
3              United  2072     34 1.6409266    26.04
4         Continental 70032    475 0.6782614    25.48
5          US_Airways  4082     46 1.1268986    24.15
6             SkyWest 16061    224 1.3946828    23.6

Data manipulation in R - Filter & Arrange

We can select rows which we want to display using the filter and arrange functions.

Syntax for filter function

#1

b <- hflights %>%
        filter(Distance >= 2000)
View(b)

Notice, how we are referring more than one character in this code. 

#2

> hflights %>%
+ filter(UniqueCarrier %in% c("American", "Alaska"))
Source: local data frame [3,609 x 21]

You can even combine tests with boolean operators. If you use different tests separated with a comma, R just returns the 'ANDed' result ultimately. Otherwise, you can use &, | & ! operators.

Question -

1. Find out all the American Airlines flight that got cancelled after getting delayed.

Sample Code
> a <- hflights %>%

filter(UniqueCarrier == "American",DepDelay > 0, Cancelled == 1)
> head(a)
Source: local data frame [2 x 21]

2. Display a table that contains only flights that got cancelled over weekdays.

> f3 <- hflights %>%
 filter(DayOfWeek %in% c(1,2,3,4,5),Cancelled == 1)
> head(f3)
Source: local data frame [6 x 21]

3. Display a table that contains only flights that whose TaxiIn time is greater than Taxi Out time and Depdelay is not NA. 

> a <- hflights %>%
 filter(TaxiIn > TaxiOut, !is.na(DepDelay))
> head(a)

Syntax for arrange function

Let's understand the role of arrange function through a question.

Question -

1. Arrange the names of all unique carriers in alphabetical order and also arrange by depdelay. Do no t include columns that might have depdelay = NA

> a <- hflights %>%
         filter(TaxiIn > TaxiOut, !is.na(DepDelay))
> head(a)
 
> a <-a %>%
          arrange(UniqueCarrier,DepDelay)
> select(a,UniqueCarrier,DepDelay)
 
  UniqueCarrier DepDelay
1        AirTran      -14
2        AirTran      -13
3        AirTran      -12
4        AirTran      -11
5        AirTran      -11
6        AirTran      -11

Note: Wrapping any of the variable with desc() function will arrange the values in descending order.

We can always perform simple operations inside the arrange function directly.

Example -

> a <- hflights %>%
         arrange(desc(TaxiIn + TaxiOut))
> head(a)
 

This will arrange the data in descending order of Total Taxing Time. 

Cheatsheets

GGVIS
The goal of ggvis is to make it easy to build interactive graphics for exploratory data analysis. It adds new features to make your plots interactive. ggvis also incorporates shiny’s reactive programming model and dplyr’s grammar of data transformation.

Click here to download the pdf for part 1.
Click here to download the pdf for part 2.

Data Manipulation: Deleting columns in R

Adding or deleting columns are one of the most basic tasks that need to be performed. In R, we can do so using multiple techniques. The trade off between the methods can be truly appreciated only the operations are performed on large datasets i.e. speed of operation becomes visible.

Nevertheless, this post is here to give you a simple guide on different ways to delete columns in R.

Let us first create a data frame.

Code

year <- c(2001,2002,2003,2004)
> name <- c("A","B","C","D")
> price <- c(12,32,42,53)
> volume <- c(32,42,53,64)
> sales <- data.frame(year,name,price,volume)
> class(sales)
[1] "data.frame"

1. First method

Use the subset command on the data frame

> sales <- subset(sales, select = -c(name,volume))
> class(sales)

2. Second Method

sales[1] <- NULL
sales[1:3] <- list(NULL) #the list needs to be used here

Computing Correlation Coefficients in R

Correlation Coefficients

The general formulae to compute correlation coefficient between 2 variables is -

where cov(A,B) is the covariance between A & B and S_A and S_B are the standard deviations.

Manual Way in R

#Define 2 verctors 
> 
> A <- c(1,2,4,5)
> B <- c(5,6,8,1)
> 
> #Finding Covariance between A & B 
> A_diff <- A - mean(A)
> B_diff <- B - mean(B)
> 
> #Print both the variables created above
> A_diff
[1] -2 -1  1  2
> B_diff
[1]  0  1  3 -4
> 
> #Do the summation and divide by N-1 to get the covariance between the two vectors
> #N = 3 in this case 
> cov <- sum(A_diff*B_diff)/(3-1)
> 
> #Finding the squared difference w.r.t to mean for the vectors 
> A_sq <- A_diff^2
> B_sq <- B_diff^2
> 
> #Using the standard deviation formulae 
> 
> A_sd <- sqrt(sum(A_sq)/(3-1))
> 
> B_sd <- sqrt(sum(B_sq)/(3-1))
> 
> #Print the standard deviation 
> A_sd
[1] 2.236068
> B_sd
[1] 3.605551
> 
> #Plugging in values to find the correlation coefficient
> 
> corr <- cov/(A_sd*B_sd)
> 
> #Printing the correlation obtained - Manual way
> corr
[1] -0.3721042
> 
> #Using formulae for direct computation 
> 
> corr_test <- cor(A,B)
> corr_test
[1] -0.3721042

 Using the in-built function and manual way, we get the same result. 

Data manipulation in R

DPLYR Package Basics

Dplyr is a very powerful package for data manipulation in R. The 6 main verbs for dplyr are -
1. Select
2. Filter
3. Arrange
4. Group
5. Mutate
6. Summarise

Together, these verbs can be used to perform powerful data analysis.

Select

All you need to know about select function.

#Shown below are 2 ways to select the set of columns. Notice, how easy it becomes when we make use of the select function. The "%>%" is the piping operator and is of great use when longer codes need to be written.

> #Difficult way
> hflights[c("Year","Month","DayOfWeek","DepTime","ArrTime")]
Source: local data frame [227,496 x 5]
 
   Year Month DayOfWeek DepTime ArrTime
1  2011     1         6    1400    1500
2  2011     1         7    1401    1501
3  2011     1         1    1352    1502
4  2011     1         2    1403    1513
5  2011     1         3    1405    1507
6  2011     1         4    1359    1503
7  2011     1         5    1359    1509
8  2011     1         6    1355    1454
9  2011     1         7    1443    1554
10 2011     1         1    1443    1553
..  ...   ...       ...     ...     ...
> #Easy Way
> hflights %>%
          select(Year:ArrTime, - DayofMonth)

Mutate

Mutate is used to create new columns. You can always reuse variables created within mutate function to create more columns. Always remember that the new columns are created to a copy of the dataset.
Let's write a code to create 2 new columns - TotalTaxiTime, GroundTime

#Creating new columns
hflights %>%
          mutate(TotalTaxiTime = TaxiIn+ TaxiOut, GroundTime = ActualElapsedTime - ArrTime)

R Basics: Graphics in R

R is a powerful tool which can be used to make amazing graphics for representing your data.This post is going to just scratch the surface of the most commonly used graphics.

BOXPLOT: It is used for quantitative variables and its goal is to give you an idea of the distribution of the data. The upper and the lower bounds of the box represent the 75th percentile and the 25th percentile respectively. The black line is seen to be towards the top which means the distribution is asymmetric.
Dataset Source: http://datamarket.com/data/set/12sc/infant-mortality-rate#!

#Graphical Plots in R 
install.packages('knitr')
library(knitr)
infant_mortality<-read.csv("infant-mortality-rate.csv")
dim(infant_mortality)
[1] 92  4
#Reading first few lines of datahead(infant_mortality)
  Year Australia India Pakistan
1 2010     4.437 50.58    71.35
2 2011     4.437 50.58    71.35
3 2012     4.437 50.58    71.35
4 2013     4.437 50.58    71.35
5 2014     4.437 50.58    71.35
6 2015     4.437 50.58    71.35
#Boxlpot
boxplot(infant_mortality$India,col="red")

#Seeing the summary statistics of the dataset
summary(infant_mortality)
      Year      Australia        India         Pakistan   
 2010   : 1   Min.   :4.44   Min.   :50.6   Min.   :71.3  
 2011   : 1   1st Qu.:4.44   1st Qu.:50.6   1st Qu.:71.3  
 2012   : 1   Median :4.44   Median :50.6   Median :71.3  
 2013   : 1   Mean   :4.44   Mean   :50.6   Mean   :71.3  
 2014   : 1   3rd Qu.:4.44   3rd Qu.:50.6   3rd Qu.:71.3  
 2015   : 1   Max.   :4.44   Max.   :50.6   Max.   :71.3  
 (Other):86   NA's   :1      NA's   :1      NA's   :1  
#Boxplot for comarisons#Compares mortality rate VS  Year
 boxplot(infant_mortality$Pakistan ~ as.factor(infant_mortality$Year),col="blue")

Dataset Source: http://datamarket.com/data/set/vih/retail-prices-of-some-commodities-and-services-1996-2013#!

 #Barplot 
retail_prices<-read.csv("retail-prices-of-some-commoditie.csv")
head(retail_prices)
    Month Dairy.cheese Dark.chocolate Eggs Grapes
1 1996-11          701            190  348    447
2 1997-02          736            188  360    593
3 1997-05          742            189  360    381
4 1997-08          747            191  359    375
5 1997-11          758            194  360    479
6 1998-02          786            192  341    378
barplot(table(retail_prices$Eggs),col="red")


  #Histogram  hist(retail_prices$Eggs,col="blue",breaks=50) 


#Density Plots

#Density plots are smoothed histograms
 dens<-density(retail_prices$Eggs) 
#lwd is for line thickness 
#Density plots the percentage of observation instead of absolute numbers 
plot(dens,lwd=3,col="blue") 
#Density plots - Multiple Distributions 
dens_grapes<-density(retail_prices$Grapes) 
lines(dens_grapes,lwd=3,col="orange")

#Timeseries 

#Install the following package to plot a timeseries
install.packages("astsa")
library(astsa)

#start parameter is used to indicate the year on x-axis
retail_prices_timeseries<-ts(retail_prices$Eggs,start=c(1961,1))
plot(retail_prices_timeseries,,col="blue",lwd=3)

#To plot multiple timeseries on same graph for comparison 
retail_prices_timeseries1<-ts(retail_prices$Dark.chocolate,start=c(1961,1))
#Lines command is used to overlay multiple plots on same graph
lines(retail_prices_timeseries1,col="red",lwd=3)
retail_prices_timeseries2<-ts(retail_prices$Grapes,start=c(1961,1))
lines(retail_prices_timeseries2,col="green",lwd=3)
retail_prices_timeseries2<-ts(retail_prices$Dairy.cheese,start=c(1961,1))
lines(retail_prices_timeseries2,col="yellow",lwd=3)