需要使用R将数据作为摘要数据排列

Question

我在R中使用以下数据。

dput：

structure(list(uid = c("K-1", "K-1", 
"K-2", "K-3", "K-4", "K-5", 
"K-6", "K-7", "K-8", "K-9", 
"K-10", "K-11", "K-12", "K-13", 
"K-14"), Date = c("2020-03-16 12:11:33", "2020-03-16 12:11:33", 
"2020-03-16 06:13:55", "2020-03-16 10:03:43", "2020-03-16 12:37:09", 
"2020-03-16 06:40:24", "2020-03-16 09:46:45", "2020-03-16 12:07:44", 
"2020-03-16 14:09:51", "2020-03-16 09:19:23", "2020-03-16 09:07:37", 
"2020-03-16 11:48:34", "2020-03-16 06:23:24", "2020-03-16 04:39:03", 
"2020-03-16 04:59:13"), batch_no = c(7, 7, 8, 9, 9, 8, 
7, 6, 7, 9, 8, 8, 7, 7, 7), marking = c("S1", "S1", "S2", 
"SE_hold1", "SD_hold1", "SD_hold2", "S3", "S3", "", "SA_hold3", "S1", "S1", "S2", 
"S3", "S3"), seq = c("FRD", 
"FHL", NA, NA, NA, NA, NA, NA, "ABC", NA, NA, NA, NA, "DEF", NA)), .Names = c("uid", 
"Date", "batch_no", "marking",
"seq"), row.names = c(NA, 15L), class = "data.frame")






uid     Date                  batch_no       marking       seq
K-1     16/03/2020  12:11:33  7              S1            FRD
K-1     16/03/2020  12:11:33  7              S1            FHL
K-2     16/03/2020  12:11:33  8              SE_hold1      ABC
K-3     16/03/2020  12:11:33  9              SD_hold2      DEF
K-4     16/03/2020  12:11:33  8              S1            XYZ
K-5     16/03/2020  12:11:33                 NA            ABC
K-6     16/03/2020  12:11:33  7                            ZZZ
K-7     16/03/2020  12:11:33  NA             S2            NA
K-8     16/03/2020  12:11:33  6              S3            FRD

date.

• batch_no列将有八个唯一值(包括NA )，而不需要所有8个值，因为每天的seq将有6个惟一值，包括NA和空格，而不需要所有六个值都可用于每天的日期。

• marking列将有~25个唯一值，但需要将带有后缀_hold#的值考虑为Hold之后，将出现6个包含空格和NA.

的唯一值。

要求将dcast数据按以下顺序合并，以便有一个单独的视图摘要进行分析。

我希望在代码中保持所有唯一的值是静态的，这样如果某个特定日期的特定值不可用，我将得到0或- in汇总表。

期望产出：

seq      count  percentage   Marking     count     Percentage     batch_no   count    Percentage
FRD      1      12.50%       S1          2         25.00%         6          1        12.50%
FHL      1      12.50%       S2          1         12.50%         7          2        25.00%
ABC      2      25.00%       S3          1         12.50%         8          2        25.00%
DEF      1      12.50%       Hold        2         25.00%         9          1        12.50%
XYZ      1      12.50%       NA          1         12.50%         NA         1        12.50%
ZZZ      1      12.50%       (Blank)     1         12.50%         (Blank)    1        12.50%
FRD      1      12.50%         -         -           -             -         -           -
NA       1      12.50%         -         -           -             -         -           -
(Blank)  0      0.00%          -         -           -             -         -           -
Total    8      112.50%        -         8         100.00%         -         8         100.00%

对于seq，我们有%> 100，因为对值FRD和FHL重复计算相同的uid。这是公认的情况。在Total中，uid的计数将是不同的。

我正在使用下面提到的代码，但是无法获得所需的输出。

df = df_original %>% 
  mutate(marking = if_else(str_detect(marking,"hold"),"Hold", marking)) %>% 
  mutate_at(vars(c("seq", "batch_no", "marking")), forcats::fct_explicit_na, na_level = "(Blank)") 

## You Need to do something similar with vectors of the possible values
df_combinations = purrr::cross_df(list(seq = df$seq %>% unique(),
                     batch_no = df$batch_no %>% unique(),
                     marking = df$marking %>% unique()))

df_all_combination = df_combinations %>% 
  left_join(df, by = c("seq", "batch_no", "marking")) %>% 
  group_by(seq, batch_no, marking) %>% 
  summarise(count = n())

Answer 1

基本R解决方案(注:我不完全理解你的问题)：

  # Function to summarise each of the vectors required: summariser => function
summariser <- function(vec) {
  within(unique(data.frame(
    vec = vec,
    counter = as.numeric(ifelse(is.na(vec), sum(is.na(vec)),
                     ave(vec, vec, FUN = length))), stringsAsFactors = FALSE
  )),
  {
    perc = paste0(round(counter / sum(counter) * 100, 2), "%")
  })
}
# Vectors to summarise: vecs_to_summarise => character vector
vecs_to_summarise <- c("seq", "marking", "batch_no")

# Create an empty list in order to allocate some memory: df_list => list
df_list <- vector("list", length(vecs_to_summarise))

# Apply the summariser function to each of the vectors required: df_list => list of dfs
df_list <- lapply(df[,vecs_to_summarise], summariser)

# Rename the vectors of each data.frame in the list: df_list => list of dfs: 
df_list <- lapply(seq_along(df_list), function(i) {
  names(df_list[[i]]) <- gsub("_vec", "",
                              paste(names(df_list[i]), names(df_list[[i]]), sep = "_"))
  return(df_list[[i]])
})


# Determine the number of rows of the maximum data.frame: numeric scalar 
max_df_length <- max(sapply(df_list, nrow))

# Extend each data.frame to be the same length (pad with NAs if necessary): df_list => list
df_list <- lapply(seq_along(df_list), function(i){
  y <- data.frame(df_list[[i]][rep(seq_len(nrow(df_list[[1]])), each = 1),])
  y[1:(nrow(y)),] <- NA
  y <- y[1:(max_df_length - nrow(df_list[[i]])),]
  if(length(y) > 0){
    x <- data.frame(rbind(df_list[[i]], y)[1:max_df_length,])
  }else{
      x <- data.frame(df_list[[i]][1:max_df_length,])
      }
  return(x)
  }
)

# Bind the data.frames in the list into a single df: analysed_df => data.frame
analysed_df <- do.call("cbind", df_list)

数据：

df <- structure(list(uid = c("K-1", "K-1", "K-2", "K-3", "K-4", "K-5", 
    "K-6", "K-7", "K-8"), Date = structure(c(1584321093, 1584321093, 
    1584321093, 1584321093, 1584321093, 1584321093, 1584321093, 1584321093, 
    1584321093), class = c("POSIXct", "POSIXt"), tzone = ""), batch_no = c(7L, 
    7L, 8L, 9L, 8L, NA, 7L, NA, 6L), marking = c("S1", "S1", "SE_hold1", 
    "SD_hold2", "S1", NA, NA, "S2", "S3"), seq = c("FRD", "FHL", 
    "ABC", "DEF", "XYZ", "ABC", "ZZZ", NA, "FRD")), row.names = c(NA, 
    -9L), class = "data.frame")