I have a data table that looks like this:
old1 old2 old3 old4
aaa ccc
aaa bbb
bbb ccc ddd
I want to remove the empty columns to have something like this:
new1 new2 new3
aaa ccc
aaa bbb
bbb ccc ddd
I've tried the following which does not work for me:
df[, colSums(df!= "") != ""]
df[!sapply(df, function (x) all(is.na(x) | x == ""))]
Filter(function(x) !(all(x==""|x==0)), df)
One option using base R apply is to first calculate number of columns which are going to be present in the final dataframe (cols). Filter empty values from each row and insert empty values using rep.
cols <- max(rowSums(df != ""))
as.data.frame(t(apply(df, 1, function(x) {
vals <- x[x != ""]
c(vals, rep("", cols - length(vals)))
})))
# V1 V2 V3
#1 aaa ccc
#2 aaa bbb
#3 bbb ccc ddd
Another option with gather/spread would be to add a new column for row number convert it to long format using gather, filter the non-empty values, group_by every row and give new column names using paste0 and finally convert it to wide format using spread.
library(dplyr)
library(tidyr)
df %>%
mutate(row = row_number()) %>%
gather(key, value, -row) %>%
filter(value != "") %>%
group_by(row) %>%
mutate(key = paste0("new", row_number())) %>%
spread(key, value, fill = "") %>%
ungroup() %>%
select(-row)
# new1 new2 new3
# <chr> <chr> <chr>
#1 aaa ccc ""
#2 aaa bbb ""
#3 bbb ccc ddd