[R] Help with transpose please.

[Bill Poling]

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}}