5 Data transformations

5.1 Dealing with NAs in our data

5.1.1 Removing NAs

NAs are generated when our data contains a missing value. This can become problematic for certain computations and we can decide to remove all NAs.

The function to do this is is.na.

#create a vector that includes an NA
y <- c(1,2,3,NA,5)

#check whether we have NAs
is.na(y)

[1] FALSE FALSE FALSE  TRUE FALSE

#remove NAs from our data
y[!is.na(y)]

[1] 1 2 3 5

! means “not”. It turns TRUE into FALSE and the other way around.
The function is.na(y) gives a logical vector of the same size as y with value TRUE if and only if the corresponding element in y is NA.
I.e. in this example we have FALSE FALSE FALSE TRUE FALSE
When using y[!is.na(y)], we keep the elements where is.na is FALSE

5.1.1.1 Replacing NAs with something else

Another option might be to replace a NA with a 0 (or whatever else makes sense in a given context)

#create a vector that includes an NA
x <- c(1,2,3,NA,5)

#check whether we have NAs
is.na(x)

[1] FALSE FALSE FALSE  TRUE FALSE

#replace NAs with 0
x[is.na(x)] <- 0

#check data
x

[1] 1 2 3 0 5

Notice, that here we use a similar syntax compared to before. Now we do

Find the NAs (I.e. in this example we have FALSE FALSE FALSE TRUE FALSE)
If NA is TRUE then replace it with a 0

Be careful with this: a 0 is a real measurement, a missing value is not. If you replace missing root lengths with 0, a mean or a statistical test will treat these plants as plants without roots.

5.1.2 Removing NAs from our dataframe

Remember that our growth data contains NAs: for a few plants, the root length or the shoot weight is missing. Let’s first count how many NAs we have in each measurement column. is.na() gives TRUE for each missing value, and sum() counts the TRUE values:

#count the NAs in the root length and in the shoot weight
sum(is.na(growth_data$Rootlength))

[1] 15

sum(is.na(growth_data$FW_shoot_mg))

[1] 8

which() tells us in which rows the NAs are:

#check in which rows we have NAs inside the shoot weight measurements
which(is.na(growth_data$FW_shoot_mg))

[1]  32  33 208 209 383 384 527 562

#check a row with a NA value
growth_data[32,]

If we want to analyse the shoot weight, we can remove the rows without a shoot weight:

#remove rows that contain NAs in the shoot weight
growth_data_shoot <- growth_data[!is.na(growth_data$FW_shoot_mg), ]

#check if that worked
sum(is.na(growth_data_shoot$FW_shoot_mg))

[1] 0

#compare the dimensions before and after
dim(growth_data)

[1] 594   5

dim(growth_data_shoot)

[1] 586   5

Notice that we only removed rows with a missing shoot weight. All of the removed plants do have a root length. So if we want to analyse the root length, we should not use growth_data_shoot, but remove the NAs in the column Rootlength instead. Only remove the NAs you need to remove, otherwise you throw away good data.

5.2 Subsetting our data

We already have seen important ways to subset data:

Use of the index by using the square brackets

#subsetting rows and columns using the index
growth_data[1:3,2:4]

Use of operators

#subset using operators (only keep rows if the Nutrient column equals low_P)
low_P_data <- growth_data[growth_data$Nutrient == "low_P", ]
head(low_P_data)

Now we want to look at some other examples:

5.2.1 grep and grepl

Oftentimes you may need to filter a data set based on a partial character string that is beyond the scope of comparison operators.

R provides such functions (grep and grepl) that match character patterns in specified vector. While both of these functions find patterns, they return different output types based on those patterns.

grep returns numeric values that correspond to the indexed locations of the patterns
grepl returns a logical vector in which ‘TRUE’ represents a pattern match.

In our growth data, we only want to print measurements of our controls (i.e. the mock treatments). Our table has many rows, so we only look at the first few results with head().

#find the row index values with mock treatments using grep
head(grep("mock", growth_data$Condition))

[1] 1 2 3 4 5 6

To see how grepl works, let’s first try it on a short vector with our three conditions. Both strains (129E and 13C) contain a 1, the mock treatment does not:

#use grepl to search for conditions that contain a 1, returning FALSE/TRUE statements for each element
#with "TRUE" representing matched patterns
grepl("1", c("mock", "129E", "13C"))

[1] FALSE  TRUE  TRUE

