R help - Nov 2007 - Help Required in using cast (reshape package) function

Hello everyone,

I am new to R. I have data in the form of excel pivot table format and I
want to cast it into a format which can make it compatible with
computation. 

Since I already have the package in pivot format; I avoid melt function
and use the cast directly. 
I inspect the 

dataread <- read.csv(".....", header=TRUE)

Data in the format 
Id		Region        	Country	Industry
Period	variable		value
"Aaa11  xx"  "Latin America" 	"Mexico"	"food
& beverages"
"2002:FY"   "account payable" 10000
"Aaa11  xx"  "Latin America" 	"Mexico"	"food
& beverages"
"2002:FY"   "account receivable" 50000
"Aaa11  xx"  "Latin America" 	"Mexico"	"food
& beverages"
"2002:FY"   "XXXX" 	70000
"Aaa11  xx"  "Latin America" 	"Mexico"	"food
& beverages"
"2002:FY"   "YYYY" "NA"
"Aaa11  xx"  "Latin America" 	"Mexico"	"food
& beverages"
"2002:FY"   "ZZZZ" 
"Aaa11  xx"  "Latin America" 	"Mexico"	"food
& beverages"
"2002:FY"   "AAAA" 10000
"Aaa11  xx"  "Latin America" 	"Mexico"	"food
& beverages"
"2002:FY"   "ccccccc" 10000

# I want to arrange data in the format
Id		Region        	Country	Industry
Period	"account payable"	"account receivable"	XXXX"
"YYYYY"
"ZZZZ"	"AAAA"	"CCCCC"
"Aaa11  xx"  "Latin America" 	"Mexico"	"food
& beverages"
"2002:FY"    10000	50000		70000	 "NA"		10000
10000		

# casting the data
cast <- cast(dataread, ID + Period ~ variable)

When I do that the data is casted as a pivot with a warning "Aggregation
requires fun.aggregate: length used as default", and the casted data
gives me the count of variables (as suggested by the warning )

Id		Region        	Country	Industry
Period	"account payable"	"account receivable"	XXXX" 
"YYYYY"
"ZZZZ"	"AAAA"	"CCCCC"
"Aaa11  xx"  "Latin America" 	"Mexico"	"food
& beverages"
"2002:FY"    1	1	1	 NA	NA		10000
10000		

How do I use the fun.aggregate feature of the cast function to obtain
the desired result?

Moreover, I want to subdivide the casted data into subsets based on ids.
How do I achieve that?


Thanks in advance,

Punit Anand

Hello everyone, 

Since the fields in variables column are unique with respect to ID and
fiscal year; any function like 
sum,min,max,mean etc will lead to the desired result

Therefore cast(dataread, ID + Period ~ variable,sum)
Will lead to the desired result in my case; 

Thanks,
Punit



-----Original Message-----
From: Punit Anand 
Sent: Wednesday, November 21, 2007 11:21 AM
To: 'r-help at r-project.org'
Subject: Help Required in using cast (reshape package) function

Hello everyone,

I am new to R. I have data in the form of excel pivot table format and I
want to cast it into a format which can make it compatible with
computation. 

Since I already have the package in pivot format; I avoid melt function
and use the cast directly. 
I inspect the 

dataread <- read.csv(".....", header=TRUE)

Data in the format 
Id		Region        	Country	Industry
Period	variable		value
"Aaa11  xx"  "Latin America" 	"Mexico"	"food
& beverages"
"2002:FY"   "account payable" 10000
"Aaa11  xx"  "Latin America" 	"Mexico"	"food
& beverages"
"2002:FY"   "account receivable" 50000
"Aaa11  xx"  "Latin America" 	"Mexico"	"food
& beverages"
"2002:FY"   "XXXX" 	70000
"Aaa11  xx"  "Latin America" 	"Mexico"	"food
& beverages"
"2002:FY"   "YYYY" "NA"
"Aaa11  xx"  "Latin America" 	"Mexico"	"food
& beverages"
"2002:FY"   "ZZZZ" 
"Aaa11  xx"  "Latin America" 	"Mexico"	"food
& beverages"
"2002:FY"   "AAAA" 10000
"Aaa11  xx"  "Latin America" 	"Mexico"	"food
& beverages"
"2002:FY"   "ccccccc" 10000

# I want to arrange data in the format
Id		Region        	Country	Industry
Period	"account payable"	"account receivable"	XXXX"
"YYYYY"
"ZZZZ"	"AAAA"	"CCCCC"
"Aaa11  xx"  "Latin America" 	"Mexico"	"food
& beverages"
"2002:FY"    10000	50000		70000	 "NA"		10000
10000		

# casting the data
cast <- cast(dataread, ID + Period ~ variable)

When I do that the data is casted as a pivot with a warning "Aggregation
requires fun.aggregate: length used as default", and the casted data
gives me the count of variables (as suggested by the warning )

Id		Region        	Country	Industry
Period	"account payable"	"account receivable"	XXXX" 
"YYYYY"
"ZZZZ"	"AAAA"	"CCCCC"
"Aaa11  xx"  "Latin America" 	"Mexico"	"food
& beverages"
"2002:FY"    1	1	1	 NA	NA		10000
10000		

How do I use the fun.aggregate feature of the cast function to obtain
the desired result?

Moreover, I want to subdivide the casted data into subsets based on ids.
How do I achieve that?


Thanks in advance,

Punit Anand