R help - May 2020 - How to combine two rows in a data table into a third new row, such that the values in the row are added together in the new row?

If I have a data table that is essentially output titled: "dt_count" -
it contains:

"","STATUS","N"
"1","Resolved",650
"2","Assigned",135
"3","Closed",530
"4","In Progress",56
"5","Pending",75
"6","Cancelled",20

Need to change the "dt_count" data table to a new data table that
looks like this:

"","STATUS","N"
"1","Resolved/Closed",1180
"2","Assigned",135
"3","In Progress",56
"4","Pending",75
"5","Cancelled",20

Or, to state the question:

I need to combine the "Resolved" Row with the "Closed" Row,
into a Third new row titled "Resolved/Closed", whereby the
"N" ticket count in each of the "Resolved" row and the
"Closed" row are added together in the third
new?"Resolved/Closed" - also, would need the old?"Resolved"
Row with the "Closed" Rows to go away.

To complicate the issue, the rows in the "dt_count" data table when
they are output, are not always in the same order.

I have the data.table library is installed.

I'm thinking there is a very easy way to do this... but I am not finding it.
I've search thru several data table cheatsheets, and I've also read thru
this:
https://cran.r-project.org/web/packages/data.table/vignettes/datatable-intro.html

Just can't sort it out. Just started using R a few weeks ago.

Any help would be so very much appreciated!

Thanks.

Gregg
AZ, USA
-------------- next part --------------
A non-text attachment was scrubbed...
Name: signature.asc
Type: application/pgp-signature
Size: 477 bytes
Desc: OpenPGP digital signature
URL:
<https://stat.ethz.ch/pipermail/r-help/attachments/20200506/8fddece8/attachment.sig>

Hi!

With 'dplyr':

dt_count %>% mutate(STATUS=ifelse(STATUS %in%
c("Resolved","Closed"),"Resolved/Closed",STATUS))
%>% group_by(STATUS)
%>% summarise(n=sum(N))

Output:

1 Assigned          135
2 Cancelled          20
3 In Progress        56
4 Pending            75
5 Resolved/Closed  1180

HTH,
Kimmo

2020-05-06, 04:41 +0000, Gregg via R-help wrote:
> If I have a data table that is essentially output titled:
"dt_count"
> - it contains:
> 
> "","STATUS","N"
> "1","Resolved",650
> "2","Assigned",135
> "3","Closed",530
> "4","In Progress",56
> "5","Pending",75
> "6","Cancelled",20
> 
> Need to change the "dt_count" data table to a new data table that
> looks like this:
> 
> "","STATUS","N"
> "1","Resolved/Closed",1180
> "2","Assigned",135
> "3","In Progress",56
> "4","Pending",75
> "5","Cancelled",20
> 
> Or, to state the question:
> 
> I need to combine the "Resolved" Row with the "Closed"
Row, into a
> Third new row titled "Resolved/Closed", whereby the "N"
ticket count
> in each of the "Resolved" row and the "Closed" row are
added together
> in the third new "Resolved/Closed" - also, would need the
> old "Resolved" Row with the "Closed" Rows to go away.
> 
> To complicate the issue, the rows in the "dt_count" data table
when
> they are output, are not always in the same order.
> 
> I have the data.table library is installed.
> 
> I'm thinking there is a very easy way to do this... but I am not
> finding it. I've search thru several data table cheatsheets, and
I've
> also read thru this:
> 
https://cran.r-project.org/web/packages/data.table/vignettes/datatable-intro.html
> 
> Just can't sort it out. Just started using R a few weeks ago.
> 
> Any help would be so very much appreciated!
> 
> Thanks.
> 
> Gregg
> AZ, USA
> ______________________________________________
> R-help at r-project.org mailing list -- To UNSUBSCRIBE and more, see
> https://stat.ethz.ch/mailman/listinfo/r-help
> PLEASE do read the posting guide 
> http://www.R-project.org/posting-guide.html
> and provide commented, minimal, self-contained, reproducible code.

Hi

Maybe aggregate?

1. Make your column STATUS a factor
2. Combine levels Resolved and Closed to one common factor named 
Reslolved/Closed
3. aggregate according to new STATUS

temp <- read.table("clipboard", sep=",", header=T)
temp$STATUS
[1] "Resolved"    "Assigned"    "Closed"     
"In Progress" "Pending"
[6] "Cancelled"
temp$STATUS <- factor(temp$STATUS)
levels(temp$STATUS)
[1] "Assigned"    "Cancelled"   "Closed"     
"In Progress" "Pending"
[6] "Resolved"
levels(temp$STATUS)[c(3,6)] <- "Resolved/Closed"
aggregate(temp$N, list(temp$STATUS), sum)
          Group.1    x
1        Assigned  135
2       Cancelled   20
3 Resolved/Closed 1180
4     In Progress   56
5         Pending   75

Cheers
Petr
> -----Original Message-----
> From: R-help <r-help-bounces at r-project.org> On Behalf Of Gregg via
R-help
> Sent: Wednesday, May 6, 2020 6:42 AM
> To: r-help at r-project.org
> Subject: [R] How to combine two rows in a data table into a third new row,
> such that the values in the row are added together in the new row?
>
> If I have a data table that is essentially output titled:
"dt_count" - it
> contains:
>
> "","STATUS","N"
> "1","Resolved",650
> "2","Assigned",135
> "3","Closed",530
> "4","In Progress",56
> "5","Pending",75
> "6","Cancelled",20
>
> Need to change the "dt_count" data table to a new data table that
looks like
> this:
>
> "","STATUS","N"
> "1","Resolved/Closed",1180
> "2","Assigned",135
> "3","In Progress",56
> "4","Pending",75
> "5","Cancelled",20
>
> Or, to state the question:
>
> I need to combine the "Resolved" Row with the "Closed"
Row, into a Third
> new row titled "Resolved/Closed", whereby the "N"
ticket count in each of
> the
> "Resolved" row and the "Closed" row are added together
in the third
> new "Resolved/Closed" - also, would need the old
"Resolved" Row with the
> "Closed" Rows to go away.
>
> To complicate the issue, the rows in the "dt_count" data table
when they are
> output, are not always in the same order.
>
> I have the data.table library is installed.
>
> I'm thinking there is a very easy way to do this... but I am not
finding it.
> I've
> search thru several data table cheatsheets, and I've also read thru
this:
> https://cran.r-project.org/web/packages/data.table/vignettes/datatable-
> intro.html
>
> Just can't sort it out. Just started using R a few weeks ago.
>
> Any help would be so very much appreciated!
>
> Thanks.
>
> Gregg
> AZ, USA

On 2020-05-06 09:13 +0000, PIKAL Petr wrote:
> Maybe aggregate?
Hi!  I agree aggregate is an elegant solution 
for this, so I continued your example a bit:

dt_count <- '"","STATUS","N"
"1","Resolved",650
"2","Assigned",135
"3","Closed",530
"4","In Progress",56
"5","Pending",75
"6","Cancelled",20'
dt_count <- read.csv(text=dt_count)

dt_count[
  dt_count$STATUS %in%
  c("Resolved", "Closed"),
  "STATUS"] <-
  "Resolved/Closed"
aggregate(
  x=list("N"=dt_count$N),
  by=list("STATUS"=dt_count$STATUS),
  FUN=sum)

Best,
Rasmus

-------------- next part --------------
A non-text attachment was scrubbed...
Name: signature.asc
Type: application/pgp-signature
Size: 833 bytes
Desc: not available
URL:
<https://stat.ethz.ch/pipermail/r-help/attachments/20200507/5178fe64/attachment.sig>