Spline interpolation for panel data

[Thomas Wolbach-Newman]

Dear Users,

I am trying to use the spline interpolation to convert my annual datapoints (company information) to monthly ones in my panel data set (theoretically, it can be done in my specific case - I am aware that it requires assumptions etc.) - however, I am having difficulties conducting it using mata and spline3 options.

A simple extract from my dataset is as follows [firm name not shown]

yearstart	abc	date_id	abc2
20111	16356000	20111
20121	114879000	20112
20131	127693000	20113
20141	98716000	20114
20151	85565000	20115
20161	66861000	20116
		20117
		20118
		20119
		201110
		201111
		201112
		20121
		20122
		20123
		20124
		20125
		20126
		20127
		20128
		20129
		201210
		201211
		201212
		20131
		20132
		20133
		20134
		20135
		20136
		20137
		20138
		20139
		201310
		201311
		201312
		20141
		20142
		20143
		20144
		20145
		20146
		20147
		20148
		20149
		201410
		201411
		201412
		20151
		20152
		20153
		20154
		20155
		20156
		20157
		20158
		20159
		201510
		201511
		201512
		20161
		20162
		20163
		20164
		20165
		20166
		20167
		20168
		20169
		201610
		201611
		201612

when using the following code:

use [name of the file]

mata

X = st_data((1,6),"yearstart")

Y = st_data((1,6),"abc")

XX = st_data(.,"date_id")

A = spline3(X,Y)

B = spline3eval(A, XX)

st_store(.,"abc2",B)

end

[saving the dataset]

browse

I am not getting the datapoints for abc2.

I would be grateful if someone could point out my mistake or alternatively suggest a different way of conducting the spline interpolation.

I've tried using the denton function too but since both my annual and monthly datasets are xtset, this didn't work out.

Thank you,

Thomas

[Nick Cox]

mipolate (SSC) includes an implementation of cubic spline interpolation using precisely these Mata functions that can be used with panel data. As you hint, this exercise in extracting sunbeams from cucumbers is a long stretch.

Strictly denton (also SSC) s a command, not a function.

[Thomas Wolbach-Newman]

Thank you for your reply, Nick. One more question relating to spline interpolation - is there a way in Stata to do it all at once with multiple independent variables?

[Nick Cox]

I am not aware even that spline interpolation methods are defined in spaces of arbitrary dimension. The closest thing may be mvrs (SJ). That's not interpolation at all.

[Thomas Wolbach-Newman]

I have installed and conducted the mipolate command as advised.

The extract of my dataset is as follows [OUTPUT]:

yearstart	abc	firm	date_id	abc2
20111	16356000	xyz	20111	16356000
20121	1.15E+08	xyz	20112	1.15E+08
20131	1.28E+08	xyz	20113	1.28E+08
20141	98716000	xyz	20114	98716000
20151	85565000	xyz	20115	85565000
20161	66861000	xyz	20116	66861000
		xyz	20117	43572005
		xyz	20118	18602032
		xyz	20119	-5144905
		xyz	201110	2.87E+21
		xyz	201111	2.87E+21
		xyz	201112	2.87E+21
		xyz	20121	-3.7E+07
		xyz	20122	-4E+07
		xyz	20123	-3E+07
		xyz	20124	-3893472
		xyz	20125	40682149
		xyz	20126	1.07E+08
		xyz	20127	1.97E+08
		xyz	20128	3.15E+08
		xyz	20129	4.64E+08
		xyz	201210	2.87E+21
		xyz	201211	2.87E+21
		xyz	201212	2.88E+21
		xyz	20131	8.62E+08
		xyz	20132	1.12E+09
		xyz	20133	1.42E+09
		xyz	20134	1.76E+09
		xyz	20135	2.15E+09
		xyz	20136	2.59E+09
		xyz	20137	3.09E+09
		xyz	20138	3.64E+09
		xyz	20139	4.25E+09
		xyz	201310	2.88E+21
		xyz	201311	2.88E+21
		xyz	201312	2.88E+21
		xyz	20141	5.66E+09
		xyz	20142	6.47E+09
		xyz	20143	7.34E+09
		xyz	20144	8.29E+09
		xyz	20145	9.32E+09
		xyz	20146	1.04E+10
		xyz	20147	1.16E+10
		xyz	20148	1.29E+10
		xyz	20149	1.43E+10
		xyz	201410	2.88E+21
		xyz	201411	2.88E+21
		xyz	201412	2.88E+21
		xyz	20151	1.73E+10
		xyz	20152	1.89E+10
		xyz	20153	2.07E+10
		xyz	20154	2.25E+10
		xyz	20155	2.45E+10
		xyz	20156	2.65E+10
		xyz	20157	2.87E+10
		xyz	20158	3.10E+10
		xyz	20159	3.34E+10
		xyz	201510	2.89E+21
		xyz	201511	2.89E+21
		xyz	201512	2.89E+21
		xyz	20161	3.86E+10
		xyz	20162	4.13E+10
		xyz	20163	4.42E+10
		xyz	20164	4.73E+10
		xyz	20165	5.05E+10
		xyz	20166	5.38E+10
		xyz	20167	5.72E+10
		xyz	20168	6.08E+10
		xyz	20169	6.46E+10
		xyz	201610	2.89E+21
		xyz	201611	2.89E+21
		xyz	201612	2.89E+21

