thr3ads.net - R help - [R] Data frame manipulation [Nov 2012]

If this information is useful, please help other people find it:
Share via:

Raoni Rodrigues

2012-Nov-23 01:53 UTC

[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@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]]

arun

2012-Nov-23 02:21 UTC

head link

[R] Data frame manipulation

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.

jim holtman

2012-Nov-23 03:02 UTC

head link

[R] Data frame manipulation

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.

Apparently Analagous Threads

Search for more reasonably related threads

R help - Nov 2012 - Data frame manipulation

[R] Data frame manipulation

[R] Data frame manipulation

[R] Data frame manipulation

Apparently Analagous Threads