Extract column names for each column in dataframe where value is not NA over defined range of columns within dplyr function

3
votes

I am trying to write a function that takes a dataframe, a column that determines if a specific condition is met to subset the frame, a column with a numeric value, a grouping clause, and a subset name as arguments.

I would like the function to then check to see which of the columns across a defined range has a value that is not NA, and then extract the column names of those columns and append them to the dplyr table generated by the function for each summary row that dplyr generates as per the grouping clause. I am doing this over hundreds of subsets of a much bigger much more complicated dataframe so this is why I need a function that I can use over and over again.

The example dataset is as follows, I have updated it with the expanded dataset apologies tot those who tried to help with the other one:

structure(list(superKey = c("12015", "12016", "12017", "12018", 
"22015", "22016", "22017", "22018", "32015", "1722016", "1722017", 
"1722018", "1732015", "1732016", "1732017", "1732018", "1742015", 
"1752015", "1752016", "1752017", "1762015", "1762018", "1772016", 
"1772017", "1772018", "1782015", "1782016", "1782017", "1782018", 
"1792015", "1792016", "1792017", "1792018", "1802015", "1812018", 
"1822016", "1832015", "1832017", "1842015", "1842016", "1842017", 
"1842018", "1852016", "1852017", "1852018", "2112018", "2122015", 
"2122016", "2122017", "2122018", "2132015", "2132016", "2132017", 
"2132018", "2142015", "2142016", "2142017", "2142018", "2152015", 
"2152016", "2152017", "2152018", "2162016", "2162017", "2172015", 
"2172016", "2172017", "2172018", "2182015", "2182016", "2182017", 
"2182018", "2192016", "2192017", "2192018", "2202015", "2202016", 
"2202017", "2202018", "2212015", "2212016", "2212017", "2212018", 
"2222015", "2222018", "2232015", "2232016", "2232017", "2232018", 
"2242015", "2242016", "2302018", "2312015", "2312016", "2312017", 
"2312018", "2322016", "2322017", "2332015", "2332016", "2332017", 
"2332018", "2342016", "2342017", "2432018", "2442015", "2442016"
), reportDate = c("2015-12-31", "2016-12-31", "2017-12-31", "2018-12-31", 
"2015-12-31", "2016-12-31", "2017-12-31", "2018-12-31", "2015-12-31", 
"2016-12-31", "2017-12-31", "2018-12-31", "2015-12-31", "2016-12-31", 
"2017-12-31", "2018-12-31", "2015-12-31", "2015-12-31", "2016-12-31", 
"2017-12-31", "2015-12-31", "2018-12-31", "2016-12-31", "2017-12-31", 
"2018-12-31", "2015-12-31", "2016-12-31", "2017-12-31", "2018-12-31", 
"2015-12-31", "2016-12-31", "2017-12-31", "2018-12-31", "2015-12-31", 
"2018-12-31", "2016-12-31", "2015-12-31", "2017-12-31", "2015-12-31", 
"2016-12-31", "2017-12-31", "2018-12-31", "2016-12-31", "2017-12-31", 
"2018-12-31", "2018-12-31", "2015-12-31", "2016-12-31", "2017-12-31", 
"2018-12-31", "2015-12-31", "2016-12-31", "2017-12-31", "2018-12-31", 
"2015-12-31", "2016-12-31", "2017-12-31", "2018-12-31", "2015-12-31", 
"2016-12-31", "2017-12-31", "2018-12-31", "2016-12-31", "2017-12-31", 
"2015-12-31", "2016-12-31", "2017-12-31", "2018-12-31", "2015-12-31", 
"2016-12-31", "2017-12-31", "2018-12-31", "2016-12-31", "2017-12-31", 
"2018-12-31", "2015-12-31", "2016-12-31", "2017-12-31", "2018-12-31", 
"2015-12-31", "2016-12-31", "2017-12-31", "2018-12-31", "2015-12-31", 
"2018-12-31", "2015-12-31", "2016-12-31", "2017-12-31", "2018-12-31", 
"2015-12-31", "2016-12-31", "2018-12-31", "2015-12-31", "2016-12-31", 
"2017-12-31", "2018-12-31", "2016-12-31", "2017-12-31", "2015-12-31", 
"2016-12-31", "2017-12-31", "2018-12-31", "2016-12-31", "2017-12-31", 
"2018-12-31", "2015-12-31", "2016-12-31"), employerClass = c("class1", 
"class1", "class1", "class1", "class1", "class1", "class1", "class1", 
"class1", "class1", "class1", "class1", "class1", "class1", "class1", 
"class1", "class1", "class1", "class1", "class1", "class1", "class1", 
"class1", "class1", "class1", "class1", "class1", "class1", "class1", 
"class1", "class1", "class1", "class1", "class1", "class1", "class1", 
"class1", "class1", "class1", "class1", "class1", "class1", "class1", 
"class1", "class1", "class2", "class2", "class2", "class2", "class2", 
"class2", "class2", "class2", "class2", "class2", "class2", "class2", 
"class2", "class2", "class2", "class2", "class2", "class2", "class2", 
"class2", "class2", "class2", "class2", "class2", "class2", "class2", 
"class2", "class2", "class2", "class2", "class2", "class2", "class2", 
"class2", "class2", "class2", "class2", "class2", "class2", "class1", 
"class2", "class2", "class2", "class2", "class2", "class2", "class2", 
"class2", "class2", "class2", "class2", "class2", "class2", "class2", 
"class2", "class2", "class2", "class2", "class2", "class2", "class2", 
"class2"), healthRegion = c("Region1", "Region1", "Region1", 
"Region1", "Region2", "Region2", "Region2", "Region2", "Region2", 
"Region3", "Region3", "Region3", "Region4", "Region4", "Region4", 
"Region4", "Region3", "Region3", "Region3", "Region3", "Region3", 
"Region3", "Region2", "Region2", "Region2", "Region3", "Region3", 
"Region3", "Region3", "Region2", "Region2", "Region2", "Region2", 
"Region3", "Region4", "Region4", "Region3", "Region3", "Region1", 
"Region1", "Region1", "Region1", "Region2", "Region2", "Region2", 
"Region2", "Region2", "Region2", "Region2", "Region2", "Region3", 
"Region3", "Region3", "Region3", "Region3", "Region3", "Region3", 
"Region3", "Region4", "Region4", "Region4", "Region4", "Region2", 
"Region2", "Region3", "Region3", "Region3", "Region3", "Region3", 
"Region3", "Region3", "Region3", "Region1", "Region1", "Region1", 
"Region3", "Region3", "Region3", "Region3", "Region4", "Region4", 
"Region4", "Region4", "Region3", "Region3", "Region3", "Region3", 
"Region3", "Region3", "Region4", "Region4", "Region1", "Region1", 
"Region1", "Region1", "Region1", "Region3", "Region3", "Region2", 
"Region2", "Region2", "Region2", "Region1", "Region1", "Region1", 
"Region2", "Region2"), contactID = c(1, 1, 1, 1, 2, 2, 2, 2, 
3, 172, 172, 172, 173, 173, 173, 173, 174, 175, 175, 175, 176, 
176, 177, 177, 177, 178, 178, 178, 178, 179, 179, 179, 179, 180, 
181, 182, 183, 183, 184, 184, 184, 184, 185, 185, 185, 211, 212, 
212, 212, 212, 213, 213, 213, 213, 214, 214, 214, 214, 215, 215, 
215, 215, 216, 216, 217, 217, 217, 217, 218, 218, 218, 218, 219, 
219, 219, 220, 220, 220, 220, 221, 221, 221, 221, 222, 222, 223, 
223, 223, 223, 224, 224, 230, 231, 231, 231, 231, 232, 232, 233, 
233, 233, 233, 234, 234, 243, 244, 244), funder1 = c(500, 500, 
500, 500, 500, 500, 500, 500, NA, NA, NA, NA, 500, 500, 500, 
500, 500, 500, 500, 500, 500, 500, 500, 500, 500, NA, NA, NA, 
NA, 500, 500, 500, 500, 500, 500, 500, 500, 500, NA, NA, NA, 
NA, NA, NA, NA, 500, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
NA, NA, 500, 500, 500, 500, NA, NA, 500, 500, 500, 500, 500, 
500, 500, 500, 500, 500, 500, 500, 500, 500, 500, 500, 500, 500, 
500, 500, 500, 500, 500, 500, 500, 500, 500, NA, NA, NA, NA, 
NA, NA, NA, 500, 500, 500, 500, NA, NA, 500, NA, NA), funder2 = c(1000, 
1000, 1000, 1000, 1000, 1000, 1000, 1000, 1000, NA, NA, NA, NA, 
NA, NA, NA, 1000, NA, NA, NA, 1000, NA, 1000, 1000, 1000, NA, 
NA, 1000, 1000, 1000, 1000, 1000, 1000, 1000, NA, NA, 1000, 1000, 
1000, 1000, 1000, 1000, NA, NA, NA, 1000, NA, NA, NA, NA, NA, 
NA, NA, NA, NA, NA, NA, NA, 1000, 1000, 1000, 1000, NA, NA, NA, 
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
NA, NA, NA, NA, 1000, 1000, 1000, 1000, NA, NA, NA, NA, NA, NA, 
NA, 1000, 1000, NA, NA, NA, NA, 1000, 1000, NA, NA, NA), funder3 = c(NA, 
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA), funder4 = c(NA, NA, 
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
NA, NA, NA, NA, NA, NA, NA, NA, NA), funder5 = c(2500, 2500, 
2500, 2500, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
NA, NA, NA, NA, 2500, 2500, 2500, 2500, NA, NA, NA, NA, NA, NA, 
NA, NA, NA, NA, NA, NA, NA, 2500, 2500, NA, NA, NA), others = c(3000, 
3000, 3000, 3000, NA, NA, NA, NA, 3000, 3000, 3000, 3000, NA, 
NA, NA, NA, 3000, NA, NA, NA, 3000, NA, NA, NA, NA, 3000, 3000, 
3000, 3000, 3000, 3000, 3000, 3000, NA, NA, NA, NA, NA, 3000, 
3000, 3000, 3000, NA, NA, NA, NA, NA, NA, NA, NA, 3000, 3000, 
3000, NA, NA, NA, NA, NA, NA, NA, 3000, 3000, 3000, 3000, 3000, 
3000, 3000, 3000, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
NA, NA, NA, NA, NA, NA, 3000, 3000, NA, NA, 3000, 3000, 3000, 
3000, 3000, 3000, 3000, 3000, 3000, NA, NA, NA, NA, 3000, NA, 
NA, NA, 3000), total = c(7000, 7000, 7000, 7000, 1500, 1500, 
1500, 1500, 4000, 3000, 3000, 3000, 500, 500, 500, 500, 4500, 
500, 500, 500, 4500, 500, 1500, 1500, 1500, 3000, 3000, 4000, 
4000, 4500, 4500, 4500, 4500, 1500, 500, 500, 1500, 1500, 4000, 
4000, 4000, 4000, 0, 0, 0, 1500, 0, 0, 0, 0, 3000, 3000, 3000, 
0, 0, 0, 0, 0, 1500, 1500, 4500, 4500, 3000, 3000, 3500, 3500, 
3500, 3500, 500, 500, 500, 500, 500, 500, 500, 500, 500, 500, 
500, 500, 500, 500, 500, 500, 500, 7000, 7000, 4000, 4000, 3500, 
3500, 3000, 3000, 3000, 3000, 3000, 4000, 4000, 500, 500, 500, 
500, 6500, 3500, 500, 0, 3000), year = c("2015", "2016", "2017", 
"2018", "2015", "2016", "2017", "2018", "2015", "2016", "2017", 
"2018", "2015", "2016", "2017", "2018", "2015", "2015", "2016", 
"2017", "2015", "2018", "2016", "2017", "2018", "2015", "2016", 
"2017", "2018", "2015", "2016", "2017", "2018", "2015", "2018", 
"2016", "2015", "2017", "2015", "2016", "2017", "2018", "2016", 
"2017", "2018", "2018", "2015", "2016", "2017", "2018", "2015", 
"2016", "2017", "2018", "2015", "2016", "2017", "2018", "2015", 
"2016", "2017", "2018", "2016", "2017", "2015", "2016", "2017", 
"2018", "2015", "2016", "2017", "2018", "2016", "2017", "2018", 
"2015", "2016", "2017", "2018", "2015", "2016", "2017", "2018", 
"2015", "2018", "2015", "2016", "2017", "2018", "2015", "2016", 
"2018", "2015", "2016", "2017", "2018", "2016", "2017", "2015", 
"2016", "2017", "2018", "2016", "2017", "2018", "2015", "2016"
), serviceDivision = c("division1", "division1", "division1", 
"division1", "division2", "division2", "division2", "division2", 
"division1", "division1", "division1", "division1", "division2", 
"division2", "division2", "division2", "division1", "division2", 
"division2", "division2", "division2", "division2", "division2", 
"division2", "division2", "division3", "division3", "division3", 
"division3", "division1", "division1", "division1", "division1", 
"division2", "division2", "division2", "division2", "division2", 
"division1", "division1", "division1", "division1", "division1", 
"division1", "division1", NA, NA, NA, NA, NA, NA, NA, NA, NA, 
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, "division2", 
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
NA, NA, NA, NA, NA, NA), numFunders = c(4, 4, 4, 4, 2, 2, 2, 
2, 2, 1, 1, 1, 1, 1, 1, 1, 3, 1, 1, 1, 3, 1, 2, 2, 2, 1, 1, 2, 
2, 3, 3, 3, 3, 2, 1, 1, 2, 2, 2, 2, 2, 2, 0, 0, 0, 2, 0, 0, 0, 
0, 1, 1, 1, 0, 0, 0, 0, 0, 2, 2, 3, 3, 1, 1, 2, 2, 2, 2, 1, 1, 
1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 4, 4, 3, 3, 2, 2, 
1, 1, 1, 1, 1, 2, 2, 1, 1, 1, 1, 3, 2, 1, 0, 1), multiFunder = c(TRUE, 
TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, FALSE, FALSE, 
FALSE, FALSE, FALSE, FALSE, FALSE, TRUE, FALSE, FALSE, FALSE, 
TRUE, FALSE, TRUE, TRUE, TRUE, FALSE, FALSE, TRUE, TRUE, TRUE, 
TRUE, TRUE, TRUE, TRUE, FALSE, FALSE, TRUE, TRUE, TRUE, TRUE, 
TRUE, TRUE, FALSE, FALSE, FALSE, TRUE, FALSE, FALSE, FALSE, FALSE, 
FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, TRUE, 
TRUE, TRUE, TRUE, FALSE, FALSE, TRUE, TRUE, TRUE, TRUE, FALSE, 
FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, 
FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, TRUE, TRUE, 
TRUE, TRUE, TRUE, TRUE, FALSE, FALSE, FALSE, FALSE, FALSE, TRUE, 
TRUE, FALSE, FALSE, FALSE, FALSE, TRUE, TRUE, FALSE, FALSE, FALSE
), provFunders = c(8, 8, 7, 7, 3, 3, 3, 3, 2, 3, 2, 3, 1, 1, 
1, 1, 5, 2, 2, 1, 2, 1, 3, 3, 3, 2, 2, 3, 3, 5, 5, 5, 5, 3, 1, 
1, 3, 3, 4, 3, 3, 4, 1, 1, 1, 3, 1, 1, 1, 1, 2, 2, 2, 1, 3, 2, 
2, 2, 3, 4, 4, 4, 5, 5, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 3, 3, 
3, 3, 1, 1, 1, 1, 1, 1, 3, 3, 3, 3, 2, 2, 3, 1, 1, 1, 1, 3, 3, 
3, 4, 3, 3, 3, 5, 1, 1, 1), nonProvFunders = c(3, 4, 4, 4, 0, 
0, 0, 0, 1, 5, 3, 5, 0, 0, 0, 0, 2, 0, 0, 0, 4, 0, 1, 1, 1, 2, 
4, 3, 3, 2, 2, 3, 3, 1, 0, 0, 0, 0, 3, 4, 4, 4, 0, 0, 0, 0, 0, 
0, 0, 0, 2, 2, 2, 0, 1, 0, 0, 0, 0, 0, 1, 1, 4, 3, 1, 1, 1, 1, 
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 1, 1, 0, 0, 
3, 2, 4, 1, 1, 2, 1, 2, 2, 0, 0, 0, 1, 3, 3, 0, 0, 2), provFundedOnly = c(FALSE, 
FALSE, FALSE, FALSE, TRUE, TRUE, TRUE, TRUE, FALSE, FALSE, FALSE, 
FALSE, TRUE, TRUE, TRUE, TRUE, FALSE, TRUE, TRUE, TRUE, FALSE, 
TRUE, TRUE, TRUE, TRUE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, 
FALSE, FALSE, TRUE, TRUE, TRUE, TRUE, TRUE, FALSE, FALSE, FALSE, 
FALSE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, FALSE, 
FALSE, FALSE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, FALSE, 
FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, TRUE, TRUE, 
TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, 
TRUE, TRUE, TRUE, TRUE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, 
FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, TRUE, TRUE, 
TRUE, TRUE, FALSE, FALSE, TRUE, TRUE, FALSE), provincialFunding = c(1500, 
1500, 1500, 1500, 1500, 1500, 1500, 1500, 1000, 0, 0, 0, 500, 
500, 500, 500, 1500, 500, 500, 500, 1500, 500, 1500, 1500, 1500, 
0, 0, 1000, 1000, 1500, 1500, 1500, 1500, 1500, 500, 500, 1500, 
1500, 1000, 1000, 1000, 1000, 0, 0, 0, 1500, 0, 0, 0, 0, 0, 0, 
0, 0, 0, 0, 0, 0, 1500, 1500, 1500, 1500, 0, 0, 500, 500, 500, 
500, 500, 500, 500, 500, 500, 500, 500, 500, 500, 500, 500, 500, 
500, 500, 500, 500, 500, 1500, 1500, 1500, 1500, 500, 500, 0, 
0, 0, 0, 0, 1000, 1000, 500, 500, 500, 500, 1000, 1000, 500, 
0, 0), nonProvincialFunding = c(5500, 5500, 5500, 5500, 0, 0, 
0, 0, 3000, 3000, 3000, 3000, 0, 0, 0, 0, 3000, 0, 0, 0, 3000, 
0, 0, 0, 0, 3000, 3000, 3000, 3000, 3000, 3000, 3000, 3000, 0, 
0, 0, 0, 0, 3000, 3000, 3000, 3000, 0, 0, 0, 0, 0, 0, 0, 0, 3000, 
3000, 3000, 0, 0, 0, 0, 0, 0, 0, 3000, 3000, 3000, 3000, 3000, 
3000, 3000, 3000, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 
0, 0, 5500, 5500, 2500, 2500, 3000, 3000, 3000, 3000, 3000, 3000, 
3000, 3000, 3000, 0, 0, 0, 0, 5500, 2500, 0, 0, 3000), agencySize = c("greater than $15M", 
"greater than $15M", "greater than $15M", "greater than $15M", 
"greater than $15M", "greater than $15M", "greater than $15M", 
"greater than $15M", "less than $1M", "$7M - $15M", "$7M - $15M", 
"$7M - $15M", "$1M - $2M", "$1M - $2M", "$1M - $2M", "$1M - $2M", 
"$2M - $4M", "less than $1M", "less than $1M", "less than $1M", 
"$2M - $4M", "$2M - $4M", "$4M - $7M", "$4M - $7M", "$4M - $7M", 
"$1M - $2M", "$1M - $2M", "$2M - $4M", "$2M - $4M", "$2M - $4M", 
"$2M - $4M", "$4M - $7M", "$4M - $7M", "$2M - $4M", "less than $1M", 
"$1M - $2M", "$2M - $4M", "$2M - $4M", "$2M - $4M", "$2M - $4M", 
"$4M - $7M", "$4M - $7M", "$1M - $2M", "$1M - $2M", "$1M - $2M", 
"less than $1M", "less than $1M", "less than $1M", "less than $1M", 
"less than $1M", "$1M - $2M", "$2M - $4M", "$2M - $4M", "$1M - $2M", 
"less than $1M", "less than $1M", "less than $1M", "less than $1M", 
"$4M - $7M", "$4M - $7M", "$4M - $7M", "$4M - $7M", "greater than $15M", 
"greater than $15M", "$2M - $4M", "$2M - $4M", "$2M - $4M", "$2M - $4M", 
"$1M - $2M", "$1M - $2M", "$1M - $2M", "$1M - $2M", "$2M - $4M", 
"$2M - $4M", "$2M - $4M", "less than $1M", "less than $1M", "less than $1M", 
"less than $1M", "less than $1M", "less than $1M", "$1M - $2M", 
"$1M - $2M", "$7M - $15M", "$7M - $15M", "greater than $15M", 
"greater than $15M", "greater than $15M", "greater than $15M", 
"$1M - $2M", "$1M - $2M", "$2M - $4M", "less than $1M", "less than $1M", 
"less than $1M", "less than $1M", "$1M - $2M", "$1M - $2M", "less than $1M", 
"less than $1M", "less than $1M", "less than $1M", "$2M - $4M", 
"$2M - $4M", "$2M - $4M", "$1M - $2M", "$1M - $2M"), funder1funded = c(TRUE, 
TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, FALSE, FALSE, FALSE, 
FALSE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, 
TRUE, TRUE, TRUE, TRUE, FALSE, FALSE, FALSE, FALSE, TRUE, TRUE, 
TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, FALSE, FALSE, FALSE, 
FALSE, FALSE, FALSE, FALSE, TRUE, FALSE, FALSE, FALSE, FALSE, 
FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, TRUE, 
TRUE, TRUE, TRUE, FALSE, FALSE, TRUE, TRUE, TRUE, TRUE, TRUE, 
TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, 
TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, 
FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, TRUE, TRUE, 
TRUE, TRUE, FALSE, FALSE, TRUE, FALSE, FALSE), funder13funded = c(TRUE, 
TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, FALSE, FALSE, 
FALSE, FALSE, FALSE, FALSE, FALSE, TRUE, FALSE, FALSE, FALSE, 
TRUE, FALSE, TRUE, TRUE, TRUE, FALSE, FALSE, TRUE, TRUE, TRUE, 
TRUE, TRUE, TRUE, TRUE, FALSE, FALSE, TRUE, TRUE, TRUE, TRUE, 
TRUE, TRUE, FALSE, FALSE, FALSE, TRUE, FALSE, FALSE, FALSE, FALSE, 
FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, TRUE, 
TRUE, TRUE, TRUE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, 
FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, 
FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, TRUE, TRUE, 
TRUE, TRUE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, 
TRUE, TRUE, FALSE, FALSE, FALSE, FALSE, TRUE, TRUE, FALSE, FALSE, 
FALSE), funder2funded = c(FALSE, FALSE, FALSE, FALSE, FALSE, 
FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, 
FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, 
FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, 
FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, 
FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, 
FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, 
FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, 
FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, 
FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, 
FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, 
FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, 
FALSE, FALSE, FALSE), funder3funded = c(FALSE, FALSE, FALSE, 
FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, 
FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, 
FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, 
FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, 
FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, 
FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, 
FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, 
FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, 
FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, 
FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, 
FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, 
FALSE, FALSE, FALSE, FALSE, FALSE), funder4funded = c(TRUE, TRUE, 
TRUE, TRUE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, 
FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, 
FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, 
FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, 
FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, 
FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, 
FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, 
FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, 
FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, 
FALSE, FALSE, TRUE, TRUE, TRUE, TRUE, FALSE, FALSE, FALSE, FALSE, 
FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, 
TRUE, TRUE, FALSE, FALSE, FALSE), funder5funded = c(TRUE, TRUE, 
TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, 
TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, 
TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, 
TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, 
TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, 
TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, 
TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, 
TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, 
TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, 
TRUE, TRUE, TRUE, TRUE, TRUE, TRUE), sectorFunded = c("TRUE", 
"TRUE", "TRUE", "TRUE", "TRUE", "TRUE", "TRUE", "TRUE", "TRUE", 
"TRUE", "TRUE", "TRUE", "TRUE", "TRUE", "TRUE", "TRUE", "TRUE", 
"TRUE", "TRUE", "TRUE", "TRUE", "TRUE", "TRUE", "TRUE", "TRUE", 
"TRUE", "TRUE", "TRUE", "TRUE", "TRUE", "TRUE", "TRUE", "TRUE", 
"TRUE", "TRUE", "TRUE", "TRUE", "TRUE", "TRUE", "TRUE", "TRUE", 
"TRUE", "TRUE", "TRUE", "TRUE", "TRUE", "TRUE", "TRUE", "TRUE", 
"TRUE", "TRUE", "TRUE", "TRUE", "TRUE", "TRUE", "TRUE", "TRUE", 
"TRUE", "TRUE", "TRUE", "TRUE", "TRUE", "TRUE", "TRUE", "TRUE", 
"TRUE", "TRUE", "TRUE", "TRUE", "TRUE", "TRUE", "TRUE", "TRUE", 
"TRUE", "TRUE", "TRUE", "TRUE", "TRUE", "TRUE", "TRUE", "TRUE", 
"TRUE", "TRUE", "TRUE", "TRUE", "TRUE", "TRUE", "TRUE", "TRUE", 
"TRUE", "TRUE", "TRUE", "TRUE", "TRUE", "TRUE", "TRUE", "TRUE", 
"TRUE", "TRUE", "TRUE", "TRUE", "TRUE", "TRUE", "TRUE", "TRUE", 
"TRUE", "TRUE"), totHolder = c(7000, 7000, 7000, 7000, 1500, 
1500, 1500, 1500, 4000, 3000, 3000, 3000, 500, 500, 500, 500, 
4500, 500, 500, 500, 4500, 500, 1500, 1500, 1500, 3000, 3000, 
4000, 4000, 4500, 4500, 4500, 4500, 1500, 500, 500, 1500, 1500, 
4000, 4000, 4000, 4000, 0, 0, 0, 1500, 0, 0, 0, 0, 3000, 3000, 
3000, 0, 0, 0, 0, 0, 1500, 1500, 4500, 4500, 3000, 3000, 3500, 
3500, 3500, 3500, 500, 500, 500, 500, 500, 500, 500, 500, 500, 
500, 500, 500, 500, 500, 500, 500, 500, 7000, 7000, 4000, 4000, 
3500, 3500, 3000, 3000, 3000, 3000, 3000, 4000, 4000, 500, 500, 
500, 500, 6500, 3500, 500, 0, 3000)), row.names = c(NA, -107L
), class = c("tbl_df", "tbl", "data.frame"))

