R添加/填充缺少的组合dcast reshape2表(R add in/populate missing combinations dcast reshape2 table)

R添加/填充缺少的组合dcast reshape2表(R add in/populate missing combinations dcast reshape2 table)

这是我的数据表：

Name.1 <- c(rep("IVa",12),rep("VIa",10),rep("VIIb",3),rep("IVa",5)) qrt <- c(rep("Q1",6),rep("Q3",10),rep("Q4",3),rep("Q1",5),rep("Q1",3),rep("Q3",3)) variable <- c(rep("wtTonnes",30)) value <- c(201:230) df <- data.frame(Name.1,qrt,variable,value) df1 <- dcast(df, Name.1 ~ qrt, fun.aggregate=sum, value.var="value",margins=TRUE)

它给了我这样的输出;

Name.1 Q1 Q3 Q4 (all) IVa 1674 1944 0 3618 VIa 663 858 654 2175 VIIb 672 0 0 672 (all) 3009 2802 654 6465

'qrt'值Q1，Q3，Q4代表一年中的季度。 基本上我希望该表包含缺少的四分之一并填充0.因为每年我运行脚本时可能有任何四分之一组合的wtTonne值，我不想每次都添加硬编码来添加缺少的那些。

在这种情况下，我希望它看起来像：

Name.1 Q1 Q2 Q3 Q4 (all) IVa 1674 0 1944 0 3618 VIa 663 0 858 654 2175 VIIb 672 0 0 0 672 (all) 3009 0 2802 654 6465

是否可以在任何阶段将列表传递给表或原始数据以说明我想要哪些列？ （如果总是存在Q1，Q2，Q3，Q4），如果需要，可以使用虚拟值。

This is my data table:

Name.1 <- c(rep("IVa",12),rep("VIa",10),rep("VIIb",3),rep("IVa",5)) qrt <- c(rep("Q1",6),rep("Q3",10),rep("Q4",3),rep("Q1",5),rep("Q1",3),rep("Q3",3)) variable <- c(rep("wtTonnes",30)) value <- c(201:230) df <- data.frame(Name.1,qrt,variable,value) df1 <- dcast(df, Name.1 ~ qrt, fun.aggregate=sum, value.var="value",margins=TRUE)

It gives me an output like this;

Name.1 Q1 Q3 Q4 (all) IVa 1674 1944 0 3618 VIa 663 858 654 2175 VIIb 672 0 0 672 (all) 3009 2802 654 6465

The 'qrt' values Q1, Q3, Q4 represent quarters of the year. Basically I would like the table to include missing quarters and populate with 0. As every year when I run the script there could be wtTonne values for any combination of quarters and I don't want to hard code each time to add whichever are missing.

In this case I would like it to look like:

Name.1 Q1 Q2 Q3 Q4 (all) IVa 1674 0 1944 0 3618 VIa 663 0 858 654 2175 VIIb 672 0 0 0 672 (all) 3009 0 2802 654 6465

Is it possible to pass a list to a table or the raw data at any stage to say which columns I want to have? (i.e. there always to be Q1, Q2, Q3, Q4) with dummy values if needs be.

最满意答案

以下内容应该为您提供所需的输出：

df$qrt <- factor(df$qrt, levels = c("Q1", "Q2", "Q3", "Q4")) df1 <- dcast(df, Name.1 ~ qrt, fun.aggregate=sum, value.var="value",margins=TRUE, drop = F)

首先，我告诉R ， qrt是一个具有相应级别的因素，包括不会发生的级别，然后我告诉dcast避免删除未使用的组合。 这给出了：

Name.1 Q1 Q2 Q3 Q4 (all) 1 IVa 1674 0 1944 0 3618 2 VIa 663 0 858 654 2175 3 VIIb 672 0 0 0 672 4 (all) 3009 0 2802 654 6465

The following should give you the required output:

df$qrt <- factor(df$qrt, levels = c("Q1", "Q2", "Q3", "Q4")) df1 <- dcast(df, Name.1 ~ qrt, fun.aggregate=sum, value.var="value",margins=TRUE, drop = F)

At first, I tell R that qrt is a factor with the corresponding levels, including the level that does not occur, and then I tell dcast to avoid droppping unused combinations. This gives:

Name.1 Q1 Q2 Q3 Q4 (all) 1 IVa 1674 0 1944 0 3618 2 VIa 663 0 858 654 2175 3 VIIb 672 0 0 0 672 4 (all) 3009 0 2802 654 6465