Compounding Panel Data

[Alex Miller]

Dear all,

I am trying to analyse the effect of Incentives on Electric Vehicle adoption (Panel Data). One Variable should be the number of ChargingPoints per county per month.

For this variable I was only able to get the the opening date of the ChargingPoints per month per county and not the number of ChargingPoints. See dataset below:

County    ChargingPoints    OpeningDate    type
6    1    5/10/2010    Visitors
5    8    5/10/2010    Public
50    2    4/20/2016    Public
19    3    2/15/2017    Visitors
6    12    12/23/2015    Visitors
6    3    2/15/2016    Public
6    8    5/19/2014    Public
18    1    10/15/2012    By appointment
12    2    4/29/2010    Visitors
12    16    5/10/2010    Visitors
3    4    7/5/2013    Visitors
3    4    4/14/2014    Public

So in order to get the number of ChargingPoints per county per month (and type) I thought I could compound the data.
Example for County 6 the data should then look the following:

County    ChargingPoints    OpeningDate    type
6    1    5/10/2010    Visitors
6    13    12/23/2015    Visitors
6    11    2/15/2016    Public
6    8    5/19/2014    Public

Is there an easy way to do this in STATA?

Thanks a lot for you help. I would really appreciate it.

Regards,
Alex

[Clyde Schechter]

Assuming that your variable OpeningDate is a bona fide Stata internal format date variable:

by County (OpeningDate), sort: replace ChargingPoints = sum(ChargingPoints)

will give you the running total.

If OpeningDate is just a string variable, then you need to convert it using the -daily()- function.

Added: Using the running total implicitly assumes that once a charging point opens it is never removed from service. Is that a reasonable assumption in the real world? Also, as a matter of programming practice, it is probably better to generate a new variable to contain the running sum than to overwrite the existing variable.

In the future, when showing data examples, please use the -dataex- command to do so. If you are running version 15.1 or a fully updated version 14.2, it is already part of your official Stata installation. If not, run -ssc install dataex- to get it. Either way, run -help dataex- to read the simple instructions for using it. -dataex- will save you time; it is easier and quicker than typing out tables. It includes complete information about aspects of the data that are often critical to answering your question but cannot be seen from tabular displays or screenshots. It also makes it possible for those who want to help you to create a faithful representation of your example to try out their code, which in turn makes it more likely that their answer will actually work in your data.

When asking for help with code, always show example data. When showing example data, always use -dataex-.

[Alex Miller]

Hi Clyde,

thanks for the quick response. Yes I am assuming no stations have been removed. Unfortunately there is no data recoded for removal of chargingpoints, but the government in Norway told me this will have minimal impact...

Sorry, I did not know about the dataex function. I have just installed it right now.

Using your code this changes the data as follows:

* Example generated by -dataex-. To install: ssc install dataex
clear
input str3 Active byte County int(ChargingPoints OpeningDate) float type
"Yes" 1   3 18346 3
"Yes" 1   5 18346 3
"Yes" 1   7 18346 3
"Yes" 1  12 18346 5
"Yes" 1  16 18347 5
"Yes" 1  18 18348 5
"Yes" 1  24 18348 3
"Yes" 1  25 18371 5
"Yes" 1  29 18380 3
"Yes" 1  31 18392 3
"Yes" 1  33 18392 3
"Yes" 1  35 18392 3
"Yes" 1  38 18392 3
"Yes" 1  40 18392 3
"Yes" 1  42 18392 3
"Yes" 1  44 18392 3
"Yes" 1  50 18392 3
"Yes" 1  52 18392 3
"Yes" 1  58 18392 3
"Yes" 1  59 18392 3
"Yes" 1  61 18392 3
"Yes" 1  63 18392 3
"Yes" 1  68 18392 3
"Yes" 1  70 18392 1
"Yes" 1  76 18392 3
"Yes" 1  82 18392 3
"Yes" 1  88 18392 3
"Yes" 1  90 18520 3
"Yes" 1  92 18520 3
"Yes" 1  94 18626 3
"Yes" 1  96 18626 3
"Yes" 1 110 18858 3
"Yes" 1 112 18865 3
"Yes" 1 115 19058 3
"Yes" 1 121 19178 3
"Yes" 1 123 19194 3
"Yes" 1 127 19211 5
"Yes" 1 137 19232 2
"Yes" 1 151 19232 3
"Yes" 1 152 19278 3
"Yes" 1 156 19290 3
"Yes" 1 158 19310 5
"Yes" 1 160 19509 5
"Yes" 1 161 19509 3
"Yes" 1 165 19512 3
"Yes" 1 167 19625 5
"Yes" 1 169 19678 3
"Yes" 1 187 19683 5
"Yes" 1 191 19712 3
"Yes" 1 197 19712 3
"Yes" 1 201 19712 3
"Yes" 1 203 19728 3
"Yes" 1 205 19729 3
"Yes" 1 209 19729 3
"Yes" 1 211 19733 3
"Yes" 1 217 19746 3
"Yes" 1 219 19775 3
"Yes" 1 221 19782 3
"Yes" 1 225 19782 3
"Yes" 1 226 19840 3
"Yes" 1 230 19858 3
"Yes" 1 232 19880 5
"Yes" 1 242 19912 3
"Yes" 1 244 19996 1
"Yes" 1 247 20027 3
"Yes" 1 251 20075 3
"Yes" 1 253 20164 3
"Yes" 1 254 20192 5
"Yes" 1 260 20220 3
"Yes" 1 276 20244 3
"Yes" 1 280 20264 3
"Yes" 1 282 20264 3
"Yes" 1 288 20265 5
"Yes" 1 291 20276 3
"Yes" 1 295 20276 3
"Yes" 1 299 20285 3
"Yes" 1 302 20286 3
"Yes" 1 304 20369 3
"Yes" 1 306 20369 3
"Yes" 1 310 20375 2
"Yes" 1 312 20375 3
"Yes" 1 318 20382 3
"Yes" 1 320 20433 4
"Yes" 1 324 20445 3
"Yes" 1 328 20464 5
"Yes" 1 336 20500 3
"Yes" 1 338 20501 3
"Yes" 1 344 20562 3
"Yes" 1 354 20605 3
"Yes" 1 368 20650 5
"Yes" 1 381 20754 3
"Yes" 1 385 20767 3
"Yes" 1 389 20786 5
"Yes" 1 393 20797 3
"Yes" 1 397 20801 3
"Yes" 1 405 20801 3
"Yes" 1 409 20801 3
"Yes" 1 417 20801 3
"Yes" 1 421 20801 3
"Yes" 1 427 20848 3
end
format %tdnn/dd/CCYY OpeningDate
label values type type
label def type 1 "By appointment", modify
label def type 2 "Employees", modify
label def type 3 "Public", modify
label def type 4 "Residents", modify
label def type 5 "Visitors", modify

Problem now is, I loose the categorization by type. I is possible to generate the runnig total by county (OpeningDate) as well as by type?

Thanks again.
Alex

[Clyde Schechter]

Sure. I didn't understand from your original post that type was relevant, although looking back on it, your example does make that distinction. Sorry about that.

by County type (OpeningDate), sort: gen TotalChargingPoints = sum(ChargingPoints)

[Alex Miller]

Thanks a lot!