Dear R users, Suppose I have the following data.frame: myID myType myNum1 myNum2 myNum3 a Single 10 11 12 b Single 15 25 35 c Double 22 33 44 d Double 4 6 8 and I want to have new records: myID myType myNum1 myNum2 myNum3 e Single 12.5 18 23.5 f Double 13 19.5 28 where record e got its myNum1-3 as the average from record a and b, and record f got its myNum1-3 as the average from record c and d. and the final data.frame should be like the following: myID myType myNum1 myNum2 myNum3 a Single 10 11 12 b Single 15 25 35 e Single 12.5 18 43.5 c Double 22 33 44 d Double 4 6 8 f Double 13 19.5 28 Any idea is appreciated. Thanks beforehand. Ferry [[alternative HTML version deleted]]
jim holtman
2008-Dec-05 22:05 UTC
[R] adding rows as arithmatic calculation on original rows
This should get you close:> x <- read.table(textConnection("myID myType myNum1 myNum2 myNum3+ a Single 10 11 12 + b Single 15 25 35 + c Double 22 33 44 + d Double 4 6 8"), header=TRUE)> closeAllConnections() > y <- lapply(split(x, x$myType), function(.type){+ .means <- colMeans(.type[,3:5]) + # create the new line for the data frame + .df <- data.frame(myID='', myType=.type$myType[1], myNum1=.means[1], + myNum2=.means[2], myNum3=.means[3]) + rbind(.type, .df) # append the line to the original dataframe + })> do.call(rbind, y) # you can add the names your wantmyID myType myNum1 myNum2 myNum3 Double.3 c Double 22.0 33.0 44.0 Double.4 d Double 4.0 6.0 8.0 Double.myNum1 Double 13.0 19.5 26.0 Single.1 a Single 10.0 11.0 12.0 Single.2 b Single 15.0 25.0 35.0 Single.myNum1 Single 12.5 18.0 23.5 On Fri, Dec 5, 2008 at 3:21 PM, Ferry <fmi.mlist at gmail.com> wrote:> Dear R users, > > Suppose I have the following data.frame: > > myID myType myNum1 myNum2 myNum3 > a Single 10 11 12 > b Single 15 25 35 > c Double 22 33 44 > d Double 4 6 8 > > and I want to have new records: > > myID myType myNum1 myNum2 myNum3 > e Single 12.5 18 23.5 > f Double 13 19.5 28 > > where record e got its myNum1-3 as the average from record a and b, and > record f got its myNum1-3 as the average from record c and d. > > and the final data.frame should be like the following: > > myID myType myNum1 myNum2 myNum3 > a Single 10 11 12 > b Single 15 25 35 > e Single 12.5 18 43.5 > c Double 22 33 44 > d Double 4 6 8 > f Double 13 19.5 28 > > Any idea is appreciated. Thanks beforehand. > > Ferry > > [[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 Cincinnati, OH +1 513 646 9390 What is the problem that you are trying to solve?
Gabor Grothendieck
2008-Dec-05 22:50 UTC
[R] adding rows as arithmatic calculation on original rows
Here is a solution using sqldf
library(sqldf)
DF2 <-
structure(list(myID = structure(1:4, .Label = c("a", "b",
"c",
"d"), class = "factor"), myType = structure(c(2L, 2L, 1L,
1L), .Label
= c("Double",
"Single"), class = "factor"), myNum1 = c(10, 15, 22, 4),
myNum2 = c(11,
25, 33, 6), myNum3 = c(12, 35, 44, 8)), .Names = c("myID",
"myType",
"myNum1", "myNum2", "myNum3"), row.names = c(NA,
-4L), class = "data.frame")
sqldf("select 1 TotalLevel, '+' myID, myType, avg(myNum1) myNum1,
avg(myNum2) myNum2, avg(myNum3) myNum3
from DF2
group by myType
union
select 0 TotalLevel, *
from DF2
order by myType, TotalLevel, myID",
method = "raw")[-1]
The output is (display in fixed font):
myID myType myNum1 myNum2 myNum3
1 c Double 22.0 33.0 44.0
2 d Double 4.0 6.0 8.0
3 + Double 13.0 19.5 26.0
4 a Single 10.0 11.0 12.0
5 b Single 15.0 25.0 35.0
6 + Single 12.5 18.0 23.5
On Fri, Dec 5, 2008 at 3:21 PM, Ferry <fmi.mlist at gmail.com>
wrote:> Dear R users,
>
> Suppose I have the following data.frame:
>
> myID myType myNum1 myNum2 myNum3
> a Single 10 11 12
> b Single 15 25 35
> c Double 22 33 44
> d Double 4 6 8
>
> and I want to have new records:
>
> myID myType myNum1 myNum2 myNum3
> e Single 12.5 18 23.5
> f Double 13 19.5 28
>
> where record e got its myNum1-3 as the average from record a and b, and
> record f got its myNum1-3 as the average from record c and d.
>
> and the final data.frame should be like the following:
>
> myID myType myNum1 myNum2 myNum3
> a Single 10 11 12
> b Single 15 25 35
> e Single 12.5 18 43.5
> c Double 22 33 44
> d Double 4 6 8
> f Double 13 19.5 28
>
> Any idea is appreciated. Thanks beforehand.
>
> Ferry
>
> [[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.
>