0

I am trying to create grouped tables like what I could make with an excel pivot table. So far, I've used ddply to sum my data by groups, but I can't figure out how to create subtotals other than making them each individually and piecing them into the table.

Here is a sample of my data after I grouped it with ddply:

Species<-c("Bigeye","Bigeye","Bigeye","Bigeye","Yellowfin","Yellowfin",
"Yellowfin")
Country<-c("Japan", "Canada", "HongKong", "SouthKorea", "Japan", "Canada", 
"Malaysia")
Pounds<-c(445274,152467,9768,2406,216323,19689,108)
Dollars<-c(4298063, 2420140, 58596, 14432,3682677,212323, 5309)
UnitValue<-c(9.65, 15.87, 6, 6, 17.02,10.78, 49.16)
VolumeShare <-c(73, 25, 1.6, .39, 91.62, 8.34, .05)
ValueShare <-c(63.29, 35.64, .86, .21, 94.42, 5.44, .14)
fish<-data.frame(Species, Country, Pounds, Dollars, UnitValue, VolumeShare, ValueShare)

It creates a table like this:

Species Country Pounds Dollars UnitValue VolumeShare ValueShare Bigeye Japan 445274 4298063 9.65 73.00 63.29 Bigeye Canada 152467 2420140 15.87 25.00 35.64 Bigeye HongKong 9768 58596 6.00 1.60 0.86 Bigeye SouthKorea 2406 14432 6.00 0.39 0.21 Yellowfin Japan 216323 3682677 17.02 91.62 94.42 Yellowfin Canada 19689 212323 10.78 8.34 5.44 Yellowfin Malaysia 108 5309 49.16 0.05 0.14

I'd like to tabulate the data by species, and include a total row for each species group (in the case below the total would be after the species) except for the unitvalue column instead of including a group summation in the total row, I'd like to include the quotient of Dollars/Pounds for the species group. This is how I'd like it to turn out:

Species/Country Pounds Dollars UnitValue .... ValueShare Bigeye 609,975 6,791,231 37.52 59.44 Canada 152,467 2,420,140 15.87..........21.81 HongKong . . Japan . . SouthKorea . . Yellowfin 236,120.................................... Canada 19,689 Japan Malaysia

I've tried using the function "tabular" by following the advice to this question "R: output a pivot-like table with subtotals", but I haven't been able to get it to work. When I include my dataframe, the output includes countries and species the dataframe does not have, and ends up being very long. If I manually create a dataframe like the one above, it works, but when I used the output from ddply using my large dataframe, tabulate seems to ignore the small dataframe I reference and includes every single country and species.

Any help or tips on this would be greatly appreciated.

Thank you!

Anthony
  • 65
  • 2
  • 10
  • 1
    please include the code you've tried so far. – Reeza Sep 12 '17 at 02:07
  • @Reeza, this is the code I've tried: tabular(Species * (Country + 1) + 1 ~ (Pounds * sum)+(Dollars * sum), data = fish) (I was just using pounds and dollars for now trying to get it to work). It works when I use the small dataframe I created from scratch above (fish), but when I try to use a dataframe I created using ddply, it pulls in countries and species that only exist in the larger dataset that I used to create it. – Anthony Sep 12 '17 at 17:39

0 Answers0