Please note that provincialFunding is the sum of funders 1,2, and 3 and nonProvincialFunding is the sum of funders 4,5 and others.

The grouping clause passed to the function for this example is:

GRclauseSDregion <- c("year", "employerClass", "multiFunder", "serviceDivision", "healthRegion")

There are multiple grouping clauses;

GRsectorProfile <- c("year", "employerClass", "multiFunder")
GRclass1SD <- c("year", "employerClass", "multiFunder", "serviceDivision")
GRsectorRegion <- c("year", "employerClass", "multiFunder", "healthRegion")
GRclass1SDregion <- c("year", "employerClass", "multiFunder", "serviceDivision", "healthRegion")
GRsectorAgencySize <- c("year", "employerClass", "multiFunder", "agencySize")
GRclass1AgencySize <- c("year","employerClass", "multiFunder", "agencySize")
GRclass1SDagencySize <- c("year", "employerClass", "multiFunder", "serviceDivision", "agencySize")
GRclass1SDregionAgencySize <- c("year", "employerClass", "multiFunder", "serviceDivision", "healthRegion", "agencySize")
GRnonCLASS1AregionAgencySize <- c("year", "employerClass", "multiFunder", "healthRegion", "agencySize")
GRclass1RegionAgencySize <- c("year", "employerClass", "multiFunder", "healthRegion", "agencySize")