The relevant piece of code that I've used is:

by firm: mipolate abcdate_id, pchip gen(abc2)

But the output that I got is not what I am looking for - I am looking for obtaining the 'monthly' data based on annual 'abc' so that I get the data between 20111 and 20121 etc. and not getting data post 20116 as was wrongfully done in this case. Would be really grateful for any advice on how to achieve that. Thanks.

EDIT: when using: by firm: mipolate abcdate_id, spline gen(abc2) - the missing points for abc2 are just empty with the first six entries same as in the above extract.

[Nick Cox]

Your time variable looks crazy to me. Stata won't be smart on your behalf and realise that you intend 20169 should be thought of as just less than 201610. On the contrary, 201610 is almost 10 times bigger than 20169. If you plot your results you'll realise that accidentally you're writing science fiction and projecting several thousand years into the future.

Don't blame mipolate: it's just a robot.

You should use Stata monthly dates for what you want and assign annual values to some notional montht.

[Thomas Wolbach-Newman]

That's true. I've changed the date format to the one understandable by STATA. Another issue that has arisen in the meantime is that mipolate spline/pchip etc. only yields the desired outcome when, say, yearstart Jan 2011, Jan 2012 etc. are in the same rows as date_id Jan 2011, Jan 2012 etc., is there a way to overcome this problem, for instance, by generating 11 empty cells automatically in between Jan 2011 and Jan 2012 etc. so that each Jan 20XX from yearstart is at the same row as Jan 20XX from date_id? or maybe there is a different way of doing so?

Thanks

[Nick Cox]

Please give a dataset example of the yearly data using dataex (SSC) http://www.statalist.org/forums/help#stata. Don't worry about the extra monthly observations.

[Thomas Wolbach-Newman]

Please find an example of my dataset below. Thanks.

