How to neatly combine sparse columns
A colleague has some data consisting of many sparse columns that needs to be collapsed into multiple filled columns. For example:
d1 <- data.frame(X1 = c(rep("Northampton", times=3), rep(NA, times=7)),
X2 = c(rep(NA, times=3), rep("Amherst", times=5), rep(NA, times=2)),
X3 = c(rep(NA, times=8), rep("Hadley", times=2)),
X4 = c(rep("Stop and Shop", times=2), rep(NA, times=6), rep("Stop and Shop", times=2)),
X5 = c(rep(NA, times=2), rep("Whole Foods", times=6), rep(NA, times=2)))
d1
X1 X2 X3 X4 X5
1 Northampton <NA> <NA> Stop and Shop <NA>
2 Northampton <NA> <NA> Stop and Shop <NA>
3 Northampton <NA> <NA> <NA> Whole Foods
4 <NA> Amherst <NA> <NA> Whole Foods
5 <NA> Amherst <NA> <NA> Whole Foods
6 <NA> Amherst <NA> <NA> Whole Foods
7 <NA> Amherst <NA> <NA> Whole Foods
8 <NA> Amherst <NA> <NA> Whole Foods
9 <NA> <NA> Hadley Stop and Shop <NA>
10 <NA> <NA> Hadley Stop and Shop <NA>
X1:X3
must be collapsed into one column named City and X4:X5
one column named Store. There should be a reverse solution here. I tried with gather()
and unite()
but found nothing.
source to share
You can use coalesce
:
d1 %>% mutate_if(is.factor, as.character) %>% # coerce explicitly
transmute(town = coalesce(X1, X2, X3),
store = coalesce(X4, X5))
## town store
## 1 Northampton Stop and Shop
## 2 Northampton Stop and Shop
## 3 Northampton Whole Foods
## 4 Amherst Whole Foods
## 5 Amherst Whole Foods
## 6 Amherst Whole Foods
## 7 Amherst Whole Foods
## 8 Amherst Whole Foods
## 9 Hadley Stop and Shop
## 10 Hadley Stop and Shop
source to share
I think the call sequence gather()
and some cropping will get you what you want. One wrinkle is to use the argument na.rm = TRUE
for gather()
to discard unwanted lines.
d1 %>%
gather(key = "town", value = "town_name", X1:X3, na.rm = TRUE) %>%
gather(key = "store", value = "store_name", X4:X5, na.rm = TRUE) %>%
select(-town, -store)
Is this a trick?
source to share
You can also do it in R base with apply
run rowwise:
d2 <- data.frame(X1 = apply(d1[,c("X1", "X2", "X3")], 1, function(x) x[!is.na(x)]),
X2 = apply(d1[,c("X4", "X5")], 1, function(x) x[!is.na(x)]),
stringsAsFactors = FALSE)
Result:
> d2
X1 X2
1 Northampton Stop and Shop
2 Northampton Stop and Shop
3 Northampton Whole Foods
4 Amherst Whole Foods
5 Amherst Whole Foods
6 Amherst Whole Foods
7 Amherst Whole Foods
8 Amherst Whole Foods
9 Hadley Stop and Shop
10 Hadley Stop and Shop
source to share
Here's another way base R
usingpmax/pmin
data.frame(lapply(list(Town = d1[1:3], Store = d1[4:5]), function(x)
do.call(pmax, c(x, na.rm = TRUE))), stringsAsFactors=FALSE)
# Town Store
#1 Northampton Stop and Shop
#2 Northampton Stop and Shop
#3 Northampton Whole Foods
#4 Amherst Whole Foods
#5 Amherst Whole Foods
#6 Amherst Whole Foods
#7 Amherst Whole Foods
#8 Amherst Whole Foods
#9 Hadley Stop and Shop
#10 Hadley Stop and Shop
data
d1 <- data.frame(X1 = c(rep("Northampton", times=3),rep(NA, times=7)),
X2 = c(rep(NA, times=3), rep("Amherst", times=5), rep(NA, times=2)),
X3 = c(rep(NA, times=8), rep("Hadley", times=2)),
X4 = c(rep("Stop and Shop", times=2), rep(NA, times=6), rep("Stop and Shop", times=2)),
X5 = c(rep(NA, times=2), rep("Whole Foods", times=6),
rep(NA, times=2)), stringsAsFactors=FALSE)
source to share