Creating an index column based on months across years

Question

I want to make an index column that accounts for a period from October month of a year to the September month next year. Here is a large sample data to emphasise the point. Please take note of panel setting of data.Suppose, I am trying to calculate the mean of A for each stock over this window e.g. October 20011 to September 2012. Once I have the Index column I will perform the following:

meanDF = aggregate(cbind(A) ~ Index + Firm, df, FUN = mean)

I will be performing many customized operations apart mean calculation so I can easily replace my customized function in the above code. Please help. Thank you very much.

Answer 1

I'd made an index column ('yymm') with your data, so that it appears as four digit numerical format, e.g. 1110 for October 2011.

dat <- read.csv("./input/p_df.csv")
dat$Date <- as.character(dat$Date)
dat$Date<-as.Date(dat$Date, format="%m/%d/%Y")
dat$yymm <- format(dat$Date, format="%y%m")

Create a matrix with start date and end date for each Oct-Sep period:

dd <- structure(c(1110, 1209, 1210, 1309, 1310, 1409, 1410, 1509), .Dim = c(2L, 4L))

     [,1] [,2] [,3] [,4]
[1,] 1110 1210 1310 1410
[2,] 1209 1309 1409 1509

Subset data into 4 separate data.frame corresponding to start-end period of matrix:

df2<-lapply(1:4, function(x)dat %>% filter(mmyy >= dd[1,x] & mmyy <= dd[2,x]))

Group each dataset by Firm and summarise mean of stocks (A to F):

plyr::llply(df2, function(x) x %>% group_by(Firm) %>% select(A:F) %>% summarise_each(funs(mean)))

[[1]]
Source: local data frame [5 x 7]

            Firm        A       B        C        D         E         F
          (fctr)    (dbl)   (dbl)    (dbl)    (dbl)     (dbl)     (dbl)
1  BOB IS Equity 145.9267 3316808 62.52732 84.29513 1957.7310  285642.5
2 GAIL IS Equity 370.0094 1106420 49.80055 82.06510 1268.4775  469232.8
3  ITC IS Equity 227.2641 6970928 48.01366 67.84061 7809.3682 1778660.0
4   MM IS Equity 720.6503 1704623 53.01366 36.21561  613.9769  443013.4
5  RIL IS Equity 771.9296 3915459 47.72951 22.04312 3274.5789 2528920.7

[[2]]
Source: local data frame [5 x 7]

            Firm        A       B        C        D         E         F
          (fctr)    (dbl)   (dbl)    (dbl)    (dbl)     (dbl)     (dbl)
1  BOB IS Equity 137.7357 5329819 64.82192 81.98227 2055.4590  281634.8
2 GAIL IS Equity 333.9021 1148524 53.84932 82.13927 1268.4770  423761.6
3  ITC IS Equity 311.1275 7100443 46.88767 74.57744 7890.6657 2456360.8
4   MM IS Equity 898.4038 1329277 55.72329 46.41512  614.4784  552200.7
5  RIL IS Equity 833.1956 3224021 50.81096 49.91264 3245.9668 2703932.9

[[3]]
Source: local data frame [5 x 7]

            Firm         A       B        C        D         E         F
          (fctr)     (dbl)   (dbl)    (dbl)    (dbl)     (dbl)     (dbl)
1  BOB IS Equity  146.6735 8628298 58.94795 81.65596 2133.6639  314165.4
2 GAIL IS Equity  383.4397 1279186 46.99178 82.22435 1268.4770  487096.0
3  ITC IS Equity  337.2251 6373170 49.96164 76.48013 7946.3991 2681621.5
4   MM IS Equity 1062.1181 1057952 53.12877 53.80728  616.1057  656305.1
5  RIL IS Equity  934.2914 3138729 47.23288 60.38028 3232.1816 3023599.4

[[4]]
Source: local data frame [5 x 7]

            Firm         A       B        C        D        E         F
          (fctr)     (dbl)   (dbl)    (dbl)    (dbl)    (dbl)     (dbl)
1  BOB IS Equity  181.0604 6415760 54.68493 85.77090 2176.903  394006.5
2 GAIL IS Equity  398.5686 1480755 40.84932 83.58569 1268.477  504064.4
3  ITC IS Equity  341.9144 7534123 44.30411 78.84935 8005.011 2736656.1
4   MM IS Equity 1250.7123 1084946 46.51781 62.64578  621.092  777771.6
5  RIL IS Equity  914.7201 3571817 42.55068 59.33441 3236.035 2960117.6

To create an Index for each period:

for(i in 1:nrow(dat)){
  dat[i,"Index"]<- ifelse(dat[i,"mmyy"] >= dd[1,1] &  dat[i,"mmyy"] <= dd[2,1], 1, 
                         ifelse(dat[i,"mmyy"] >= dd[1,2] &  dat[i,"mmyy"] <= dd[2,2], 2, 
                                ifelse(dat[i,"mmyy"] >= dd[1,3] &  dat[i,"mmyy"] <= dd[2,3], 3, 4)))
}