lapioche75 lapioche75 - 2 months ago 7
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 ... ... ...

Answer

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
6      6/30/2015 Healthcare   400", header=T)