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}}
Please check out tidyr package and use commands like spread/ gather which would make data wide or long> On Jun 22, 2018, at 07:43, Bill Poling <Bill.Poling at zelis.com> 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 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.
1. "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 count about 20 different DX codes in the data you show, so I don't know what this means. 2. I was not able to unambiguously parse your request (I admit that I just may be dense) , but maybe split() is what you want, at least to start with: ##untested in the absence of a reprex ## dat is your data frame split(dat, dat$ClaimServiceID) This will give you a list of data frames, one for eachClaimServiceID. You can order each list however you like using, e.g. lapply() with order(). If I have missed your intent completely, just say so and hopefully someone else can help. Or follow Ramesh's suggestion Cheers, Bert Bert Gunter "The trouble with having an open mind is that people keep coming along and sticking things into it." -- Opus (aka Berkeley Breathed in his "Bloom County" comic strip ) On Fri, Jun 22, 2018 at 4:43 AM, Bill Poling <Bill.Poling at zelis.com> 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 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. >[[alternative HTML version deleted]]
Hi Bert and thank you for your assistance, I will try this.
I was not clear enough about the number of expected output columns based on the
DiagnosisCode column, my appologies.
Specifically, by virtue of the ClaimServiceID being the unique identifier, among
them there is one with 12 DiagnosisCodes so the record length would be at the
most 12 + the ClaimServiceID and the ClaimID
Example:
ClaimServiceID ClaimID DX1 DX2 DX3 DX4 DX5 DX6 DX7 DX8 DX9 DX10 DX11 DX12
And of course those ClaimServiceID?s with less than 12 DX?s would be NULL in
place of a DiagnosisCode
I will work with what you suggest and report back thank you again Sir!
WHP
From: Bert Gunter [mailto:bgunter.4567 at gmail.com]
Sent: Friday, June 22, 2018 11:54 AM
To: Bill Poling <Bill.Poling at zelis.com>
Cc: r-help (r-help at r-project.org) <r-help at r-project.org>
Subject: Re: [R] Help with transpose please.
1. "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 count about 20 different DX codes in the data you show, so I don't know
what this means.
2. I was not able to unambiguously parse your request (I admit that I just may
be dense) , but maybe split() is what you want, at least to start with:
##untested in the absence of a reprex
## dat is your data frame
split(dat, dat$ClaimServiceID)
This will give you a list of data frames, one for eachClaimServiceID. You can
order each list however you like using, e.g. lapply() with order().
If I have missed your intent completely, just say so and hopefully someone else
can help. Or follow Ramesh's suggestion
Cheers,
Bert
Bert Gunter
"The trouble with having an open mind is that people keep coming along and
sticking things into it."
-- Opus (aka Berkeley Breathed in his "Bloom County" comic strip )
On Fri, Jun 22, 2018 at 4:43 AM, Bill Poling <Bill.Poling at
zelis.com<mailto:Bill.Poling at zelis.com>> 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://www.r-bloggers.com/pivot-tables-in-r/>
#
https://stackoverflow.com/questions/18449938/pivot-on-data-table-similar-to-rehape-melt-function<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/<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 at r-project.org<mailto:R-help at r-project.org> mailing list -- To
UNSUBSCRIBE and more, see
https://stat.ethz.ch/mailman/listinfo/r-help<https://stat.ethz.ch/mailman/listinfo/r-help>
PLEASE do read the posting guide
http://www.R-project.org/posting-guide.html<http://www.R-project.org/posting-guide.html>
and provide commented, minimal, self-contained, reproducible code.
Confidentiality Notice This message is sent from Zelis. This transmission may
contain information which is privileged and confidential and is intended for the
personal and confidential use of the named recipient only. Such information may
be protected by applicable State and Federal laws from this disclosure or
unauthorized use. If the reader of this message is not the intended recipient,
or the employee or agent responsible for delivering the message to the intended
recipient, you are hereby notified that any disclosure, review, discussion,
copying, or taking any action in reliance on the contents of this transmission
is strictly prohibited. If you have received this transmission in error, please
contact the sender immediately. Zelis, 2018.
[[alternative HTML version deleted]]
Thank you Ramesh, I will investigate, appreciate your response Sir! WHP From: Ramesh YAPALPARVI [mailto:ramesh.yapalparvi at icloud.com] Sent: Friday, June 22, 2018 7:47 AM To: Bill Poling <Bill.Poling at zelis.com> Cc: r-help (r-help at r-project.org) <r-help at r-project.org> Subject: Re: [R] Help with transpose please. Please check out tidyr package and use commands like spread/ gather which would make data wide or long> On Jun 22, 2018, at 07:43, Bill Poling <Bill.Poling at zelis.com<mailto:Bill.Poling at zelis.com>> 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://www.r-bloggers.com/pivot-tables-in-r/> > > # https://stackoverflow.com/questions/18449938/pivot-on-data-table-similar-to-rehape-melt-function<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/<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 at r-project.org<mailto:R-help at r-project.org> mailing list -- To UNSUBSCRIBE and more, see > https://stat.ethz.ch/mailman/listinfo/r-help<https://stat.ethz.ch/mailman/listinfo/r-help> > PLEASE do read the posting guide http://www.R-project.org/posting-guide.html<http://www.R-project.org/posting-guide.html> > and provide commented, minimal, self-contained, reproducible code.Confidentiality Notice This message is sent from Zelis. ...{{dropped:15}}
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 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. >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
Thank you Dan, that was very helpful. Sorry for the delayed response. Cheers WHP From: Daniel Nordlund [mailto:djnordlund at gmail.com] Sent: Saturday, June 23, 2018 6:04 AM To: Bill Poling <Bill.Poling at zelis.com>; r-help (r-help at r-project.org) <r-help at r-project.org> Subject: Re: [R] Help with transpose please. 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://www.r-bloggers.com/pivot-tables-in-r/> > > # https://stackoverflow.com/questions/18449938/pivot-on-data-table-similar-to-rehape-melt-function<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/<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 at r-project.org<mailto:R-help at r-project.org> mailing list -- To UNSUBSCRIBE and more, see > https://stat.ethz.ch/mailman/listinfo/r-help<https://stat.ethz.ch/mailman/listinfo/r-help> > PLEASE do read the posting guide http://www.R-project.org/posting-guide.html<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 Confidentiality Notice This message is sent from Zelis. This transmission may contain information which is privileged and confidential and is intended for the personal and confidential use of the named recipient only. Such information may be protected by applicable State and Federal laws from this disclosure or unauthorized use. If the reader of this message is not the intended recipient, or the employee or agent responsible for delivering the message to the intended recipient, you are hereby notified that any disclosure, review, discussion, copying, or taking any action in reliance on the contents of this transmission is strictly prohibited. If you have received this transmission in error, please contact the sender immediately. Zelis, 2018. [[alternative HTML version deleted]]
Yep, thanks Dan, that?s got it. Thank you to everyone who responded as well. WHP ClaimServiceID ClaimID Dx1 Dx10 Dx11 Dx12 Dx2 Dx3 Dx4 Dx5 Dx6 Dx7 Dx8 Dx9 1 183056004 78044473 C562 <NA> <NA> <NA> C778 C784 C786 C7961 C7982 C7989 <NA> <NA> 2 183056008 78044473 C562 <NA> <NA> <NA> C778 C784 C786 C7961 C7982 C7989 <NA> <NA> 3 183139945 78078925 M79606 <NA> <NA> <NA> M7989 R600 <NA> <NA> <NA> <NA> <NA> <NA> From: Daniel Nordlund [mailto:djnordlund at gmail.com] Sent: Saturday, June 23, 2018 6:04 AM To: Bill Poling <Bill.Poling at zelis.com>; r-help (r-help at r-project.org) <r-help at r-project.org> Subject: Re: [R] Help with transpose please. 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://www.r-bloggers.com/pivot-tables-in-r/> > > # https://stackoverflow.com/questions/18449938/pivot-on-data-table-similar-to-rehape-melt-function<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/<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 at r-project.org<mailto:R-help at r-project.org> mailing list -- To UNSUBSCRIBE and more, see > https://stat.ethz.ch/mailman/listinfo/r-help<https://stat.ethz.ch/mailman/listinfo/r-help> > PLEASE do read the posting guide http://www.R-project.org/posting-guide.html<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 Confidentiality Notice This message is sent from Zelis. This transmission may contain information which is privileged and confidential and is intended for the personal and confidential use of the named recipient only. Such information may be protected by applicable State and Federal laws from this disclosure or unauthorized use. If the reader of this message is not the intended recipient, or the employee or agent responsible for delivering the message to the intended recipient, you are hereby notified that any disclosure, review, discussion, copying, or taking any action in reliance on the contents of this transmission is strictly prohibited. If you have received this transmission in error, please contact the sender immediately. Zelis, 2018. [[alternative HTML version deleted]]