Any quick way to run cubic spline regression on panel data in Stata?

[Jurat Shahidin]

I have panel data that resulted from temperature bin data and respective economic sector. So I want to run cubic spline regression where knots can be 5. I really don't know how can I run spline regression on my panel data because I've never done spline regression in my earlier work. Can anyone guide me how to make this happen in Stata?

Regarding my panel data, at the first row shown below are my dependent variables which presented in natural log terms and independent variables: average temperature, total precipitation and 11 temperature bins and each bin-width (AKA, bin's window) is 3-degree Celsius. (<-6, -6~-3,-3~0,...>21).

Regarding spline specification, natural spline/smoothing spline / cubic spline could work for me; spline knot can take a value of 4 or 5 or vice-versa.

Here is the example snippet panel data that I want to run spline regression (or piece-wise regression) for it, please take a look at the panel data down below:

 
index
year
nuts_name
ln_gdp_percapita
ln_gva_agr_perworker
ln_gva_em_perworker
ln_gva_cons_perworker
ln_gva_dist_perworker
ln_gva_fin_perworker
ln_gva_nonmark_perworker
temp
prec
prec100
nuts_id
bin1
bin2
bin3
bin4
bin5
bin6
bin7
bin8
bin9
bin10
bin11

de111
1980
Stuttgart, Stadtkreis
3.443704
2.036288
3.267888
3.137077
3.082345
3.879716
3.585174
8.862431
655.6
6.556
de111
10
10
28
45
55
43
52
54
38
24
7

de111
1981
Stuttgart, Stadtkreis
3.481725
2.022076
3.188755
3.099422
3.119275
3.892662
3.581378
9.583781
849.6
8.496
de111
5
14
40
38
40
22
54
64
44
30
14

de111
1982
Stuttgart, Stadtkreis
3.545516
2.267681
3.150235
3.073822
3.100677
3.905274
3.557841
9.98537
794.1
7.941
de111
7
13
15
39
53
53
43
36
52
37
17

de111
1983
Stuttgart, Stadtkreis
3.626141
2.138615
3.223052
3.114146
3.158287
3.940364
3.53808
10.07422
808.7
8.087
de111
3
17
29
38
45
40
48
46
34
38
27

de111
1984
Stuttgart, Stadtkreis
3.684978
2.410286
3.203391
3.101511
3.198802
3.955857
3.591974
8.962295
717.8
7.178
de111
1
9
23
67
55
36
48
57
46
18
6

de111
1985
Stuttgart, Stadtkreis
3.732419
2.406888
3.279514
3.107647
3.197114
3.946136
3.509635
8.735041
678
6.78
de111
27
11
24
36
53
33
32
54
49
36
10

de111
1986
Stuttgart, Stadtkreis
3.785341
2.659435
3.47186
3.24762
3.243426
4.039428
3.562429
9.22126
879.3
8.793
de111
16
10
24
45
41
47
47
50
32
33
20

de111
1987
Stuttgart, Stadtkreis
3.814674
2.630838
3.604336
3.339925
3.251627
4.092345
3.562793
8.896849
812.8
8.128
de111
13
19
30
38
32
54
43
49
44
26
17

de111
1988
Stuttgart, Stadtkreis
3.833437
2.891356
3.600405
3.398345
3.336716
4.12587
3.517508
10.19284
932.8
9.328
de111
1
2
23
50
49
44
46
55
54
31
11

de111
1989
Stuttgart, Stadtkreis
3.836867
2.942866
3.604008
3.463536
3.382585
4.163869
3.54217
10.38375
625.1
6.251
de111
0
10
31
32
42
44
58
51
42
44
11

de111
1990
Stuttgart, Stadtkreis
3.876456
3.103234
3.708044
3.516409
3.436605
4.211224
3.54022
10.49575
788.9
7.889
de111
0
4
26
34
47
50
56
62
43
25
18

de111
1991
Stuttgart, Stadtkreis
3.897659
3.210014
4.092638
4.051353
3.832299
4.678628
3.839186
9.680164
548.3
5.483
de111
7
16
25
48
37
44
58
35
33
39
23

de111
1992
Stuttgart, Stadtkreis
3.885848
2.979214
4.076821
3.971901
3.789868
4.624136
3.803127
10.43615
794
7.94
de111
1
12
14
40
58
60
28
40
51
35
27

de111
1993
Stuttgart, Stadtkreis
3.801015
3.056502
4.029931
3.87738
3.688005
4.554346
3.722416
9.924466
818.3
8.183
de111
4
15
27
33
47
41
42
57
54
27
18

de111
1994
Stuttgart, Stadtkreis
3.79825
3.032449
4.14937
3.897591
3.714159
4.530776
3.739223
11.28066
817.9
8.179
de111
0
6
13
24
55
59
56
49
41
26
36

de111
1995
Stuttgart, Stadtkreis
3.817734
3.041164
4.206553
3.894684
3.714305
4.51307
3.705217
10.11049
853.7
8.537
de111
5
7
27
40
44
44
48
59
34
30
27

de111
1996
Stuttgart, Stadtkreis
3.833069
3.114541
4.170473
3.819833
3.621079
4.440529
3.650682
8.610082
749.8
7.498
de111
10
15
37
51
29
31
65
45
42
29
12

de111
1997
Stuttgart, Stadtkreis
3.876873
3.285263
4.244434
3.815267
3.615122
4.486804
3.632658
10.17825
672.3
6.723
de111
5
13
11
44
54
46
42
48
44
39
19

de111
1998
Stuttgart, Stadtkreis
3.848286
3.320186
4.112788
3.74954
3.65927
4.447877
3.636042
10.2949
764.5
7.645
de111
2
15
22
37
38
51
53
45
47
35
20

de111
1999
Stuttgart, Stadtkreis
3.886336
3.377673
4.214713
3.838001
3.676687
4.432903
3.640623
10.536
877.6
8.776
de111
2
13
16
42
51
40
37
43
60
42
19

de111
2000
Stuttgart, Stadtkreis
3.896116
3.430957
4.242776
3.945306
3.70603
4.344573
3.626586
11.12235
739.8
7.398
de111
4
1
13
26
54
56
49
49
60
32
22

de111
2001
Stuttgart, Stadtkreis
3.95803
3.244338
4.336602
3.938404
3.751117
4.411861
3.622915
10.43556
868.8
8.688
de111
4
5
26
39
46
35
59
49
43
35
24

de111
2002
Stuttgart, Stadtkreis
3.971981
3.251374
4.41133
3.920738
3.722426
4.400343
3.640432
10.81953
991.3
9.913
de111
6
10
7
24
42
80
52
35
51
41
17

de111
2003
Stuttgart, Stadtkreis
3.990064
3.255644
4.49792
3.930972
3.743837
4.398983
3.634836
10.94255
572.1
5.721
de111
5
14
25
32
59
42
26
39
29
43
51

de111
2004
Stuttgart, Stadtkreis
3.971676
3.389947
4.496017
3.903058
3.752117
4.363626
3.629086
10.01566
637.8
6.378
de111
1
9
31
51
31
41
43
58
47
32
22

de111
2005
Stuttgart, Stadtkreis
3.947286
3.629409
4.44542
3.969516
3.7438
4.333296
3.626842
9.911068
681
6.81
de111
8
16
28
41
25
35
64
44
49
33
22

de111
2006
Stuttgart, Stadtkreis
3.98523
3.703238
4.592952
4.033929
3.775914
4.344024
3.640576
10.46386
696.9
6.969
de111
4
17
32
36
30
31
43
58
49
30
35

de111
2007
Stuttgart, Stadtkreis
4.021808
3.651737
4.666639
4.076498
3.77496
4.366379
3.641507
10.7291
719.8
7.198
de111
1
11
13
24
50
62
44
45
63
38
14

de111
2008
Stuttgart, Stadtkreis
4.031365
3.711969
4.647009
4.135829
3.774766
4.359774
3.640695
10.28653
703.8
7.038
de111
0
2
29
40
47
55
48
38
50
38
19

I lack of idea how to run cubic spline regression on this panel data in Stata. Anyone knows how to get this done quickly? Any thoughts?

Here is my likely output:

Any thought? Thanks in advance!

[Rich Goldstein]

your question is not clear; e.g., what variable do you want splines for? does it exist for each time point? are the data in long or wide format? (and more); please see the FAQ; also see

help mkspline

[Jurat Shahidin]

Thanks for your comment, here is example panel data snippet on the fly, please take a look : https://jmp.sh/8YBdDK5. Let's say, let's choose the dependent variable

ln_gdp_percapita

, and independent variable:

bin5

. How can I run spline regression for that? Any further help with Stata command?

Plus, I tried the same problem in R and not going well, here is my attempt:

dat= read.xlsx(file = "~/panel_bin_3.xls", sheetIndex = 1)
sf <- lm(dat$ln_gdp_percapita ~ splines::ns(x = c(dat$bin10), degree = 1, knots =c(1)), data = dat)
panel_dat <- mutate(dat, smooth=fitted(sf))

but repression output is not very nice, so I come here to ask help in Stata. Do you have any idea, please? I got stuck here what to do. Any more thoughts?

[Igor Paploski]

Help would be highly expected? Please note that questions are answered by volunteers that have (believe it or not) other commitments.

That said, please look at the FAQ specially regarding data snippets and code (topic 12). Most people in the forum are unwilling to download a file/follow a link which integrity is hard to ascertain. That's one of the reasons using dataex is important. Regarding code, please post your attempt to the code in Stata. Even thought a non-negligible portion of Statalist users probably do also use R, this is a Stata forum. Note that the utility of answers one get is proportional to how well they pose their question, to data availability and to (Stata) code sharing.

I assume you looked into other sources of info on the topic (such as here, here and here)? Last but not least, please look into Godstein's suggestion:

help mkspline

[Jurat Shahidin]

Dear Igor:

I understand your concern, and the example panel data snippet would be enough for testing and running spline regression. I didn't find a
better post that resolved cubic spline regression for panel data. Any possible hint or workflow in Stata would be appreciated. Do you have any concrete idea or example code snippet? Thank you!

PS: here is the example panel data snippets:

 
index
year
nuts_name
ln_gdp_percapita
ln_gva_agr_perworker
ln_gva_em_perworker
ln_gva_cons_perworker
ln_gva_dist_perworker
ln_gva_fin_perworker
ln_gva_nonmark_perworker
temp
prec
prec100
nuts_id
bin1
bin2
bin3
bin4
bin5
bin6
bin7
bin8
bin9
bin10
bin11

de111
1980
Stuttgart, Stadtkreis
3.443704
2.036288
3.267888
3.137077
3.082345
3.879716
3.585174
8.862431
655.6
6.556
de111
10
10
28
45
55
43
52
54
38
24
7

de111
1981
Stuttgart, Stadtkreis
3.481725
2.022076
3.188755
3.099422
3.119275
3.892662
3.581378
9.583781
849.6
8.496
de111
5
14
40
38
40
22
54
64
44
30
14

de111
1982
Stuttgart, Stadtkreis
3.545516
2.267681
3.150235
3.073822
3.100677
3.905274
3.557841
9.98537
794.1
7.941
de111
7
13
15
39
53
53
43
36
52
37
17

de111
1983
Stuttgart, Stadtkreis
3.626141
2.138615
3.223052
3.114146
3.158287
3.940364
3.53808
10.07422
808.7
8.087
de111
3
17
29
38
45
40
48
46
34
38
27

de111
1984
Stuttgart, Stadtkreis
3.684978
2.410286
3.203391
3.101511
3.198802
3.955857
3.591974
8.962295
717.8
7.178
de111
1
9
23
67
55
36
48
57
46
18
6

de111
1985
Stuttgart, Stadtkreis
3.732419
2.406888
3.279514
3.107647
3.197114
3.946136
3.509635
8.735041
678
6.78
de111
27
11
24
36
53
33
32
54
49
36
10

de111
1986
Stuttgart, Stadtkreis
3.785341
2.659435
3.47186
3.24762
3.243426
4.039428
3.562429
9.22126
879.3
8.793
de111
16
10
24
45
41
47
47
50
32
33
20

de111
1987
Stuttgart, Stadtkreis
3.814674
2.630838
3.604336
3.339925
3.251627
4.092345
3.562793
8.896849
812.8
8.128
de111
13
19
30
38
32
54
43
49
44
26
17

de111
1988
Stuttgart, Stadtkreis
3.833437
2.891356
3.600405
3.398345
3.336716
4.12587
3.517508
10.19284
932.8
9.328
de111
1
2
23
50
49
44
46
55
54
31
11

de111
1989
Stuttgart, Stadtkreis
3.836867
2.942866
3.604008
3.463536
3.382585
4.163869
3.54217
10.38375
625.1
6.251
de111
0
10
31
32
42
44
58
51
42
44
11

de111
1990
Stuttgart, Stadtkreis
3.876456
3.103234
3.708044
3.516409
3.436605
4.211224
3.54022
10.49575
788.9
7.889
de111
0
4
26
34
47
50
56
62
43
25
18

de111
1991
Stuttgart, Stadtkreis
3.897659
3.210014
4.092638
4.051353
3.832299
4.678628
3.839186
9.680164
548.3
5.483
de111
7
16
25
48
37
44
58
35
33
39
23

de111
1992
Stuttgart, Stadtkreis
3.885848
2.979214
4.076821
3.971901
3.789868
4.624136
3.803127
10.43615
794
7.94
de111
1
12
14
40
58
60
28
40
51
35
27

de111
1993
Stuttgart, Stadtkreis
3.801015
3.056502
4.029931
3.87738
3.688005
4.554346
3.722416
9.924466
818.3
8.183
de111
4
15
27
33
47
41
42
57
54
27
18

de111
1994
Stuttgart, Stadtkreis
3.79825
3.032449
4.14937
3.897591
3.714159
4.530776
3.739223
11.28066
817.9
8.179
de111
0
6
13
24
55
59
56
49
41
26
36

de111
1995
Stuttgart, Stadtkreis
3.817734
3.041164
4.206553
3.894684
3.714305
4.51307
3.705217
10.11049
853.7
8.537
de111
5
7
27
40
44
44
48
59
34
30
27

de111
1996
Stuttgart, Stadtkreis
3.833069
3.114541
4.170473
3.819833
3.621079
4.440529
3.650682
8.610082
749.8
7.498
de111
10
15
37
51
29
31
65
45
42
29
12

de111
1997
Stuttgart, Stadtkreis
3.876873
3.285263
4.244434
3.815267
3.615122
4.486804
3.632658
10.17825
672.3
6.723
de111
5
13
11
44
54
46
42
48
44
39
19

de111
1998
Stuttgart, Stadtkreis
3.848286
3.320186
4.112788
3.74954
3.65927
4.447877
3.636042
10.2949
764.5
7.645
de111
2
15
22
37
38
51
53
45
47
35
20

de111
1999
Stuttgart, Stadtkreis
3.886336
3.377673
4.214713
3.838001
3.676687
4.432903
3.640623
10.536
877.6
8.776
de111
2
13
16
42
51
40
37
43
60
42
19

de111
2000
Stuttgart, Stadtkreis
3.896116
3.430957
4.242776
3.945306
3.70603
4.344573
3.626586
11.12235
739.8
7.398
de111
4
1
13
26
54
56
49
49
60
32
22

de111
2001
Stuttgart, Stadtkreis
3.95803
3.244338
4.336602
3.938404
3.751117
4.411861
3.622915
10.43556
868.8
8.688
de111
4
5
26
39
46
35
59
49
43
35
24

de111
2002
Stuttgart, Stadtkreis
3.971981
3.251374
4.41133
3.920738
3.722426
4.400343
3.640432
10.81953
991.3
9.913
de111
6
10
7
24
42
80
52
35
51
41
17

de111
2003
Stuttgart, Stadtkreis
3.990064
3.255644
4.49792
3.930972
3.743837
4.398983
3.634836
10.94255
572.1
5.721
de111
5
14
25
32
59
42
26
39
29
43
51

de111
2004
Stuttgart, Stadtkreis
3.971676
3.389947
4.496017
3.903058
3.752117
4.363626
3.629086
10.01566
637.8
6.378
de111
1
9
31
51
31
41
43
58
47
32
22

de111
2005
Stuttgart, Stadtkreis
3.947286
3.629409
4.44542
3.969516
3.7438
4.333296
3.626842
9.911068
681
6.81
de111
8
16
28
41
25
35
64
44
49
33
22

de111
2006
Stuttgart, Stadtkreis
3.98523
3.703238
4.592952
4.033929
3.775914
4.344024
3.640576
10.46386
696.9
6.969
de111
4
17
32
36
30
31
43
58
49
30
35

de111
2007
Stuttgart, Stadtkreis
4.021808
3.651737
4.666639
4.076498
3.77496
4.366379
3.641507
10.7291
719.8
7.198
de111
1
11
13
24
50
62
44
45
63
38
14

de111
2008
Stuttgart, Stadtkreis
4.031365
3.711969
4.647009
4.135829
3.774766
4.359774
3.640695
10.28653
703.8
7.038
de111
0
2
29
40
47
55
48
38
50
38
19

Plus, Could you give me the idea which spline I should choose (i.e, natural spline, cubic spline) ? Any more thoughts?