This is where I have gotten to:

# collapse function (funder)
collapsePassFunderArray <- function(df, funderTrueCol, fundingCol, groupClause, name) {
  funderTrueHolder <- enquo(funderTrueCol)
  fundingColHolder <- enquo(fundingCol)
  newDf <- df %>% 
    filter((!!funderTrueHolder) == TRUE) %>% 
    group_by(.dots = groupClause) %>% 
    summarise(funderName = name, 
              totFund = sum(total),
              minFunder = min(numFunders),
              maxFunder = max(numFunders),
              avgFunder = mean(numFunders, na.rm = TRUE),
              medianFunder = median(numFunders, na.rm = TRUE),
              provPerc = sum(provincialFunding)/sum(total),
              nonProvPerc = sum(nonProvincialFunding)/sum(total),
              funderPercProv = sum((!!fundingColHolder))/sum(provincialFunding),
              funderPercTot = sum((!!fundingColHolder))/sum(total),
              numAgencies = n_distinct(contactID),
              funders = simplify2array(apply(df[8:13], 1, 
                                            function(x)paste(names(df[6:11]) [which(!is.na(x))], collapse = " ")))
    )
}

Please note that the dataframe columns being selected [6:11] are those columns that would have the funders under scrutiny. This could change over time but I don't mind making the changes manually.

