Hello, I have a table that was constructed in a wrong way (dput data on bottom - wrong data-frame): Local Mês Dia Colonia X6h X7h X8h X9h X10h X11h X12h X13h X14h X15h X16h X17h 1 Conceição Junho 1 3 2.16137 2.20412 2.08991 1.72428 1.69897 1.62325 1.44716 1.51851 1.43136 1.47712 1.51851 1.04139 2 Conceição Junho 2 3 2.46538 2.13672 2.06819 1.97772 2.00000 1.80618 1.64345 1.20412 1.62325 1.36173 1.69020 1.57978 3 Conceição Junho 3 3 2.53275 2.52504 2.49276 2.32222 2.12710 2.26007 2.24551 1.95424 2.09342 1.04139 1.53148 1.17609 4 Conceição Junho 1 4 1.65321 2.16435 1.91381 1.75587 1.74036 1.17609 1.66276 1.51851 1.39794 1.04139 1.11394 1.04139 5 Conceição Junho 2 4 2.30320 1.71600 2.02531 2.05690 1.86332 1.66276 1.17609 1.04139 1.30103 1.27875 1.32222 1.32222 6 Conceição Junho 3 4 2.71012 2.30320 2.53403 1.80618 2.24551 2.20683 2.02531 1.07918 1.36173 1.39794 1.11394 1.93450 7 Conceição Junho 1 5 2.21748 1.99564 2.26007 2.28103 2.10380 1.41497 0.47712 1.07918 0.90309 1.04139 1.49136 1.23045 8 Conceição Junho 2 5 2.10721 2.16435 2.05308 2.38561 2.14613 1.61278 1.27875 0.47712 1.61278 1.00000 1.44716 1.07918 9 Conceição Junho 3 5 1.62325 1.93450 2.33041 2.24797 2.29885 2.48001 2.29003 1.43136 1.49136 1.17609 1.41497 1.14613 10 Conceição julho 1 3 2.20952 2.01284 1.79239 1.59106 1.62325 1.51851 1.41497 1.38021 1.66276 1.46240 1.53148 1.66276 I have to create a new column (hour) and transpose just the last 12 columns, and first four columns have to be copied 12 time, like this (dput data on bottom - correct data-frame): Local Mês Dia Colonia Hora N 1 Conceição Junho 1 3 6h 2.161370 2 Conceição Junho 1 3 7h 2.204120 3 Conceição Junho 1 3 8h 2.089910 4 Conceição Junho 1 3 9h 1.724280 5 Conceição Junho 1 3 10h 1.698970 6 Conceição Junho 1 3 11h 1.623250 7 Conceição Junho 1 3 12h 1.447160 8 Conceição Junho 1 3 13h 1.518510 9 Conceição Junho 1 3 14h 1.431360 10 Conceição Junho 1 3 15h 1.477120 11 Conceição Junho 1 3 16h 1.518510 12 Conceição Junho 1 3 17h 1.041390 13 Conceição Junho 2 3 6h 2.465383 Some one could give me some ideas? I don't even know how to start... Thanks in advanced, -- Raoni Rosa Rodrigues Research Associate of Fish Transposition Center CTPeixes Universidade Federal de Minas Gerais - UFMG Brasil rodrigues.raoni@gmail.com wrong data frame: structure(list(Local = structure(c(1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L), .Label = "Conceição", class = "factor"), Mês = structure(c(2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 1L), .Label = c("julho", "Junho" ), class = "factor"), Dia = c(1L, 2L, 3L, 1L, 2L, 3L, 1L, 2L, 3L, 1L), Colonia = c(3L, 3L, 3L, 4L, 4L, 4L, 5L, 5L, 5L, 3L), X6h = c(2.16137, 2.46538, 2.53275, 1.65321, 2.3032, 2.71012, 2.21748, 2.10721, 1.62325, 2.20952), X7h = c(2.20412, 2.13672, 2.52504, 2.16435, 1.716, 2.3032, 1.99564, 2.16435, 1.9345, 2.01284), X8h = c(2.08991, 2.06819, 2.49276, 1.91381, 2.02531, 2.53403, 2.26007, 2.05308, 2.33041, 1.79239), X9h = c(1.72428, 1.97772, 2.32222, 1.75587, 2.0569, 1.80618, 2.28103, 2.38561, 2.24797, 1.59106), X10h = c(1.69897, 2, 2.1271, 1.74036, 1.86332, 2.24551, 2.1038, 2.14613, 2.29885, 1.62325), X11h = c(1.62325, 1.80618, 2.26007, 1.17609, 1.66276, 2.20683, 1.41497, 1.61278, 2.48001, 1.51851), X12h = c(1.44716, 1.64345, 2.24551, 1.66276, 1.17609, 2.02531, 0.47712, 1.27875, 2.29003, 1.41497), X13h c(1.51851, 1.20412, 1.95424, 1.51851, 1.04139, 1.07918, 1.07918, 0.47712, 1.43136, 1.38021), X14h = c(1.43136, 1.62325, 2.09342, 1.39794, 1.30103, 1.36173, 0.90309, 1.61278, 1.49136, 1.66276), X15h c(1.47712, 1.36173, 1.04139, 1.04139, 1.27875, 1.39794, 1.04139, 1, 1.17609, 1.4624), X16h = c(1.51851, 1.6902, 1.53148, 1.11394, 1.32222, 1.11394, 1.49136, 1.44716, 1.41497, 1.53148), X17h c(1.04139, 1.57978, 1.17609, 1.04139, 1.32222, 1.9345, 1.23045, 1.07918, 1.14613, 1.66276)), .Names = c("Local", "Mês", "Dia", "Colonia", "X6h", "X7h", "X8h", "X9h", "X10h", "X11h", "X12h", "X13h", "X14h", "X15h", "X16h", "X17h"), row.names = c(NA, 10L), class = "data.frame") Correct data frame: structure(list(Local = structure(c(1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L), .Label = "Conceição", class = "factor"), Mês = structure(c(2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L), .Label = c("julho", "Junho" ), class = "factor"), Dia = c(1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L), Colonia = c(3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L), Hora = structure(1:10, .Label = c("6h", "7h", "8h", "9h", "10h", "11h", "12h", "13h", "14h", "15h", "16h", "17h"), class "factor"), N = c(2.16137, 2.20412, 2.08991, 1.72428, 1.69897, 1.62325, 1.44716, 1.51851, 1.43136, 1.47712)), .Names = c("Local", "Mês", "Dia", "Colonia", "Hora", "N"), row.names = c(NA, 10L), class "data.frame") [[alternative HTML version deleted]]
Hi, May be this helps: library(reshape) dat1 # data that needs to be converted res<-melt(dat1,id=c("Local","M?s","Dia","Colonia")) ?names(res)[5:6]<-c("Hora","N") ?res1<-res[order(res$Dia),] ?row.names(res1)<-1:nrow(res1) res1$Hora<-gsub("[X]","",res1$Hora) ?head(res1) #????? Local?? M?s Dia Colonia Hora?????? N #1 Concei??o Junho?? 1?????? 3?? 6h 2.16137 #2 Concei??o Junho?? 1?????? 4?? 6h 1.65321 #3 Concei??o Junho?? 1?????? 5?? 6h 2.21748 #4 Concei??o julho?? 1?????? 3?? 6h 2.20952 #5 Concei??o Junho?? 1?????? 3?? 7h 2.20412 #6 Concei??o Junho?? 1?????? 4?? 7h 2.16435 A.K. To: r-help at r-project.org Cc: Sent: Thursday, November 22, 2012 8:53 PM Subject: [R] Data frame manipulation Hello, I have a table that was constructed in a wrong way (dput data on bottom - wrong data-frame): ? ? Local? M?s Dia Colonia? ? X6h? ? X7h? ? X8h? ? X9h? ? X10h? ? X11h ? X12h? ? X13h? ? X14h? ? X15h? ? X16h? ? X17h 1? Concei??o Junho? 1? ? ? 3 2.16137 2.20412 2.08991 1.72428 1.69897 1.62325 1.44716 1.51851 1.43136 1.47712 1.51851 1.04139 2? Concei??o Junho? 2? ? ? 3 2.46538 2.13672 2.06819 1.97772 2.00000 1.80618 1.64345 1.20412 1.62325 1.36173 1.69020 1.57978 3? Concei??o Junho? 3? ? ? 3 2.53275 2.52504 2.49276 2.32222 2.12710 2.26007 2.24551 1.95424 2.09342 1.04139 1.53148 1.17609 4? Concei??o Junho? 1? ? ? 4 1.65321 2.16435 1.91381 1.75587 1.74036 1.17609 1.66276 1.51851 1.39794 1.04139 1.11394 1.04139 5? Concei??o Junho? 2? ? ? 4 2.30320 1.71600 2.02531 2.05690 1.86332 1.66276 1.17609 1.04139 1.30103 1.27875 1.32222 1.32222 6? Concei??o Junho? 3? ? ? 4 2.71012 2.30320 2.53403 1.80618 2.24551 2.20683 2.02531 1.07918 1.36173 1.39794 1.11394 1.93450 7? Concei??o Junho? 1? ? ? 5 2.21748 1.99564 2.26007 2.28103 2.10380 1.41497 0.47712 1.07918 0.90309 1.04139 1.49136 1.23045 8? Concei??o Junho? 2? ? ? 5 2.10721 2.16435 2.05308 2.38561 2.14613 1.61278 1.27875 0.47712 1.61278 1.00000 1.44716 1.07918 9? Concei??o Junho? 3? ? ? 5 1.62325 1.93450 2.33041 2.24797 2.29885 2.48001 2.29003 1.43136 1.49136 1.17609 1.41497 1.14613 10 Concei??o julho? 1? ? ? 3 2.20952 2.01284 1.79239 1.59106 1.62325 1.51851 1.41497 1.38021 1.66276 1.46240 1.53148 1.66276 I have to create a new column (hour) and transpose just the last 12 columns, and first four columns have to be copied 12 time, like this (dput data on bottom - correct data-frame): ? ? Local? M?s Dia Colonia Hora? ? ? ? N 1? Concei??o Junho? 1? ? ? 3? 6h 2.161370 2? Concei??o Junho? 1? ? ? 3? 7h 2.204120 3? Concei??o Junho? 1? ? ? 3? 8h 2.089910 4? Concei??o Junho? 1? ? ? 3? 9h 1.724280 5? Concei??o Junho? 1? ? ? 3? 10h 1.698970 6? Concei??o Junho? 1? ? ? 3? 11h 1.623250 7? Concei??o Junho? 1? ? ? 3? 12h 1.447160 8? Concei??o Junho? 1? ? ? 3? 13h 1.518510 9? Concei??o Junho? 1? ? ? 3? 14h 1.431360 10 Concei??o Junho? 1? ? ? 3? 15h 1.477120 11 Concei??o Junho? 1? ? ? 3? 16h 1.518510 12 Concei??o Junho? 1? ? ? 3? 17h 1.041390 13 Concei??o Junho? 2? ? ? 3? 6h 2.465383 Some one could give me some ideas? I don't even know how to start... Thanks in advanced, -- Raoni Rosa Rodrigues Research Associate of Fish Transposition Center CTPeixes Universidade Federal de Minas Gerais - UFMG Brasil rodrigues.raoni at gmail.com wrong data frame: structure(list(Local = structure(c(1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L), .Label = "Concei??o", class = "factor"), M?s = structure(c(2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 1L), .Label = c("julho", "Junho" ), class = "factor"), Dia = c(1L, 2L, 3L, 1L, 2L, 3L, 1L, 2L, 3L, 1L), Colonia = c(3L, 3L, 3L, 4L, 4L, 4L, 5L, 5L, 5L, 3L), ? ? X6h = c(2.16137, 2.46538, 2.53275, 1.65321, 2.3032, 2.71012, ? ? 2.21748, 2.10721, 1.62325, 2.20952), X7h = c(2.20412, 2.13672, ? ? 2.52504, 2.16435, 1.716, 2.3032, 1.99564, 2.16435, 1.9345, ? ? 2.01284), X8h = c(2.08991, 2.06819, 2.49276, 1.91381, 2.02531, ? ? 2.53403, 2.26007, 2.05308, 2.33041, 1.79239), X9h = c(1.72428, ? ? 1.97772, 2.32222, 1.75587, 2.0569, 1.80618, 2.28103, 2.38561, ? ? 2.24797, 1.59106), X10h = c(1.69897, 2, 2.1271, 1.74036, ? ? 1.86332, 2.24551, 2.1038, 2.14613, 2.29885, 1.62325), X11h = c(1.62325, ? ? 1.80618, 2.26007, 1.17609, 1.66276, 2.20683, 1.41497, 1.61278, ? ? 2.48001, 1.51851), X12h = c(1.44716, 1.64345, 2.24551, 1.66276, ? ? 1.17609, 2.02531, 0.47712, 1.27875, 2.29003, 1.41497), X13h c(1.51851, ? ? 1.20412, 1.95424, 1.51851, 1.04139, 1.07918, 1.07918, 0.47712, ? ? 1.43136, 1.38021), X14h = c(1.43136, 1.62325, 2.09342, 1.39794, ? ? 1.30103, 1.36173, 0.90309, 1.61278, 1.49136, 1.66276), X15h c(1.47712, ? ? 1.36173, 1.04139, 1.04139, 1.27875, 1.39794, 1.04139, 1, ? ? 1.17609, 1.4624), X16h = c(1.51851, 1.6902, 1.53148, 1.11394, ? ? 1.32222, 1.11394, 1.49136, 1.44716, 1.41497, 1.53148), X17h c(1.04139, ? ? 1.57978, 1.17609, 1.04139, 1.32222, 1.9345, 1.23045, 1.07918, ? ? 1.14613, 1.66276)), .Names = c("Local", "M?s", "Dia", "Colonia", "X6h", "X7h", "X8h", "X9h", "X10h", "X11h", "X12h", "X13h", "X14h", "X15h", "X16h", "X17h"), row.names = c(NA, 10L), class = "data.frame") Correct data frame: structure(list(Local = structure(c(1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L), .Label = "Concei??o", class = "factor"), M?s = structure(c(2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L), .Label = c("julho", "Junho" ), class = "factor"), Dia = c(1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L), Colonia = c(3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L), ? ? Hora = structure(1:10, .Label = c("6h", "7h", "8h", "9h", ? ? "10h", "11h", "12h", "13h", "14h", "15h", "16h", "17h"), class "factor"), ? ? N = c(2.16137, 2.20412, 2.08991, 1.72428, 1.69897, 1.62325, ? ? 1.44716, 1.51851, 1.43136, 1.47712)), .Names = c("Local", "M?s", "Dia", "Colonia", "Hora", "N"), row.names = c(NA, 10L), class "data.frame") ??? [[alternative HTML version deleted]] ______________________________________________ R-help at r-project.org mailing list stat.ethz.ch/mailman/listinfo/r-help PLEASE do read the posting guide R-project.org/posting-guide.html and provide commented, minimal, self-contained, reproducible code.
The 'reshape2' package is your friend:> require(reshape2) > x <- melt(wrong, id = c("Local", "M?s", "Dia", "Colonia"), variable.name = "Hora") > # remove "X" from Hora > x$Hora <- as.character(substring(x$Hora, 2)) > head(x) # not in the right orderLocal M?s Dia Colonia Hora value 1 Concei??o Junho 1 3 6h 2.16137 2 Concei??o Junho 2 3 6h 2.46538 3 Concei??o Junho 3 3 6h 2.53275 4 Concei??o Junho 1 4 6h 1.65321 5 Concei??o Junho 2 4 6h 2.30320 6 Concei??o Junho 3 4 6h 2.71012> # sort, but first add blank on Hora for less that 10h for sorting > x$Hora <- ifelse(nchar(x$Hora) == 2, paste0(" ", x$Hora), x$Hora) > x <- x[order(x$Local, x$M?s, x$Dia, x$Colonia, x$Hora), ] > > head(x,20)Local M?s Dia Colonia Hora value 10 Concei??o julho 1 3 6h 2.20952 20 Concei??o julho 1 3 7h 2.01284 30 Concei??o julho 1 3 8h 1.79239 40 Concei??o julho 1 3 9h 1.59106 50 Concei??o julho 1 3 10h 1.62325 60 Concei??o julho 1 3 11h 1.51851 70 Concei??o julho 1 3 12h 1.41497 80 Concei??o julho 1 3 13h 1.38021 90 Concei??o julho 1 3 14h 1.66276 100 Concei??o julho 1 3 15h 1.46240 110 Concei??o julho 1 3 16h 1.53148 120 Concei??o julho 1 3 17h 1.66276 1 Concei??o Junho 1 3 6h 2.16137 11 Concei??o Junho 1 3 7h 2.20412 21 Concei??o Junho 1 3 8h 2.08991 31 Concei??o Junho 1 3 9h 1.72428 41 Concei??o Junho 1 3 10h 1.69897 51 Concei??o Junho 1 3 11h 1.62325 61 Concei??o Junho 1 3 12h 1.44716 71 Concei??o Junho 1 3 13h 1.51851 On Thu, Nov 22, 2012 at 8:53 PM, Raoni Rodrigues <caciquesamurai at gmail.com> wrote:> structure(list(Local = structure(c(1L, 1L, 1L, 1L, 1L, 1L, 1L, > 1L, 1L, 1L), .Label = "Concei??o", class = "factor"), M?s = structure(c(2L, > 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L), .Label = c("julho", "Junho" > ), class = "factor"), Dia = c(1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, > 1L, 1L), Colonia = c(3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L), > Hora = structure(1:10, .Label = c("6h", "7h", "8h", "9h", > "10h", "11h", "12h", "13h", "14h", "15h", "16h", "17h"), class > "factor"), > N = c(2.16137, 2.20412, 2.08991, 1.72428, 1.69897, 1.62325, > 1.44716, 1.51851, 1.43136, 1.47712)), .Names = c("Local", > "M?s", "Dia", "Colonia", "Hora", "N"), row.names = c(NA, 10L), class > "data.frame")-- Jim Holtman Data Munger Guru What is the problem that you are trying to solve? Tell me what you want to do, not how you want to do it.