代码之家 › 专栏 › 技术社区 › Varun

R tidyr在给定变量的所有类别中展开列

spread data-manipulation tidyr r

Varun · 技术社区 · 7 年前

我正在处理一个看起来像这样的数据集。

#Dataframe
df=data.frame(Type=c(1,2,4,5,4,3,3,4,5,1,2,3,2,1,2,3,3,2,1,1,NA),
          Q1=c(1,2,6,8,9,10,2,6,7,4,9,9,1,2,NA,4,3,8,7,6,4),
          Q2=c(1,2,4,NA,8,2,1,2,10,7,5,5,5,8,2,7,4,8,7,5,1))

数据框架由问卷调查的结果组成。

第一列, Type ,是指回答问卷的员工类型,其中1=' Worker ', 2 = ' Factory Lead ', 3 = ' Administrative Staff ', 4 = ' Middle Management '和';5 = ' Executive '

Q1 & Q2 )是问题,评分为1分 Strongly Agree '到10( Strongly Disagree

我想要达到的目标

类型 ,基于分数。我已经为分数创建了垃圾箱,它们是-

Low 一致性-得分从0到4

2) Medium 同意-5分或6分

High 同意-7分或8分

Very High 同意-9分或10分

我的尝试

library(dplyr)
library(tidyr)

result=df %>%
gather(Item,response,-1) %>%
filter(!is.na(response)) %>%
group_by(Type,Item) %>%
filter(!is.na(Type)) %>%
summarise(Low=sum(response %in% c(0,1,2,3,4)),
        Medium=sum(response %in% c(5,6)),
        High=sum(response %in% c(7,8)),
        VHigh=sum(response %in% c(9,10)) %>%
spread(Type,-Item)

我的逻辑是我用 tidyr gather 计算总回答的分数。然后将这些列展开,这样我就可以得到按工人和按分数类别列出的小计。

Low-Worker ,那么 Medium-Worker ,那么 High-Worker Very High-Worker ,那么 Low-Factory Lead Medium-Factory Lead .... 对于所有员工和分数类别的组合,依此类推。

期望输出

具有两排 ( 第一季度 & )以及 (针对每个员工得分组合)。

3 回复 | 直到 7 年前

Paul Rougieux 7 年前

创建分数数据框

library(tidyr)
library(dplyr)
df <- data_frame(type=c(1,2,4,5,4,3,3,4,5,1,2,3,2,1,2,3,3,2,1,1,NA),
                 q1=c(1,2,6,8,9,10,2,6,7,4,9,9,1,2,NA,4,3,8,7,6,4),
                 q2=c(1,2,4,NA,8,2,1,2,10,7,5,5,5,8,2,7,4,8,7,5,1))

scores <- data_frame(score = 0:10,
                     scorebin = c(rep("Low", 5),
                                  rep("Medium", 2),
                                  rep("High", 2),
                                  rep("Very High", 2)))

以长格式收集数据。加入分数数据框以添加 scorebin 列。分组依据 item , type 和 计分箱 并计算每组的答案数。

df2 <- df %>%
    gather(item, score, -type) %>% 
    left_join(scores, by = "score") %>% 
    group_by(item, type, scorebin) %>% 
    summarise(n = n()) %>% 
    unite(employeescore, type, scorebin)

改变 employeescore 到一个有序水平的因子使它们不按字母顺序显示(高、低、中)

employeescoreorder <- scores %>% 
    distinct(scorebin) %>% 
    merge(distinct(df, type)) %>% 
    unite(employeescore, type, scorebin)
df2$employeescore <- factor(df2$employeescore, 
                            levels = employeescoreorder$employeescore)

以宽格式展开数据帧以获得20列。

df2 %>% 
    spread(employeescore, n)

# A tibble: 2 x 20
# Groups:   item [2]
   item `1_Low` `1_Medium` `1_High` `2_Low` `2_Medium` `2_High` `2_Very High` `4_Low`
* <chr>   <int>      <int>    <int>   <int>      <int>    <int>         <int>   <int>
1    q1       3          1        1       2         NA        1             1      NA
2    q2       1          1        3       2          2        1            NA       2
# ... with 11 more variables: `4_Medium` <int>, `4_High` <int>, `4_Very High` <int>,
#   `5_High` <int>, `5_Very High` <int>, `3_Low` <int>, `3_Medium` <int>, `3_High` <int>,
#   `3_Very High` <int>, NA_Low <int>, `<NA>` <int>

jyjek 7 年前

 df%>%
   mutate(Type_real=case_when(
             Type==1~"Worker",
             Type==2~"Factory Lead",
             Type==3~"Administrative Staff",
             Type==4~"Middle Management",
             Type==5~"Executive"),
         Score=case_when(
             Q1<5~"Low",
             Q1>=5 & Q1<=6~"Medium",
             Q1>=7 & Q1<=8~"High",
             Q1>8~"Very High"))%>%
   na.omit()%>%
   group_by(Type_real,Score)%>%
   summarise(count=n())
# A tibble: 11 x 3
# Groups:   Type_real [?]
   Type_real            Score     count
   <chr>                <chr>     <int>
 1 Administrative Staff Low           3
 2 Administrative Staff Very High     2
 3 Executive            High          1
 4 Factory Lead         High          1
 5 Factory Lead         Low           2
 6 Factory Lead         Very High     1
 7 Middle Management    Medium        2
 8 Middle Management    Very High     1
 9 Worker               High          1
10 Worker               Low           3
11 Worker               Medium        1

kath 7 年前

另一个类似于Paul Rougieux但没有连接的解决方案是:

df %>% 
  mutate(Type = case_when(Type == 1 ~ "Worker",
                          Type == 2 ~ "Factory Lead",
                          Type == 3 ~ "Administrative Staff",
                          Type == 4 ~ "Middle Management",
                          Type == 5 ~ "Executive")) %>% 
  mutate_at(c("Q1", "Q2"), 
            funs(case_when(. %in% 1:4 ~ "Low",
                           . %in% 5:6 ~ "Medium",
                           . %in% 7:8 ~ "High",
                           . %in% 9:10 ~ "Very High"))) %>%
  gather(Questions, Score, Q1:Q2) %>% 
  unite(Type_Score, Type, Score, sep = "_") %>% 
  count(Questions, Type_Score) %>% 
  spread(Type_Score, n)

# A tibble: 2 x 21
#   Questions `Administrative~ `Administrative~ `Administrative~ `Administrative~ Executive_High Executive_NA `Executive_Very~ `Factory Lead_H~
#   <chr>                <int>            <int>            <int>            <int>          <int>        <int>            <int>            <int>
# 1 Q1                      NA                3               NA                2              2           NA               NA                1
# 2 Q2                       1                3                1               NA             NA            1                1                1
# ... with 12 more variables: `Factory Lead_Low` <int>, `Factory Lead_Medium` <int>, `Factory Lead_NA` <int>, `Factory Lead_Very High` <int>,
#   `Middle Management_High` <int>, `Middle Management_Low` <int>, `Middle Management_Medium` <int>, `Middle Management_Very High` <int>,
#   NA_Low <int>, Worker_High <int>, Worker_Low <int>, Worker_Medium <int>