bcrombie
2014-Dec-18 03:15 UTC
[R] Make 2nd col of 2-col df into header row of same df then adjust col1 data display
# I have a dataframe that contains 2 columns: CaseID <- c('1015285', '1005317', '1012281', '1015285', '1015285', '1007183', '1008833', '1015315', '1015322', '1015285') Primary.Viol.Type <- c('AS.Age', 'HS.Hours', 'HS.Hours', 'HS.Hours', 'RK.Records_CL', 'OT.Overtime', 'OT.Overtime', 'OT.Overtime', 'V.Poster_Other', 'V.Poster_Other') PViol.Type.Per.Case.Original <- data.frame(CaseID,Primary.Viol.Type) # CaseID?s can be repeated because there can be up to 14 Primary.Viol.Type?s per CaseID. # I want to transform this dataframe into one that has 15 columns, where the first column is CaseID, and the rest are the 14 primary viol. types. The CaseID column will contain a list of the unique CaseID?s (no replicates) and for each of their rows, there will be a ?1? under a column corresponding to a primary violation type recorded for that CaseID. So, technically, there could be zero to 14 ?1?s? in a CaseID?s row. # For example, the row for CaseID '1015285' above would have a ?1? under ?AS.Age?, ?HS.Hours?, ?RK.Records_CL?, and ?V.Poster_Other?, but have "NA" under the rest of the columns. PViol.Type <- c("CaseID", "BW.BackWages", "LD.Liquid_Damages", "MW.Minimum_Wage", "OT.Overtime", "RK.Records_FLSA", "V.Poster_Other", "AS.Age", "BW.WHMIS_BackWages", "HS.Hours", "OA.HazOccupationAg", "ON.HazOccupationNonAg", "R3.Reg3AgeOccupation", "RK.Records_CL", "V.Other") PViol.Type.Columns <- t(data.frame(PViol.Type) # What is the best way to do this in R? -- View this message in context: http://r.789695.n4.nabble.com/Make-2nd-col-of-2-col-df-into-header-row-of-same-df-then-adjust-col1-data-display-tp4700878.html Sent from the R help mailing list archive at Nabble.com.
Boris Steipe
2014-Dec-18 10:28 UTC
[R] Make 2nd col of 2-col df into header row of same df then adjust col1 data display
What you are describing sounds like a very spreadsheet-y thing. - The information is already IN your dataframe, and easy to get out by subsetting. Depending on your usecase, that may actually be the "best". - If the number of CaseIDs is large, I would use a hash of lists (if the data is sparse), or hash of named vectors if it's not sparse. Lookup is O(1) so that may be the best. (Cf package hash, and explanations there). - If it must be the spreadsheet-y thing, you could make a matrix with rownames and colnames taken from unique() of your respective dataframe. Instead of 1 and NA I probably would use TRUE/FALSE. - If it takes less time to wait for the results than to look up how apply() works, you can write a simple loop to populate your matrix. Otherwise apply() is much faster. - You could even use a loop to build the datastructure, checking for every cbind() whether the value in column 1 already exists in the table - but that's terrible and would make a kitten die somewhere on every iteration. All of these are possible, and you haven't told us enough about what you want to achieve to figure out what the "best" is. If you choose one of the options and need help with the code, let us know. Cheers, B. On Dec 17, 2014, at 10:15 PM, bcrombie <bcrombie at utk.edu> wrote:> # I have a dataframe that contains 2 columns: > CaseID <- c('1015285', > '1005317', > '1012281', > '1015285', > '1015285', > '1007183', > '1008833', > '1015315', > '1015322', > '1015285') > > Primary.Viol.Type <- c('AS.Age', > 'HS.Hours', > 'HS.Hours', > 'HS.Hours', > 'RK.Records_CL', > 'OT.Overtime', > 'OT.Overtime', > 'OT.Overtime', > 'V.Poster_Other', > 'V.Poster_Other') > > PViol.Type.Per.Case.Original <- data.frame(CaseID,Primary.Viol.Type) > > # CaseID?s can be repeated because there can be up to 14 Primary.Viol.Type?s > per CaseID. > > # I want to transform this dataframe into one that has 15 columns, where the > first column is CaseID, and the rest are the 14 primary viol. types. The > CaseID column will contain a list of the unique CaseID?s (no replicates) and > for each of their rows, there will be a ?1? under a column corresponding to > a primary violation type recorded for that CaseID. So, technically, there > could be zero to 14 ?1?s? in a CaseID?s row. > > # For example, the row for CaseID '1015285' above would have a ?1? under > ?AS.Age?, ?HS.Hours?, ?RK.Records_CL?, and ?V.Poster_Other?, but have "NA" > under the rest of the columns. > > PViol.Type <- c("CaseID", > "BW.BackWages", > "LD.Liquid_Damages", > "MW.Minimum_Wage", > "OT.Overtime", > "RK.Records_FLSA", > "V.Poster_Other", > "AS.Age", > "BW.WHMIS_BackWages", > "HS.Hours", > "OA.HazOccupationAg", > "ON.HazOccupationNonAg", > "R3.Reg3AgeOccupation", > "RK.Records_CL", > "V.Other") > > PViol.Type.Columns <- t(data.frame(PViol.Type) > > # What is the best way to do this in R? > > > > > -- > View this message in context: http://r.789695.n4.nabble.com/Make-2nd-col-of-2-col-df-into-header-row-of-same-df-then-adjust-col1-data-display-tp4700878.html > Sent from the R help mailing list archive at Nabble.com. > > ______________________________________________ > 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.
Crombie, Burnette N
2014-Dec-18 13:09 UTC
[R] Make 2nd col of 2-col df into header row of same df then adjust col1 data display
I want to achieve a table that looks like a grid of 1's for all cases in a survey. I'm an R beginner and don't have a clue how to do all the things you just suggested. I really appreciate the time you took to explain all of those options, though. -- BNC -----Original Message----- From: Boris Steipe [mailto:boris.steipe at utoronto.ca] Sent: Thursday, December 18, 2014 5:29 AM To: Crombie, Burnette N Cc: r-help at r-project.org Subject: Re: [R] Make 2nd col of 2-col df into header row of same df then adjust col1 data display What you are describing sounds like a very spreadsheet-y thing. - The information is already IN your dataframe, and easy to get out by subsetting. Depending on your usecase, that may actually be the "best". - If the number of CaseIDs is large, I would use a hash of lists (if the data is sparse), or hash of named vectors if it's not sparse. Lookup is O(1) so that may be the best. (Cf package hash, and explanations there). - If it must be the spreadsheet-y thing, you could make a matrix with rownames and colnames taken from unique() of your respective dataframe. Instead of 1 and NA I probably would use TRUE/FALSE. - If it takes less time to wait for the results than to look up how apply() works, you can write a simple loop to populate your matrix. Otherwise apply() is much faster. - You could even use a loop to build the datastructure, checking for every cbind() whether the value in column 1 already exists in the table - but that's terrible and would make a kitten die somewhere on every iteration. All of these are possible, and you haven't told us enough about what you want to achieve to figure out what the "best" is. If you choose one of the options and need help with the code, let us know. Cheers, B. On Dec 17, 2014, at 10:15 PM, bcrombie <bcrombie at utk.edu> wrote:> # I have a dataframe that contains 2 columns: > CaseID <- c('1015285', > '1005317', > '1012281', > '1015285', > '1015285', > '1007183', > '1008833', > '1015315', > '1015322', > '1015285') > > Primary.Viol.Type <- c('AS.Age', > 'HS.Hours', > 'HS.Hours', > 'HS.Hours', > 'RK.Records_CL', > 'OT.Overtime', > 'OT.Overtime', > 'OT.Overtime', > 'V.Poster_Other', > 'V.Poster_Other') > > PViol.Type.Per.Case.Original <- data.frame(CaseID,Primary.Viol.Type) > > # CaseID's can be repeated because there can be up to 14 > Primary.Viol.Type's per CaseID. > > # I want to transform this dataframe into one that has 15 columns, > where the first column is CaseID, and the rest are the 14 primary > viol. types. The CaseID column will contain a list of the unique > CaseID's (no replicates) and for each of their rows, there will be a > "1" under a column corresponding to a primary violation type recorded > for that CaseID. So, technically, there could be zero to 14 "1's" in a CaseID's row. > > # For example, the row for CaseID '1015285' above would have a "1" > under "AS.Age", "HS.Hours", "RK.Records_CL", and "V.Poster_Other", but have "NA" > under the rest of the columns. > > PViol.Type <- c("CaseID", > "BW.BackWages", > "LD.Liquid_Damages", > "MW.Minimum_Wage", > "OT.Overtime", > "RK.Records_FLSA", > "V.Poster_Other", > "AS.Age", > "BW.WHMIS_BackWages", > "HS.Hours", > "OA.HazOccupationAg", > "ON.HazOccupationNonAg", > "R3.Reg3AgeOccupation", > "RK.Records_CL", > "V.Other") > > PViol.Type.Columns <- t(data.frame(PViol.Type) > > # What is the best way to do this in R? > > > > > -- > View this message in context: > http://r.789695.n4.nabble.com/Make-2nd-col-of-2-col-df-into-header-row > -of-same-df-then-adjust-col1-data-display-tp4700878.html > Sent from the R help mailing list archive at Nabble.com. > > ______________________________________________ > 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.
John Kane
2014-Dec-18 15:05 UTC
[R] Make 2nd col of 2-col df into header row of same df then adjust col1 data display
Of course, but why? As Brian S says you have not given us enough information to know exactly what you are after. Have a look at https://github.com/hadley/devtools/wiki/Reproducibility or http://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example for some information on how to form a question for the list. It is good that you provided some data but it is better to use dput() (see links above or ?dput) to supply the data as different R users have different settings on their systems and may not read that data in the same way. Note that I have simplified your incredibly verbose names and put everything into lower case (see ?tolower) just to make life easier. Because R is case-sensitive, it is usually easier to keep to lower case as much as possible particularly when posting to the list and to use simple variable names where the actual variables are likely to meaningless to the reader and long upper case names just makes for more typing. In any case here is a quick and dirty semi-solution using the reshape2 package which I imagine you will have to install using ?install.packages("reshape2"). Depending on exactly what you need to know there may be, as Brian S says many different and better approaches. While we really don't need the actual variable names we need an overall idea of what you are going in substantive terms and what the final results are. Anyway welcome to the R-help list #========start code====library(reshape2) dat1 <- structure(list(id = structure(c(5L, 1L, 4L, 5L, 5L, 2L, 3L, 6L, 7L, 5L), .Label = c("1005317", "1007183", "1008833", "1012281", "1015285", "1015315", "1015322"), class = "factor"), type = structure(c(1L, 2L, 2L, 2L, 4L, 3L, 3L, 3L, 5L, 5L), .Label = c("as.age", "hs.hours", "ot.overtime", "rk.records_cl", "v.poster_other"), class = "factor")), .Names = c("id", "type"), row.names = c(NA, -10L), class = "data.frame") dcast(dat1, id ~ type) #=======end code ====== John Kane Kingston ON Canada> -----Original Message----- > From: bcrombie at utk.edu > Sent: Wed, 17 Dec 2014 19:15:14 -0800 (PST) > To: r-help at r-project.org > Subject: [R] Make 2nd col of 2-col df into header row of same df then > adjust col1 data display > > # I have a dataframe that contains 2 columns: > CaseID <- c('1015285', > '1005317', > '1012281', > '1015285', > '1015285', > '1007183', > '1008833', > '1015315', > '1015322', > '1015285') > > Primary.Viol.Type <- c('AS.Age', > 'HS.Hours', > 'HS.Hours', > 'HS.Hours', > 'RK.Records_CL', > 'OT.Overtime', > 'OT.Overtime', > 'OT.Overtime', > 'V.Poster_Other', > 'V.Poster_Other') > > PViol.Type.Per.Case.Original <- data.frame(CaseID,Primary.Viol.Type) > > # CaseID?s can be repeated because there can be up to 14 > Primary.Viol.Type?s > per CaseID. > > # I want to transform this dataframe into one that has 15 columns, where > the > first column is CaseID, and the rest are the 14 primary viol. types. The > CaseID column will contain a list of the unique CaseID?s (no replicates) > and > for each of their rows, there will be a ?1? under a column corresponding > to > a primary violation type recorded for that CaseID. So, technically, > there > could be zero to 14 ?1?s? in a CaseID?s row. > > # For example, the row for CaseID '1015285' above would have a ?1? under > ?AS.Age?, ?HS.Hours?, ?RK.Records_CL?, and ?V.Poster_Other?, but have > "NA" > under the rest of the columns. > > PViol.Type <- c("CaseID", > "BW.BackWages", > "LD.Liquid_Damages", > "MW.Minimum_Wage", > "OT.Overtime", > "RK.Records_FLSA", > "V.Poster_Other", > "AS.Age", > "BW.WHMIS_BackWages", > "HS.Hours", > "OA.HazOccupationAg", > "ON.HazOccupationNonAg", > "R3.Reg3AgeOccupation", > "RK.Records_CL", > "V.Other") > > PViol.Type.Columns <- t(data.frame(PViol.Type) > > # What is the best way to do this in R? > > > > > -- > View this message in context: > http://r.789695.n4.nabble.com/Make-2nd-col-of-2-col-df-into-header-row-of-same-df-then-adjust-col1-data-display-tp4700878.html > Sent from the R help mailing list archive at Nabble.com. > > ______________________________________________ > 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.____________________________________________________________ FREE 3D EARTH SCREENSAVER - Watch the Earth right on your desktop!
Jeff Newmiller
2014-Dec-18 16:02 UTC
[R] Make 2nd col of 2-col df into header row of same df then adjust col1 data display
No guarantees on "best"... but one way using base R could be: # Note that "CaseID" is actually not a valid PViol.Type as you had it PViol.Type <- c( "BW.BackWages" , "LD.Liquid_Damages" , "MW.Minimum_Wage" , "OT.Overtime" , "RK.Records_FLSA" , "V.Poster_Other" , "AS.Age" , "BW.WHMIS_BackWages" , "HS.Hours" , "OA.HazOccupationAg" , "ON.HazOccupationNonAg" , "R3.Reg3AgeOccupation" , "RK.Records_CL" , "V.Other" ) # explicitly specifying all levels to the factor insures a complete # set of column outputs regardless of what is in the input PViol.Type.Per.Case.Original <- data.frame( CaseID , Primary.Viol.Type=factor( Primary.Viol.Type , levels=PViol.Type ) ) tmp <- table( PViol.Type.Per.Case.Original ) ans <- data.frame( CaseID=rownames( tmp ) , as.data.frame( ifelse( 0==tmp, NA, 1 ) ) ) On Wed, 17 Dec 2014, bcrombie wrote:> # I have a dataframe that contains 2 columns: > CaseID <- c('1015285', > '1005317', > '1012281', > '1015285', > '1015285', > '1007183', > '1008833', > '1015315', > '1015322', > '1015285') > > Primary.Viol.Type <- c('AS.Age', > 'HS.Hours', > 'HS.Hours', > 'HS.Hours', > 'RK.Records_CL', > 'OT.Overtime', > 'OT.Overtime', > 'OT.Overtime', > 'V.Poster_Other', > 'V.Poster_Other') > > PViol.Type.Per.Case.Original <- data.frame(CaseID,Primary.Viol.Type) > > # CaseID?s can be repeated because there can be up to 14 Primary.Viol.Type?s > per CaseID. > > # I want to transform this dataframe into one that has 15 columns, where the > first column is CaseID, and the rest are the 14 primary viol. types. The > CaseID column will contain a list of the unique CaseID?s (no replicates) and > for each of their rows, there will be a ?1? under a column corresponding to > a primary violation type recorded for that CaseID. So, technically, there > could be zero to 14 ?1?s? in a CaseID?s row. > > # For example, the row for CaseID '1015285' above would have a ?1? under > ?AS.Age?, ?HS.Hours?, ?RK.Records_CL?, and ?V.Poster_Other?, but have "NA" > under the rest of the columns. > > PViol.Type <- c("CaseID", > "BW.BackWages", > "LD.Liquid_Damages", > "MW.Minimum_Wage", > "OT.Overtime", > "RK.Records_FLSA", > "V.Poster_Other", > "AS.Age", > "BW.WHMIS_BackWages", > "HS.Hours", > "OA.HazOccupationAg", > "ON.HazOccupationNonAg", > "R3.Reg3AgeOccupation", > "RK.Records_CL", > "V.Other") > > PViol.Type.Columns <- t(data.frame(PViol.Type) > > # What is the best way to do this in R? > > > > > -- > View this message in context: http://r.789695.n4.nabble.com/Make-2nd-col-of-2-col-df-into-header-row-of-same-df-then-adjust-col1-data-display-tp4700878.html > Sent from the R help mailing list archive at Nabble.com. > > ______________________________________________ > 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.--------------------------------------------------------------------------- Jeff Newmiller The ..... ..... Go Live... DCN:<jdnewmil at dcn.davis.ca.us> Basics: ##.#. ##.#. Live Go... Live: OO#.. Dead: OO#.. Playing Research Engineer (Solar/Batteries O.O#. #.O#. with /Software/Embedded Controllers) .OO#. .OO#. rocks...1k
Chel Hee Lee
2014-Dec-18 19:43 UTC
[R] Make 2nd col of 2-col df into header row of same df then adjust col1 data display
I like the approach presented by Jeff Newmiller as shown in the previous post (I really like his way). As he suggested, it would be good to start with 'factor' since you have all values of 'Primary.Viol.Type'. You may try to use 'split()' function for creating table that you wish to build. Please see the below (I hope this helps): > PViol.Type.Per.Case.Original$Primary.Viol.Type <- factor(Primary.Viol.Type, levels=PViol.Type, labels=PViol.Type) > > tmp <- split(PViol.Type.Per.Case.Original, PViol.Type.Per.Case.Original$CaseID) > ans <- ifelse(do.call(rbind, lapply(tmp, function(x) table(x$Primary.Viol.Type))), 1, NA) > ans CaseID BW.BackWages LD.Liquid_Damages MW.Minimum_Wage OT.Overtime 1005317 NA NA NA NA NA 1007183 NA NA NA NA 1 1008833 NA NA NA NA 1 1012281 NA NA NA NA NA 1015285 NA NA NA NA NA 1015315 NA NA NA NA 1 1015322 NA NA NA NA NA RK.Records_FLSA V.Poster_Other AS.Age BW.WHMIS_BackWages HS.Hours 1005317 NA NA NA NA 1 1007183 NA NA NA NA NA 1008833 NA NA NA NA NA 1012281 NA NA NA NA 1 1015285 NA 1 1 NA 1 1015315 NA NA NA NA NA 1015322 NA 1 NA NA NA OA.HazOccupationAg ON.HazOccupationNonAg R3.Reg3AgeOccupation 1005317 NA NA NA 1007183 NA NA NA 1008833 NA NA NA 1012281 NA NA NA 1015285 NA NA NA 1015315 NA NA NA 1015322 NA NA NA RK.Records_CL V.Other 1005317 NA NA 1007183 NA NA 1008833 NA NA 1012281 NA NA 1015285 1 NA 1015315 NA NA 1015322 NA NA > Chel Hee Lee On 12/18/2014 10:02 AM, Jeff Newmiller wrote:> No guarantees on "best"... but one way using base R could be: > > # Note that "CaseID" is actually not a valid PViol.Type as you had it > PViol.Type <- c( "BW.BackWages" > , "LD.Liquid_Damages" > , "MW.Minimum_Wage" > , "OT.Overtime" > , "RK.Records_FLSA" > , "V.Poster_Other" > , "AS.Age" > , "BW.WHMIS_BackWages" > , "HS.Hours" > , "OA.HazOccupationAg" > , "ON.HazOccupationNonAg" > , "R3.Reg3AgeOccupation" > , "RK.Records_CL" > , "V.Other" ) > > # explicitly specifying all levels to the factor insures a complete > # set of column outputs regardless of what is in the input > PViol.Type.Per.Case.Original <- > data.frame( CaseID > , Primary.Viol.Type=factor( Primary.Viol.Type > , levels=PViol.Type ) ) > > tmp <- table( PViol.Type.Per.Case.Original ) > ans <- data.frame( CaseID=rownames( tmp ) > , as.data.frame( ifelse( 0==tmp, NA, 1 ) ) > ) > > > On Wed, 17 Dec 2014, bcrombie wrote: > >> # I have a dataframe that contains 2 columns: >> CaseID <- c('1015285', >> '1005317', >> '1012281', >> '1015285', >> '1015285', >> '1007183', >> '1008833', >> '1015315', >> '1015322', >> '1015285') >> >> Primary.Viol.Type <- c('AS.Age', >> 'HS.Hours', >> 'HS.Hours', >> 'HS.Hours', >> 'RK.Records_CL', >> 'OT.Overtime', >> 'OT.Overtime', >> 'OT.Overtime', >> 'V.Poster_Other', >> 'V.Poster_Other') >> >> PViol.Type.Per.Case.Original <- data.frame(CaseID,Primary.Viol.Type) >> >> # CaseID?s can be repeated because there can be up to 14 >> Primary.Viol.Type?s >> per CaseID. >> >> # I want to transform this dataframe into one that has 15 columns, >> where the >> first column is CaseID, and the rest are the 14 primary viol. types. The >> CaseID column will contain a list of the unique CaseID?s (no >> replicates) and >> for each of their rows, there will be a ?1? under a column >> corresponding to >> a primary violation type recorded for that CaseID. So, technically, >> there >> could be zero to 14 ?1?s? in a CaseID?s row. >> >> # For example, the row for CaseID '1015285' above would have a ?1? under >> ?AS.Age?, ?HS.Hours?, ?RK.Records_CL?, and ?V.Poster_Other?, but have >> "NA" >> under the rest of the columns. >> >> PViol.Type <- c("CaseID", >> "BW.BackWages", >> "LD.Liquid_Damages", >> "MW.Minimum_Wage", >> "OT.Overtime", >> "RK.Records_FLSA", >> "V.Poster_Other", >> "AS.Age", >> "BW.WHMIS_BackWages", >> "HS.Hours", >> "OA.HazOccupationAg", >> "ON.HazOccupationNonAg", >> "R3.Reg3AgeOccupation", >> "RK.Records_CL", >> "V.Other") >> >> PViol.Type.Columns <- t(data.frame(PViol.Type) >> >> # What is the best way to do this in R? >> >> >> >> >> -- >> View this message in context: >> http://r.789695.n4.nabble.com/Make-2nd-col-of-2-col-df-into-header-row-of-same-df-then-adjust-col1-data-display-tp4700878.html >> >> Sent from the R help mailing list archive at Nabble.com. >> >> ______________________________________________ >> 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. > > --------------------------------------------------------------------------- > Jeff Newmiller The ..... ..... Go Live... > DCN:<jdnewmil at dcn.davis.ca.us> Basics: ##.#. ##.#. Live Go... > Live: OO#.. Dead: OO#.. Playing > Research Engineer (Solar/Batteries O.O#. #.O#. with > /Software/Embedded Controllers) .OO#. .OO#. rocks...1k > > ______________________________________________ > 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.
Jeff Newmiller
2014-Dec-19 04:21 UTC
[R] Make 2nd col of 2-col df into header row of same df then adjust col1 data display
Please keep the list in the loop. Take a look at my code again... the factor function can accept a vector of all levels you want it to include. --------------------------------------------------------------------------- Jeff Newmiller The ..... ..... Go Live... DCN:<jdnewmil at dcn.davis.ca.us> Basics: ##.#. ##.#. Live Go... Live: OO#.. Dead: OO#.. Playing Research Engineer (Solar/Batteries O.O#. #.O#. with /Software/Embedded Controllers) .OO#. .OO#. rocks...1k --------------------------------------------------------------------------- Sent from my phone. Please excuse my brevity. On December 18, 2014 7:35:28 PM PST, "Crombie, Burnette N" <bcrombie at utk.edu> wrote:>Jeff, your code works fabulously on the dataset I submitted with my >question, but I can't get it to retain all 14 of the PViol.Type's with >my real dataset which is imported as a csv. Do you have any ideas how >to fix this? > >########## >MERGE_PViol.Detail.Per.Case <- >read.csv("~/FOIA_FLSA/MERGE_PViol.Detail.Per.Case_for_rtf10.csv", >stringsAsFactors=TRUE) > >###select only certain columns from dataset >PViol.Type.Per.Case <- MERGE_PViol.Detail.Per.Case[,c("CaseID", >"Primary.Viol.Type")] >PViol.Type.Per.Case$CaseID <- factor(PViol.Type.Per.Case$CaseID) >str(PViol.Type.Per.Case) >### 'data.frame': 13 obs. of 2 variables: >### $ CaseID : Factor w/ 8 levels "1005317","1007183",..: 5 >1 4 5 5 2 3 6 7 8 ... >### $ Primary.Viol.Type: Factor w/ 5 levels "AS.Age","HS.Hours",..: 1 2 >2 2 4 3 3 3 3 3 ... > > >PViol.Type <- c("BW.BackWages", > "LD.Liquid_Damages", > "MW.Minimum_Wage", > "OT.Overtime", > "RK.Records_FLSA", > "V.Poster_Other", > "AS.Age", > "BW.WHMIS_BackWages", > "HS.Hours", > "OA.HazOccupationAg", > "ON.HazOccupationNonAg", > "R3.Reg3AgeOccupation", > "RK.Records_CL", > "V.Other") > >###### Jeff Newmiller (RHelp) >#PViol.Type.Per.Case <- data.frame( CaseID, Primary.Viol.Type=factor( >Primary.Viol.Type, levels=PViol.Type ) ) > >tmp <- table( PViol.Type.Per.Case ) >ans <- data.frame( CaseID=rownames( tmp ), as.data.frame( ifelse( >0==tmp, NA, 1 ) )) >########## > > > >-----Original Message----- >From: Crombie, Burnette N >Sent: Thursday, December 18, 2014 11:17 AM >To: 'Jeff Newmiller' >Subject: RE: [R] Make 2nd col of 2-col df into header row of same df >then adjust col1 data display > >Thanks so much for your time. I will reply as soon as possible. I've >been pulled away from my desk -- BNC > >-----Original Message----- >From: Jeff Newmiller [mailto:jdnewmil at dcn.davis.ca.us] >Sent: Thursday, December 18, 2014 11:02 AM >To: Crombie, Burnette N >Cc: r-help at r-project.org >Subject: Re: [R] Make 2nd col of 2-col df into header row of same df >then adjust col1 data display > >No guarantees on "best"... but one way using base R could be: > ># Note that "CaseID" is actually not a valid PViol.Type as you had it >PViol.Type <- c( "BW.BackWages" > , "LD.Liquid_Damages" > , "MW.Minimum_Wage" > , "OT.Overtime" > , "RK.Records_FLSA" > , "V.Poster_Other" > , "AS.Age" > , "BW.WHMIS_BackWages" > , "HS.Hours" > , "OA.HazOccupationAg" > , "ON.HazOccupationNonAg" > , "R3.Reg3AgeOccupation" > , "RK.Records_CL" > , "V.Other" ) > ># explicitly specifying all levels to the factor insures a complete # >set of column outputs regardless of what is in the input >PViol.Type.Per.Case.Original <- > data.frame( CaseID > , Primary.Viol.Type=factor( Primary.Viol.Type > , levels=PViol.Type ) ) > >tmp <- table( PViol.Type.Per.Case.Original ) ans <- data.frame( >CaseID=rownames( tmp ) > , as.data.frame( ifelse( 0==tmp, NA, 1 ) ) > ) > > >On Wed, 17 Dec 2014, bcrombie wrote: > >> # I have a dataframe that contains 2 columns: >> CaseID <- c('1015285', >> '1005317', >> '1012281', >> '1015285', >> '1015285', >> '1007183', >> '1008833', >> '1015315', >> '1015322', >> '1015285') >> >> Primary.Viol.Type <- c('AS.Age', >> 'HS.Hours', >> 'HS.Hours', >> 'HS.Hours', >> 'RK.Records_CL', >> 'OT.Overtime', >> 'OT.Overtime', >> 'OT.Overtime', >> 'V.Poster_Other', >> 'V.Poster_Other') >> >> PViol.Type.Per.Case.Original <- data.frame(CaseID,Primary.Viol.Type) >> >> # CaseID?s can be repeated because there can be up to 14 >> Primary.Viol.Type?s per CaseID. >> >> # I want to transform this dataframe into one that has 15 columns, >> where the first column is CaseID, and the rest are the 14 primary >> viol. types. The CaseID column will contain a list of the unique >> CaseID?s (no replicates) and for each of their rows, there will be a >> ?1? under a column corresponding to a primary violation type >recorded >> for that CaseID. So, technically, there could be zero to 14 ?1?s? in >a CaseID?s row. >> >> # For example, the row for CaseID '1015285' above would have a ?1? >> under ?AS.Age?, ?HS.Hours?, ?RK.Records_CL?, and ?V.Poster_Other?, >but have "NA" >> under the rest of the columns. >> >> PViol.Type <- c("CaseID", >> "BW.BackWages", >> "LD.Liquid_Damages", >> "MW.Minimum_Wage", >> "OT.Overtime", >> "RK.Records_FLSA", >> "V.Poster_Other", >> "AS.Age", >> "BW.WHMIS_BackWages", >> "HS.Hours", >> "OA.HazOccupationAg", >> "ON.HazOccupationNonAg", >> "R3.Reg3AgeOccupation", >> "RK.Records_CL", >> "V.Other") >> >> PViol.Type.Columns <- t(data.frame(PViol.Type) >> >> # What is the best way to do this in R? >> >> >> >> >> -- >> View this message in context: >> >http://r.789695.n4.nabble.com/Make-2nd-col-of-2-col-df-into-header-row >> -of-same-df-then-adjust-col1-data-display-tp4700878.html >> Sent from the R help mailing list archive at Nabble.com. >> >> ______________________________________________ >> 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. > >--------------------------------------------------------------------------- >Jeff Newmiller The ..... ..... Go >Live... >DCN:<jdnewmil at dcn.davis.ca.us> Basics: ##.#. ##.#. Live >Go... > Live: OO#.. Dead: OO#.. Playing >Research Engineer (Solar/Batteries O.O#. #.O#. with >/Software/Embedded Controllers) .OO#. .OO#. >rocks...1k >---------------------------------------------------------------------------
Chel Hee Lee
2014-Dec-19 05:35 UTC
[R] Make 2nd col of 2-col df into header row of same df then adjust col1 data display
Please take a look at my code again. The error message says that object 'Primary.Viol.Type' not found. Have you ever created the object 'Primary.Viol.Type'? It will be working if you replace 'Primary.Viol.Type' by 'PViol.Type.Per.Case.Original$Primary.Viol.Type' where 'factor()' is used. I hope this helps. Chel Hee Lee On 12/18/2014 08:57 PM, Crombie, Burnette N wrote:> Chel, your solution is fantastic on the dataset I submitted in my question but it is not working when I import my real dataset into R. Do I need to vectorize the columns in my real dataset after importing? I tried a few things (###) but not making progress: > > MERGE_PViol.Detail.Per.Case <- read.csv("~/FOIA_FLSA/MERGE_PViol.Detail.Per.Case_for_rtf10.csv", stringsAsFactors=TRUE) > > ### select only certain columns > PViol.Type.Per.Case.Original <- MERGE_PViol.Detail.Per.Case[,c("CaseID", "Primary.Viol.Type")] > > ### write.csv(PViol.Type.Per.Case,file="PViol.Type.Per.Case.Select.csv") > ### PViol.Type.Per.Case.Original <- read.csv("~/FOIA_FLSA/PViol.Type.Per.Case.Select.csv") > ### PViol.Type.Per.Case.Original$X <- NULL > ###PViol.Type.Per.Case.Original[] <- lapply(PViol.Type.Per.Case.Original, as.character) > > PViol.Type <- c("CaseID", > "BW.BackWages", > "LD.Liquid_Damages", > "MW.Minimum_Wage", > "OT.Overtime", > "RK.Records_FLSA", > "V.Poster_Other", > "AS.Age", > "BW.WHMIS_BackWages", > "HS.Hours", > "OA.HazOccupationAg", > "ON.HazOccupationNonAg", > "R3.Reg3AgeOccupation", > "RK.Records_CL", > "V.Other") > > PViol.Type.Per.Case.Original$Primary.Viol.Type <- factor(Primary.Viol.Type, levels=PViol.Type, labels=PViol.Type) > > ### Error in factor(Primary.Viol.Type, levels = PViol.Type, labels = PViol.Type) : object 'Primary.Viol.Type' not found > > tmp <- split(PViol.Type.Per.Case.Original,PViol.Type.Per.Case.Original$CaseID) > ans <- ifelse(do.call(rbind, lapply(tmp, function(x)table(x$Primary.Viol.Type))), 1, NA) > > > > -----Original Message----- > From: Crombie, Burnette N > Sent: Thursday, December 18, 2014 3:01 PM > To: 'Chel Hee Lee' > Subject: RE: [R] Make 2nd col of 2-col df into header row of same df then adjust col1 data display > > Thanks for taking the time to review this, Chel. I've got to step away from my desk, but will reply more substantially as soon as possible. -- BNC > > -----Original Message----- > From: Chel Hee Lee [mailto:chl948 at mail.usask.ca] > Sent: Thursday, December 18, 2014 2:43 PM > To: Jeff Newmiller; Crombie, Burnette N > Cc: r-help at r-project.org > Subject: Re: [R] Make 2nd col of 2-col df into header row of same df then adjust col1 data display > > I like the approach presented by Jeff Newmiller as shown in the previous post (I really like his way). As he suggested, it would be good to start with 'factor' since you have all values of 'Primary.Viol.Type'. > You may try to use 'split()' function for creating table that you wish to build. Please see the below (I hope this helps): > > > PViol.Type.Per.Case.Original$Primary.Viol.Type <- factor(Primary.Viol.Type, levels=PViol.Type, labels=PViol.Type) > > tmp <- split(PViol.Type.Per.Case.Original, > PViol.Type.Per.Case.Original$CaseID) > > ans <- ifelse(do.call(rbind, lapply(tmp, function(x) table(x$Primary.Viol.Type))), 1, NA) > ans > CaseID BW.BackWages LD.Liquid_Damages MW.Minimum_Wage OT.Overtime > 1005317 NA NA NA NA NA > 1007183 NA NA NA NA 1 > 1008833 NA NA NA NA 1 > 1012281 NA NA NA NA NA > 1015285 NA NA NA NA NA > 1015315 NA NA NA NA 1 > 1015322 NA NA NA NA NA > RK.Records_FLSA V.Poster_Other AS.Age BW.WHMIS_BackWages HS.Hours > 1005317 NA NA NA NA 1 > 1007183 NA NA NA NA NA > 1008833 NA NA NA NA NA > 1012281 NA NA NA NA 1 > 1015285 NA 1 1 NA 1 > 1015315 NA NA NA NA NA > 1015322 NA 1 NA NA NA > OA.HazOccupationAg ON.HazOccupationNonAg R3.Reg3AgeOccupation > 1005317 NA NA NA > 1007183 NA NA NA > 1008833 NA NA NA > 1012281 NA NA NA > 1015285 NA NA NA > 1015315 NA NA NA > 1015322 NA NA NA > RK.Records_CL V.Other > 1005317 NA NA > 1007183 NA NA > 1008833 NA NA > 1012281 NA NA > 1015285 1 NA > 1015315 NA NA > 1015322 NA NA > > > > Chel Hee Lee > > On 12/18/2014 10:02 AM, Jeff Newmiller wrote: >> No guarantees on "best"... but one way using base R could be: >> >> # Note that "CaseID" is actually not a valid PViol.Type as you had it >> PViol.Type <- c( "BW.BackWages" >> , "LD.Liquid_Damages" >> , "MW.Minimum_Wage" >> , "OT.Overtime" >> , "RK.Records_FLSA" >> , "V.Poster_Other" >> , "AS.Age" >> , "BW.WHMIS_BackWages" >> , "HS.Hours" >> , "OA.HazOccupationAg" >> , "ON.HazOccupationNonAg" >> , "R3.Reg3AgeOccupation" >> , "RK.Records_CL" >> , "V.Other" ) >> >> # explicitly specifying all levels to the factor insures a complete # >> set of column outputs regardless of what is in the input >> PViol.Type.Per.Case.Original <- >> data.frame( CaseID >> , Primary.Viol.Type=factor( Primary.Viol.Type >> , levels=PViol.Type ) ) >> >> tmp <- table( PViol.Type.Per.Case.Original ) ans <- data.frame( >> CaseID=rownames( tmp ) >> , as.data.frame( ifelse( 0==tmp, NA, 1 ) ) >> ) >> >> >> On Wed, 17 Dec 2014, bcrombie wrote: >> >>> # I have a dataframe that contains 2 columns: >>> CaseID <- c('1015285', >>> '1005317', >>> '1012281', >>> '1015285', >>> '1015285', >>> '1007183', >>> '1008833', >>> '1015315', >>> '1015322', >>> '1015285') >>> >>> Primary.Viol.Type <- c('AS.Age', >>> 'HS.Hours', >>> 'HS.Hours', >>> 'HS.Hours', >>> 'RK.Records_CL', >>> 'OT.Overtime', >>> 'OT.Overtime', >>> 'OT.Overtime', >>> 'V.Poster_Other', >>> 'V.Poster_Other') >>> >>> PViol.Type.Per.Case.Original <- data.frame(CaseID,Primary.Viol.Type) >>> >>> # CaseID?s can be repeated because there can be up to 14 >>> Primary.Viol.Type?s per CaseID. >>> >>> # I want to transform this dataframe into one that has 15 columns, >>> where the first column is CaseID, and the rest are the 14 primary >>> viol. types. The CaseID column will contain a list of the unique >>> CaseID?s (no >>> replicates) and >>> for each of their rows, there will be a ?1? under a column >>> corresponding to a primary violation type recorded for that CaseID. >>> So, technically, there could be zero to 14 ?1?s? in a CaseID?s row. >>> >>> # For example, the row for CaseID '1015285' above would have a ?1? >>> under ?AS.Age?, ?HS.Hours?, ?RK.Records_CL?, and ?V.Poster_Other?, >>> but have "NA" >>> under the rest of the columns. >>> >>> PViol.Type <- c("CaseID", >>> "BW.BackWages", >>> "LD.Liquid_Damages", >>> "MW.Minimum_Wage", >>> "OT.Overtime", >>> "RK.Records_FLSA", >>> "V.Poster_Other", >>> "AS.Age", >>> "BW.WHMIS_BackWages", >>> "HS.Hours", >>> "OA.HazOccupationAg", >>> "ON.HazOccupationNonAg", >>> "R3.Reg3AgeOccupation", >>> "RK.Records_CL", >>> "V.Other") >>> >>> PViol.Type.Columns <- t(data.frame(PViol.Type) >>> >>> # What is the best way to do this in R? >>> >>> >>> >>> >>> -- >>> View this message in context: >>> http://r.789695.n4.nabble.com/Make-2nd-col-of-2-col-df-into-header-ro >>> w-of-same-df-then-adjust-col1-data-display-tp4700878.html >>> >>> Sent from the R help mailing list archive at Nabble.com. >>> >>> ______________________________________________ >>> 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. >> >> --------------------------------------------------------------------------- >> Jeff Newmiller The ..... ..... Go Live... >> DCN:<jdnewmil at dcn.davis.ca.us> Basics: ##.#. ##.#. Live Go... >> Live: OO#.. Dead: OO#.. Playing >> Research Engineer (Solar/Batteries O.O#. #.O#. with >> /Software/Embedded Controllers) .OO#. .OO#. rocks...1k >> >> ______________________________________________ >> 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.
Sven E. Templer
2014-Dec-19 09:13 UTC
[R] Make 2nd col of 2-col df into header row of same df then adjust col1 data display
Another solution: CaseID <- c("1015285", "1005317", "1012281", "1015285", "1015285", "1007183", "1008833", "1015315", "1015322", "1015285") Primary.Viol.Type <- c("AS.Age", "HS.Hours", "HS.Hours", "HS.Hours", "RK.Records_CL", "OT.Overtime", "OT.Overtime", "OT.Overtime", "V.Poster_Other", "V.Poster_Other") library(reshape2) dcast(data.frame(CaseID, Primary.Viol.Type), CaseID~Primary.Viol.Type, length) # result: Using Primary.Viol.Type as value column: use value.var to override. CaseID AS.Age HS.Hours OT.Overtime RK.Records_CL V.Poster_Other 1 1005317 0 1 0 0 0 2 1007183 0 0 1 0 0 3 1008833 0 0 1 0 0 4 1012281 0 1 0 0 0 5 1015285 1 1 0 1 1 6 1015315 0 0 1 0 0 7 1015322 0 0 0 0 1 best, s. On 19 December 2014 at 06:35, Chel Hee Lee <chl948 at mail.usask.ca> wrote:> Please take a look at my code again. The error message says that object > 'Primary.Viol.Type' not found. Have you ever created the object > 'Primary.Viol.Type'? It will be working if you replace 'Primary.Viol.Type' > by 'PViol.Type.Per.Case.Original$Primary.Viol.Type' where 'factor()' is > used. I hope this helps. > > Chel Hee Lee > > On 12/18/2014 08:57 PM, Crombie, Burnette N wrote: >> >> Chel, your solution is fantastic on the dataset I submitted in my question >> but it is not working when I import my real dataset into R. Do I need to >> vectorize the columns in my real dataset after importing? I tried a few >> things (###) but not making progress: >> >> MERGE_PViol.Detail.Per.Case <- >> read.csv("~/FOIA_FLSA/MERGE_PViol.Detail.Per.Case_for_rtf10.csv", >> stringsAsFactors=TRUE) >> >> ### select only certain columns >> PViol.Type.Per.Case.Original <- MERGE_PViol.Detail.Per.Case[,c("CaseID", >> "Primary.Viol.Type")] >> >> ### write.csv(PViol.Type.Per.Case,file="PViol.Type.Per.Case.Select.csv") >> ### PViol.Type.Per.Case.Original <- >> read.csv("~/FOIA_FLSA/PViol.Type.Per.Case.Select.csv") >> ### PViol.Type.Per.Case.Original$X <- NULL >> ###PViol.Type.Per.Case.Original[] <- lapply(PViol.Type.Per.Case.Original, >> as.character) >> >> PViol.Type <- c("CaseID", >> "BW.BackWages", >> "LD.Liquid_Damages", >> "MW.Minimum_Wage", >> "OT.Overtime", >> "RK.Records_FLSA", >> "V.Poster_Other", >> "AS.Age", >> "BW.WHMIS_BackWages", >> "HS.Hours", >> "OA.HazOccupationAg", >> "ON.HazOccupationNonAg", >> "R3.Reg3AgeOccupation", >> "RK.Records_CL", >> "V.Other") >> >> PViol.Type.Per.Case.Original$Primary.Viol.Type <- >> factor(Primary.Viol.Type, levels=PViol.Type, labels=PViol.Type) >> >> ### Error in factor(Primary.Viol.Type, levels = PViol.Type, labels >> PViol.Type) : object 'Primary.Viol.Type' not found >> >> tmp <- >> split(PViol.Type.Per.Case.Original,PViol.Type.Per.Case.Original$CaseID) >> ans <- ifelse(do.call(rbind, lapply(tmp, >> function(x)table(x$Primary.Viol.Type))), 1, NA) >> >> >> >> -----Original Message----- >> From: Crombie, Burnette N >> Sent: Thursday, December 18, 2014 3:01 PM >> To: 'Chel Hee Lee' >> Subject: RE: [R] Make 2nd col of 2-col df into header row of same df then >> adjust col1 data display >> >> Thanks for taking the time to review this, Chel. I've got to step away >> from my desk, but will reply more substantially as soon as possible. -- BNC >> >> -----Original Message----- >> From: Chel Hee Lee [mailto:chl948 at mail.usask.ca] >> Sent: Thursday, December 18, 2014 2:43 PM >> To: Jeff Newmiller; Crombie, Burnette N >> Cc: r-help at r-project.org >> Subject: Re: [R] Make 2nd col of 2-col df into header row of same df then >> adjust col1 data display >> >> I like the approach presented by Jeff Newmiller as shown in the previous >> post (I really like his way). As he suggested, it would be good to start >> with 'factor' since you have all values of 'Primary.Viol.Type'. >> You may try to use 'split()' function for creating table that you wish to >> build. Please see the below (I hope this helps): >> >> > PViol.Type.Per.Case.Original$Primary.Viol.Type <- >> factor(Primary.Viol.Type, levels=PViol.Type, labels=PViol.Type) > > tmp <- >> split(PViol.Type.Per.Case.Original, >> PViol.Type.Per.Case.Original$CaseID) >> > ans <- ifelse(do.call(rbind, lapply(tmp, function(x) >> table(x$Primary.Viol.Type))), 1, NA) > ans >> CaseID BW.BackWages LD.Liquid_Damages MW.Minimum_Wage >> OT.Overtime >> 1005317 NA NA NA NA NA >> 1007183 NA NA NA NA 1 >> 1008833 NA NA NA NA 1 >> 1012281 NA NA NA NA NA >> 1015285 NA NA NA NA NA >> 1015315 NA NA NA NA 1 >> 1015322 NA NA NA NA NA >> RK.Records_FLSA V.Poster_Other AS.Age BW.WHMIS_BackWages >> HS.Hours >> 1005317 NA NA NA NA 1 >> 1007183 NA NA NA NA NA >> 1008833 NA NA NA NA NA >> 1012281 NA NA NA NA 1 >> 1015285 NA 1 1 NA 1 >> 1015315 NA NA NA NA NA >> 1015322 NA 1 NA NA NA >> OA.HazOccupationAg ON.HazOccupationNonAg R3.Reg3AgeOccupation >> 1005317 NA NA NA >> 1007183 NA NA NA >> 1008833 NA NA NA >> 1012281 NA NA NA >> 1015285 NA NA NA >> 1015315 NA NA NA >> 1015322 NA NA NA >> RK.Records_CL V.Other >> 1005317 NA NA >> 1007183 NA NA >> 1008833 NA NA >> 1012281 NA NA >> 1015285 1 NA >> 1015315 NA NA >> 1015322 NA NA >> > >> >> Chel Hee Lee >> >> On 12/18/2014 10:02 AM, Jeff Newmiller wrote: >>> >>> No guarantees on "best"... but one way using base R could be: >>> >>> # Note that "CaseID" is actually not a valid PViol.Type as you had it >>> PViol.Type <- c( "BW.BackWages" >>> , "LD.Liquid_Damages" >>> , "MW.Minimum_Wage" >>> , "OT.Overtime" >>> , "RK.Records_FLSA" >>> , "V.Poster_Other" >>> , "AS.Age" >>> , "BW.WHMIS_BackWages" >>> , "HS.Hours" >>> , "OA.HazOccupationAg" >>> , "ON.HazOccupationNonAg" >>> , "R3.Reg3AgeOccupation" >>> , "RK.Records_CL" >>> , "V.Other" ) >>> >>> # explicitly specifying all levels to the factor insures a complete # >>> set of column outputs regardless of what is in the input >>> PViol.Type.Per.Case.Original <- >>> data.frame( CaseID >>> , Primary.Viol.Type=factor( Primary.Viol.Type >>> , levels=PViol.Type ) ) >>> >>> tmp <- table( PViol.Type.Per.Case.Original ) ans <- data.frame( >>> CaseID=rownames( tmp ) >>> , as.data.frame( ifelse( 0==tmp, NA, 1 ) ) >>> ) >>> >>> >>> On Wed, 17 Dec 2014, bcrombie wrote: >>> >>>> # I have a dataframe that contains 2 columns: >>>> CaseID <- c('1015285', >>>> '1005317', >>>> '1012281', >>>> '1015285', >>>> '1015285', >>>> '1007183', >>>> '1008833', >>>> '1015315', >>>> '1015322', >>>> '1015285') >>>> >>>> Primary.Viol.Type <- c('AS.Age', >>>> 'HS.Hours', >>>> 'HS.Hours', >>>> 'HS.Hours', >>>> 'RK.Records_CL', >>>> 'OT.Overtime', >>>> 'OT.Overtime', >>>> 'OT.Overtime', >>>> 'V.Poster_Other', >>>> 'V.Poster_Other') >>>> >>>> PViol.Type.Per.Case.Original <- data.frame(CaseID,Primary.Viol.Type) >>>> >>>> # CaseID?s can be repeated because there can be up to 14 >>>> Primary.Viol.Type?s per CaseID. >>>> >>>> # I want to transform this dataframe into one that has 15 columns, >>>> where the first column is CaseID, and the rest are the 14 primary >>>> viol. types. The CaseID column will contain a list of the unique >>>> CaseID?s (no >>>> replicates) and >>>> for each of their rows, there will be a ?1? under a column >>>> corresponding to a primary violation type recorded for that CaseID. >>>> So, technically, there could be zero to 14 ?1?s? in a CaseID?s row. >>>> >>>> # For example, the row for CaseID '1015285' above would have a ?1? >>>> under ?AS.Age?, ?HS.Hours?, ?RK.Records_CL?, and ?V.Poster_Other?, >>>> but have "NA" >>>> under the rest of the columns. >>>> >>>> PViol.Type <- c("CaseID", >>>> "BW.BackWages", >>>> "LD.Liquid_Damages", >>>> "MW.Minimum_Wage", >>>> "OT.Overtime", >>>> "RK.Records_FLSA", >>>> "V.Poster_Other", >>>> "AS.Age", >>>> "BW.WHMIS_BackWages", >>>> "HS.Hours", >>>> "OA.HazOccupationAg", >>>> "ON.HazOccupationNonAg", >>>> "R3.Reg3AgeOccupation", >>>> "RK.Records_CL", >>>> "V.Other") >>>> >>>> PViol.Type.Columns <- t(data.frame(PViol.Type) >>>> >>>> # What is the best way to do this in R? >>>> >>>> >>>> >>>> >>>> -- >>>> View this message in context: >>>> http://r.789695.n4.nabble.com/Make-2nd-col-of-2-col-df-into-header-ro >>>> w-of-same-df-then-adjust-col1-data-display-tp4700878.html >>>> >>>> Sent from the R help mailing list archive at Nabble.com. >>>> >>>> ______________________________________________ >>>> 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. >>> >>> >>> >>> --------------------------------------------------------------------------- >>> Jeff Newmiller The ..... ..... Go >>> Live... >>> DCN:<jdnewmil at dcn.davis.ca.us> Basics: ##.#. ##.#. Live >>> Go... >>> Live: OO#.. Dead: OO#.. >>> Playing >>> Research Engineer (Solar/Batteries O.O#. #.O#. with >>> /Software/Embedded Controllers) .OO#. .OO#. >>> rocks...1k >>> >>> ______________________________________________ >>> 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. > > > ______________________________________________ > 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.
John Kane
2014-Dec-19 13:44 UTC
[R] Make 2nd col of 2-col df into header row of same df then adjust col1 data display
Very pretty. I could have saved myself about 1/2 hour of mucking about if I had thought ot "length". John Kane Kingston ON Canada> -----Original Message----- > From: sven.templer at gmail.com > Sent: Fri, 19 Dec 2014 10:13:55 +0100 > To: chl948 at mail.usask.ca > Subject: Re: [R] Make 2nd col of 2-col df into header row of same df then > adjust col1 data display > > Another solution: > > CaseID <- c("1015285", "1005317", "1012281", "1015285", "1015285", > "1007183", > "1008833", "1015315", "1015322", "1015285") > Primary.Viol.Type <- c("AS.Age", "HS.Hours", "HS.Hours", "HS.Hours", > "RK.Records_CL", > "OT.Overtime", "OT.Overtime", "OT.Overtime", "V.Poster_Other", > "V.Poster_Other") > > library(reshape2) > dcast(data.frame(CaseID, Primary.Viol.Type), CaseID~Primary.Viol.Type, > length) > > # result: > > Using Primary.Viol.Type as value column: use value.var to override. > CaseID AS.Age HS.Hours OT.Overtime RK.Records_CL V.Poster_Other > 1 1005317 0 1 0 0 0 > 2 1007183 0 0 1 0 0 > 3 1008833 0 0 1 0 0 > 4 1012281 0 1 0 0 0 > 5 1015285 1 1 0 1 1 > 6 1015315 0 0 1 0 0 > 7 1015322 0 0 0 0 1 > > > best, s. > > On 19 December 2014 at 06:35, Chel Hee Lee <chl948 at mail.usask.ca> wrote: >> Please take a look at my code again. The error message says that object >> 'Primary.Viol.Type' not found. Have you ever created the object >> 'Primary.Viol.Type'? It will be working if you replace >> 'Primary.Viol.Type' >> by 'PViol.Type.Per.Case.Original$Primary.Viol.Type' where 'factor()' is >> used. I hope this helps. >> >> Chel Hee Lee >> >> On 12/18/2014 08:57 PM, Crombie, Burnette N wrote: >>> >>> Chel, your solution is fantastic on the dataset I submitted in my >>> question >>> but it is not working when I import my real dataset into R. Do I need >>> to >>> vectorize the columns in my real dataset after importing? I tried a >>> few >>> things (###) but not making progress: >>> >>> MERGE_PViol.Detail.Per.Case <- >>> read.csv("~/FOIA_FLSA/MERGE_PViol.Detail.Per.Case_for_rtf10.csv", >>> stringsAsFactors=TRUE) >>> >>> ### select only certain columns >>> PViol.Type.Per.Case.Original <- >>> MERGE_PViol.Detail.Per.Case[,c("CaseID", >>> "Primary.Viol.Type")] >>> >>> ### >>> write.csv(PViol.Type.Per.Case,file="PViol.Type.Per.Case.Select.csv") >>> ### PViol.Type.Per.Case.Original <- >>> read.csv("~/FOIA_FLSA/PViol.Type.Per.Case.Select.csv") >>> ### PViol.Type.Per.Case.Original$X <- NULL >>> ###PViol.Type.Per.Case.Original[] <- >>> lapply(PViol.Type.Per.Case.Original, >>> as.character) >>> >>> PViol.Type <- c("CaseID", >>> "BW.BackWages", >>> "LD.Liquid_Damages", >>> "MW.Minimum_Wage", >>> "OT.Overtime", >>> "RK.Records_FLSA", >>> "V.Poster_Other", >>> "AS.Age", >>> "BW.WHMIS_BackWages", >>> "HS.Hours", >>> "OA.HazOccupationAg", >>> "ON.HazOccupationNonAg", >>> "R3.Reg3AgeOccupation", >>> "RK.Records_CL", >>> "V.Other") >>> >>> PViol.Type.Per.Case.Original$Primary.Viol.Type <- >>> factor(Primary.Viol.Type, levels=PViol.Type, labels=PViol.Type) >>> >>> ### Error in factor(Primary.Viol.Type, levels = PViol.Type, labels >>> PViol.Type) : object 'Primary.Viol.Type' not found >>> >>> tmp <- >>> split(PViol.Type.Per.Case.Original,PViol.Type.Per.Case.Original$CaseID) >>> ans <- ifelse(do.call(rbind, lapply(tmp, >>> function(x)table(x$Primary.Viol.Type))), 1, NA) >>> >>> >>> >>> -----Original Message----- >>> From: Crombie, Burnette N >>> Sent: Thursday, December 18, 2014 3:01 PM >>> To: 'Chel Hee Lee' >>> Subject: RE: [R] Make 2nd col of 2-col df into header row of same df >>> then >>> adjust col1 data display >>> >>> Thanks for taking the time to review this, Chel. I've got to step away >>> from my desk, but will reply more substantially as soon as possible. -- >>> BNC >>> >>> -----Original Message----- >>> From: Chel Hee Lee [mailto:chl948 at mail.usask.ca] >>> Sent: Thursday, December 18, 2014 2:43 PM >>> To: Jeff Newmiller; Crombie, Burnette N >>> Cc: r-help at r-project.org >>> Subject: Re: [R] Make 2nd col of 2-col df into header row of same df >>> then >>> adjust col1 data display >>> >>> I like the approach presented by Jeff Newmiller as shown in the >>> previous >>> post (I really like his way). As he suggested, it would be good to >>> start >>> with 'factor' since you have all values of 'Primary.Viol.Type'. >>> You may try to use 'split()' function for creating table that you wish >>> to >>> build. Please see the below (I hope this helps): >>> >>> > PViol.Type.Per.Case.Original$Primary.Viol.Type <- >>> factor(Primary.Viol.Type, levels=PViol.Type, labels=PViol.Type) > > >>> tmp <- >>> split(PViol.Type.Per.Case.Original, >>> PViol.Type.Per.Case.Original$CaseID) >>> > ans <- ifelse(do.call(rbind, lapply(tmp, function(x) >>> table(x$Primary.Viol.Type))), 1, NA) > ans >>> CaseID BW.BackWages LD.Liquid_Damages MW.Minimum_Wage >>> OT.Overtime >>> 1005317 NA NA NA NA >>> NA >>> 1007183 NA NA NA NA >>> 1 >>> 1008833 NA NA NA NA >>> 1 >>> 1012281 NA NA NA NA >>> NA >>> 1015285 NA NA NA NA >>> NA >>> 1015315 NA NA NA NA >>> 1 >>> 1015322 NA NA NA NA >>> NA >>> RK.Records_FLSA V.Poster_Other AS.Age BW.WHMIS_BackWages >>> HS.Hours >>> 1005317 NA NA NA NA >>> 1 >>> 1007183 NA NA NA NA >>> NA >>> 1008833 NA NA NA NA >>> NA >>> 1012281 NA NA NA NA >>> 1 >>> 1015285 NA 1 1 NA >>> 1 >>> 1015315 NA NA NA NA >>> NA >>> 1015322 NA 1 NA NA >>> NA >>> OA.HazOccupationAg ON.HazOccupationNonAg R3.Reg3AgeOccupation >>> 1005317 NA NA NA >>> 1007183 NA NA NA >>> 1008833 NA NA NA >>> 1012281 NA NA NA >>> 1015285 NA NA NA >>> 1015315 NA NA NA >>> 1015322 NA NA NA >>> RK.Records_CL V.Other >>> 1005317 NA NA >>> 1007183 NA NA >>> 1008833 NA NA >>> 1012281 NA NA >>> 1015285 1 NA >>> 1015315 NA NA >>> 1015322 NA NA >>> > >>> >>> Chel Hee Lee >>> >>> On 12/18/2014 10:02 AM, Jeff Newmiller wrote: >>>> >>>> No guarantees on "best"... but one way using base R could be: >>>> >>>> # Note that "CaseID" is actually not a valid PViol.Type as you had it >>>> PViol.Type <- c( "BW.BackWages" >>>> , "LD.Liquid_Damages" >>>> , "MW.Minimum_Wage" >>>> , "OT.Overtime" >>>> , "RK.Records_FLSA" >>>> , "V.Poster_Other" >>>> , "AS.Age" >>>> , "BW.WHMIS_BackWages" >>>> , "HS.Hours" >>>> , "OA.HazOccupationAg" >>>> , "ON.HazOccupationNonAg" >>>> , "R3.Reg3AgeOccupation" >>>> , "RK.Records_CL" >>>> , "V.Other" ) >>>> >>>> # explicitly specifying all levels to the factor insures a complete # >>>> set of column outputs regardless of what is in the input >>>> PViol.Type.Per.Case.Original <- >>>> data.frame( CaseID >>>> , Primary.Viol.Type=factor( Primary.Viol.Type >>>> , levels=PViol.Type ) ) >>>> >>>> tmp <- table( PViol.Type.Per.Case.Original ) ans <- data.frame( >>>> CaseID=rownames( tmp ) >>>> , as.data.frame( ifelse( 0==tmp, NA, 1 ) ) >>>> ) >>>> >>>> >>>> On Wed, 17 Dec 2014, bcrombie wrote: >>>> >>>>> # I have a dataframe that contains 2 columns: >>>>> CaseID <- c('1015285', >>>>> '1005317', >>>>> '1012281', >>>>> '1015285', >>>>> '1015285', >>>>> '1007183', >>>>> '1008833', >>>>> '1015315', >>>>> '1015322', >>>>> '1015285') >>>>> >>>>> Primary.Viol.Type <- c('AS.Age', >>>>> 'HS.Hours', >>>>> 'HS.Hours', >>>>> 'HS.Hours', >>>>> 'RK.Records_CL', >>>>> 'OT.Overtime', >>>>> 'OT.Overtime', >>>>> 'OT.Overtime', >>>>> 'V.Poster_Other', >>>>> 'V.Poster_Other') >>>>> >>>>> PViol.Type.Per.Case.Original <- data.frame(CaseID,Primary.Viol.Type) >>>>> >>>>> # CaseID?s can be repeated because there can be up to 14 >>>>> Primary.Viol.Type?s per CaseID. >>>>> >>>>> # I want to transform this dataframe into one that has 15 columns, >>>>> where the first column is CaseID, and the rest are the 14 primary >>>>> viol. types. The CaseID column will contain a list of the unique >>>>> CaseID?s (no >>>>> replicates) and >>>>> for each of their rows, there will be a ?1? under a column >>>>> corresponding to a primary violation type recorded for that CaseID. >>>>> So, technically, there could be zero to 14 ?1?s? in a CaseID?s row. >>>>> >>>>> # For example, the row for CaseID '1015285' above would have a ?1? >>>>> under ?AS.Age?, ?HS.Hours?, ?RK.Records_CL?, and ?V.Poster_Other?, >>>>> but have "NA" >>>>> under the rest of the columns. >>>>> >>>>> PViol.Type <- c("CaseID", >>>>> "BW.BackWages", >>>>> "LD.Liquid_Damages", >>>>> "MW.Minimum_Wage", >>>>> "OT.Overtime", >>>>> "RK.Records_FLSA", >>>>> "V.Poster_Other", >>>>> "AS.Age", >>>>> "BW.WHMIS_BackWages", >>>>> "HS.Hours", >>>>> "OA.HazOccupationAg", >>>>> "ON.HazOccupationNonAg", >>>>> "R3.Reg3AgeOccupation", >>>>> "RK.Records_CL", >>>>> "V.Other") >>>>> >>>>> PViol.Type.Columns <- t(data.frame(PViol.Type) >>>>> >>>>> # What is the best way to do this in R? >>>>> >>>>> >>>>> >>>>> >>>>> -- >>>>> View this message in context: >>>>> http://r.789695.n4.nabble.com/Make-2nd-col-of-2-col-df-into-header-ro >>>>> w-of-same-df-then-adjust-col1-data-display-tp4700878.html >>>>> >>>>> Sent from the R help mailing list archive at Nabble.com. >>>>> >>>>> ______________________________________________ >>>>> 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. >>>> >>>> >>>> >>>> --------------------------------------------------------------------------- >>>> Jeff Newmiller The ..... ..... Go >>>> Live... >>>> DCN:<jdnewmil at dcn.davis.ca.us> Basics: ##.#. ##.#. Live >>>> Go... >>>> Live: OO#.. Dead: OO#.. >>>> Playing >>>> Research Engineer (Solar/Batteries O.O#. #.O#. with >>>> /Software/Embedded Controllers) .OO#. .OO#. >>>> rocks...1k >>>> >>>> ______________________________________________ >>>> 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. >> >> >> ______________________________________________ >> 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. > > ______________________________________________ > 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.____________________________________________________________ Can't remember your password? Do you need a strong and secure password? Use Password manager! It stores your passwords & protects your account.
Crombie, Burnette N
2014-Dec-19 13:52 UTC
[R] Make 2nd col of 2-col df into header row of same df then adjust col1 data display
That is the solution I had tried first (yes, it's nice!), but it doesn't provide the other PViol.Type's that aren't necessarily in my dataset. That's where my problem is. I'm closer to the cure, though, and think I've thought of a solution as soon as I have time. I'll update everyone then. -- BNC -----Original Message----- From: John Kane [mailto:jrkrideau at inbox.com] Sent: Friday, December 19, 2014 8:44 AM To: Sven E. Templer; Chel Hee Lee Cc: R Help List; Crombie, Burnette N Subject: Re: [R] Make 2nd col of 2-col df into header row of same df then adjust col1 data display Very pretty. I could have saved myself about 1/2 hour of mucking about if I had thought ot "length". John Kane Kingston ON Canada> -----Original Message----- > From: sven.templer at gmail.com > Sent: Fri, 19 Dec 2014 10:13:55 +0100 > To: chl948 at mail.usask.ca > Subject: Re: [R] Make 2nd col of 2-col df into header row of same df > then adjust col1 data display > > Another solution: > > CaseID <- c("1015285", "1005317", "1012281", "1015285", "1015285", > "1007183", "1008833", "1015315", "1015322", "1015285") > Primary.Viol.Type <- c("AS.Age", "HS.Hours", "HS.Hours", "HS.Hours", > "RK.Records_CL", "OT.Overtime", "OT.Overtime", "OT.Overtime", > "V.Poster_Other", > "V.Poster_Other") > > library(reshape2) > dcast(data.frame(CaseID, Primary.Viol.Type), CaseID~Primary.Viol.Type, > length) > > # result: > > Using Primary.Viol.Type as value column: use value.var to override. > CaseID AS.Age HS.Hours OT.Overtime RK.Records_CL V.Poster_Other > 1 1005317 0 1 0 0 0 > 2 1007183 0 0 1 0 0 > 3 1008833 0 0 1 0 0 > 4 1012281 0 1 0 0 0 > 5 1015285 1 1 0 1 1 > 6 1015315 0 0 1 0 0 > 7 1015322 0 0 0 0 1 > > > best, s. > > On 19 December 2014 at 06:35, Chel Hee Lee <chl948 at mail.usask.ca> wrote: >> Please take a look at my code again. The error message says that >> object 'Primary.Viol.Type' not found. Have you ever created the object >> 'Primary.Viol.Type'? It will be working if you replace >> 'Primary.Viol.Type' >> by 'PViol.Type.Per.Case.Original$Primary.Viol.Type' where 'factor()' >> is used. I hope this helps. >> >> Chel Hee Lee >> >> On 12/18/2014 08:57 PM, Crombie, Burnette N wrote: >>> >>> Chel, your solution is fantastic on the dataset I submitted in my >>> question but it is not working when I import my real dataset into R. >>> Do I need to vectorize the columns in my real dataset after >>> importing? I tried a few things (###) but not making progress: >>> >>> MERGE_PViol.Detail.Per.Case <- >>> read.csv("~/FOIA_FLSA/MERGE_PViol.Detail.Per.Case_for_rtf10.csv", >>> stringsAsFactors=TRUE) >>> >>> ### select only certain columns >>> PViol.Type.Per.Case.Original <- >>> MERGE_PViol.Detail.Per.Case[,c("CaseID", >>> "Primary.Viol.Type")] >>> >>> ### >>> write.csv(PViol.Type.Per.Case,file="PViol.Type.Per.Case.Select.csv") >>> ### PViol.Type.Per.Case.Original <- >>> read.csv("~/FOIA_FLSA/PViol.Type.Per.Case.Select.csv") >>> ### PViol.Type.Per.Case.Original$X <- NULL >>> ###PViol.Type.Per.Case.Original[] <- >>> lapply(PViol.Type.Per.Case.Original, >>> as.character) >>> >>> PViol.Type <- c("CaseID", >>> "BW.BackWages", >>> "LD.Liquid_Damages", >>> "MW.Minimum_Wage", >>> "OT.Overtime", >>> "RK.Records_FLSA", >>> "V.Poster_Other", >>> "AS.Age", >>> "BW.WHMIS_BackWages", >>> "HS.Hours", >>> "OA.HazOccupationAg", >>> "ON.HazOccupationNonAg", >>> "R3.Reg3AgeOccupation", >>> "RK.Records_CL", >>> "V.Other") >>> >>> PViol.Type.Per.Case.Original$Primary.Viol.Type <- >>> factor(Primary.Viol.Type, levels=PViol.Type, labels=PViol.Type) >>> >>> ### Error in factor(Primary.Viol.Type, levels = PViol.Type, labels >>> PViol.Type) : object 'Primary.Viol.Type' not found >>> >>> tmp <- >>> split(PViol.Type.Per.Case.Original,PViol.Type.Per.Case.Original$Case >>> ID) ans <- ifelse(do.call(rbind, lapply(tmp, >>> function(x)table(x$Primary.Viol.Type))), 1, NA) >>> >>> >>> >>> -----Original Message----- >>> From: Crombie, Burnette N >>> Sent: Thursday, December 18, 2014 3:01 PM >>> To: 'Chel Hee Lee' >>> Subject: RE: [R] Make 2nd col of 2-col df into header row of same df >>> then adjust col1 data display >>> >>> Thanks for taking the time to review this, Chel. I've got to step >>> away from my desk, but will reply more substantially as soon as >>> possible. -- BNC >>> >>> -----Original Message----- >>> From: Chel Hee Lee [mailto:chl948 at mail.usask.ca] >>> Sent: Thursday, December 18, 2014 2:43 PM >>> To: Jeff Newmiller; Crombie, Burnette N >>> Cc: r-help at r-project.org >>> Subject: Re: [R] Make 2nd col of 2-col df into header row of same df >>> then adjust col1 data display >>> >>> I like the approach presented by Jeff Newmiller as shown in the >>> previous post (I really like his way). As he suggested, it would be >>> good to start with 'factor' since you have all values of >>> 'Primary.Viol.Type'. >>> You may try to use 'split()' function for creating table that you >>> wish to build. Please see the below (I hope this helps): >>> >>> > PViol.Type.Per.Case.Original$Primary.Viol.Type <- >>> factor(Primary.Viol.Type, levels=PViol.Type, labels=PViol.Type) > >>> > tmp <- split(PViol.Type.Per.Case.Original, >>> PViol.Type.Per.Case.Original$CaseID) >>> > ans <- ifelse(do.call(rbind, lapply(tmp, function(x) >>> table(x$Primary.Viol.Type))), 1, NA) > ans >>> CaseID BW.BackWages LD.Liquid_Damages MW.Minimum_Wage >>> OT.Overtime >>> 1005317 NA NA NA NA >>> NA >>> 1007183 NA NA NA NA >>> 1 >>> 1008833 NA NA NA NA >>> 1 >>> 1012281 NA NA NA NA >>> NA >>> 1015285 NA NA NA NA >>> NA >>> 1015315 NA NA NA NA >>> 1 >>> 1015322 NA NA NA NA >>> NA >>> RK.Records_FLSA V.Poster_Other AS.Age BW.WHMIS_BackWages >>> HS.Hours >>> 1005317 NA NA NA NA >>> 1 >>> 1007183 NA NA NA NA >>> NA >>> 1008833 NA NA NA NA >>> NA >>> 1012281 NA NA NA NA >>> 1 >>> 1015285 NA 1 1 NA >>> 1 >>> 1015315 NA NA NA NA >>> NA >>> 1015322 NA 1 NA NA >>> NA >>> OA.HazOccupationAg ON.HazOccupationNonAg R3.Reg3AgeOccupation >>> 1005317 NA NA NA >>> 1007183 NA NA NA >>> 1008833 NA NA NA >>> 1012281 NA NA NA >>> 1015285 NA NA NA >>> 1015315 NA NA NA >>> 1015322 NA NA NA >>> RK.Records_CL V.Other >>> 1005317 NA NA >>> 1007183 NA NA >>> 1008833 NA NA >>> 1012281 NA NA >>> 1015285 1 NA >>> 1015315 NA NA >>> 1015322 NA NA >>> > >>> >>> Chel Hee Lee >>> >>> On 12/18/2014 10:02 AM, Jeff Newmiller wrote: >>>> >>>> No guarantees on "best"... but one way using base R could be: >>>> >>>> # Note that "CaseID" is actually not a valid PViol.Type as you had >>>> it PViol.Type <- c( "BW.BackWages" >>>> , "LD.Liquid_Damages" >>>> , "MW.Minimum_Wage" >>>> , "OT.Overtime" >>>> , "RK.Records_FLSA" >>>> , "V.Poster_Other" >>>> , "AS.Age" >>>> , "BW.WHMIS_BackWages" >>>> , "HS.Hours" >>>> , "OA.HazOccupationAg" >>>> , "ON.HazOccupationNonAg" >>>> , "R3.Reg3AgeOccupation" >>>> , "RK.Records_CL" >>>> , "V.Other" ) >>>> >>>> # explicitly specifying all levels to the factor insures a complete >>>> # set of column outputs regardless of what is in the input >>>> PViol.Type.Per.Case.Original <- >>>> data.frame( CaseID >>>> , Primary.Viol.Type=factor( Primary.Viol.Type >>>> , levels=PViol.Type ) ) >>>> >>>> tmp <- table( PViol.Type.Per.Case.Original ) ans <- data.frame( >>>> CaseID=rownames( tmp ) >>>> , as.data.frame( ifelse( 0==tmp, NA, 1 ) ) >>>> ) >>>> >>>> >>>> On Wed, 17 Dec 2014, bcrombie wrote: >>>> >>>>> # I have a dataframe that contains 2 columns: >>>>> CaseID <- c('1015285', >>>>> '1005317', >>>>> '1012281', >>>>> '1015285', >>>>> '1015285', >>>>> '1007183', >>>>> '1008833', >>>>> '1015315', >>>>> '1015322', >>>>> '1015285') >>>>> >>>>> Primary.Viol.Type <- c('AS.Age', >>>>> 'HS.Hours', >>>>> 'HS.Hours', >>>>> 'HS.Hours', >>>>> 'RK.Records_CL', >>>>> 'OT.Overtime', >>>>> 'OT.Overtime', >>>>> 'OT.Overtime', >>>>> 'V.Poster_Other', >>>>> 'V.Poster_Other') >>>>> >>>>> PViol.Type.Per.Case.Original <- >>>>> data.frame(CaseID,Primary.Viol.Type) >>>>> >>>>> # CaseID?s can be repeated because there can be up to 14 >>>>> Primary.Viol.Type?s per CaseID. >>>>> >>>>> # I want to transform this dataframe into one that has 15 columns, >>>>> where the first column is CaseID, and the rest are the 14 primary >>>>> viol. types. The CaseID column will contain a list of the unique >>>>> CaseID?s (no >>>>> replicates) and >>>>> for each of their rows, there will be a ?1? under a column >>>>> corresponding to a primary violation type recorded for that CaseID. >>>>> So, technically, there could be zero to 14 ?1?s? in a CaseID?s row. >>>>> >>>>> # For example, the row for CaseID '1015285' above would have a ?1? >>>>> under ?AS.Age?, ?HS.Hours?, ?RK.Records_CL?, and ?V.Poster_Other?, >>>>> but have "NA" >>>>> under the rest of the columns. >>>>> >>>>> PViol.Type <- c("CaseID", >>>>> "BW.BackWages", >>>>> "LD.Liquid_Damages", >>>>> "MW.Minimum_Wage", >>>>> "OT.Overtime", >>>>> "RK.Records_FLSA", >>>>> "V.Poster_Other", >>>>> "AS.Age", >>>>> "BW.WHMIS_BackWages", >>>>> "HS.Hours", >>>>> "OA.HazOccupationAg", >>>>> "ON.HazOccupationNonAg", >>>>> "R3.Reg3AgeOccupation", >>>>> "RK.Records_CL", >>>>> "V.Other") >>>>> >>>>> PViol.Type.Columns <- t(data.frame(PViol.Type) >>>>> >>>>> # What is the best way to do this in R? >>>>> >>>>> >>>>> >>>>> >>>>> -- >>>>> View this message in context: >>>>> http://r.789695.n4.nabble.com/Make-2nd-col-of-2-col-df-into-header >>>>> -ro w-of-same-df-then-adjust-col1-data-display-tp4700878.html >>>>> >>>>> Sent from the R help mailing list archive at Nabble.com. >>>>> >>>>> ______________________________________________ >>>>> 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. >>>> >>>> >>>> >>>> --------------------------------------------------------------------------- >>>> Jeff Newmiller The ..... ..... Go >>>> Live... >>>> DCN:<jdnewmil at dcn.davis.ca.us> Basics: ##.#. ##.#. Live >>>> Go... >>>> Live: OO#.. Dead: OO#.. >>>> Playing >>>> Research Engineer (Solar/Batteries O.O#. #.O#. with >>>> /Software/Embedded Controllers) .OO#. .OO#. >>>> rocks...1k >>>> >>>> ______________________________________________ >>>> 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. >> >> >> ______________________________________________ >> 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. > > ______________________________________________ > 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.____________________________________________________________ Can't remember your password? Do you need a strong and secure password? Use Password manager! It stores your passwords & protects your account.