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
I think this gives you the result you're looking for.
Created on 2021-01-25 by the reprex package (v0.3.0)