* Example generated by -dataex-. To install: ssc install dataex
clear
input int yearstart long abc str3 firm int date_id long firm1 double abc2
18628  16356000 "xyz" 18628 1            16356000
18993 114900000 "xyz" 18659 1           1.149e+08
19359 127700000 "xyz" 18687 1           1.277e+08
19724  98716000 "xyz" 18718 1            98716000
20089  85565000 "xyz" 18748 1            85565000
20454  66861000 "xyz" 18779 1            66861000
    .         . "xyz" 18809 1  44504939.101358116
    .         . "xyz" 18840 1  19206349.152024135
    .         . "xyz" 18871 1  -6089589.141546577
    .         . "xyz" 18901 1 -28438034.462762266
    .         . "xyz" 18932 1 -47128947.258511364
    .         . "xyz" 18962 1  -58850632.93451843
    .         . "xyz" 18993 1 -62184601.294658005
    .         . "xyz" 19024 1  -54360220.19684985
    .         . "xyz" 19053 1  -34936642.44078641
    .         . "xyz" 19084 1   905597.1904231906
    .         . "xyz" 19114 1   52510217.23598722
    .         . "xyz" 19145 1  125517461.60680251
    .         . "xyz" 19175 1   217323014.7573663
    .         . "xyz" 19206 1   336247182.8055752
    .         . "xyz" 19237 1  481860181.56575334
    .         . "xyz" 19267 1   650316278.6965822
    .         . "xyz" 19298 1   855045816.5813096
    .         . "xyz" 19328 1  1084946267.1896644
    .         . "xyz" 19359 1  1357544263.1367288
    .         . "xyz" 19390 1  1667986787.5979466
    .         . "xyz" 19418 1  1982794776.5164032
    .         . "xyz" 19449 1  2371508634.8511324
    .         . "xyz" 19479 1   2789941292.131932
    .         . "xyz" 19510 1  3268188194.7520475
    .         . "xyz" 19540 1   3777500533.085376
    .         . "xyz" 19571 1   4354032398.928666
    .         . "xyz" 19602 1   4983866361.482599
    .         . "xyz" 19632 1   5646262765.290828
    .         . "xyz" 19663 1   6387581306.515091
    .         . "xyz" 19693 1   7162100811.748375
    .         . "xyz" 19724 1   8023655850.580754
    .         . "xyz" 19755 1   8949668683.925966
    .         . "xyz" 19783 1   9843350331.352444
    .         . "xyz" 19814 1  10898285553.213806
    .         . "xyz" 19844 1  11986787936.268337
    .         . "xyz" 19875 1  13183624241.960865
    .         . "xyz" 19905 1  14413685054.015455
    .         . "xyz" 19936 1  15761174362.476933
    .         . "xyz" 19967 1  17188579033.647736
    .         . "xyz" 19997 1   18648157432.67186
    .         . "xyz" 20028 1  20239414722.058765
    .         . "xyz" 20058 1  21861794970.455475
    .         . "xyz" 20089 1   23625656796.99628
    .         . "xyz" 20120 1  25480589684.048595
    .         . "xyz" 20148 1   27236170565.95236
    .         . "xyz" 20179 1  29270676896.163467
    .         . "xyz" 20209 1  31332490693.530224
    .         . "xyz" 20240 1   33561266147.11827
    .         . "xyz" 20270 1   35815317121.43262
    .         . "xyz" 20301 1    38247113617.3354
    .         . "xyz" 20332 1   40785438741.94618
    .         . "xyz" 20362 1   43345440824.72469
    .         . "xyz" 20393 1   46099986607.09737
    .         . "xyz" 20423 1   48873469287.19599
    .         . "xyz" 20454 1   51852987646.26833
    .         . "xyz" 20485 1   54950190331.85087
    .         . "xyz" 20514 1  57956116194.484344
    .         . "xyz" 20545 1  61287516049.023964
    .         . "xyz" 20575 1   64629796892.65703
    .         . "xyz" 20606 1   68207977384.20031
    .         . "xyz" 20636 1    71793312998.6133
    .         . "xyz" 20667 1   75627026046.09804
    .         . "xyz" 20698 1   79593953901.34697
    .         . "xyz" 20728 1   83561883552.62697
    .         . "xyz" 20759 1   87797543579.26451
    .         . "xyz" 20789 1   92029771421.69693
end
format %tdMon_CCYY yearstart
format %tdMon_CCYY date_id
label values firm1 firm1
label def firm1 1 "xyz", modify

[Nick Cox]

Thanks for the data example. I think I see why you're getting into difficulty. The data aren't well aligned. Starting from your code I do this:

drop abc2 
rename (yearstart date_id) (date1 date2) 
gen id = _n 
reshape long date, i(id) 
drop if abc < . & _j == 2 

mipolate abc date, by(firm1) gen(pchip) pchip
mipolate abc date, by(firm1) gen(spline) spline 

scatter abc pchip spline date, ms(O none none) c(. l l) sort legend(ring(0) pos(1) col(1))

[Thomas Wolbach-Newman]

Thank you very much Nick! This is very helpful - I have understood the process when considering one firm. Now, I have tried to apply this concept to my dataset with more than one company (more complex case), however, I'm finding difficulties trying to get to the desired monthly split. First, I have tried to generate the monthly split for abc with a dataset containing more than one independent variable (I've got over 25 variables in total - that's why it would be very helpful to have them all at one dataset and generate the monthly split using the code for each variable afterwards). Please find the code below. I have included only 3 of the variables just as an example. I would appreciate any comments/advice! Thank you