The following code produces the desired output when applied to the full dataset (the desired output being the space separated list of funders in the funders variable):

reproTable$funders <- simplify2array(apply(reproTable[6:11], 1, 
                                           function(x)paste(names(reproTable[8:13]) [which(!is.na(x))], collapse = " ")))

I run the following function call:

test1 <- collapsePassFunderArray(reproTable, funder1funded, funder1, GRclauseSDregion, "All")

Which gives the following error:

Error: Column `funders` must be length 1 (a summary value), not 107

Here is the desired output on the assumption that we are using the GRsectorRegion <- c("year", "employerClass", "multiFunder", "healthRegion") grouping clause as per user @akrun request:

https://pastebin.com/1Z8vRLir

Sorry I had to use pastebin to paste the desired output source it went over the character limit.

Thank you to those who have tried to crack this I have tried to adapt the code but have run into the problems described in the comments.

2
rdataframedplyr

2 Answers

1
votes

We can use group_by_at to pass string column names along with pmap from purrr for pasteing the elements

library(dplyr)
library(stringr)
library(purrr)
collapsePassFunderArray <- function(df, funderTrueCol, 
                   fundingCol, groupClause, name) {
  funderTrueHolder <- enquo(funderTrueCol)
  fundingColHolder <- enquo(fundingCol)
  df %>% 
    filter((!!funderTrueHolder) == TRUE) %>% 
    mutate(funder = pmap_chr(select(., matches("^(funder\\d+$)|(^others$)")),
       ~ {x1 <- c(...)
         str_c(names(x1)[!is.na(x1)], collapse=" ")
         }))  %>%
    group_by_at(vars(intersect(groupClause, names(.)), 'funder')) %>%
    summarise(funderName = name, 
              totFund = sum(total),
              minFunder = min(numFunders),
              maxFunder = max(numFunders),
              avgFunder = mean(numFunders, na.rm = TRUE),
              medianFunder = median(numFunders, na.rm = TRUE),
              provPerc = sum(provincialFunding)/sum(total),
              nonProvPerc = sum(nonProvincialFunding)/sum(total),
              funderPercProv = sum((!!fundingColHolder))/sum(provincialFunding),
              funderPercTot = sum((!!fundingColHolder))/sum(total),
              numAgencies = n_distinct(contactID))




    }

