Formula to calculate across multiple columns

Question

I have a df that I'm trying to calculate the top 2 box score, the bottom 2 box score, and the middle 3 box scores for. At present this is what I was trying to do (the myfunc doesn't work hence why I'm coming for help):

myfunc <- list(
  topbox = ~sum([[cur_column]]>"5")/[[cur_column]]n())
)

Top2Box <- cleanuxq2 %>% 
  dplyr:: select(starts_with("X"),("list"),("urespid_0"),("segment")) %>%
  group_by (segment)%>%
  summarize(across(
    .cols = is.numeric,
    .fns = WHAT DO I PUT IN HERE STACK OVERFLOW ?????
    .names = "{col}_{fn}"
  ))

Here is an example of my date frame:

list,segment,date,url,urespid_0,Usefulness_q1_needs,Usefulness_q2_everything,Usefulness_q3_effective,Usefulness_q4_productive,Usefulness_q5_useful,Usefulness_q6_control,Usefulness_q7_accomplish,Usefulness_q8_time,Ease_q1_instructions,Ease_q2_easy,Ease_q3_simple,Ease_q4_friendly,Ease_q5_steps,Ease_q6_flexible,Ease_q7_effortless,Ease_q8_inconsistency,Ease_q9_bothusers,Ease_q10_recover,Ease_q11_successfully,Learning_q1_quickly,Learning_q2_remember,Learning_q3_learn,Learning_q4_skillful,Satisfaction_q1_satisfied,Satisfaction_q2_recommend,Satisfaction_q3_fun,Satisfaction_q4_wayiwant,Satisfaction_q5_wonderful,Satifaction_q6_pleasant,Satifaction_q7_needtohave,Effective_Functional_1,Effective_Functional_2,Effective_Useful_1,Effective_Useful_2,Effective_Useful_3,Effective_Performant_1,Effective_Performant_2,Effective_Performant_3,Efficient_Efficient_1,Efficient_Efficient_2,Efficient_Efficient_3,Efficient_Efficient_4,Efficient_Ease_1,Efficient_Ease_2,Efficient_Ease_3,Efficient_Ease_4,Efficient_Ease_5,Efficient_Ease_6,Efficient_Ease_7,Efficient_Ease_8,Efficient_Learn_1,Efficient_Learn_2,Efficient_Learn_3,Efficient_Learn_4,Empowered_Satisfaction_1,Empowered_Satisfaction_2,Empowered_Satisfaction_3,Empowered_Satisfaction_4,Empowered_Enjoyable_1,Empowered_Enjoyable_2,Empowered_Enjoyable_3,Empowered_Empowered_1,Empowered_Empowered_2,Empowered_Empowered_3,SNOW_q1_load,SNOW_q2_domore,SNOW_q3_righttime,SNOW_q4_prefer,XUSE_Usefulness,XUSE_Ease,XUSE_Learning,XUSE_Satisfaction,XSnow,XEffective,X_Effective_Functional,X_Effective_Useful,X_Effective_Performant,XEfficient,X_Efficient_Efficient,X_Efficient_Ease,X_Efficient_Learn,XEmpowered,X_Empowered_Satisfaction,X_Empowered_Empowered,X_Empowered_Enjoyable
14,Novice,2021-04-14T11:50:00Z,/survey/selfserve/5c3/200818?list=14,2021-AA5795190367-
103,5,5,3,5,4,4,6,5,1,4,4,3,6,7,3,2,2,7,5,5,7,3,6,4,4,7,2,6,6,3,5,5,3,5,4,7,7,1,6,5,6,3,1,4,4,3,2,2,5,2,5,7,3,6,3,4,4,5,7,6,6,4,7,7,1,7,7,5,5,1,5,4,1,5,5,3,7,6,6,1,5,3,3,4,7
14,Novice,2021-04-16T06:54:00Z,/survey/selfserve/5c3/200818?list=14,2021-WN7090381910-103,2,2,2,1,3,2,1,1,1,2,2,2,3,4,1,1,1,3,1,3,3,3,3,2,1,1,1,1,1,3,2,2,2,1,3,4,3,5,1,1,3,1,1,2,2,2,1,1,1,1,3,3,3,3,3,2,1,4,1,1,1,2,3,1,5,3,1,4,2,1,3,2,5,2,2,2,4,1,1,1,3,3,3,2,1
14,Novice,2021-04-16T11:07:00Z,/survey/selfserve/5c3/200818?list=14,2021-KJ5334244864-103,7,5,7,7,7,7,7,7,2,7,7,7,7,7,7,6,7,7,7,7,7,7,7,7,7,7,7,6,7,7,7,5,7,7,7,7,7,7,7,7,7,7,2,7,7,7,6,7,7,7,7,7,7,7,7,7,7,7,7,6,7,7,7,7,7,7,7,7,7,2,7,7,7,7,7,7,7,7,7,2,7,7,7,7,7
14,Novice,2021-04-20T13:21:00Z,/survey/selfserve/5c3/200818?list=14,2021-GJ8144902058-103,6,6,6,6,6,5,6,6,4,6,6,6,6,6,6,6,6,6,6,6,6,6,5,6,6,5,5,5,6,5,6,6,6,6,6,6,6,6,6,6,6,6,4,6,6,6,6,6,6,5,6,6,6,5,5,6,6,5,5,5,6,5,6,6,6,6,6,5,6,4,6,6,6,6,6,6,6,6,6,4,6,5,5,5,5
14,Novice,2021-04-21T07:29:00Z,/survey/selfserve/5c3/200818?list=14,2021-EA7607238302-103,5,5,5,5,7,4,5,6,4,4,4,4,4,4,4,1,2,5,3,5,5,5,4,4,5,4,4,3,4,6,5,5,5,5,7,4,5,4,5,6,4,4,4,4,4,4,1,2,3,4,5,5,5,4,6,4,5,3,4,3,4,4,5,5,4,5,5,3,5,4,5,4,4,5,5,5,4,5,5,4,5,6,6,4,4
14,Novice,2021-04-21T13:13:00Z,/survey/selfserve/5c3/200818?list=14,2021-JC2895631742-103,7,7,7,7,7,6,7,7,6,6,6,6,7,7,6,7,7,7,7,6,7,6,6,7,7,7,7,7,7,7,7,7,7,7,7,7,7,7,7,7,7,6,6,6,6,6,7,7,7,7,6,7,6,6,7,7,7,7,7,7,7,6,6,7,7,6,7,7,7,6,6,7,7,7,7,7,7,7,7,6,6,7,7,6,7
14,Novice,2021-04-21T15:38:00Z,/survey/selfserve/5c3/200818?list=14,2021-RL7747403252-103,6,7,6,6,6,6,6,6,5,6,6,6,6,5,5,5,6,6,6,6,6,6,6,6,6,6,6,6,6,6,6,7,6,6,6,5,6,5,6,6,6,5,5,6,6,6,5,6,6,6,6,6,6,6,6,6,6,6,6,6,6,6,7,6,5,7,6,6,6,5,6,6,5,6,6,6,5,6,6,5,6,6,6,6,6
14,Advanced,2021-04-26T12:17:00Z,/survey/selfserve/5c3/200818?list=14,PARTICIPANTID,4,4,5,6,5,3,5,5,2,5,5,5,5,3,4,2,3,2,5,4,6,5,4,6,6,6,5,3,5,3,4,4,5,6,5,3,2,6,5,5,5,4,2,5,5,5,2,3,5,5,4,6,5,4,3,6,6,4,6,3,5,3,6,6,6,6,6,4,4,2,4,6,6,4,4,5,3,5,5,2,4,3,3,3,6
14,Advanced,2021-04-27T08:50:00Z,/survey/selfserve/5c3/200818?list=14,2021-KT7904802272-116,5,6,4,5,6,4,4,4,6,7,6,6,4,6,6,4,5,5,4,5,5,6,5,6,6,5,5,4,7,3,5,6,4,5,6,6,5,7,4,4,4,6,6,7,6,6,4,5,4,5,5,5,6,5,3,6,6,3,5,4,7,4,7,5,7,7,5,3,5,6,5,6,7,5,5,4,6,4,4,6,5,3,3,4,5
14,Advanced,2021-04-27T15:38:00Z,/survey/selfserve/5c3/200818?list=14,2021-BB7055478061-116,6,6,5,6,6,5,6,6,5,5,6,6,5,5,3,3,2,5,6,6,6,6,4,5,6,3,6,3,4,3,6,6,5,6,6,5,5,6,6,6,5,3,5,5,6,6,3,2,6,6,6,6,6,4,3,5,6,6,3,3,4,5,6,5,6,6,5,6,6,5,6,5,6,6,6,5,5,6,6,5,6,3,3,5,3
14,Advanced,2021-04-28T13:24:00Z,/survey/selfserve/5c3/200818?list=14,2021-KB562378069-116,7,6,7,7,7,7,7,7,7,7,7,7,7,7,5,7,6,7,7,7,6,7,7,7,7,7,6,7,7,4,7,6,7,7,7,7,7,7,7,7,7,5,7,7,7,7,7,6,7,6,7,6,7,7,4,7,7,7,7,7,7,7,7,7,7,7,7,7,7,7,7,7,7,7,7,7,7,7,7,7,7,4,4,7,7
14,Advanced,2021-04-30T10:54:00Z,/survey/selfserve/5c3/200818?list=14,2021-SM7090381910-116,5,4,7,7,7,6,6,6,3,5,5,5,5,6,5,6,6,3,4,5,6,4,5,4,7,6,4,6,6,7,5,4,7,7,7,6,3,3,6,6,5,5,3,5,5,5,6,6,4,4,5,6,4,5,7,4,7,6,6,6,6,6,7,3,3,7,3,6,5,3,5,4,3,5,5,7,6,6,6,3,5,7,7,6,6

