Summary of multiple columns

Question

Need help with the summarise and group function on the dataset below

The goal is to get a sum of all non blanks against the column with header "name"

Sample of data using dput below

structure(list(Name = c("CICS_TEL_6_PP Nov2019", "CICS_TEL_6_PP Nov2019", 
"CICS_TEL_6_PP Nov2019", "CICS_TEL_6_PP Nov2019", "CICS_TEL_6_PP Nov2019", 
"CICS_TEL_6_PP Nov2019"), ResponseID = c("R_3m9rPOhCDF9NOUy", 
"R_30pk7WWmuFJLNZ9", "R_2UXISAuayZqcSS6", "R_3szvWUgiCdhl6y2", 
"R_1DGrZ7gfZ8vkljb", "R_2usuFssDJ7wxJUv"), StartDate = c("01-11-2019 06:08", 
"01-11-2019 06:08", "01-11-2019 06:08", "01-11-2019 06:08", "01-11-2019 06:08", 
"01-11-2019 06:08"), EndDate = c("01-11-2019 06:13", "01-11-2019 06:10", 
"01-11-2019 06:51", "01-11-2019 07:14", "02-11-2019 14:54", "01-11-2019 06:29"
), Month = c("Nov", "Nov", "Nov", "Nov", "Nov", "Nov"), Year = c(2019L, 
2019L, 2019L, 2019L, 2019L, 2019L), Mnth.Yr = c("Nov-19", "Nov-19", 
"Nov-19", "Nov-19", "Nov-19", "Nov-19"), NPS = c(2L, 10L, 10L, 
2L, 10L, 10L), Effort = c(5L, 10L, 10L, 5L, 9L, 10L), Reason.did.you.rate.your.support.experience.as = c("", 
"", "", "", "", ""), I.feel.that.Three.is.putting.in.a.significant.effort.to.make.my.experience.with.them.a.positive.one. = c(NA_integer_, 
NA_integer_, NA_integer_, NA_integer_, NA_integer_, NA_integer_
), feel.that.I.need.to.put.in.more.effort.than.I.would.expect.when.I.deal.with.Three. = c(NA_integer_, 
NA_integer_, NA_integer_, NA_integer_, NA_integer_, NA_integer_
), How.satisfied.were.you.will.your.recent.experiences..Network.Performance = c(NA_integer_, 
NA_integer_, NA_integer_, NA_integer_, NA_integer_, NA_integer_
), How.satisfied.were.youCustomer.Care = c(NA_integer_, NA_integer_, 
NA_integer_, NA_integer_, NA_integer_, NA_integer_), How.satisfied.Website = c(NA_integer_, 
NA_integer_, NA_integer_, NA_integer_, NA_integer_, NA_integer_
), How.satisfied..Store = c(NA_integer_, NA_integer_, NA_integer_, 
NA_integer_, NA_integer_, NA_integer_), How.satisfied.were.you.N.A = c(NA_integer_, 
NA_integer_, NA_integer_, NA_integer_, NA_integer_, NA_integer_
), ffers.good.value.for.money = c(NA_integer_, NA_integer_, NA_integer_, 
NA_integer_, NA_integer_, NA_integer_), Is.a.brand.I.can.trust = c(NA_integer_, 
NA_integer_, NA_integer_, NA_integer_, NA_integer_, NA_integer_
), Understands.my.needs.as.a.customer = c(NA_integer_, NA_integer_, 
NA_integer_, NA_integer_, NA_integer_, NA_integer_), Rewards.customers.for.their.loyalty = c(NA_integer_, 
NA_integer_, NA_integer_, NA_integer_, NA_integer_, NA_integer_
), Meets.my.expectations = c(NA_integer_, NA_integer_, NA_integer_, 
NA_integer_, NA_integer_, NA_integer_), Three.makes.a.significant.effort.to.make = c(NA_integer_, 
NA_integer_, NA_integer_, NA_integer_, NA_integer_, NA_integer_
), My.monthly.mobile.costs.meets.my.expectations. = c(NA_integer_, 
NA_integer_, NA_integer_, NA_integer_, NA_integer_, NA_integer_
), My.experience.with.Three.s.network.meets.my.everyday.needs. = c(NA_integer_, 
NA_integer_, NA_integer_, NA_integer_, NA_integer_, NA_integer_
), What.is.your.preferred.channel.to.use.if.you.need.support.or.assistance. = c(NA_integer_, 
NA_integer_, NA_integer_, NA_integer_, NA_integer_, NA_integer_
), Do.you.find.it.easy.to.access.support.when.you.need.help. = c(NA_integer_, 
NA_integer_, NA_integer_, NA_integer_, NA_integer_, NA_integer_
), Three.s.products.and.services.enhance.my.life. = c(NA_integer_, 
NA_integer_, NA_integer_, NA_integer_, NA_integer_, NA_integer_
), Are.you.aware.of.3Plus.rewards. = c(NA_integer_, NA_integer_, 
NA_integer_, NA_integer_, NA_integer_, NA_integer_), Three.is.a.brand.I.trust. = c(NA_integer_, 
NA_integer_, NA_integer_, NA_integer_, NA_integer_, NA_integer_
), We.would.like.to.hear.your.thoughts..is.there.anything.else.you.would.like.us.to.focus.on. = c("", 
"", "", "", "", ""), What.steps.can.we.take.to.build.your.trust.with.us. = c("", 
"", "", "", "", ""), I.need.to.put.in.more.effort.than.I.would.expe = c("", 
"", "", "", "", ""), How.satisfied.were.you.with.your.recent.Network.experience = c(NA_integer_, 
NA_integer_, NA_integer_, NA_integer_, NA_integer_, NA_integer_
), Three.offers.good.value.for.money = c(NA_integer_, NA_integer_, 
NA_integer_, NA_integer_, NA_integer_, NA_integer_), Three.is.a.brand.I.can.trust = c(NA_integer_, 
NA_integer_, NA_integer_, NA_integer_, NA_integer_, NA_integer_
), Three.understands.my.needs.as.a.customer = c(NA_integer_, 
NA_integer_, NA_integer_, NA_integer_, NA_integer_, NA_integer_
), Three.Rewards.customers.for.their.loyalty = c(NA_integer_, 
NA_integer_, NA_integer_, NA_integer_, NA_integer_, NA_integer_
), Three.meets.my.expectations = c(NA_integer_, NA_integer_, 
NA_integer_, NA_integer_, NA_integer_, NA_integer_), what.you.feel.could.be.done.better.to.improve.your.overall.exper = c("", 
"", "", "", "", ""), Ability.to.make.and.receive.calls.and.texts = c(NA_integer_, 
NA_integer_, NA_integer_, NA_integer_, NA_integer_, NA_integer_
), experience.on.data.speeds.with.your.data.connection... = c(NA_integer_, 
NA_integer_, NA_integer_, NA_integer_, NA_integer_, NA_integer_
), On.a.scale.of.1.to.5.how.would.you.rate.your.coverage.in.your.home = c(NA_integer_, 
NA_integer_, NA_integer_, NA_integer_, NA_integer_, NA_integer_
), Is.this.related.to.indoor.outdoor.coverage = c(NA_integer_, 
NA_integer_, NA_integer_, NA_integer_, NA_integer_, NA_integer_
), Thinking.about.your.recent.experience.usage.of.the.Three.Mobile.Network.over.the.last.7.days = c(NA_integer_, 
NA_integer_, NA_integer_, NA_integer_, NA_integer_, NA_integer_
), Usage.of.the.Three.Mobile.Network.over.the.last.7.days..hav... = c(NA_integer_, 
NA_integer_, NA_integer_, NA_integer_, NA_integer_, NA_integer_
), improve.your.overall.network.experience.over... = c("", "", 
"", "", "", ""), reason.did.you.rate.your.network.experience.as = c("", 
"", "", "", "", ""), To.what.extent.do.you.trust.the.Three.Network = c(NA_integer_, 
NA_integer_, NA_integer_, NA_integer_, NA_integer_, NA_integer_
), easy.or.difficult.was.it.to.purchase.in.store = c(NA_integer_, 
NA_integer_, NA_integer_, NA_integer_, NA_integer_, NA_integer_
), Was.the.Sales.Agent.Welcoming = c(NA_integer_, NA_integer_, 
NA_integer_, NA_integer_, NA_integer_, NA_integer_), Sales.Agent.take.the.time.to.understand.your.needs.and.tailor.the.offer.to.suit = c(NA_integer_, 
NA_integer_, NA_integer_, NA_integer_, NA_integer_, NA_integer_
), Was.the.Sales..Agent.helpful. = c(NA_integer_, NA_integer_, 
NA_integer_, NA_integer_, NA_integer_, NA_integer_), Did.the.Sales.Agent.inform.you.of.the.3Plus.Programme = c(NA_integer_, 
NA_integer_, NA_integer_, NA_integer_, NA_integer_, NA_integer_
), Did.the.Sales.Agent.say.Thank.You = c(NA_integer_, NA_integer_, 
NA_integer_, NA_integer_, NA_integer_, NA_integer_), rate.your.recent.purchase.experience = c("", 
"", "", "", "", ""), Sales.Agent.could.have.done.better.to.improve = c("", 
"", "", "", "", ""), Three.may.call.you.to.discuss.your.feedback = c(NA_integer_, 
NA_integer_, NA_integer_, NA_integer_, NA_integer_, NA_integer_
), person.was.knowledgeable = c(NA, NA, 5L, NA, NA, 5L), complete.confidence.in.the.person = c(NA, 
NA, NA, 4L, NA, NA), Person.listened.and.understood = c(5L, 5L, 
NA, NA, 5L, NA), FCR = c(1L, 1L, 1L, 1L, 1L, 1L), Three.is.good.at.fixing.issues = c(NA_integer_, 
NA_integer_, NA_integer_, NA_integer_, NA_integer_, NA_integer_
), Information..required.on.the.My3.App = c(NA_integer_, NA_integer_, 
NA_integer_, NA_integer_, NA_integer_, NA_integer_), Verb = c("You obviously have no loyalty dept", 
"Nothing", "He couldn't have done any more very polite very helpful and a great Person for ur company ??", 
"Nothing cause 3 take my money and fee me for no reason", "", 
"There is nothing I can say that it needed to improve I was very happy with the way my query was handled in a very professional manner"
)), row.names = c(NA, 6L), class = "data.frame")