-testing

test1 <- collapsePassFunderArray(reproTable, funder1funded, funder1, GRclauseSDregion, "All")
test1$funder
#[1] "funder1 funder3 funder5 others"  "funder1 funder3 funder4 funder5" "funder1 funder3 funder4 funder5"
#[4] "funder1 funder3 funder5"

test2 <- collapsePassFunderArray(reproTable, funder2funded, funder2, GRclauseSDregion, "All")
test2$funder
#[1] "funder2 funder4"        "funder2 funder4 others" "funder2 funder4 others"
testothers <- collapsePassFunderArray(reproTable, otherFunded, others, GRclauseSDregion, "All")
testothers$funder
#[1] "funder1 funder3 funder5 others" "funder2 funder4 others"         "funder2 funder4 others"
1
votes

Update on 2020-04-01

"Thing is I need the provvincialFunding summary value that it spits out to be the sum off all the provincial; funding within the subset set by the group clause. So when I run this on upper layers of aggregation like to the overall sector by the funder it starts giving me the wrong figures. Also when I take total out of the equation I can't run it on the sector as a whole."

I had another stab at this based on your comments and the output shared via your pastebin link. If this still doesn't answer your question, could I please ask that you specifically share examples of the "right" answers for a wider array of grouping clauses as this would hopefully make it easier to see where/when the method is failing. For example, it is not clear to me what your expected output for "aggregation like to the overall sector by the funder" is.

