r - 如何在 R 中使用 Dataframe 创建所需的矩阵

Question

我有一个数据框，它看起来像:

DF_1>

T_id  D1             D2                   Num     type    type_2     fig
xt-1  2017-05-01     2017-03-25 12:11:45  10      A       X          25.20
xt-2  2017-05-01     2017-03-25 21:05:25  20      A       Y          20.15
xt-3  2017-05-01     2017-03-25 08:10:55  25      B       X          15.11
xt-4  2017-05-03     2017-03-25 07:19:35  30      B       Y          22.56
xt-5  2017-05-03     2017-03-25 13:12:56  45      C       Z          35.45
xt-6  2017-05-03     2017-03-25 18:14:44  20      D       Z          27.21
xt-7  2017-04-06     2017-03-25 19:21:35  15      A       Z          23.20
xt-8  2017-04-06     2017-03-25 21:11:15  40      C       X          21.40
xt-9  2017-04-08     2017-02-25 22:25:04  20      A       A          27.50
xt-10 2017-04-06     2017-02-25 16:04:08  30      A       Y          32.20
xt-11 2017-04-05     2017-02-25 18:15:25  20      C       Z          30.20
xt-12 2017-04-01     2017-01-25 19:22:25  50      A       Z          33.15
xt-13 2017-04-02     2017-01-25 23:19:05  15      A       A          30.12
xt-14 2017-03-03     2017-01-25 14:25:09  15      D       Y          31.25
xt-15 2017-03-10     2017-01-25 23:25:36  40      A       X          25.45

从上面的数据帧我想要下面提到的两个矩阵:

1. Date (Last Three Date from `sys.date()`)

    D1    count  sum  mean_num total_sum count_A sum_A count_other sum_other mean_fig   mean_TAT

    2017-05-03 3 95  31.66     6         0       0     3           95        28.40
    2017-05-02 0 0   0         3         0       0     0           0         0.00
    2017-05-01 3 55  18.33     3         2       30    1           25        20.15

用于计算 mean_TAT :减去D2 - D1而不是拿
当天的平均值基于 count同一日期的值。

total_sum将从该月的第一个日期开始累积。

count_A和 sum_A基于 type如 A对于特定的一天。

count_other和 sum_other对于那些type不是 A .

2.基于月份(根据数据帧的最后三个月)


对于基于月份的格式将是相同的，只是计算将以月份为基础。

每个月还有 5 行和 2 列，其中前三行是前 3 行 type_2基于特定月份的计数。