The code i have tried

`Dropoff_II %>% group_by(Name) %>%`
summarise_at(vars(NPS:Verb),list(count=~sum(!is.na(.x))))`

The summarized output (sum) does not match the actual numbers, the formula is unable to produce the actual sum of rows for the given conditions

A crude sample of the output i'd like is illustrated below with only two columns, (nps count and effort count) (total 59 columns given the distribution of data)

Name	nps count		effort count
CICS_TEL_6_PPJan Feb2020	xxx	xxx
J_FTC_RTL_PPUpdated JanFeb2020	xxx	xxx

PS: OP had some typos, apologies!

Help appreciated guys

This question makes no sense. What does "The goal is to get a sum or rows for all non blanks for all the columns against two summary variables" mean? Which two summary variables? Do you mean "sum of rows"? — masher, Jan 25 '21 at 05:38
Thank for you responding guys. Essentially i am trying to get the sum of all non blank rows against the two variables, Mnth-Yr and Name columns. I'll post a sample of the output i got but the values are incorrect compared to the actual values if computed manually — Iqbal S, Jan 25 '21 at 06:10
Hi @RonakShah The output i am getting does not match the actuals, say, a summary of the first row "CICS_TEL_6_PPJan Feb2020" for subsequent columns does not add to actual value, if total rows for "nps"in the raw data is 1538, is repeats the same output for "Reason.did.you.rate.your.support.experience" . where as the actual total for this column is 0 as there are no values in that column at all — Iqbal S, Jan 25 '21 at 06:39
so do you want "nps count" or "nps sum"? The text says sum, the table says count. — masher, Jan 25 '21 at 07:00

masher · Answer 1 · 2021-01-25T08:46:37.507

I think this gives you the result you're looking for.

library(tidyverse)

data <- 
  structure(list(Name = c("CICS_TEL_6_PP Nov2019", "CICS_TEL_6_PP Nov2019", "CICS_TEL_6_PP Nov2019", "CICS_TEL_6_PP Nov2019", "CICS_TEL_6_PP Nov2019", "CICS_TEL_6_PP Nov2019"), 
                 ResponseID = c("R_3m9rPOhCDF9NOUy", "R_30pk7WWmuFJLNZ9", "R_2UXISAuayZqcSS6", "R_3szvWUgiCdhl6y2","R_1DGrZ7gfZ8vkljb", "R_2usuFssDJ7wxJUv"), 
                 StartDate = c("01-11-2019 06:08", "01-11-2019 06:08", "01-11-2019 06:08", "01-11-2019 06:08", "01-11-2019 06:08", "01-11-2019 06:08"), 
                 EndDate = c("01-11-2019 06:13", "01-11-2019 06:10", "01-11-2019 06:51", "01-11-2019 07:14", "02-11-2019 14:54", "01-11-2019 06:29"), 
                 Month = c("Nov", "Nov", "Nov", "Nov", "Nov", "Nov"), 
                 Year = c(2019L,2019L, 2019L, 2019L, 2019L, 2019L), 
                 Mnth.Yr = c("Nov-19", "Nov-19","Nov-19", "Nov-19", "Nov-19", "Nov-19"), 
                 NPS = c(2L, 10L, 10L, 2L, 10L, 10L), 
                 Effort = c(5L, 10L, 10L, 5L, 9L, 10L), 
                 Reason.did.you.rate.your.support.experience.as = c("", "", "", "", "", ""), 
                 I.feel.that.Three.is.putting.in.a.significant.effort.to.make.my.experience.with.them.a.positive.one. = c(NA_integer_,NA_integer_, NA_integer_, NA_integer_, NA_integer_, NA_integer_), 
                 feel.that.I.need.to.put.in.more.effort.than.I.would.expect.when.I.deal.with.Three. = c(NA_integer_, NA_integer_, NA_integer_, NA_integer_, NA_integer_, NA_integer_), 
                 How.satisfied.were.you.will.your.recent.experiences..Network.Performance = c(NA_integer_, NA_integer_, NA_integer_, NA_integer_, NA_integer_, NA_integer_), 
                 How.satisfied.were.youCustomer.Care = c(NA_integer_, NA_integer_, NA_integer_, NA_integer_, NA_integer_, NA_integer_), 
                 How.satisfied.Website = c(NA_integer_, NA_integer_, NA_integer_, NA_integer_, NA_integer_, NA_integer_), 
                 How.satisfied..Store = c(NA_integer_, NA_integer_, NA_integer_, NA_integer_, NA_integer_, NA_integer_), 
                 How.satisfied.were.you.N.A = c(NA_integer_, NA_integer_, NA_integer_, NA_integer_, NA_integer_, NA_integer_), 
                 ffers.good.value.for.money = c(NA_integer_, NA_integer_, NA_integer_, NA_integer_, NA_integer_, NA_integer_), 
                 Is.a.brand.I.can.trust = c(NA_integer_, NA_integer_, NA_integer_, NA_integer_, NA_integer_, NA_integer_), 
                 Understands.my.needs.as.a.customer = c(NA_integer_, NA_integer_, NA_integer_, NA_integer_, NA_integer_, NA_integer_), 
                 Rewards.customers.for.their.loyalty = c(NA_integer_, NA_integer_, NA_integer_, NA_integer_, NA_integer_, NA_integer_), 
                 Meets.my.expectations = c(NA_integer_, NA_integer_, NA_integer_, NA_integer_, NA_integer_, NA_integer_), 
                 Three.makes.a.significant.effort.to.make = c(NA_integer_, NA_integer_, NA_integer_, NA_integer_, NA_integer_, NA_integer_), 
                 My.monthly.mobile.costs.meets.my.expectations. = c(NA_integer_, NA_integer_, NA_integer_, NA_integer_, NA_integer_, NA_integer_), 
                 My.experience.with.Three.s.network.meets.my.everyday.needs. = c(NA_integer_, NA_integer_, NA_integer_, NA_integer_, NA_integer_, NA_integer_), 
                 What.is.your.preferred.channel.to.use.if.you.need.support.or.assistance. = c(NA_integer_,NA_integer_, NA_integer_, NA_integer_, NA_integer_, NA_integer_), 
                 Do.you.find.it.easy.to.access.support.when.you.need.help. = c(NA_integer_,NA_integer_, NA_integer_, NA_integer_, NA_integer_, NA_integer_), 
                 Three.s.products.and.services.enhance.my.life. = c(NA_integer_,NA_integer_, NA_integer_, NA_integer_, NA_integer_, NA_integer_), 
                 Are.you.aware.of.3Plus.rewards. = c(NA_integer_, NA_integer_, NA_integer_, NA_integer_, NA_integer_, NA_integer_), 
                 Three.is.a.brand.I.trust. = c(NA_integer_, NA_integer_, NA_integer_, NA_integer_, NA_integer_, NA_integer_), 
                 We.would.like.to.hear.your.thoughts..is.there.anything.else.you.would.like.us.to.focus.on. = c("", "", "", "", "", ""), 
                 What.steps.can.we.take.to.build.your.trust.with.us. = c("","", "", "", "", ""), 
                 I.need.to.put.in.more.effort.than.I.would.expe = c("", "", "", "", "", ""), 
                 How.satisfied.were.you.with.your.recent.Network.experience = c(NA_integer_, NA_integer_, NA_integer_, NA_integer_, NA_integer_, NA_integer_), 
                 Three.offers.good.value.for.money = c(NA_integer_, NA_integer_,NA_integer_, NA_integer_, NA_integer_, NA_integer_), 
                 Three.is.a.brand.I.can.trust = c(NA_integer_, NA_integer_, NA_integer_, NA_integer_, NA_integer_, NA_integer_), 
                 Three.understands.my.needs.as.a.customer = c(NA_integer_, NA_integer_, NA_integer_, NA_integer_, NA_integer_, NA_integer_), 
                 Three.Rewards.customers.for.their.loyalty = c(NA_integer_, NA_integer_, NA_integer_, NA_integer_, NA_integer_, NA_integer_), 
                 Three.meets.my.expectations = c(NA_integer_, NA_integer_, NA_integer_, NA_integer_, NA_integer_, NA_integer_), 
                 what.you.feel.could.be.done.better.to.improve.your.overall.exper = c("", "", "", "", "", ""), 
                 Ability.to.make.and.receive.calls.and.texts = c(NA_integer_, NA_integer_, NA_integer_, NA_integer_, NA_integer_, NA_integer_), 
                 experience.on.data.speeds.with.your.data.connection... = c(NA_integer_, NA_integer_, NA_integer_, NA_integer_, NA_integer_, NA_integer_), 
                 On.a.scale.of.1.to.5.how.would.you.rate.your.coverage.in.your.home = c(NA_integer_, NA_integer_, NA_integer_, NA_integer_, NA_integer_, NA_integer_), 
                 Is.this.related.to.indoor.outdoor.coverage = c(NA_integer_, NA_integer_, NA_integer_, NA_integer_, NA_integer_, NA_integer_), 
                 Thinking.about.your.recent.experience.usage.of.the.Three.Mobile.Network.over.the.last.7.days = c(NA_integer_, NA_integer_, NA_integer_, NA_integer_, NA_integer_, NA_integer_), 
                 Usage.of.the.Three.Mobile.Network.over.the.last.7.days..hav... = c(NA_integer_, NA_integer_, NA_integer_, NA_integer_, NA_integer_, NA_integer_), 
                 improve.your.overall.network.experience.over... = c("", "", "", "", "", ""), 
                 reason.did.you.rate.your.network.experience.as = c("", "", "", "", "", ""), 
                 To.what.extent.do.you.trust.the.Three.Network = c(NA_integer_, NA_integer_, NA_integer_, NA_integer_, NA_integer_, NA_integer_), 
                 easy.or.difficult.was.it.to.purchase.in.store = c(NA_integer_, NA_integer_, NA_integer_, NA_integer_, NA_integer_, NA_integer_), 
                 Was.the.Sales.Agent.Welcoming = c(NA_integer_, NA_integer_, NA_integer_, NA_integer_, NA_integer_, NA_integer_), 
                 Sales.Agent.take.the.time.to.understand.your.needs.and.tailor.the.offer.to.suit = c(NA_integer_, NA_integer_, NA_integer_, NA_integer_, NA_integer_, NA_integer_), 
                 Was.the.Sales..Agent.helpful. = c(NA_integer_, NA_integer_, NA_integer_, NA_integer_, NA_integer_, NA_integer_), 
                 Did.the.Sales.Agent.inform.you.of.the.3Plus.Programme = c(NA_integer_, NA_integer_, NA_integer_, NA_integer_, NA_integer_, NA_integer_), 
                 Did.the.Sales.Agent.say.Thank.You = c(NA_integer_, NA_integer_, NA_integer_, NA_integer_, NA_integer_, NA_integer_), 
                 rate.your.recent.purchase.experience = c("", "", "", "", "", ""), 
                 Sales.Agent.could.have.done.better.to.improve = c("", "", "", "", "", ""), 
                 Three.may.call.you.to.discuss.your.feedback = c(NA_integer_, NA_integer_, NA_integer_, NA_integer_, NA_integer_, NA_integer_), 
                 person.was.knowledgeable = c(NA, NA, 5L, NA, NA, 5L), 
                 complete.confidence.in.the.person = c(NA, NA, NA, 4L, NA, NA), 
                 Person.listened.and.understood = c(5L, 5L, NA, NA, 5L, NA), 
                 FCR = c(1L, 1L, 1L, 1L, 1L, 1L), 
                 Three.is.good.at.fixing.issues = c(NA_integer_, NA_integer_, NA_integer_, NA_integer_, NA_integer_, NA_integer_), 
                 Information..required.on.the.My3.App = c(NA_integer_, NA_integer_, NA_integer_, NA_integer_, NA_integer_, NA_integer_), 
                 Verb = c("You obviously have no loyalty dept", "Nothing", "He couldn't have done any more very polite very helpful and a great Person for ur company ??", "Nothing cause 3 take my money and fee me for no reason", "",  "There is nothing I can say that it needed to improve I was very happy with the way my query was handled in a very professional manner")), 
            row.names = c(NA, 6L), 
            class = "data.frame")




#


#make the data a tibble to make viewing it easier in the consol
data <- data %>% as_tibble()

#select just a subset of columns to make debugging easier
df <- data %>% select(Name, StartDate, EndDate, NPS, Effort, Reason.did.you.rate.your.support.experience.as,I.feel.that.Three.is.putting.in.a.significant.effort.to.make.my.experience.with.them.a.positive.one.,  Verb)

#This one sums numeric data and counts character entries
df %>% 
  group_by(Name) %>%
  summarise(across(where(is.numeric), sum), #if it is a numeric column, add it up
            across(where(is.character), ~sum(!is.na(.))) #if it is a character column, count the non-NA values
            )
#> `summarise()` ungrouping output (override with `.groups` argument)
#> # A tibble: 1 x 8
#>   Name     NPS Effort I.feel.that.Thre~ StartDate EndDate Reason.did.you.~  Verb
#>   <chr>  <int>  <int>             <int>     <int>   <int>            <int> <int>
#> 1 CICS_~    44     49                NA         6       6                6     6

#this on counts how many non-NA values there are
df %>% 
  group_by(Name) %>%
  summarise(across(NPS:Verb, ~sum(!is.na(.))))
#> `summarise()` ungrouping output (override with `.groups` argument)
#> # A tibble: 1 x 6
#>   Name       NPS Effort Reason.did.you.rate.y~ I.feel.that.Three.is.putti~  Verb
#>   <chr>    <int>  <int>                  <int>                       <int> <int>
#> 1 CICS_TE~     6      6                      6                           0     6


#now with the entire dataset
data %>% 
  group_by(Name) %>%
  summarise(across(NPS:Verb, ~sum(!is.na(.))))
#> `summarise()` ungrouping output (override with `.groups` argument)
#> # A tibble: 1 x 59
#>   Name    NPS Effort Reason.did.you.~ I.feel.that.Thr~ feel.that.I.nee~
#>   <chr> <int>  <int>            <int>            <int>            <int>
#> 1 CICS~     6      6                6                0                0
#> # ... with 53 more variables:
#> #   How.satisfied.were.you.will.your.recent.experiences..Network.Performance <int>,
#> #   How.satisfied.were.youCustomer.Care <int>, How.satisfied.Website <int>,
#> #   How.satisfied..Store <int>, How.satisfied.were.you.N.A <int>,
#> #   ffers.good.value.for.money <int>, Is.a.brand.I.can.trust <int>,
#> #   Understands.my.needs.as.a.customer <int>,
#> #   Rewards.customers.for.their.loyalty <int>, Meets.my.expectations <int>,
#> #   Three.makes.a.significant.effort.to.make <int>,
#> #   My.monthly.mobile.costs.meets.my.expectations. <int>,
#> #   My.experience.with.Three.s.network.meets.my.everyday.needs. <int>,
#> #   What.is.your.preferred.channel.to.use.if.you.need.support.or.assistance. <int>,
#> #   Do.you.find.it.easy.to.access.support.when.you.need.help. <int>,
#> #   Three.s.products.and.services.enhance.my.life. <int>,
#> #   Are.you.aware.of.3Plus.rewards. <int>, Three.is.a.brand.I.trust. <int>,
#> #   We.would.like.to.hear.your.thoughts..is.there.anything.else.you.would.like.us.to.focus.on. <int>,
#> #   What.steps.can.we.take.to.build.your.trust.with.us. <int>,
#> #   I.need.to.put.in.more.effort.than.I.would.expe <int>,
#> #   How.satisfied.were.you.with.your.recent.Network.experience <int>,
#> #   Three.offers.good.value.for.money <int>,
#> #   Three.is.a.brand.I.can.trust <int>,
#> #   Three.understands.my.needs.as.a.customer <int>,
#> #   Three.Rewards.customers.for.their.loyalty <int>,
#> #   Three.meets.my.expectations <int>,
#> #   what.you.feel.could.be.done.better.to.improve.your.overall.exper <int>,
#> #   Ability.to.make.and.receive.calls.and.texts <int>,
#> #   experience.on.data.speeds.with.your.data.connection... <int>,
#> #   On.a.scale.of.1.to.5.how.would.you.rate.your.coverage.in.your.home <int>,
#> #   Is.this.related.to.indoor.outdoor.coverage <int>,
#> #   Thinking.about.your.recent.experience.usage.of.the.Three.Mobile.Network.over.the.last.7.days <int>,
#> #   Usage.of.the.Three.Mobile.Network.over.the.last.7.days..hav... <int>,
#> #   improve.your.overall.network.experience.over... <int>,
#> #   reason.did.you.rate.your.network.experience.as <int>,
#> #   To.what.extent.do.you.trust.the.Three.Network <int>,
#> #   easy.or.difficult.was.it.to.purchase.in.store <int>,
#> #   Was.the.Sales.Agent.Welcoming <int>,
#> #   Sales.Agent.take.the.time.to.understand.your.needs.and.tailor.the.offer.to.suit <int>,
#> #   Was.the.Sales..Agent.helpful. <int>,
#> #   Did.the.Sales.Agent.inform.you.of.the.3Plus.Programme <int>,
#> #   Did.the.Sales.Agent.say.Thank.You <int>,
#> #   rate.your.recent.purchase.experience <int>,
#> #   Sales.Agent.could.have.done.better.to.improve <int>,
#> #   Three.may.call.you.to.discuss.your.feedback <int>,
#> #   person.was.knowledgeable <int>, complete.confidence.in.the.person <int>,
#> #   Person.listened.and.understood <int>, FCR <int>,
#> #   Three.is.good.at.fixing.issues <int>,
#> #   Information..required.on.the.My3.App <int>, Verb <int>

^{Created on 2021-01-25 by the reprex package (v0.3.0)}

thanks masher, out is sum, i am looking for count.. Also the output from this code shows no results for the first 42 columns, (although not all columns apply for all rows) — Iqbal S, Jan 25 '21 at 07:57
`# A tibble: 31 x 65 Name Year NPS Effort I.feel.that.Thr~ feel.that.I.nee~ How.satisfied.w~ 1 CICS~ 1.33e5 426 NA NA NA NA 2 CICS~ 2.61e5 684 NA NA NA NA 3 CICS~ 8.10e5 2288 NA NA NA NA 10 J_Ci~ 2.42e5 557 NA NA NA NA # ... with 21 more rows, and 58 more variables:` — Iqbal S, Jan 25 '21 at 08:01
@IqbalS How about now? data %>% group_by(Name) %>% summarise(across(NPS:Verb, ~sum(!is.na(.)))) — masher, Jan 25 '21 at 08:24
this is simply spitting out the raw data with few columns excluded and no grouping whatsoever — Iqbal S, Jan 25 '21 at 08:41
Then I don't know what is going on on your end. I've updated the answer with a reprex which shows a single row with counts. — masher, Jan 25 '21 at 08:47
i am not sure either, the sample data i posted, for column "Reason.did.you.rate.your.support.experience.as" there are no entries for "CICS_TEL_6_PP Nov2019", output for the NPS and Effort column are correct, 6 entries for both, however why the code produces the same data for empty subsequent columns, i cant figure that — Iqbal S, Jan 25 '21 at 09:02
There are 6 entries for that Reason.did.you column, they're just all empty strings. — masher, Jan 25 '21 at 09:05
1) we want to ignore the blanks or empty string. 2) Also as we go into further columns the output is basically the same value (6 in this case) across all the columns regardless of the actual data (actual data may have only 4 entries for column a, 2 for column b, 20 for column c so on ...) 3) also given the code should compute the opposing condition, i.e. should not count blanks but only actual entries (!is.na) empty strings should have been eliminated — Iqbal S, Jan 25 '21 at 10:17
I'm not doing your homework for you. If you can't see how to extend this result, then I don't know what I'm doing for you. — masher, Jan 25 '21 at 10:23
appreciate your help mate, the extension does not produce the results we thought it was. Thanks again. — Iqbal S, Jan 25 '21 at 10:29
i figured why the code was failing, the data had blanks (silly me!), i replaced it with NA and the original code eventually worked. Thought it was a good idea to let you know. — Iqbal S, Jan 25 '21 at 12:00

Summary of multiple columns

1 Answers1