On our dataframe, grepl gives one TRUE or FALSE for each of the 594 rows. Instead of printing them all, we can count the TRUE values with sum(), i.e. the number of plants treated with a strain:

#count the rows where the condition contains a 1
sum(grepl("1", growth_data$Condition))

[1] 385

Now lets use this to actually filter our data table for a pattern.

5.2.1.1 Filter using grep

#use grep
filter_for_value <- timecourse[grep("mock", timecourse$Condition),]

#check the first rows of our data
head(filter_for_value)

#check the dimensions of the original dataframe
dim(timecourse)

[1] 2323    4

#check the dimensions of our new dataframe
dim(filter_for_value)

[1] 1156    4

5.2.1.2 Filter using grepl

filter_for_value <- timecourse[grepl("moc", timecourse$Condition),]

#check the first rows of our data
head(filter_for_value)

#check the dimensions of the original dataframe
dim(timecourse)

[1] 2323    4

#check the dimensions of our new dataframe
dim(filter_for_value)

[1] 1156    4

5.2.2 Discard matching rows

#filter data set based on values that do not match the specified pattern (by using the ! symbol)
filter_for_not_a_value <- timecourse[!grepl("mock", timecourse$Condition),]

#view dataframe
head(filter_for_not_a_value)

#check the dimensions of the original dataframe
dim(timecourse)

[1] 2323    4

#check the dimensions of our new dataframe
dim(filter_for_not_a_value)

[1] 1167    4

Other comments:

Using regular expressions (programming symbol pattern) will increase their functionality
Specified patterns are case sensitive (‘t’ does not equal ‘T’)
Any matching pattern will be returned despite the context in which that pattern is located (i.e., grep(‘the’, data) with return matches for ‘the’, ‘theme’, ‘heather’, ‘breathe’, and so on. This is where regular expressions are useful for specifying where in a string the pattern should appear.

5.2.3 Using regular expressions

A regular expression (regex or regexp for short) is a special text string for describing a search pattern. You can think of regular expressions as wildcards on steroids. You are probably familiar with wildcard notations such as *.txt to find all text files in a file manager.

Regular expressions are explained in the AWK and General notebook. But just to give an example lets just grep strains that have a number with 3 digits

If you want to know more, see this cheat sheet here

filter_3digits <- growth_data[grepl("[0-9]{3}", growth_data$Condition),]

#check the structure of our data
head(filter_3digits)

#check the dimensions of the original dataframe
dim(growth_data)

[1] 594   5

#check the dimensions of our new dataframe
dim(filter_3digits)

[1] 210   5

Here, [0-9] searches for every number from 0-9 and with {3} we look for three numbers in a row. 129E matches, but 13C does not, because it only has two numbers.

5.2.4 Levels and subsetting

One important thing with subsetting is that the levels are still kept. So with the command above we remove every row that is not 129E. However, the levels are still kept. Let’s check this:

levels(filter_3digits$Condition)

[1] "129E" "13C"  "mock"

So we see that 13C and mock are still in the levels even if they do not occur in our table itself. Sometimes when working with subsetted dataframes, i.e. when doing stats or plotting, this can interfere with our analysis. Here, it is useful to drop empty levels.

filter_3digits_clean <- droplevels(filter_3digits)
levels(filter_3digits_clean$Condition)

[1] "129E"

5.3 Add new columns to our data and combining values in different columns

We can also add new columns into original table, i.e. if we want to not show the fresh weight in mg but in g.

Below you can see that if we have numerical data in a column, we can use normal math operators (like +,-,/)

#convert mg to g (1 g = 1000 mg)
growth_data$FW_shoot_g <- growth_data$FW_shoot_mg/1000
head(growth_data)

#we can also round our data
growth_data$FW_shoot_g <- round(growth_data$FW_shoot_mg/1000, digits = 4)
head(growth_data)

We can also add (or substract, etc …) values from different columns. I.e. here we could calculate the ratios.

#we can also do math with the values in two columns, i.e. if we want to calculate the ratio between root length and fresh weight
growth_data$ratio <- growth_data$Rootlength/growth_data$FW_shoot_mg
head(growth_data)

In the rows where the shoot weight is missing, the ratio is NA too: any calculation with a missing value gives a missing value. Let’s check this for rows 32 and 33:

#rows with a missing shoot weight
growth_data[32:33, ]