On 6/22/2018 4:43 AM, Bill Poling wrote:
Good morning.
I have data in the form:
head(Edit041IA, n=25)
ClaimServiceID ClaimID DiagnosisCode
1 183056004 78044473 C562
2 183056004 78044473 C778
3 183056004 78044473 C784
4 183056004 78044473 C786
5 183056004 78044473 C7961
6 183056004 78044473 C7982
7 183056004 78044473 C7989
8 183056008 78044473 C562
9 183056008 78044473 C778
10 183056008 78044473 C784
11 183056008 78044473 C786
12 183056008 78044473 C7961
13 183056008 78044473 C7982
14 183056008 78044473 C7989
15 183139945 78078925 M79606
16 183139945 78078925 M7989
17 183139945 78078925 R600
18 183236728 78119632 H02831
19 183236728 78119632 H02832
20 183236728 78119632 H02834
21 183236728 78119632 H02835
22 183236728 78119632 H04123
23 183236728 78119632 Z411
24 183236728 78119632 H2513
25 183236728 78119632 H43813
And wish to transpose to single record for single claimServiceID, ClaimID, and
Dx1,Dx2,Dx3, etc:
There can be multiple claimServiceIDs for a ClaimID so I want the unique
ClaimServiceID as the identifier when I join this data back into a longer
single record length file by that column.
claimServiceID ClaimID Dx1 Dx2 Dx3 ...etc
1 183056004 78044473 C562 C778 C784 C786 C7961 ...etc
2 183056008 78044473 C562 C778 C784 C786 C7961 ...etc
(If you would prefer the complete dput of the 1272 records I will gladly provide .Names
= c("ClaimServiceID",
"ClaimID", "DiagnosisCode"), class = "data.frame", row.names = c(NA,
-1272L))
At the moment the classes are:
classes <- as.character(sapply(Edit041IA, class))
classes
# [1] "integer" "integer" "character" <---but do not have to be if that
helps its just that's how the csv load pulled them in
The max number of columns based on this transpose of the DiagnosisCode column
(in this dataset) is 12 if that is important to know.
I have looked at a variety of webpages and cannot get this right,
dta2 <- melt(dta1, id=c("ClaimServiceID","ClaimID"))
View(dta2)
# https://www.r-bloggers.com/pivot-tables-in-r/
#
https://stackoverflow.com/questions/18449938/pivot-on-data-table-similar-to-rehape-melt-function
dta3 <- cast(Edit041IA, ClaimServiceID ~ DiagnosisCode, ClaimID)
View(dta3)
dta3 <- cast(Edit041IA, DiagnosisCode ~ ClaimServiceID, ClaimID)
View(dta3)
dta3 <- melt(Edit041IA, id=c("ClaimServiceID"))
View(dta3)
dta3 <- aggregate(Edit041IA, by=list(ClaimServiceID, ClaimID, DiagnosisCode))
View(dta3)
dta3 <- aggregate(Edit041IA, by=list(ClaimServiceID))
View(dta3)
# https://www.r-statistics.com/tag/transpose/
dta3 <- aggregate(Edit041IA, by=list(DiagnosisCode, ClaimServiceID, ClaimID))
View(dta3)
I am sure it's a basic, simple procedure, but I am pressed for time on this
one, any support will be greatly appreciated, thank you.
WHP
Confidentiality Notice This message is sent from Zelis. ...{{dropped:15}}
______________________________________________
R-help@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.
Bill,
you have received some good suggestions and since you are pressed for
time this may be too late. However, here is a solution using ave()
function and cast() from the reshape package.
# create diagnosis variable names
dxnames <- paste('Dx',ave(rep(1, nrow(have)), have[,1:2], FUN =
seq_along), sep='')
# cast the data into wide format
cast(cbind(have,dxnames), ClaimServiceID + ClaimID ~ dxnames,
value='DiagnosisCode')
Hope this is helpful,
Dan
--
Daniel Nordlund
Port Townsend, WA USA
______________________________________________
R-help@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.
