ODK响应
1
1; 2
1; 2; 3
1; 2; 3; 5
1; 2; 4
1; 2; 4; 5; 6
1; 2; 4; 6
1; 2; 4; 7
1 is Crop failure-
2 is Water shortage
3 is Land degradation
4 is Lack of HH Labor
5 is Lack of income from agriculture
6 is Lack of manure / fertilizer
7 is Others
我想要这样的表格
Crop failure- 8
Water shortage- 7
Land degradation- 6
Lack of HH Labor- 1
Lack of income from agriculture- 2
Lack of manure / fertilizer- 2
Others- 1
我尝试过在R中使用dplyr"将具有多个值的单列拆分为多列"，但无法提供帮助。
这是我解决这个问题的方法。我使用tidyverse，因为它为我加载字符串和tidyr
library(tidyverse)
id <- data.frame(Code = 1:7, #Make a coding data frame so you can label the results
Cause = c("Crop failure", "Water shortage", "Land degradation", "Lack of HH Labor", "Lack of income from agriculture", "Lack of manure / fertilizer", "Others"), stringsAsFactors = FALSE))
data <- Book1 %>%
separate(X1, into = paste0("X", 1:7), sep = ";") %>% #split the data by the ;, This induces NA that are removed later
gather(key = "drop", value = "Code") %>% #put it into 1 column to exploit R's vectorization
mutate(Code = as.integer(Code)) %>% #Make the code an integer for the join later
filter(!is.na(Code)) %>% #remove those previous NAs
group_by(Code) %>%
count() %>% # Counts
left_join(., id) #labels
colnames(data) <- c("Code", "Count", "Cause")
它会在单独的行上发出警告，但它只是让你知道它正在用NA填充额外的细胞，我们稍后会删除。您可能只需要更改DataFrame和X1，这取决于您对对象的命名。
以下是我的结果
Code Count Cause
<int> <int> <chr>
1 1 8 Crop failure
2 2 7 Water shortage
3 3 2 Land degradation
4 4 4 Lack of HH Labor
5 5 2 Lack of income from agriculture
6 6 2 Lack of manure / fertilizer
7 7 1 Others
希望有帮助！！
- 如果您想使用
base R，以下解决方案可能会对您有所帮助
假设您的输入是
response <- c(1, 1, 2, 1, 2, 3, 1, 2, 3, 5, 1, 2, 4, 1, 2, 4, 5, 6, 1, 2, 4, 6, 1, 2, 4, 7)
然后
status <- c("Crop failure", "Water shortage", "Lang degradation", "Lack of HH Labor", "Lack of income from agriculture", "Lack of manure / fertilizer", "Others")
df <- as.data.frame(table(factor(response,labels = status),dnn = list("Status")))
可以给你像一样的输出
> df
Status Freq
1 Crop failure 8
2 Water shortage 7
3 Lang degradation 2
4 Lack of HH Labor 4
5 Lack of income from agriculture 2
6 Lack of manure / fertilizer 2
7 Others 1
- 如果您想要一个详细的表格：假设您的输入是：
r <- list(1, c(1, 2), c(1, 2, 3), c(1, 2, 3, 5), c(1, 2, 4), c(1,
2, 4, 5, 6), c(1, 2, 4), 6, c(1, 2, 4, 7))
type = seq(1,7)
dt <- as.data.frame(t(sapply(r, function(v) sapply(type, function(k) sum(k==v)))))
colnames(M) <- paste0("type",type)
它给出
> dt
type1 type2 type3 type4 type5 type6 type7
1 1 0 0 0 0 0 0
2 1 1 0 0 0 0 0
3 1 1 1 0 0 0 0
4 1 1 1 0 1 0 0
5 1 1 0 1 0 0 0
6 1 1 0 1 1 1 0
7 1 1 0 1 0 0 0
8 0 0 0 0 0 1 0
9 1 1 0 1 0 0 1
此外，每种类型条目的总和可以通过colSums:来计算
> colSums(dt)
type1 type2 type3 type4 type5 type6 type7
8 7 2 4 2 2 1
也可以使用match()，即
dt <- as.data.frame(t(sapply(r, function(v) !is.na(match(type,v)))))
> dt
type1 type2 type3 type4 type5 type6 type7
1 TRUE FALSE FALSE FALSE FALSE FALSE FALSE
2 TRUE TRUE FALSE FALSE FALSE FALSE FALSE
3 TRUE TRUE TRUE FALSE FALSE FALSE FALSE
4 TRUE TRUE TRUE FALSE TRUE FALSE FALSE
5 TRUE TRUE FALSE TRUE FALSE FALSE FALSE
6 TRUE TRUE FALSE TRUE TRUE TRUE FALSE
7 TRUE TRUE FALSE TRUE FALSE FALSE FALSE
8 FALSE FALSE FALSE FALSE FALSE TRUE FALSE
9 TRUE TRUE FALSE TRUE FALSE FALSE TRUE
使用plyr，您可以获得以下内容：
Condition = c("Crop failure", "Water shortage", "Lang degradation", "Lack of HH Labor", "Lack of income from agriculture", "Lack of manure / fertilizer", "Others")
Type = c(1:7)
df = data.frame(Condition, Type)
vector = c(1,1,2,1,2,3,1,2,3,5,1,2,4,1,2,4,5,6,1,2,4,6,1,2,4,7)
t = plyr::count(vector)
colnames(t) = c("Type","Freq")
df =merge(df,t)
你会得到：
> df
Type Condition Freq
1 1 Crop failure 8
2 2 Water shortage 7
3 3 Lang degradation 2
4 4 Lack of HH Labor 4
5 5 Lack of income from agriculture 2
6 6 Lack of manure / fertilizer 2
7 7 Others 1