CODEWITHSUNDEEP
rdataframeggplot2dplyr
Rahul shah
Rahul shah

Reputation: 185

How to create matrix on Month basis using multiple dataframe in R

I have below mentioned eight dataframe:

DF_1

Date                    ID
2017-01-01 12:04:01     AB-1
2017-01-12 22:15:21     AB-2
2017-01-15 18:12:20     AB-3
2017-02-02 17:05:45     AB-4
2017-02-08 19:55:45     AB-5
2017-02-15 13:04:09     AB-6
2017-03-05 20:22:22     AB-7
2017-03-10 14:15:16     AB-8
2017-03-25 19:40:11     AB-9
2017-03-28 21:45:24     AB-10

DF_2

2017-01-01 12:04:01     AB-1
2017-01-12 22:15:21     AB-2
2017-01-15 18:12:20     AB-3
2017-02-02 17:05:45     AB-4
2017-02-08 19:55:45     AB-5
2017-02-15 13:04:09     AB-6
2017-03-05 20:22:22     AB-7
2017-03-10 14:15:16     AB-8
2017-03-25 19:40:11     AB-9

DF_3

2017-01-01 12:04:01     AB-1
2017-01-12 22:15:21     AB-2
2017-01-15 18:12:20     AB-3
2017-02-02 17:05:45     AB-4
2017-02-08 19:55:45     AB-5
2017-02-15 13:04:09     AB-6
2017-03-05 20:22:22     AB-7
2017-03-10 14:15:16     AB-8
2017-03-25 19:40:11     AB-9

DF_4

2017-01-01 12:04:01     AB-1
2017-01-12 22:15:21     AB-2
2017-01-15 18:12:20     AB-3
2017-02-02 17:05:45     AB-4
2017-02-08 19:55:45     AB-5
2017-02-15 13:04:09     AB-6
2017-03-05 20:22:22     AB-7
2017-03-10 14:15:16     AB-8
2017-03-25 19:40:11     AB-9

DF_5

2017-01-01 12:04:01     AB-1
2017-01-12 22:15:21     AB-2
2017-01-15 18:12:20     AB-3
2017-02-02 17:05:45     AB-4
2017-02-15 13:04:09     AB-6
2017-03-05 20:22:22     AB-7
2017-03-10 14:15:16     AB-8
2017-03-25 19:40:11     AB-9

DF_6

2017-01-01 12:04:01     AB-1
2017-01-12 22:15:21     AB-2
2017-01-15 18:12:20     AB-3
2017-02-02 17:05:45     AB-4
2017-02-15 13:04:09     AB-6
2017-03-05 20:22:22     AB-7
2017-03-25 19:40:11     AB-9

DF_7

2017-01-01 12:04:01     AB-1
2017-01-12 22:15:21     AB-2
2017-02-02 17:05:45     AB-4
2017-02-10 13:04:09     AB-6
2017-04-02 20:22:22     AB-7
2017-05-20 19:40:11     AB-9

DF_8

2017-01-01 12:04:01     AB-1
2017-02-12 22:15:21     AB-2
2017-03-02 17:05:45     AB-4
2017-03-15 13:04:09     AB-6
2017-04-05 20:22:22     AB-7
2017-05-25 19:40:11     AB-9

Using above dataframe i want to create below mentioned matrix by month, where we should consider date and month of only first dataframe DF_1 for rest of the other data frame we match only ID.

Required Output

Month   DF_1  DF_2  DF_3  DF_4  DF_5  DF_6  DF_7  DF_8
Jan-17  3     3     3     3     3     3     2     2    
Feb-17  3     3     3     3     2     2     2     2
Mar-17  4     3     3     3     3     2     2     2

Upvotes: 0

Views: 337

Answers (1)

akrun
akrun

Reputation: 887851

One option would be to place the datasets in a list

lst <- mget(paste0("DF_", 1:8))

In case, the 'Date' is not a Datetime class, do the conversion

lst <- lapply(lst, transform, Date = as.POSIXct(Date))

split the 'ID' of the first dataset by the extracted 'month-year' in 'Date' column ('lst1'), loop through the list, check how many of 'ID' are there in each of 'lst1' elements

lst1 <- split(DF_1$ID, format(DF_1$Date, "%b-%y"))

Or use as.yearmon from zoo 

lst1 <- split(DF_1$ID, zoo::as.yearmon(DF_1$Date))
sapply(lst, function(x) sapply(lst1, function(y) sum(x$ID %in% y)))
#          DF_1 DF_2 DF_3 DF_4 DF_5 DF_6 DF_7 DF_8
#Jan 2017    3    3    3    3    3    3    2    2
#Feb 2017    3    3    3    3    2    2    2    2
#Mar 2017    4    3    3    3    3    2    2    2

Or another option is to create a named vector, then loop through the list, match with 'ID' column, and get the table

nm1 <- setNames(as.yearmon(DF_1$Date), DF_1$ID)
sapply(lst, function(x) table(nm1[x$ID]))
#         DF_1 DF_2 DF_3 DF_4 DF_5 DF_6 DF_7 DF_8
#Jan 2017    3    3    3    3    3    3    2    2
#Feb 2017    3    3    3    3    2    2    2    2
#Mar 2017    4    3    3    3    3    2    2    2

If some cases are missing, then convert to factor with levels specified. For testing, removing some of the rows of 'DF_8'

lst$DF_8 <- lst$DF_8[1:2,]
sapply(lst, function(x) table(factor(nm1[x$ID], levels = as.character(unique(nm1)))))
#         DF_1 DF_2 DF_3 DF_4 DF_5 DF_6 DF_7 DF_8
#Jan 2017    3    3    3    3    3    3    2    2
#Feb 2017    3    3    3    3    2    2    2    0
#Mar 2017    4    3    3    3    3    2    2    0

Upvotes: 5

Related Questions