我有一个包含地区、县和年份的数据集。如果某个地区/县组合发生在任何一年，我希望该组合发生在每年。以下是我想出的两种方法。第一种方法使用一个函数来创建地区、县和年份的组合，并且只需要六行代码。底部方法使用paste、expand.grid和strsplit的组合，并且更加复杂/复杂。
可能有比上述任何一种方法都更有效的方法。例如，有没有一种方法可以使用expand.grid来实现地区/县/年的组合，也许只需要1或2行代码？
谢谢你的建议。我的职能部门可以胜任这项工作，但这个问题对我来说是一个学习的机会
以下是示例数据集：
df.1 <- read.table(text = '
state district county year apples
AA EC A 1980 100
AA EC B 1980 10
AA EC C 1980 150
AA C G 1980 200
AA C other 1980 20
AA C I 1980 250
AA WC R 1980 300
AA WC S 1980 30
AA WC other 1980 350
AA EC A 1999 1100
AA EC D 1999 110
AA EC E 1999 1150
AA C H 1999 1200
AA C I 1999 120
AA C J 1999 1250
AA WC R 1999 1300
AA WC other 1999 130
AA WC T 1999 1350
', header=TRUE, stringsAsFactors = FALSE)
这是想要的结果：
desired.result <- read.table(text = '
state district county year apples
AA C G 1980 200
AA C H 1980 NA
AA C I 1980 250
AA C J 1980 NA
AA C other 1980 20
AA EC A 1980 100
AA EC B 1980 10
AA EC C 1980 150
AA EC D 1980 NA
AA EC E 1980 NA
AA WC other 1980 350
AA WC R 1980 300
AA WC S 1980 30
AA WC T 1980 NA
AA C G 1999 NA
AA C H 1999 1200
AA C I 1999 120
AA C J 1999 1250
AA C other 1999 NA
AA EC A 1999 1100
AA EC B 1999 NA
AA EC C 1999 NA
AA EC D 1999 110
AA EC E 1999 1150
AA WC other 1999 130
AA WC R 1999 1300
AA WC S 1999 NA
AA WC T 1999 1350
', header=TRUE, stringsAsFactors = FALSE)
这是我迄今为止最直接的解决方案，它使用一个函数来表示每年的每个地区/县组合：
my.unique.function <- function(year) {
my.unique <- data.frame(unique(df.1[, c('state', 'district', 'county')]), year)
return(my.unique = my.unique)
}
years <- as.data.frame(unique(df.1[, 'year']))
my.unique.output <- apply(years, 1, function(x) {my.unique.function(x)})
my.unique.output2 <- do.call(rbind.data.frame, my.unique.output)
desired.result2 <- merge(df.1, my.unique.output2, by = c('state', 'year', 'district', 'county'), all=TRUE)
# compare output with a priori desired result
desired.result <- desired.result[order(desired.result$state, desired.result$year, desired.result$district, desired.result$county),]
all.equal(desired.result[,c(1,4,2,3,5)], desired.result2[,1:5])
这是我最初的更复杂的解决方案：
# find unique combinations of district and county
my.unique <- unique(df.1[, c('district', 'county')])
# paste district and county together
my.unique$x <- apply( my.unique[ , c('district', 'county') ] , 1 , paste , collapse = "-" )
# represent each district/county combination for each year
expand.unique <- expand.grid(my.unique$x, year = c(1980, 1999))
expand.unique$Var1 <- as.character(expand.unique$Var1)
# split combined district/county combinations into two columns
expand.unique$Var1b <- sub('-', ' ', expand.unique$Var1)
unique.split <- strsplit(expand.unique$Var1b, ' ')
unique.splits <- matrix(unlist(unique.split), ncol=2, byrow=TRUE, dimnames = list(NULL, c("district", "county")))
# create template prior to merging with original data set
state <- 'AA'
desired.resultb <- data.frame(state, expand.unique, unique.splits)
# merge template with original data set so missing observations are present if a county is not included for a given year
desired.resultc <- merge(df.1, desired.resultb, by = c('state', 'year', 'district', 'county'), all=TRUE)
desired.resultc
# compare output with a priori desired result
desired.result <- desired.result[order(desired.result$state, desired.result$year, desired.result$district, desired.result$county),]
all.equal(desired.result[,c(1,4,2,3,5)], desired.resultc[,1:5])
#find all (unique) state-district-county combinations
df.2 <- unique(df.1[,c("state","district","county")])
#find all (unique) years
df.3 <- unique(df.1[,"year",drop=FALSE])
#Cartesian product of combinations
df.4 <- merge(df.2,df.3)
#merge this with the original data frame,
#leaving NA's for unmatched parts in df.4
merge(df.1,df.4,all=TRUE)
这里有一个使用expand.grid的解决方案，它只需要四行。不过，我更喜欢蓝魔法师的回答。
my.template <- expand.grid( unique(paste(df.1$state, df.1$district, df.1$county, sep= ' ')), year = unique(df.1$year))
my.template2 <- data.frame(do.call(rbind, strsplit(as.character(my.template$Var1), ' ')), year = my.template$year)
colnames(my.template2) <- names(df.1)[1:4]
desired.result2 <- merge(df.1, my.template2, all=TRUE)