lapioche75 - 1 year ago 58
R Question

# Create Sub-group weights in R

I currently have the following table/csv in R:

``````Name    Value   Sector  Date
Company1    100 Financials  3/31/2015
Company2    100 Energy  3/31/2015
Company3    100 Healthcare  3/31/2015
Company4    100 Financials  3/31/2015
Company5    100 Energy  3/31/2015
Company6    100 Healthcare  3/31/2015
Company1    100 Financials  6/30/2015
Company2    200 Energy  6/30/2015
Company3    200 Healthcare  6/30/2015
Company4    200 Financials  6/30/2015
Company5    200 Energy  6/30/2015
``````

What I want to create is for each quarter end date, the weights for each Sector based on Value.

I have been using:

``````cdata <- ddply(Test.Exposure, c("Date", "Sector"), summarise,
Sumx1 = sum(Value))
``````

This gives me:

``````            Date     Sector Sumx1
1      3/31/2015     Energy   200
2      3/31/2015 Financials   200
3      3/31/2015 Healthcare   200
4      6/30/2015     Energy   400
5      6/30/2015 Financials   300
6      6/30/2015 Healthcare   400
``````

1) Is there a way to get
`%`
of a sum?

2) Is it possible to show only one line for each quarter end date, and have each sector in one column like this:

``````         Financials Energy  Healthcare
3/31/2015   33,33%  33,33%  33,33%
6/30/2015   ... ... ...
``````

You can use `xtabs` following by `rowSums`:

``````a <- xtabs(Sumx1~Date+Sector, d)

#           Sector
#Date        Energy Financials Healthcare
#  3/31/2015    200        200        200
#  6/30/2015    400        300        400

round(a/rowSums(a)*100, 2)

#           Sector
#Date        Energy Financials Healthcare
#  3/31/2015  33.33      33.33      33.33
#  6/30/2015  36.36      27.27      36.36
``````

data

``````d <- read.table(text="            Date     Sector Sumx1
1      3/31/2015     Energy   200
2      3/31/2015 Financials   200
3      3/31/2015 Healthcare   200
4      6/30/2015     Energy   400
5      6/30/2015 Financials   300