How to Create Required Matrix Using Dataframe in R

Question

I have one dataframe which looks like:

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

From above dataframe i want below mentioned two matrix:

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

1. For calculation of mean_TAT: substract D2-D1 and than take mean for that day based on count value for the same date.
2. total_sum would be cumulative from the first date of the month.
3. count_A and sum_A based on type as A for the particular day.
4. count_other and sum_other for those where type is other than A.

2.Based of Month (Last Three month as per the dataframe)

For based on the Month the format would be same only calculation would be month basis.

• There are 5 additional rows and 2 columns for each month where first three would be the top 3 type_2 based on count for the particular month.
• increase_% would be calculated on previous month (i.e if count for May-17 is 50 than Apr-17 100 than there would be -50% and same for the other 5 rows based on their previous month count and sum.
• Fourth A would be constant for each month for the value where type_2 is "A".
• Fifth Other would be the other than those 4 type_2 as mentioned above.
• Total would be as per the column for count and sum there would be addition and for mean there would be mean.

It seems i couldn't explained properly, hope the matrix are understandable by dataframe.

Looking forward for some help.

Answer 1

Here's already the first part:

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

data

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

Some hints for part 2:

I can't do miracles without you reworking your questions (giving an accurate reproducible output is more than necessary here). But here's a way to get close, hopefully:

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)