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>
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?
4 messages · Gregg, Kimmo Elo, PIKAL Petr +1 more
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>