An embedded and charset-unspecified text was scrubbed... Name: not available URL: <https://stat.ethz.ch/pipermail/r-help/attachments/20121122/484bf7a7/attachment.pl>
Data frame manipulation
3 messages · Raoni Rodrigues, arun, jim holtman
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
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.
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 order
Local 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.