代码之家 › 专栏 › 技术社区 › Jake

在R的数据帧中按ID折叠数据

collapse dplyr r

1

Jake · 技术社区 · 8 年前

我有数据框:

ID <- c(1,1,1,1,2,2,2,3,3,3,4,4,4)
Gender <- c("M","M","M","M","F","F",'F',"F","F","F", "M", "M", "M")
Test1 <- c("70", "NA", "NA", "NA", "NA", "85", "NA", "NA", "90", "NA", "NA", "NA", "90")
Test2 <- c("NA", "60", "NA", "NA", "NA", "NA", "82", "NA", "NA", "87", "NA", "88", "NA")

df <- data.frame(ID, Gender, Test1, Test2)

   ID Gender Test1 Test2
1   1      M    70    NA
2   1      M    NA    60
3   1      M    NA    NA
4   1      M    NA    NA
5   2      F    NA    NA
6   2      F    85    NA
7   2      F    NA    82
8   3      F    NA    NA
9   3      F    90    NA
10  3      F    NA    87
11  4      M    NA    NA
12  4      M    NA    88
13  4      M    90    NA

我希望得到帮助,了解如何在ID和性别之间折叠数据,以便每个ID有一行。如下所示:

  id gender test1 test2
1  1      M    70    60
2  2      F    85    82
3  3      F    90    87
4  4      M    90    88

任何帮助都将不胜感激!

3 回复 | 直到 8 年前

1

3

OzanStats 8 年前

# convert the type from factor to integer
# this step is not necessary if you create these columns of type integer
df$Test1 <- as.integer(as.character(df$Test1))
df$Test2 <- as.integer(as.character(df$Test2))

# choose non-NA value for each (ID, gender) combination
# the function max is interchangeable, you just need the NA treatment 
df %>%
  group_by(ID, Gender) %>%
  summarise(
    Test1 = max(Test1, na.rm = T),
    Test2 = max(Test2, na.rm = T)
  )

# # A tibble: 4 x 4
# # Groups:   ID [?]
#      ID Gender Test1 Test2
#   <dbl> <fct>  <int> <int>
# 1     1 M         70    60
# 2     2 F         85    82
# 3     3 F         90    87
# 4     4 M         90    88

通过一些类型调整:

# create the example data with suitable column types
df <- data_frame(
  ID = c(rep(1, 4), rep(2:4, each = 3)),
  Gender = c(rep("M", 4), rep("F", 6), rep("M", 3)),
  Test1 = c(70, rep(NA, 4), 85, rep(NA, 2), 90, rep(NA, 3), 90),
  Test2 = c(NA, 60, rep(NA, 4), 82, rep(NA, 2), 87, NA, 88, NA)
)

df %>%
  group_by(ID, Gender) %>%
  summarise(
    Test1 = max(Test1, na.rm = T),
    Test2 = max(Test2, na.rm = T)
  )

2

Christian Million 8 年前

我通过添加 stringAsFactors = FALSE data.frame 争论。如果这个解决方案对你有用,请告诉我:

df <- data.frame(ID = c(1,1,1,1,2,2,2,3,3,3,4,4,4),
                 Gender = c("M","M","M","M","F","F",'F',"F","F","F", "M", "M", "M"),
                 Test1 = c("70", "NA", "NA", "NA", "NA", "85", "NA", "NA", "90", "NA", "NA", "NA", "90"),
                 Test2 = c("NA", "60", "NA", "NA", "NA", "NA", "82", "NA", "NA", "87", "NA", "88", "NA"),
                 stringsAsFactors = FALSE)

library(dplyr)
library(tidyr)

new_df <- df %>%
          gather(key = "test_num", value = "score", Test1, Test2)%>%
          filter(score != "NA")%>%
          spread(test_num, score)

3

1

Roman LuÅ¡trik 8 年前

下面是一个将Test1和Test2的值粘贴在一起的解决方案。即使Test1和Test2有多个性别和值,这也会起作用,但将这些值保留为因子。

df$Test1 <- as.integer(as.character(df$Test1))
df$Test2 <- as.integer(as.character(df$Test2))

xy <- sapply(split(df, f = df$ID), FUN = function(x) {
  out <- data.frame(ID = unique(x$ID),
                    Gender = paste(unique(x$Gender), collapse = ", "),
                    Test1 = paste(unique(na.omit(x$Test1)), collapse = ","),
                    Test2 = paste(unique(na.omit(x$Test2)), collapse = ","))
  out
}, simplify = FALSE)

xy <- do.call(rbind, xy)

  ID Gender Test1 Test2
1  1      M    70    60
2  2      F    85    82
3  3      F    90    87
4  4      M    90    88