* Example generated by -dataex-. To install: ssc install dataex
clear
input float id byte _j str8 firm_year double(abc def ghi) str8 firm_month int date long(firm_year1 firm_month1) double(pchip spline)
 1 1 "company1"     16356000   1150149000    4004000 "company1" 18628 1 1          942817250          942817250
 2 1 "company1"    114879000   3911420000   50291000 "company1" 18993 1 1         2547962500         2547962500
 3 1 "company1"    127693000   2891634000   93698000 "company1" 19359 1 1        -2642785000        -2642785000
 4 1 "company1"     98716000   2684734000   81573000 "company1" 19724 1 1         1970721750         1970721750
 5 1 "company1"     85565000   2461904000   81614000 "company1" 20089 1 1         -370151250         -370151250
 6 1 "company1"     66861000   1922002000   66778000 "company1" 20454 1 1         2076513000         2076513000
 7 1 "company2"     34294000   1346265000   19656000 "company1" 17532 2 1          448288000          448288000
 8 1 "company2"    109377000   3841834000   80317000 "company1" 17898 2 1         3743242000         3743242000
 9 1 "company2"     10799000  11998551000  173192000 "company1" 18263 2 1         3679356000         3679356000
10 1 "company2"   1104991000  48633931000  663506000 "company1" 18628 2 1          942817250          942817250
11 1 "company2"    2.311e+09   8.7178e+10  1.415e+09 "company1" 18993 2 1         2547962500         2547962500
12 1 "company2"    2.200e+09   6.4443e+10  1.659e+09 "company1" 19359 2 1        -2642785000        -2642785000
13 1 "company2"    1.585e+09   5.1057e+10  1.094e+09 "company1" 19724 2 1         1970721750         1970721750
14 1 "company2"    1.510e+09   4.6102e+10  9.020e+08 "company1" 20089 2 1         -370151250         -370151250
15 1 "company2"    1.272e+09   4.1355e+10  7.990e+08 "company1" 20454 2 1         2076513000         2076513000
16 1 "company3"      7212000     46838000    3402000 "company1" 13880 3 1  9993333.333333334  9993333.333333334
17 1 "company3"     10953000    105097000   15516000 "company1" 14245 3 1           12480000           12480000
18 1 "company3"     -1531000            0          0 "company1" 14610 3 1            5879000            5879000
19 1 "company3"     -1491000            0          0 "company1" 14976 3 1 43101333.333333336 43101333.333333336
20 1 "company4"     20394000   1280510000   14377000 "company1" 13880 4 1  9993333.333333334  9993333.333333334
21 1 "company4"     23810000   1338296000   17082000 "company1" 14245 4 1           12480000           12480000
22 1 "company4"     17209000   1628359000   17456000 "company1" 14610 4 1            5879000            5879000
23 1 "company4"    126333000   6367710000   56105000 "company1" 14976 4 1 43101333.333333336 43101333.333333336
24 1 "company4"    306086000  10163174000  201999000 "company1" 15341 4 1          175257500          175257500
25 1 "company4"    3.550e+08  11012903000  1.920e+08 "company1" 15706 4 1          220355500          220355500
26 1 "company4"    422293000  16707879000  230131000 "company1" 16071 4 1          264932500          264932500
27 1 "company4"    279511000  13576301000  189998000 "company1" 16437 4 1          184626500          184626500
28 1 "company4"    208175000  27514020000   95534000 "company1" 16802 4 1          126068500          126068500
29 1 "company4"    446878000  46046560000  263671000 "company1" 17167 4 1          228249500          228249500
30 1 "company4"   1203793000  46674885000  975068000 "company1" 17532 4 1          448288000          448288000
31 1 "company4"  10963814000  54627186000 1269420000 "company1" 17898 4 1         3743242000         3743242000
32 1 "company4"  10864669000  66351378000 1599339000 "company1" 18263 4 1         3679356000         3679356000
33 1 "company4"   2469560000  85442699000 2041489000 "company1" 18628 4 1          942817250          942817250
34 1 "company4"   7598637000 105419553000 2149872000 "company1" 18993 4 1         2547962500         2547962500
35 1 "company4" -13027154000  65153263000 1640748000 "company1" 19359 4 1        -2642785000        -2642785000
36 1 "company4"   6111592000  72614474000 1208984000 "company1" 19724 4 1         1970721750         1970721750
37 1 "company4"  -3174345000  60951326000 1209250000 "company1" 20089 4 1         -370151250         -370151250
38 1 "company4"   6889223000  73067956000 1220931000 "company1" 20454 4 1         2076513000         2076513000
39 1 "company5"      2374000    170033000     582000 "company1" 13880 5 1  9993333.333333334  9993333.333333334
40 1 "company5"      2677000    147690000    1169000 "company1" 14245 5 1           12480000           12480000
41 1 "company5"      1959000    121891000    1254000 "company1" 14610 5 1            5879000            5879000
42 1 "company5"      4462000    325307000    2599000 "company1" 14976 5 1 43101333.333333336 43101333.333333336
43 1 "company5"     44429000   2153870000   25671000 "company1" 15341 5 1          175257500          175257500
44 1 "company5"     85711000   3775691000   52761000 "company1" 15706 5 1          220355500          220355500
45 1 "company5"    107572000   6768520000   58635000 "company1" 16071 5 1          264932500          264932500
46 1 "company5"     89742000   6636836000   37764000 "company1" 16437 5 1          184626500          184626500
47 1 "company5"     43962000   6114758000    7679000 "company1" 16802 5 1          126068500          126068500
48 1 "company5"      9621000   4264480000   13053000 "company1" 17167 5 1          228249500          228249500
49 1 "company5"    106777000   4702380000   75817000 "company1" 17532 5 1          448288000          448288000
50 1 "company5"    156535000   5396380000  125791000 "company1" 17898 5 1         3743242000         3743242000
51 1 "company5"    1.626e+08   6412380000  126837000 "company1" 18263 5 1         3679356000         3679356000
52 1 "company5"    180362000   7632380000  127024000 "company1" 18628 5 1          942817250          942817250
53 1 "company5"    167334000   8057380000  108568000 "company1" 18993 5 1         2547962500         2547962500
54 1 "company5"    128321000   7617380000   87003000 "company1" 19359 5 1        -2642785000        -2642785000
55 1 "company5"     87579000   6408120000   69450000 "company1" 19724 5 1         1970721750         1970721750
56 1 "company5"     98175000   4952908000   68201000 "company1" 20089 5 1         -370151250         -370151250
57 1 "company5"     77968000   3948395000   64728000 "company1" 20454 5 1         2076513000         2076513000
58 1 ""                    .            .          . "company1"     . . 1                  .                  .
58 2 ""                    .            .          . "company1" 20545 . 1 3427990125.9756503                  .
59 1 ""                    .            .          . "company1"     . . 1                  .                  .
59 2 ""                    .            .          . "company1" 20575 . 1 3936477013.8610125                  .
60 1 ""                    .            .          . "company1"     . . 1                  .                  .
60 2 ""                    .            .          . "company1" 20606 . 1  4494391147.235005                  .
61 1 ""                    .            .          . "company1"     . . 1                  .                  .
61 2 ""                    .            .          . "company1" 20636 . 1  5065556917.838988                  .
62 1 ""                    .            .          . "company1"     . . 1                  .                  .
62 2 ""                    .            .          . "company1" 20667 . 1  5687862800.411832                  .
63 1 ""                    .            .          . "company1"     . . 1                  .                  .
63 2 ""                    .            .          . "company1" 20698 . 1 6342603840.3311405                  .
64 1 ""                    .            .          . "company1"     . . 1                  .                  .
64 2 ""                    .            .          . "company1" 20728 . 1  7006923652.845891                  .
65 1 ""                    .            .          . "company1"     . . 1                  .                  .
65 2 ""                    .            .          . "company1" 20759 . 1 7724920982.1556635                  .
66 1 ""                    .            .          . "company1"     . . 1                  .                  .
66 2 ""                    .            .          . "company1" 20789 . 1  8450092271.876934                  .
67 1 ""                    .            .          . "company2"     . . 2                  .                  .
67 2 ""                    .            .          . "company2" 17684 . 2                  .                  .
68 1 ""                    .            .          . "company2"     . . 2                  .                  .
68 2 ""                    .            .          . "company2" 17714 . 2                  .                  .
69 1 ""                    .            .          . "company2"     . . 2                  .                  .
69 2 ""                    .            .          . "company2" 17745 . 2                  .                  .
70 1 ""                    .            .          . "company2"     . . 2                  .                  .
70 2 ""                    .            .          . "company2" 17776 . 2                  .                  .
71 1 ""                    .            .          . "company2"     . . 2                  .                  .
71 2 ""                    .            .          . "company2" 17806 . 2                  .                  .
72 1 ""                    .            .          . "company2"     . . 2                  .                  .
72 2 ""                    .            .          . "company2" 17837 . 2                  .                  .
73 1 ""                    .            .          . "company2"     . . 2                  .                  .
73 2 ""                    .            .          . "company2" 17867 . 2                  .                  .
74 1 ""                    .            .          . "company2"     . . 2                  .                  .
74 2 ""                    .            .          . "company2" 17898 . 2                  .                  .
75 1 ""                    .            .          . "company2"     . . 2                  .                  .
75 2 ""                    .            .          . "company2" 17929 . 2                  .                  .
76 1 ""                    .            .          . "company2"     . . 2                  .                  .
76 2 ""                    .            .          . "company2" 17957 . 2                  .                  .
77 1 ""                    .            .          . "company2"     . . 2                  .                  .
77 2 ""                    .            .          . "company2" 17988 . 2                  .                  .
78 1 ""                    .            .          . "company2"     . . 2                  .                  .
78 2 ""                    .            .          . "company2" 18018 . 2                  .                  .
79 1 ""                    .            .          . "company2"     . . 2                  .                  .
end
format %tdMon_CCYY date
label values firm_year1 firm_year1
label def firm_year1 1 "company1", modify
label def firm_year1 2 "company2", modify
label def firm_year1 3 "company3", modify
label def firm_year1 4 "company4", modify
label def firm_year1 5 "company5", modify
label values firm_month1 firm_month1
label def firm_month1 1 "company1", modify
label def firm_month1 2 "company2", modify

