How to use cast on a data frame?

Question

I have a data frame like the following:

  year income    group
1  2008  27907  Under25
2  2009  25522  Under25
3  2010  26777  Under25
4  2008  58809 Age25_34
5  2009  57239 Age25_34
6  2010  58558 Age25_34
7  2008  75677 Age35_44
8  2009  74900 Age35_44
9  2010  74136 Age35_44
10 2008  78537 Age45_54
11 2009  77460 Age45_54
12 2010  76266 Age45_54
13 2008  69009 Age55_64
14 2009  67586 Age55_64
15 2008  44402 Age65_74
16 2009  46147 Age65_74
17 2010  48595 Age65_74
18 2008  32747   Over75
19 2009  31272   Over75
20 2010  31638   Over75

> str(df)
'data.frame':   20 obs. of  3 variables:
 $ year  : int  2008 2009 2010 2008 2009 2010 2008 2009 2010 2008 ...
 $ income: int  27907 25522 26777 58809 57239 58558 75677 74900 74136 78537 ...
 $ group : Factor w/ 7 levels "Age25_34","Age35_44",..: 7 7 7 1 1 1 2 2 2 3 ...

I would like to use cast to find the mean by group. In addition, I would like to create a wide data.frame from this df where the first column is year and the following columns are incomes for the different groups. For Example

year     under25     Age25_34     Age35_44     Age45_54    ...
2008     27907        58809        75677        78537      ...
2009     25522        57239        74900        77460      ...
...

When I try cast I get the following error:

cast(df, income ~ group, mean) Using group as value column. Use the value argument to cast to override this choice Error in [.data.frame(data, , variables, drop = FALSE) : undefined columns selected

What am I doing wrong with the cast command?

How would I convert this to the wide format as shown in the example?

My R version information is listed below.

> unlist(R.Version())
   platform                            arch                              os 
"x86_64-pc-mingw32"                  "x86_64"                       "mingw32" 

 system                          status                           major 
"x86_64, mingw32"                   ""                             "2" 

minor                            year                           month 
"13.1"                          "2011"                            "07" 

day                         svn rev                        language 
"08"                         "56322"                             "R" 
version.string 
"R version 2.13.1 (2011-07-08)"

score 37 · Accepted Answer · answered Oct 12 '11 at 18:32

37

Try this with cast

cast(df, year ~ group, mean, value = 'income')

  year Age25_34 Age35_44 Age45_54 Age55_64 Age65_74 Over75 Under25
1 2008    58809    75677    78537    69009    44402  32747   27907
2 2009    57239    74900    77460    67586    46147  31272   25522
3 2010    58558    74136    76266      NaN    48595  31638   26777

answered Oct 12 '11 at 18:32

Ramnath

54,439
16
125
152

4

Thanks! I actually tried this but didn't put the quote marks around income. That was my problem! – pipertom Oct 12 '11 at 19:29

score 5 · Answer 2 · answered Oct 12 '11 at 17:20

aggregate(cbind(year, income)~group, data=df, FUN=mean)
     group   year   income
1 Age25_34 2009.0 58202.00
2 Age35_44 2009.0 74904.33
3 Age45_54 2009.0 77421.00
4 Age55_64 2008.5 68297.50
5 Age65_74 2009.0 46381.33
6   Over75 2009.0 31885.67
7  Under25 2009.0 26735.33

Tomas · Answer 3 · 2011-10-12T21:26:43.847

2

Why not to use tapply?

with(df, tapply(income, list(year, group), mean))

(Thanks Ramnath for good comments)

edited Oct 12 '11 at 21:26

answered Oct 12 '11 at 17:21

Tomas

57,621
49
238
373

doesn't give the right output as it computes means across years. – Ramnath Oct 12 '11 at 18:34
look at the sample output provided, as well as the description `...first column is year...` – Ramnath Oct 12 '11 at 18:46
perfect! i would use a `with` statement to avoid repeating `df` multiple times. – Ramnath Oct 12 '11 at 18:51
Yes, this works great for getting the means I want. The reason I was focused on cast is that I use melt quite a bit and thought this would be a good complementbut this works just fine. Thanks. – pipertom Oct 12 '11 at 19:28

score -1 · Answer 4 · edited Aug 08 '17 at 09:26

Create the data frame:

year<-c(2008,2009, 2010,2008,2009, 2010, 2008,2009, 2010,2008, 2009, 2010, 2008, 2009, 2008, 2009, 2010, 2008,2009,2010)
income<-c(27907,25522, 26777,58809, 57239, 58558, 75677,74900, 74136, 78537,77460,76266, 69009,67586, 44402, 46147,48595,32747, 31272,31638)
group<-c("Under25","Under25","Under25","Age25_34","Age25_34","Age25_34","Age35_44","Age35_44","Age35_44","Age45_54","Age45_54","Age45_54","Age55_64","Age55_64","Age65_74","Age65_74","Age65_74","Over75","Over75","Over75")

demographic_data<-data.frame(year, income,group)
demographic_data

str(demographic_data)

Melt the demographic data by year:

library(reshape)
melted_demographic_data<-melt(demographic_data,id=c("group","year")) 
melted_demographic_data

groupmeans<-cast(melted_demographic_data,group~variable, mean)
groupmeans
yearmeans<-cast(melted_demographic_data,year~variable, mean)
yearmeans

How to use cast on a data frame?

4 Answers4

Linked