如何使用R中的多个数据框在月份基础上创建矩阵 [英] 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
使用上面的数据框，我想按月创建下面提到的矩阵，其中我们应该考虑只有第一个数据帧的日期和月份 DF_1 ，以便我们匹配其他数据框的其余部分只有 ID 。
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.
必需输出
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
推荐答案
一种选择是将数据集放入 list
One option would be to place the datasets in a list
lst <- mget(paste0("DF_", 1:8))
如果'Date'不是Datetime类，
In case, the 'Date' is not a Datetime class, do the conversion
lst <- lapply(lst, transform, Date = as.POSIXct(Date))
split （'lst1'）中的'year'，通过列表循环，检查每个'lst1'元素中有多少'ID'。
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"))
或从使用 as.yearmon 动物园
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
或者另一种选择是创建一个名为 vector ，然后遍历列表，与'ID'列匹配，并获取表
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
如果缺少一些案例，则转换为 factor ，并指定 levels 。为了测试，删除'DF_8'的一些行
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
这篇关于如何使用R中的多个数据框在月份基础上创建矩阵的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