Before showing the updated approach below, there are a number of caveats:

  • There seems to be some sort of disconnect between the data shared in your OP and the output shared via pastebin. Specifically:
    • It seems as though the input data used to generate the output shared via pastebin differs somewhat from what was shared in your OP.
    • The pastebin data contains column multiFunded as opposed to multiFunder as per your OP
    • The pastebin data includes a numFunder column, though the collapsePassFunderArray() function in your OP does not
    • Your pastebin output does not contain a medianFunder column as per the collapsePassFunderArray() function in your OP
    • The values in your pastebin output for columns funderPercProv and funderPercTot confuse me: I would have assumed that funderPercProv should represent total funding by funder x as a percentage of total provincial funding within the grouping clause. However, despite the fact that there are several instances where, for example, total funding by others exceeds total provincial funding within your example grouping clause, the value of funderPercProv in your pastebin data never exceeds 1. This doesn't seem right to me, but perhaps I'm missing something. Similarly, the funderPercTot values in your pastebin data don't seem to accord with the definition of total funding by funder x as a percentage of total funding within the grouping clause. For example, if you look at the data from your OP for which the following filter holds true: filter(year == 2015 & employerClass == 'class1' & multiFunder == F & healthRegion == 'Region3'), then one can clearly see that funding by others amounts to 3000/3500 = 0.8571429 of total funding within the grouping clause ("year", "employerClass", "multiFunder", "healthRegion"). However, your pastebin output reports this value as 0.14285714.