Can you describe what you want `[[cur_column]]n())` here – akrun May 18 '21 at 22:36 — akrun, May 18 '21 at 22:36

akrun · Accepted Answer · 2021-05-18T22:44:54.607

0

Perhaps the myfunc would be

...
 .fns = list(
    topbox = ~sum(. > 5)/n())
  
...

edited May 18 '21 at 22:44

answered May 18 '21 at 22:30

akrun

874,273
37
540
662

Any chance you could help me with one last thing? How do I get the middle box to sum for values of 3,4,5? When I try multiple things it calculates them wrong. myfunc <- list( topbox = ~sum(. > 5)/n(), middlebox = ~sum(. =3)/ n(), How do I sum for values 3,4,5???????? bottombox = ~sum(. < 3)/n()) Thank you Akrun – mdutton27 May 19 '21 at 02:27
@mdutton27 sure, please let me know – akrun May 19 '21 at 02:27
@mdutton27 can you please tell me the issue – akrun May 19 '21 at 02:29
1

I figured it out! Took forever but I did it. Thank you. – mdutton27 May 19 '21 at 04:33
any chance I would ask you to please take a look at this https://stackoverflow.com/questions/67617784/formula-based-on-row-value-applied-across-columns-with-a-group-by – mdutton27 May 24 '21 at 00:01
@mdutton27 i think you are breaking the grouping with `cleanuxq3$t1_time_task)`. If you need the summary mean or sum of t1_time_task remove the `cleanuxq3$` – akrun May 24 '21 at 00:12

Formula to calculate across multiple columns

1 Answers1