r - 如何整齐地合并稀疏列

一位同事有一些数据，这些数据由许多稀疏列组成，应该将其折叠成几个填充列。例如:

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应该折叠到名为Town的一列中，X4:X5折叠到名为Store的一列中。这里必须有一个整洁的解决方案。我尝试了gather()和unite()，但没有找到任何优雅的方法。

最佳答案

您可以使用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