2.7 Data Management
We first cover data format conversion since sometimes the data is not in the right format. Second, we will learn how to load a built-in dataset to illustrate how to take a snapshot of the data, sorting the data, selecting observation, selecting variable, subsetting the dataset, and merging dataframes. Then we will learn two useful packages: dplyr and reshape. Finally, we apply it to world bank data.
2.7.1 Loading built-in dataset
Load a standard dataset and store it as a dataframe
df <- data.frame(mtcars)2.7.2 Peeping Data
We may use the head() to checking the top rows and tail() to check bottom rows.
Here is the example data:
x <- 1:5
y <- seq(5,1,-1)
z <- c(1,1,2,2,3)
df <- data.frame(x,y,z)Here we look at the first three row:
head(df, 3) ## x y z
## 1 1 5 1
## 2 2 4 1
## 3 3 3 2Here we look at the last three row:
tail(df, 3)## x y z
## 3 3 3 2
## 4 4 2 2
## 5 5 1 32.7.3 Sorting Data
Sorting data by giving multiple criteria using order.
The following code first sort the data by y (in an ascending order) and then break tie using z if they have the same y.
newdf <- df[order(df$y,df$z),]
newdf## x y z
## 5 5 1 3
## 4 4 2 2
## 3 3 3 2
## 2 2 4 1
## 1 1 5 12.7.4 Joining dataframes
To merge two dataframes horizontally by joining through an unique identifier, one may use merge().
df1<-data.frame(ID=c("a","b"), x=c(1,2))
df2<-data.frame(ID=c("b","a"), y=c(3,4))
df3 <-merge(df1,df2,by="ID")
df3## ID x y
## 1 a 1 4
## 2 b 2 3If two dataframes are just to join horizontally without an unique identifier, then use cbind().
df1<-data.frame(ID=c("a","b"), x=c(1,2))
df2<-data.frame(ID=c("b","a"), y=c(3,4))
df3 <-cbind(df1,df2)
df3## ID x ID y
## 1 a 1 b 3
## 2 b 2 a 42.7.5 Stacking Dataframe
If dataframes are just to join vertically, then use rbind(). Note that rbind requires dataframes have the same columns names.
df1<-data.frame(ID=c("a","b"),
x=c(1,2), y=c(1,2))
df2<-data.frame(ID=c("c","d"),
x=c(3,4), y=c(1,2))
df3<-data.frame(ID=c("e","f"),
x=c(5,6), y=c(1,2))
df4 <-rbind(df1,df2,df3)
df4## ID x y
## 1 a 1 1
## 2 b 2 2
## 3 c 3 1
## 4 d 4 2
## 5 e 5 1
## 6 f 6 22.7.6 Selecting Columns (Variables)
The following code selecting columns 1 to 3.
df <- data.frame(ID=c("a","b","c","d","e","f"),
x=c(1,2,3,4,5,6),
y=c(1,2,1,2,1,2),
z=c(6,5,4,3,2,1))
newdf <- df[,c(1:3)]
head(newdf,3)## ID x y
## 1 a 1 1
## 2 b 2 2
## 3 c 3 1The following code drops columns 1 to 2.
df <- data.frame(ID=c("a","b","c","d","e","f"),
x=c(1,2,3,4,5,6),
y=c(1,2,1,2,1,2),
z=c(6,5,4,3,2,1))
newdf <- df[,-c(1:2)]
head(newdf,3)## y z
## 1 1 6
## 2 2 5
## 3 1 4We can delete columns by name.
df <- data.frame(ID=c("a","b","c","d","e","f"),
x=c(1,2,3,4,5,6),
y=c(1,2,1,2,1,2),
z=c(6,5,4,3,2,1))
df$z<- NULL
head(df,3)## ID x y
## 1 a 1 1
## 2 b 2 2
## 3 c 3 12.7.7 Selecting Rows (observation) based on row number
The functions head() and tail() allow as to directly obtain rows from the top and bottom.
The following code selects the top 3 observations.
df <- data.frame(ID=c("a","b","c","d","e","f"),
x=c(1,2,3,4,5,6),
y=c(1,2,1,2,1,2),
z=c(6,5,4,3,2,1))
newdf<-head(df,3)
newdf## ID x y z
## 1 a 1 1 6
## 2 b 2 2 5
## 3 c 3 1 4The following code drops the last 2 observations.
df <- data.frame(ID=c("a","b","c","d","e","f"),
x=c(1,2,3,4,5,6),
y=c(1,2,1,2,1,2),
z=c(6,5,4,3,2,1))
newdf<-head(df,-2)
newdf## ID x y z
## 1 a 1 1 6
## 2 b 2 2 5
## 3 c 3 1 4
## 4 d 4 2 3The following code selects the bottom 3 observations.
df <- data.frame(ID=c("a","b","c","d","e","f"),
x=c(1,2,3,4,5,6),
y=c(1,2,1,2,1,2),
z=c(6,5,4,3,2,1))
newdf <- tail(df,3)
newdf## ID x y z
## 4 d 4 2 3
## 5 e 5 1 2
## 6 f 6 2 1The following code drops the top 2 observations.
df <- data.frame(ID=c("a","b","c","d","e","f"),
x=c(1,2,3,4,5,6),
y=c(1,2,1,2,1,2),
z=c(6,5,4,3,2,1))
newdf <- tail(df,-2)
newdf## ID x y z
## 3 c 3 1 4
## 4 d 4 2 3
## 5 e 5 1 2
## 6 f 6 2 1The following code selects observations from row 2 to row 4.
df <- data.frame(ID=c("a","b","c","d","e","f"),
x=c(1,2,3,4,5,6),
y=c(1,2,1,2,1,2),
z=c(6,5,4,3,2,1))
newdf <- df[c(2:4),]
head(newdf,3)## ID x y z
## 2 b 2 2 5
## 3 c 3 1 4
## 4 d 4 2 3The following code excludes observations from row 2 to row 4.
df <- data.frame(ID=c("a","b","c","d","e","f"),
x=c(1,2,3,4,5,6),
y=c(1,2,1,2,1,2),
z=c(6,5,4,3,2,1))
newdf <- df[-c(2:4),]
head(newdf,3)## ID x y z
## 1 a 1 1 6
## 5 e 5 1 2
## 6 f 6 2 12.7.8 Selecting Rows (observation) based on condition
To select observations based on conditions, we may use which. The following code selects observations such that \(y=1\).
df <- data.frame(ID=c("a","b","c","d","e","f"),
x=c(1,2,3,4,5,6),
y=c(1,2,1,2,1,2),
z=c(6,5,4,3,2,1))
newdf <- df[which(df$y==1), ]
newdf## ID x y z
## 1 a 1 1 6
## 3 c 3 1 4
## 5 e 5 1 2Here, which(df$y==1) returns a vector of row numbers such that \(y=1\).
The following code chooses observation that \(y=1\) and \(x>1\).
df <- data.frame(ID=c("a","b","c","d","e","f"),
x=c(1,2,3,4,5,6),
y=c(1,2,1,2,1,2),
z=c(6,5,4,3,2,1))
newdf <- df[which(df$y==1 & df$x>1), ]
newdf## ID x y z
## 3 c 3 1 4
## 5 e 5 1 2Here, which(df$y==1 & df$x>1) returns a vector of row numbers such that both \(y=1\) and \(x>1\).
2.7.9 Selecting rows (observations) and columns (variables)
To select observations based on conditions restricting to some columns, we use subset. To choose which column to include, we use ``select’’.
The following code selects columns of y and z when \(y=1\) and \(x>1\).
df <- data.frame(ID=c("a","b","c","d","e","f"),
x=c(1,2,3,4,5,6),
y=c(1,2,1,2,1,2),
z=c(6,5,4,3,2,1))
newdf <- subset(df, y== 2 & x>1, select= c(y,z))
newdf## y z
## 2 2 5
## 4 2 3
## 6 2 12.7.10 Create New Column
If a new column is simple transformation of existing column, then we can just write the expression directly because R is vectorized.
df <- data.frame(ID=c("a","b","c","d","e","f"),
x=c(1,2,3,4,5,6),
y=c(1,2,1,2,1,2),
z=c(6,5,4,3,2,1))
df$a <- 2*df$x + 3*df$y -df$z
df## ID x y z a
## 1 a 1 1 6 -1
## 2 b 2 2 5 5
## 3 c 3 1 4 5
## 4 d 4 2 3 11
## 5 e 5 1 2 11
## 6 f 6 2 1 17df## ID x y z a
## 1 a 1 1 6 -1
## 2 b 2 2 5 5
## 3 c 3 1 4 5
## 4 d 4 2 3 11
## 5 e 5 1 2 11
## 6 f 6 2 1 17When we want to create a new column where each row depends on values of existing column, we can use ifelse().
df <- data.frame(ID=c("a","b","c","d","e","f"),
x=c(1,2,3,4,5,6),
y=c(1,2,1,2,1,2),
z=c(6,5,4,3,2,1))
df$a <- ifelse(df$x>3, 1,0)
df## ID x y z a
## 1 a 1 1 6 0
## 2 b 2 2 5 0
## 3 c 3 1 4 0
## 4 d 4 2 3 1
## 5 e 5 1 2 1
## 6 f 6 2 1 12.7.11 Remove Duplicated Observation
To remove duplicate, the function is unique().
df <- data.frame(ID=c("a","b","a","b","a","b"),
x=c(1,2,3,4,1,2),
y=c(1,2,1,2,1,2))
newdf <- unique(df)
newdf## ID x y
## 1 a 1 1
## 2 b 2 2
## 3 a 3 1
## 4 b 4 22.7.12 Collapse Data by Group
There are two ways to collapse data by applying function to group: (1) aggregate and (2) by.
The following calculate the mean of data by group.
df <- data.frame(ID=c("a","b","a","b","a","b"),
x=c(1,2,3,4,1,2),
y=c(1,2,1,2,1,2))
newdf <- aggregate(df$x, by=data.frame(df$ID), mean)
newdf## df.ID x
## 1 a 1.666667
## 2 b 2.666667The following is similar by use by(). H
df <- data.frame(ID=c("a","b","a","b","a","b"),
x=c(1,2,3,4,1,2),
y=c(1,2,1,2,1,2))
newdf <-by(df$x, df$ID, mean)
newdf## df$ID: a
## [1] 1.666667
## --------------------------------------------------------
## df$ID: b
## [1] 2.666667However, it essentially gives us a list object.
We can use cbind() to convert into a column vector:
cdf <-cbind(newdf)
cdf## newdf
## a 1.666667
## b 2.666667Or we can use rbind() to convert into a row vector:
rdf <-rbind(newdf)
rdf## a b
## newdf 1.666667 2.666667