These issues make it very difficult to verify whether the following actually gives you what you need. Nevertheless, here's hoping that it does. I have assumed that the above-mentioned definitions for funderPercProv and funderPercTot are what is actually desired.

# load required libraries
library(tidyverse)

# NB: define original input data as per OP and store as object reproTable

# first convert data into long format
reproTable_long <- reproTable %>%
  select(-totHolder) %>%
  pivot_longer(
    cols = matches(
      '^funder\\d{1,}$|^others$|^total$|^provincialFunding$|nonProvincialFunding'
    ),
    names_to = 'funder'
  )

 # define function of interest 
collapsePassFunderArray <- function(df, FunderVal, groupClause, name) {
  reproTable_long %>% 
    group_by(.dots = GRsectorRegion) %>% 
    filter(sum(value[funder == FunderVal], na.rm = T) > 0) %>%
    summarise(
      funderName = name,
      totFund = sum(value[funder == 'total'], na.rm = T),
      minFunder = min(numFunders, na.rm = T),
      maxFunder = max(numFunders, na.rm = T),
      avgFunder = mean(numFunders, na.rm = TRUE),
      medianFunder = median(numFunders, na.rm = TRUE),
      provPerc = sum(value[funder == 'provincialFunding'], na.rm = T) / 
        sum(value[funder == 'total'], na.rm = T),
      nonProvPerc = sum(value[funder == 'nonProvincialFunding'], na.rm = T) / 
        sum(value[funder == 'total'], na.rm = T),
      funderPercProv = sum(value[funder == FunderVal], na.rm = T) / 
        sum(value[funder == 'provincialFunding'], na.rm = T),
      funderPercTot = sum(value[funder == FunderVal], na.rm = T) / 
        sum(value[funder == 'total'], na.rm = T),
      numAgencies = n_distinct(contactID),
      funders = funder[!is.na(value) & !funder %in% c('total', 'provincialFunding', 'nonProvincialFunding')] %>% 
        unique() %>% 
        paste( collapse = " ")
    ) %>% 
    ungroup()
} 


 # define grouping clauses
