Geek Answers Handbook

Create some new derived variables from existing variables in data.frame

In R, I have a data.frame that has several variables that have been measured monthly for several years. I would like to get the monthly average (using all years) for each variable. Ideally, these new variables will be merged into a new data.frame (wrap identifier), below I just add a new variable to data.frame. The only way I know how to do this at the moment (below) seems rather laborious, and I was hoping that in R there might be a more reasonable way to do this, which would not require printing a variable every month, as I did below.

# Example data.frame with only two years, two month, and two variables
# In the real data set there are always 12 months per year 
# and there are at least four variables
df<- structure(list(ID = 1:4, ABC.M1Y2001 = c(10, 12.3, 45, 89), ABC.M2Y2001 = c(11.1, 
          34, 67.7, -15.6), ABC.M1Y2002 = c(-11.1, 9, 34, 56.5), ABC.M2Y2002 = c(12L,
          13L, 11L, 21L), DEF.M1Y2001 = c(14L, 14L, 14L, 16L), DEF.M2Y2001 = c(15L,
          15L, 15L, 12L), DEF.M1Y2002 = c(5, 12, 23.5, 34), DEF.M2Y2002 = c(6L,
          34L, 61L, 56L)), .Names = c("ID", "ABC.M1Y2001", "ABC.M2Y2001","ABC.M1Y2002", 
          "ABC.M2Y2002", "DEF.M1Y2001", "DEF.M2Y2001", "DEF.M1Y2002", 
          "DEF.M2Y2002"), class = "data.frame", row.names = c(NA, -4L))


# list variable to average for ABC Month 1 across years
ABC.M1.names <- c("ABC.M1Y2001", "ABC.M1Y2002") 
df <- transform(df,  ABC.M1 = rowMeans(df[,ABC.M1.names], na.rm = TRUE))

# list variable to average for ABC Month 2 across years
ABC.M2.names <- c("ABC.M2Y2001", "ABC.M2Y2002") 
df <- transform(df,  ABC.M2 = rowMeans(df[,ABC.M2.names], na.rm = TRUE))

# and so forth for ABC
# ...

# list variables to average for DEF Month 1 across years
DEF.M1.names <- c("DEF.M1Y2001", "DEF.M1Y2002") 
df <- transform(df,  DEF.M1 = rowMeans(df[,DEF.M1.names], na.rm = TRUE))

# and so forth for DEF
# ...
+4
source share
4

, reshape2, , , . .

#  Load required package
require(reshape2)

#  Melt your wide data into long format
mdf <- melt(df , id = "ID" )

#  Extract relevant variable names from the variable colum
mdf$Month <- gsub( "^.*\\.(M[0-9]{1,2}).*$" , "\\1" , mdf$variable )
mdf$Var <- gsub( "^(.*)\\..*" , "\\1" , mdf$variable )   

#  Aggregate by month and variable
dcast( mdf , Var ~ Month , mean  )
#  Var      M1     M2
#1 ABC 30.5875 19.275
#2 DEF 16.5625 26.750

, ID...

dcast( mdf , ID ~ Var + Month , mean  )
#  ID ABC_M1 ABC_M2 DEF_M1 DEF_M2
#1  1  -0.55  11.55   9.50   10.5
#2  2  10.65  23.50  13.00   24.5
#3  3  39.50  39.35  18.75   38.0
#4  4  72.75   2.70  25.00   34.0
+1

, data.table v1.8.11 ( melt cast, data.table ):

require(data.table)
require(reshape2) # melt/cast builds on S3 generic from reshape2
dt <- data.table(df) # where df is your data.frame
dcast.data.table(melt(dt, id="ID")[, sum(value)/.N, list(ID, 
        gsub("Y.*$", "", variable))], ID ~ gsub)
   ID ABC.M1 ABC.M2 DEF.M1 DEF.M2
1:  1  -0.55  11.55   9.50   10.5
2:  2  10.65  23.50  13.00   24.5
3:  3  39.50  39.35  18.75   38.0
4:  4  72.75   2.70  25.00   34.0

cbind .

, sum , mean S3 . sum(.)/length(.) ( , mean ). .N - data.table, .

+2

R.

mean.names <- split(names(df)[-1], gsub('Y[0-9]{4}$', '', names(df)[-1]))
means <- lapply(mean.names, function(x) rowMeans(df[, x], na.rm = TRUE))
data.frame(df, means)

data.frame :

  ABC.M1 ABC.M2 DEF.M1 DEF.M2
1  -0.55  11.55   9.50   10.5
2  10.65  23.50  13.00   24.5
3  39.50  39.35  18.75   38.0
4  72.75   2.70  25.00   34.0
+1

Reshape {splitstackshape}, plyr- data.table base R .

   library(splitstackshape) # Reshape
    library(plyr) # ddply
    kk<-Reshape(df,id.vars="ID",var.stubs=c("ABC.M1","ABC.M2","DEF.M1","DEF.M2"),sep="")
> kk
  ID AE DB time ABC.M1 ABC.M2 DEF.M1 DEF.M2
1  1 NA NA    1   10.0   11.1   14.0     15
2  2 NA NA    1   12.3   34.0   14.0     15
3  3 NA NA    1   45.0   67.7   14.0     15
4  4 NA NA    1   89.0  -15.6   16.0     12
5  1 NA NA    2  -11.1   12.0    5.0      6
6  2 NA NA    2    9.0   13.0   12.0     34
7  3 NA NA    2   34.0   11.0   23.5     61
8  4 NA NA    2   56.5   21.0   34.0     56

ddply(kk[,c(1,5:8)],.(ID),colwise(mean))
  ID ABC.M1 ABC.M2 DEF.M1 DEF.M2
1  1  -0.55  11.55   9.50   10.5
2  2  10.65  23.50  13.00   24.5
3  3  39.50  39.35  18.75   38.0
4  4  72.75   2.70  25.00   34.0
+1

More articles:


All Articles