[Nick Cox]

The code in #10 already should work for several firms. The fact that there was only one firm in the example is incidental. The by() option ensures separate interpolations.

What's essential is that you have known values as well as unknown values for each firm; otherwise there is no information to use. In your example everything is missing for company 2, so no go there.

Different variables mean different interpolations.

I can't see what mipolate code you're trying.

[Thomas Wolbach-Newman]

The code for mipolate I've tried was:

mipolate abc date, by(firm_month1) gen(pchip) pchip
mipolate abc date, by(firm_month1) gen(spline) spline

So it's impossible to do mipolate for all firms for one variable at each time? E.g. Generating monthly split for abc for each company:

mipolate abc date, by(firm_month1) gen(pchip) pchip
mipolate abc date, by(firm_month1) gen(spline) spline

and then for def, ghi, etc.?

Do I have to generate a separate dataset for each company with all of my variables and code mipolate for each variable? Or is there a way to do it for all companies at once - one variable at a time?

[Nick Cox]

Why do you think all firms at once is impossible? I've explained that it's possible. No point to a by() option otherwise.

clear
input firm time y 
1 1    .
1 2   12
1 3    .
1 4   14
1 5   15
2 1    .
2 2   66
2 3    .
2 4   45
2 5   34 
end 

mipolate y time, by(firm) gen(y2) pchip 

     +------------------------------+
     | firm   time    y          y2 |
     |------------------------------|
  1. |    1      1    .          11 |
  2. |    1      2   12          12 |
  3. |    1      3    .          13 |
  4. |    1      4   14          14 |
  5. |    1      5   15          15 |
     |------------------------------|
  6. |    2      1    .   75.954016 |
  7. |    2      2   66          66 |
  8. |    2      3    .   55.651339 |
  9. |    2      4   45          45 |
 10. |    2      5   34          34 |
     +------------------------------+

What you need to repeat is interpolation for different variables.

[Thomas Wolbach-Newman]

I can see that it works for multiple firms and then repeating the mipolate for different variables in the example above.

It's not working in my case when I type the following code:

encode firm_month, gen(firm_month1)
sort firm_month1
format %tdMon_CCYY date_id
format %tdMon_CCYY yearstart
rename (yearstart date_id) (date1 date2)
gen id = _n
reshape long date, i(id)
drop if abc < . & _j == 2
mipolate abc date, by(firm_month1) gen(pchip) pchip

The output is as per #11.

I am not getting any monthly datapoints for company2,3,4,5 even though there are annual outputs in columns abc def ghi for those firms - and I still don't know why this is happening in this case and why there are only data for 2016 for company1. They should be starting from July 2011 as the data for company1 are available from July 2011 onwards etc. - I've used the by() functionality.