如何用NA而不是X制作交叉表? [英] How to make a cross table with NA instead of X?
问题描述
我有以下数据集(请参阅下面的加载数据集)
I have the following dataset (see for loading dataset below)
ID Date qty
1 ID25 2007-12-01 45
2 ID25 2008-01-01 26
3 ID25 2008-02-01 46
4 ID25 2008-03-01 0
5 ID25 2008-04-01 78
6 ID25 2008-05-01 65
7 ID25 2008-06-01 32
8 ID99 2008-02-01 99
9 ID99 2008-03-01 0
10 ID99 2008-04-01 99
我想创建一个数据透视表.我使用以下命令来执行此操作,并且似乎运行良好:
And I would like to create a pivot table of that. I do that with the following command and that seems to be working fine:
pivottable <- xtabs(qty ~ ID + Date, table)
输出如下:
ID 2007-12-01 2008-01-01 2008-02-01 2008-03-01 2008-04-01 2008-05-01 2008-06-01
ID25 45 26 46 0 78 65 32
ID99 0 0 99 0 99 0 0
但是,对于ID99,只有3个期间的值,其余部分标记为"0".我想在第一个表中没有任何值的字段中显示NA.我想得到一个如下表:
However, for ID99 there are only values for 3 periods the rest is marked as '0'. I would like to display NA in the fields that have no values in the first table. I would like to get a table that looks as following:
ID 2007-12-01 2008-01-01 2008-02-01 2008-03-01 2008-04-01 2008-05-01 2008-06-01
ID25 45 26 46 0 78 65 32
ID99 NA NA 99 0 99 NA NA
关于如何实现此目标的任何建议?
Any suggestion on how to accomplish this?
正在加载数据集:
table <- structure(list(ID = structure(c(1L, 1L, 1L, 1L, 1L, 1L, 1L, 2L,
2L, 2L), .Label = c("ID25", "ID99"), class = "factor"), Date = structure(c(7L,
1L, 2L, 3L, 4L, 5L, 6L, 2L, 3L, 4L), .Label = c("01/01/2008",
"01/02/2008", "01/03/2008", "01/04/2008", "01/05/2008", "01/06/2008",
"01/12/2007"), class = "factor"), qty = c(45L, 26L, 46L, 0L,
78L, 65L, 32L, 99L, 0L, 99L)), .Names = c("ID", "Date", "qty"
), class = "data.frame", row.names = c(NA, -10L))
table$Date <- as.POSIXct(table$Date, format='%d/%m/%Y')
推荐答案
您可以使用xtabs
两次以获取所需的输出:
You could use xtabs
twice to obtain the output you are looking for:
-
创建表:
Create the table:
pivottable <- xtabs(qty ~ ID + Date, table)
用NA
替换不存在的组合的所有零:
Replace all zeros of non-existing combinations with NA
:
pivottable[!xtabs( ~ ID + Date, table)] <- NA
输出:
Date
ID 2007-12-01 2008-01-01 2008-02-01 2008-03-01 2008-04-01 2008-05-01 2008-06-01
ID25 45 26 46 0 78 65 32
ID99 99 0 99
请注意,不会显示NA
.这是由于此类的print
函数所致.但是您可以使用unclass(pivottable)
来实现print
的常规行为.
Note that NA
s are not displayed. This is due to the print
function for this class. But you could use unclass(pivottable)
to achieve regular behavior of print
.
这篇关于如何用NA而不是X制作交叉表?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!