increase_%将在上个月计算(即，如果 5 月 17 日的 count 是 50 比 4 月 17 日的 100，则其他 5 行基于上个月 count 和 sum 的值为 -50% 和相同。

第四 A对于 type_2 的值，每个月都是常数是“A”。

第五个Other将是除了那些 4 type_2正如刚才提到的。

Total将按照 count 的列和 sum会有加法和为 mean会有意思。

似乎我无法正确解释，希望数据框可以理解矩阵。

期待一些帮助。

Answer 1

这已经是第一部分:

library(lubridate)
library(dplyr)

df2 <- df1 %>%
  mutate(ym = year(D1)*100+month(D1)) %>%
  arrange(D1) %>%
  group_by(D1,ym) %>%
  summarize(count = n(),
            sum=sum(Num),
            mean_num=mean(Num),
            count_A=sum(type=='A'),
            sum_A=sum(Num * (type=='A')),
            count_other=sum(type!='A'),
            sum_other=sum(Num * (type!='A')),
            mean_fig = mean(fig),
            mean_TAT = mean(D2-D1)) %>%
  group_by(ym) %>%
  mutate(total_sum=cumsum(count)) %>%
  ungroup %>%
  arrange(desc(D1)) %>%
  select(D1,count,sum,mean_num,total_sum,count_A,sum_A,count_other,sum_other,mean_fig,mean_TAT)


# # A tibble: 9 x 11
# D1 count   sum mean_num total_sum count_A sum_A count_other sum_other mean_fig       mean_TAT
# <date> <int> <int>    <dbl>     <int>   <int> <int>       <int>     <int>    <dbl>         <time>
# 1 2017-05-03     3    95 31.66667         6       0     0           3        95 28.40667 -39.00000 days
# 2 2017-05-01     3    55 18.33333         3       2    30           1        25 20.15333 -37.00000 days
# 3 2017-04-08     1    20 20.00000         7       1    20           0         0 27.50000 -42.00000 days
# 4 2017-04-06     3    85 28.33333         6       2    45           1        40 25.60000 -21.33333 days
# 5 2017-04-05     1    20 20.00000         3       0     0           1        20 30.20000 -39.00000 days
# 6 2017-04-02     1    15 15.00000         2       1    15           0         0 30.12000 -67.00000 days
# 7 2017-04-01     1    50 50.00000         1       1    50           0         0 33.15000 -66.00000 days
# 8 2017-03-10     1    40 40.00000         2       1    40           0         0 25.45000 -44.00000 days
# 9 2017-03-03     1    15 15.00000         1       0     0           1        15 31.25000 -37.00000 days

数据

df1 <- read.table(text="T_id  D1             D2                   Num     type    type_2     fig
                  xt-1  2017-05-01     '2017-03-25 12:11:45'  10      A       X          25.20
                  xt-2  2017-05-01     '2017-03-25 21:05:25'  20      A       Y          20.15
                  xt-3  2017-05-01     '2017-03-25 08:10:55'  25      B       X          15.11
                  xt-4  2017-05-03     '2017-03-25 07:19:35'  30      B       Y          22.56
                  xt-5  2017-05-03     '2017-03-25 13:12:56'  45      C       Z          35.45
                  xt-6  2017-05-03     '2017-03-25 18:14:44'  20      D       Z          27.21
                  xt-7  2017-04-06     '2017-03-25 19:21:35'  15      A       Z          23.20
                  xt-8  2017-04-06     '2017-03-25 21:11:15'  40      C       W          21.40
                  xt-9  2017-04-08     '2017-02-25 22:25:04'  20      A       Q          27.50
                  xt-10 2017-04-06     '2017-02-25 16:04:08'  30      A       W          32.20
                  xt-11 2017-04-05     '2017-02-25 18:15:25'  20      C       V          30.20
                  xt-12 2017-04-01     '2017-01-25 19:22:25'  50      A       Z          33.15
                  xt-13 2017-04-02     '2017-01-25 23:19:05'  15      A       Z          30.12
                  xt-14 2017-03-03     '2017-01-25 14:25:09'  15      D       Y          31.25
                  xt-15 2017-03-10     '2017-01-25 23:25:36'  40      A       X          25.45",h=T,strin=F)

df1$D1 <- as.Date(df1$D1,"%Y-%m-%d")
df1$D2 <- as.Date(df1$D2,"%Y-%m-%d")

expected_output <- read.table(text="D1    count  sum  mean_num total_sum count_A sum_A count_other sum_other mean_fig
                     2017-05-03 3 95  31.66     6         0       0     3           95        28.40
                     2017-05-02 0 0   0         3         0       0     0           0         0.00
                     2017-05-01 3 55  18.33     3         2       30    1           25        20.15")

第 2 部分的一些提示:

如果没有您重新解决问题，我无法创造奇迹(在这里提供准确的可重复输出是不必要的)。但这是一种接近的方法，希望:

df_month <- df1 %>%
  mutate(ym = year(D1)*100+month(D1)) %>%
  arrange(D1) %>%
  group_by(ym) %>%
  summarize(count = n(),
            sum=sum(Num),
            mean_num=mean(Num),
            count_A=sum(type=='A'),
            sum_A=sum(Num * (type=='A')),
            count_other=sum(type!='A'),
            sum_other=sum(Num * (type!='A')),
            mean_fig = mean(fig),
            mean_TAT = mean(D2-D1)) %>%
  mutate(type_2=paste0(month.abb[ym%% 100],"-",ym %/% 100 -2000)) %>%
  select(ym,type_2,count,sum,mean_num,count_A,sum_A,count_other,sum_other,mean_fig,mean_TAT)


df_top3 <- df1 %>%
  filter(type_2 !="A") %>%
  mutate(ym = year(D1)*100+month(D1)) %>%
  arrange(desc(ym)) %>%
  group_by(ym,type_2) %>%
  summarize(count = n(),
            sum=sum(Num),
            mean_num=mean(Num),
            count_A=sum(type=='A'),
            sum_A=sum(Num * (type=='A')),
            count_other=sum(type!='A'),
            sum_other=sum(Num * (type!='A')),
            mean_fig = mean(fig),
            mean_TAT = mean(D2-D1)) %>%
  group_by(ym) %>%
  arrange(desc(count)) %>%
  slice(1:3) %>%
  ungroup %>%
  select(ym,type_2,count,sum,mean_num,count_A,sum_A,count_other,sum_other,mean_fig,mean_TAT)


df_A <- df1 %>%
  filter(type_2 == "A") %>%
  mutate(ym = year(D1)*100+month(D1)) %>%
  arrange(desc(ym)) %>%
  group_by(ym,type_2) %>%
  summarize(count = n(),
            sum=sum(Num),
            mean_num=mean(Num),
            count_A=sum(type=='A'),
            sum_A=sum(Num * (type=='A')),
            count_other=sum(type!='A'),
            sum_other=sum(Num * (type!='A')),
            mean_fig = mean(fig),
            mean_TAT = mean(D2-D1)) %>%
  select(ym,type_2,count,sum,mean_num,count_A,sum_A,count_other,sum_other,mean_fig,mean_TAT)



df_other <- df1 %>%
  mutate(ym = year(D1)*100+month(D1)) %>%
  anti_join(bind_rows(df_top3,df_A),by = c("ym","type_2")) %>%
  mutate(type_2="Other") %>%
  arrange(desc(ym)) %>%
  group_by(ym,type_2) %>%
  summarize(count = n(),
            sum=sum(Num),
            mean_num=mean(Num),
            count_A=sum(type=='A'),
            sum_A=sum(Num * (type=='A')),
            count_other=sum(type!='A'),
            sum_other=sum(Num * (type!='A')),
            mean_fig = mean(fig),
            mean_TAT = mean(D2-D1)) %>%
  select(ym,type_2,count,sum,mean_num,count_A,sum_A,count_other,sum_other,mean_fig,mean_TAT)
# it's empty with your example data


bind_rows(df_month,df_top3,df_A,df_other) %>%
  arrange(ym) %>%
  select(-ym) %>%
  rename(Month = type_2)