I have a long data set I would like to make wide and I'm curious if there is a way to do this all in one step using the reshape2 or tidyr packages in R.
The data frame df
looks like this:
id type transactions amount
20 income 20 100
20 expense 25 95
30 income 50 300
30 expense 45 250
I'd like to get to this:
id income_transactions expense_transactions income_amount expense_amount
20 20 25 100 95
30 50 45 300 250
I know I can get part of the way there with reshape2 via for example:
dcast(df, id ~ type, value.var="transactions")
But is there a way to reshape the entire df in one shot addressing both the "transactions" and "amount" variables at once? And ideally with new more appropriate column names?
In "reshape2", you can use
recast
(though in my experience, this isn't a widely known function).You can also use base R's
reshape
:Or, you can
melt
anddcast
, like this (here with "data.table"):In later versions of
dcast.data.table
from "data.table" (1.9.8) you will be able to do this directly. If I understand correctly, what @Arun is trying to implement would be doing the reshaping without first having tomelt
the data, which is what happens presently withrecast
, which is essentially a wrapper for amelt
+dcast
sequence of operations.And, for thoroughness, here's the
tidyr
approach:With data.table v1.9.6+, we can cast multiple
value.var
columns simultaneously (and also use multiple aggregation functions infun.aggregate
). Please see?dcast
for more and also the examples section.