This question is about data wrangling. Say I had the following data set:

df = data.frame(id=c(5012, 5012, 1256, 1256, 1256, 102),
    feature=c(1, 2, 5, 7, 6, 0),
    result=c(0, 0, 1, 1, 1, 2))

How would I manipulate feature in a way that allows predictive modeling with things like random forest, neural nets, multinomial regression etc.? Should I convert feature into K, K=length(unique(df$feature)) number of dummy binary variables? Is this feasible if K is really large, like K>=2000, for n=5*K number of observations?

Say I am trying to predict Y (result) for observation ID, given X (feature).

  • $\begingroup$ If you're mostly asking about how to use R then this is more a question for Stack Overflow. Cross Validated is intended for statistical questions. $\endgroup$ – dsaxton Feb 28 '16 at 2:01
  • $\begingroup$ @dsaxton thanks. Not an R question, but a stats question. Not sure what to do to handle a variable that encodes multiple features per observation. $\endgroup$ – user2205916 Feb 28 '16 at 2:03
  • $\begingroup$ I think it depends on whether or not feature lies on a continuum or if the different values only represent categories. Also with such a simple data set I don't think you'd need to (or even should) use any high powered methods like the ones mentioned. $\endgroup$ – dsaxton Feb 28 '16 at 2:10
  • $\begingroup$ feature would be categories. just not sure how to transform it to make it work in a predictive model (e.g. multinomial logit) $\endgroup$ – user2205916 Feb 28 '16 at 2:15
  • 1
    $\begingroup$ You can treat it as a factor and then use any method that can do multiclass classification (e.g., random forests and neural networks, but again I don't think you should use either here). If the levels have an ordering to them you can specify this in the factor function. $\endgroup$ – dsaxton Feb 28 '16 at 2:24

Here are some popular data wrangling packages in R with examples.

Data used for the examples:

l <- list(a = 1:10, b = 11:20)
l2 <- list(c = 21:30, d = 31:40)
df1 = data.frame(CustId = c(1:3), City = c(rep("Denver", 3), rep("Raleigh", 3)))
df2 = data.frame(CustId = c(1, 3, 5), State = c(rep("Colorado", 2), rep("North Carolina", 1)))

[1] apply family of functions in R base

Applying a user-defined function:

enter code heremyFunc <- function(x) {5 * x[1] + x[2]}
enter code heremtcars$newCol <- apply(mtcars, 1, myFunc)

Adding a new column with the mean of each row:

enter code heremtcars$newCol <- apply(mtcars, 1, function(x) mean(x))

lapply: lapply returns a list of the same length as X, each element of which is the result of applying FUN to the corresponding element of X.

lapply(l, mean)

sapply: sapply is a user-friendly version of lapply by default returning a vector or matrix if appropriate.

sapply(l, mean)

vapply: vapply is similar to sapply, but has a pre-specified type of return value.

vapply(l, mean, c(row1=0))

mapply: mapply is a multivariate version of sapply.

mapply(sum, l$a, l2$c)

rapply: rapply is a recursive version of lapply.

rapply(l, log2, how = "list") #returns a list structure
rapply(l, log2)  #returns a vector

tapply: Apply a function to each cell of a ragged array, that is to each (non-empty) group of values given by a unique combination of the levels of certain factors.

#mean petal length by species
tapply(iris$Petal.Length, Species, mean)

[2] dplyr package:

select: select keeps only the variables you mentio.

select(mtcars, starts_with("dis"))

filter: filter find rows/cases where conditions are true.

filter(mtcars, disp>400)

group_by: group_by() takes an existing tbl and converts it into a grouped tbl where operations are performed "by group".

group_by(iris, Species)

merge: merge two data frames by common columns or row names, or do other versions of database join operations. The following is inner join which select rows where the left side data frame has a match in the right side data frame.

merge(df1, df2, by.x = "CustId", by.y = "CustId")

[3] sqldf package:

Using sqldf package, the inner join can be expressed in SQL-like statement as follows:

sqldf("SELECT CustId, City, State FROM df1 JOIN df2 USING(CustId)")

[4] tidyr package

gather: gather takes multiple columns and collapses into key-value pairs, duplicating all other columns as needed. You use gather() when you notice that you have columns that are not variables.


separate: Given either regular expression or a vector of character positions, separate() turns a single character column into multiple columns.

separate(mtcars, col="mpg", into=c("a","b"))

unite: Convenience function to paste together multiple columns into one.

unite_(mtcars, col="MpgCyl",  c("mpg","cyl"), sep="-")

[5] stats package:

aggregate: aggregate splits the data into subsets, computes summary statistics for each, and returns the result in a convenient form.

aggregate(iris[, 1:4], by=list(Species), FUN="mean")

[6] data.table package:

data.table inherits from data.frame. It offers fast and memory efficient: file reader and writer, aggregations, updates, equi, non-equi, rolling, range and interval joins, in a short and flexible syntax, for faster development. Since a data.table is a data.frame, it is compatible with R functions and packages that accept only data.frames.

There are three parts in the DT[i,j,by] command, and in the SQL terminology the i corresponds to WHERE, j to SELECT, and by to GROUP BY.

dt[am==1, mean(mpg), by=.(am, cyl)]

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.