Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Remove columns of dataframe based on conditions in R

I have to remove columns in my dataframe which has over 4000 columns and 180 rows.The conditions I want to set in to remove the column in the dataframe are: (i) Remove the column if there are less then two values/entries in that column (ii) Remove the column if there are no two consecutive(one after the other) values in the column. (iii) Remove the column having all values as NA. I have provided with conditions on which a column is to be deleted. The aim here is not just to find a column by its name like in "How do you delete a column in data.table?". I Illustrate as follows:

A       B    C   D  E
0.018  NA    NA  NA NA
0.017  NA    NA  NA NA
0.019  NA    NA  NA NA
0.018  0.034 NA  NA NA
0.018  NA    NA  NA NA
0.015  NA    NA  NA 0.037
0.016  NA    NA  NA 0.031
0.019  NA    0.4 NA 0.025
0.016  0.03  NA  NA 0.035
0.018  NA    NA  NA 0.035
0.017  NA    NA  NA 0.043
0.023  NA    NA  NA 0.040
0.022  NA    NA  NA 0.042

Desired dataframe:

A       E
0.018   NA
0.017   NA
0.019   NA
0.018   NA
0.018   NA
0.015   0.037
0.016   0.031
0.019   0.025
0.016   0.035
0.018   0.035
0.017   0.043
0.023   0.040
0.022   0.042

How can I incoporate these three conditions in one code. I would appreciate your help in this regard. Reproducible example

structure(list(Month = c("Jan-2000", "Feb-2000", "Mar-2000", 
"Apr-2000", "May-2000", "Jun-2000"), A.G.L.SJ.INVS...LON..DEAD...13.08.15 = c(NA_real_, 
NA_real_, NA_real_, NA_real_, NA_real_, NA_real_), ABACUS.GROUP.DEAD...18.02.09 = c(0.00829384766220866, 
0.00332213653674028, 0, 0, NA, NA), ABB.R..IRS. = c(NA_real_, 
NA_real_, NA_real_, NA_real_, NA_real_, NA_real_)), .Names = c("Month", 
"A.G.L.SJ.INVS...LON..DEAD...13.08.15", "ABACUS.GROUP.DEAD...18.02.09", 
"ABB.R..IRS."), class = c("data.table", "data.frame"), row.names = c(NA, 
-6L), .internal.selfref = <pointer: 0x0000000001c90788>)
like image 342
Aquarius Avatar asked Jan 20 '16 14:01

Aquarius


People also ask

How do I remove a column from R based on condition?

By using the R base function subset() you can remove columns by name from the data frame. This function takes the data frame object as an argument and the columns you wanted to remove.

How do I remove rows from a Dataframe based on conditions in R?

For example, we can use the subset() function if we want to drop a row based on a condition. If we prefer to work with the Tidyverse package, we can use the filter() function to remove (or select) rows based on values in a column (conditionally, that is, and the same as using subset).

How do I delete certain columns in R?

The most easiest way to drop columns is by using subset() function. In the code below, we are telling R to drop variables x and z. The '-' sign indicates dropping variables. Make sure the variable names would NOT be specified in quotes when using subset() function.

How do I remove multiple columns in R?

We can delete multiple columns in the R dataframe by assigning null values through the list() function.


1 Answers

I feel like this is all over-complicated. Condition 2 already includes all the rest of the conditions, as if there are at least two non-NA values in a column, obviously the whole column aren't NAs. And if there are at least two consecutive values in a column, then obviously this column contains more than one value. So instead of 3 conditions, this all sums up into a single condition (I prefer not to run many functions per column, rather after running diff per column- vecotrize the whole thing):

cond <- colSums(is.na(sapply(df, diff))) < nrow(df) - 1

This works because if there are no consecutive values in a column, the whole column will become NAs.

Then, just

df[, cond, drop = FALSE]
#        A     E
# 1  0.018    NA
# 2  0.017    NA
# 3  0.019    NA
# 4  0.018    NA
# 5  0.018    NA
# 6  0.015 0.037
# 7  0.016 0.031
# 8  0.019 0.025
# 9  0.016 0.035
# 10 0.018 0.035
# 11 0.017 0.043
# 12 0.023 0.040
# 13 0.022 0.042

Per your edit, it seems like you have a data.table object and you also have a Date column so the code would need some modifications.

cond <- df[, lapply(.SD, function(x) sum(is.na(diff(x)))) < .N - 1, .SDcols = -1] 
df[, c(TRUE, cond), with = FALSE]

Some explanations:

  • We want to ignore the first column in our calculations so we specify .SDcols = -1 when operating on our .SD (which means Sub Data in data.tableis)
  • .N is just the rows count (similar to nrow(df)
  • Next step is to subset by condition. We need not forget to grab the first column too so we start with c(TRUE,...
  • Finally, data.table works with non standard evaluation by default, hence, if you want to select column as if you would in a data.frame you will need to specify with = FALSE

A better way though, would be just to remove the column by reference using := NULL

cond <- c(FALSE, df[, lapply(.SD, function(x) sum(is.na(diff(x)))) == .N - 1, .SDcols = -1])
df[, which(cond) := NULL]
like image 170
David Arenburg Avatar answered Sep 21 '22 23:09

David Arenburg