# Removing empty columns from a dataset

I am taking a class in data mining and I am working on a term project using the BRFSS dataset. I have a huge dataset with 405 columns and 12,000 rows. There are many columns which are completely empty. I was trying to remove empty columns using SAS, R or Excel but it doesn't work. Could you suggest a method to remove the empty columns or any tutorial that will help me cleaning up the data? There are a lot of missing cells too. I am using KNIME to train my data and it doesn't work if there are missing values. How can I handle the missing values?

-
In SAS or R you need a few lines of code to remove empty columns, but how come it did not work in Excel? Have you tried right-clicking the header of the empty column and selecting delete? –  GaBorgulya Apr 2 '11 at 0:13
SAS uses keep and drop which are about as simple as it gets. What exactly "doesn't work"? –  whuber Apr 2 '11 at 4:39
@GaBorgulya: It is a huge dataset and it is not possible to manually identify columns which are empty. I was thinking of a way in which we can automatically remove empty columns and also generate a list of these columns for ananlysis. I am not sure how to do it automatically in excel and SAS. –  user3897 Apr 2 '11 at 13:21
@whuber: I have to do it automatically since it is a huge dataset. SAS should go over the data automatically and remove the empty columns and also generate a list of those empty columns for analysis –  user3897 Apr 2 '11 at 13:22
@user In other words, your question is how to identify empty columns, not how to remove them. –  whuber Apr 2 '11 at 15:52

Empty columns contain NAs only or ""s only, they have has no variability. This code removes all columns without variability (which is probably a plus in this case).

d=data.frame(r=seq(1, 5), a=rep('a', 5), n=rep(NA, 5), n1=c(NA, NA, 3, 3, 3))

homogenous = apply(d, 2, function(var) length(unique(var)) == 1)
d[, !homogenous]

-
Thank You. This works. –  user3897 Apr 9 '11 at 0:11

If they really are just completely blank columns then in R...

read.table( 'myBigFile', strip.white = TRUE)


might do what you want. You will have to set other arguments of the read.table() command as needed. It's best to use this when specifying the specific column delimiter you have.

-

Maybe just for completeness' sake:
df[, colSums( is.na(df) ) < nrow(df)] returns only columns with at least one nonmissing value.

And what I do prior to clearing columns without variability is this

 df <- df[,sapply(df,is.character)] = colwise(       # replace empty or white-space-only
function (x) str_replace(x,"^\\s*\$",NA))        # character columns with NA
(df[,sapply(df,is.character)])                  # uses stringr for str_replace

df <- df[, colwise(function(x){
length (unique(x)) })(df)!=1]               # I like writing it like this for
# conciseness, uses plyr for colwise

-