r - 在 R 中生成综合报告格式

Question

我已将 MySQL 服务器的一些信息提取到 R 中，在我的 R 数据框中如下所示:

barcode_no   Inspection_date        current_profile      score    Tag_log   prod_log
12345678     2020-01-15 14:34:13    Large                10       C1        WIP
12345678     2020-01-15 18:33:11    Medium               20       C2        Hold
12345678     2020-01-15 13:23:24    Medium               50       C3        Hold
12345678     2020-01-15 12:12:23    Medium               70                 Shipped
12345678     2020-01-15 11:12:45    Medium               120      C1        Shipped
12345678     2020-01-15 12:22:32    Small                150      C2        Shipped
12345678     2020-01-15 15:23:23    Small                10       C3        WIP
12345678     2020-01-15 16:34:08    Small                20       C2        Hold
12345678     2020-01-15 17:07:13    Small                130      C1        Hold
12345678     2020-01-15 17:09:05    Small                40                 Hold 

要求是将上述数据帧的详细信息适合日期和蛾类的综合报告结构。

综合_df(日期):如果该日期的部分或全部记录不可用，将根据系统日期考虑最新日期，然后用0填充综合报告df。

Current_profile     # of records  % of records C1 C2 C3 [Null] # of records  % of records C1 C2 C3 [Null] # of records  % of records C1 C2 C3 [Null] Total    % Total
**Large               01            16.67        1  0  0  0      0             0            0   0  0    0      0             0            0  0  0   0     1        10.00**
Shipped             0             0.0          0  0  0  0      0             0            0   0  0    0      0             0            0  0  0   0     0        0.0
Hold                0             0.0          0  0  0  0      0             0            0   0  0    0      0             0            0  0  0   0     0        0.0
WIP                 01             1.0         1  0  0  0      0             0            0   0  0    0      0             0            0  0  0   0     1        100.00
**Small               03            50.00        0  1  1  1      0             0            0   0  0    0     02             66.67        1  1  0   0     5        50.00**
Shipped             0             0            0  0  0  0      0             0            0   0  0    0     01             50.00        0  1  0   0     1        20.00
Hold                02            66.67        0  1  0  1      0             0            0   0  0    0      1             100.00       1  0  0   0     3        60.00
WIP                 01            33.33        1  0  0  0      0             0            0   0  0    0      0             0            0  0  0   0     1        20.00
**Medium              02            33.33        0  1  1  0      1             100.00       0   0  0    1      1             33.33        1  0  0   0     4        40.00**
Shipped             0              0           0  0  0  0      1             100.00       0   0  0    1      1             100.00       0  0  0   0     2        50.00
Hold                2            100.00        0  1  1  0      0             0            0   0  0    0      0             0            0  0  0   0     2        50.00
WIP                 0            0             0  0  0  0      0             0            0   0  0    0      0             0            0  0  0   0     0        0
Total               06            0.10         1  0  0  0      1             0            0   0  0    0      3             0            0  0  0   0     1        0.10

我将综合数据框分成几部分，其中第 2 到 7 列表示得分从 0 到 <= 50 的计数，第 8 到 13 列表示得分从 50 到 100 的计数，第 14 到 20 列表示那些得分大于 100 的计数。

我正在尝试的代码:

df1<- df %>%
  mutate(Month = format(ymd(Inspection_date),'%b-%Y')) %>%
  group_by(Month) %>%
  dplyr::summarise(`current_profile` = n())

df2<- df %>%
  mutate(Month = format(ymd(Inspection_date),'%b-%Y')) %>%
  group_by(Month) %>%
  dplyr::summarise(`Tag_log` = n())

df3<- df %>%
  mutate(Month = format(ymd(Inspection_date),'%b-%Y')) %>%
  group_by(Month) %>%
  dplyr::summarise(`prod_log` = n())

对每个变量依此类推。然后尝试通过 full_join 对所有数据框进行 Date ojit_code for Date wise综合格式和month for month wise综合格式。

comprehensive_df <- df1 %>% full_join(df1, by = 'Month') %>% 
                      full_join(df2, by = 'Month') %>%
                      full_join(df3, by = 'Month')

Answer 1

我不确定我明白你需要什么，但也许是这样的？

library(magrittr)
library(lubridate)
#> 
#> Attaching package: 'lubridate'
#> The following object is masked from 'package:base':
#> 
#>     date

dat <- tibble::tribble(
  ~barcode_no, ~Inspection_date, ~current_profile, ~score, ~Tag_log, ~prod_log,
    12345678L,     "15/01/2020",          "Large",    10L,     "C1",     "WIP",
    12345678L,     "15/01/2020",         "Medium",    20L,     "C2",    "Hold",
    12345678L,     "15/01/2020",         "Medium",    50L,     "C3",    "Hold",
    12345678L,     "15/01/2020",         "Medium",    70L,       NA, "Shipped",
    12345678L,     "15/01/2020",         "Medium",   120L,     "C1", "Shipped",
    12345678L,     "15/01/2020",          "Small",   150L,     "C2", "Shipped",
    12345678L,     "15/01/2020",          "Small",    10L,     "C3",     "WIP",
    12345678L,     "15/01/2020",          "Small",    20L,     "C2",    "Hold",
    12345678L,     "15/01/2020",          "Small",   130L,     "C1",    "Hold",
    12345678L,     "15/01/2020",          "Small",    40L,       NA,    "Hold"
  )



dat$Inspection_date = as.Date(dat$Inspection_date,format = "%d/%m/%Y")

today = Sys.Date()

param_date = as.Date("15/01/2020",format = "%d/%m/%Y")

dat$month = format(ymd(dat$Inspection_date),'%b-%Y')

dat$score_group = dplyr::case_when(
  dat$score <= 50 ~ "low",
  dat$score < 100 ~ "med",
  TRUE ~ "high"
)

dat %>% dplyr::filter(Inspection_date >= param_date) %>%
  dplyr::group_by(current_profile, month, score_group, Tag_log,prod_log) %>% 
  dplyr::summarise(count = dplyr::n()) %>% 
  tidyr::pivot_wider(names_from = c("score_group","Tag_log"),
                     values_from = count,
                     values_fill  = list(count = 0)) -> res_dat


knitr::kable(res_dat,format = "markdown")

|current_profile |month    |prod_log | low_C1| high_C1| low_C2| low_C3| med_NA| high_C2| low_NA|
|:---------------|:--------|:--------|------:|-------:|------:|------:|------:|-------:|------:|
|Large           |Jan-2020 |WIP      |      1|       0|      0|      0|      0|       0|      0|
|Medium          |Jan-2020 |Shipped  |      0|       1|      0|      0|      1|       0|      0|
|Medium          |Jan-2020 |Hold     |      0|       0|      1|      1|      0|       0|      0|
|Small           |Jan-2020 |Hold     |      0|       1|      1|      0|      0|       0|      1|
|Small           |Jan-2020 |Shipped  |      0|       0|      0|      0|      0|       1|      0|
|Small           |Jan-2020 |WIP      |      0|       0|      0|      1|      0|       0|      0|