CODEWITHSUNDEEP
rdataframedplyr
Sophia Wilson
Sophia Wilson

Reputation: 477

Need to arrange dataframe as a summary dataframe using R

I am using the following dataframe in 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

The requirement is to merge the dcast dataframe in the following order to have a single view summary for an analysis.

I want to keep all the unique values static in the code, so that if the particular value is not available for a particular date I'll get 0 or - in summary table.

Desired Output:

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%

For seq we have % > 100 because of double counting of same uid for value FRD and FHL. That is the accepted scenario. In Total will have only distinct count of uid.

I'm using below-mentioned code by SO, but couldn't get the desired output.

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())

Upvotes: 0

Views: 157

Answers (1)

hello_friend
hello_friend

Reputation: 5798

Base R solution (note: I'm not entirely sure I understand your question): 

  # 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)

Data: 

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")

Upvotes: 2

Related Questions