GRclauseSDregion <- c("year", "employerClass", "multiFunder", "division", "healthRegion")
GRsectorProfile <- c("year", "employerClass", "multiFunder")
GRcsseaSD <- c("year", "employerClass", "multiFunder", "serviceDivision")
GRsectorRegion <- c("year", "employerClass", "multiFunder", "healthRegion")
GRcsseaSDregion <- c("year", "employerClass", "multiFunder", "serviceDivision", "healthRegion")
GRsectorAgencySize <- c("year", "employerClass", "multiFunder", "agencySize")
GRcsseaAgencySize <- c("year","employerClass", "multiFunder", "agencySize")
GRcsseaSDagencySize <- c("year", "employerClass", "multiFunder", "serviceDivision", "agencySize")
GRcsseaSDregionAgencySize <- c("year", "employerClass", "multiFunder", "serviceDivision", "healthRegion", "agencySize")
GRnonCSSEAregionAgencySize <- c("year", "employerClass", "multiFunder", "healthRegion", "agencySize")
GRcsseaRegionAgencySize <- c("year", "employerClass", "multiFunder", "healthRegion", "agencySize")

# test a few clauses for funder1, funder2, etc
others_GRclauseSDregion <- collapsePassFunderArray(reproTable_long, 'others', GRclauseSDregion, name = 'All')
funder1_GRcsseaSD <- collapsePassFunderArray(reproTable_long, 'funder1', GRcsseaSD, name = 'All')
funder2_GRcsseaAgencySize <- collapsePassFunderArray(reproTable_long, 'funder2', GRcsseaAgencySize, name = 'All')
funder5_GRnonCSSEAregionAgencySize <- collapsePassFunderArray(reproTable_long, 'funder5', GRnonCSSEAregionAgencySize, name = 'All')

Original reponse

Thank you for the careful explanation and the solid reprex!

I just can't seem to wrap my head around what I would do here to get the desired result. I think that I have to make quosures for the function within the function and have read and reread the vignetter on programming in dplyr several times but I'm just not getting it.

You've already highlighted that the following segment of your collapsePassFunderArray array function works perfectly fine on the ungrouped data:

funders = simplify2array(apply(df[8:13], 1,
                               function(x)
                                 paste(names(df[8:13]) [which(!is.na(x))], collapse = " "))

I suspect that (though am not 100% certain), in it's current form, this function operates on the original, unfiltered and ungrouped input data df and not on the filtered and grouped data that you are piping into your summarise call inside collapsePassFunderArray(). If this is indeed the case, the error you receive makes sense: The function is still operating on the original, 7-row input data, and will therefore try to return 7 rows.

As for generating the results you want: I took a stab at it taking a somewhat different, but hopefully still useful, approach. Below, I share the reprex first, then attempt to explain it further thereafter: 

# load required libraries
library(tidyverse)

# define data using the data pasted in the original post

# define function of interest 
collapsePassFunderArray <- function(df, FunderVal, groupClause) {

  df %>% 
    pivot_longer(cols = matches('^funder\\d{1,}$|^others$|^total$'), names_to = 'funder') %>% 
    filter(funder != 'total')  %>% 
    group_by(.dots = groupClause) %>% 
    filter(sum(value[funder == FunderVal], na.rm = T) > 0) %>% 
    summarise(
      funderName = first(name),
      totFund = sum(value, na.rm = T),
      minFunder = min(numFunders),
      maxFunder = max(numFunders),
      avgFunder = mean(numFunders, na.rm = TRUE),
      medianFunder = median(numFunders, na.rm = TRUE),
      provPerc = first(provincialFunding) / totFund,
      nonProvPerc = first(nonProvincialFunding) / totFund,
      funderPercProv = sum(value[funder == FunderVal]) / first(provincialFunding),
      funderPercTot = sum(value[funder == FunderVal]) / totFund,
      numAgencies = n_distinct(contactID),
      funders = paste(funder[!is.na(value)], collapse = " ")
    )
}

# define grouping clause
GRclauseSDregion <- c("year", "employerClass", "multiFunder", "division", "healthRegion")

# test for funder1, funder2, etc
test1 <- collapsePassFunderArray(reproTable, 'funder1', GRclauseSDregion)
test2 <- collapsePassFunderArray(reproTable, 'funder2', GRclauseSDregion)
testothers <- collapsePassFunderArray(reproTable, 'others', GRclauseSDregion)

Here's my attempt at explaining the function:

  • Take the input data and first reshape it from wide to long (I find it much, much easier to work with data in long format). The code to reshape to long is quite flexible as it relies on regex matching, rather than positional targeting, to identify the relevant funder columns. Note, however, that this code assumes that your funder columns will always either be called funder*, where * represents a numerical suffix, or others. If this is the case, the code should be able to deal with an arbitrary number of funder columns. Alternatively, you could simply adapt the regex used.
  • Next, remove all instances where the new funder column has a value of total (it is always possible to recreate this value based on the other rows in the data)
  • Next, apply your chosen grouping clause
  • With the data grouped, we now use filter to ensure that we only retain data for all groups for which the total funding provided by the funder in question is greater than zero
  • Next we jump into the summarise call. Note that most of the changes from your original function that have been made here are simply there to account for the fact that the data has been pivoted from wide to long. The only substantive difference is the way in which I create the collapsed string of funders. Specifically, I simply subset all of the rows to retain only those funders who had non NA contributions. Then I collapse these into a space-separated character string.

My testing suggests that the results align with what is generated by the code you provided in your original post. That said, I realise that I've substantively altered the way the function works and, more importantly, the way it needs to be called. Perhaps this is not what you're looking for, but I hope it's on the right track!