Panel data: replacing missing values with the nearest before available data point

[Jala Youssef]

Hi everyone,

I have data on tariffs by country, sector and year. Please find below an example. I would like to replace the missing values with the nearest available value by country, sector and year (the nearest before). I would be grateful if you could advise.

Many thanks in advance.

Jala

* Example generated by -dataex-. For more info, type help dataex
clear
input str33 country str35 sector int year float tariffsec
"Afghanistan" "MT2 - 12 - Minerals and metals"    2006 5.9
"Afghanistan" "MT2 - 12 - Minerals and metals"    2007 5.9
"Afghanistan" "MT2 - 12 - Minerals and metals"    2008 5.8
"Afghanistan" "MT2 - 12 - Minerals and metals"    2009   .
"Afghanistan" "MT2 - 12 - Minerals and metals"    2010   .
"Afghanistan" "MT2 - 12 - Minerals and metals"    2011   .
"Afghanistan" "MT2 - 12 - Minerals and metals"    2012   6
"Afghanistan" "MT2 - 12 - Minerals and metals"    2013   6
"Afghanistan" "MT2 - 12 - Minerals and metals"    2014   .
"Afghanistan" "MT2 - 12 - Minerals and metals"    2015   .
"Afghanistan" "MT2 - 12 - Minerals and metals"    2016   .
"Afghanistan" "MT2 - 12 - Minerals and metals"    2017   .
"Afghanistan" "MT2 - 12 - Minerals and metals"    2018 6.2
"Afghanistan" "MT2 - 12 - Minerals and metals"    2019   .
"Afghanistan" "MT2 - 12 - Minerals and metals"    2020   .
"Afghanistan" "MT2 - 12 - Minerals and metals"    2021   .
"Afghanistan" "MT2 - 13 - Petroleum"              2006 5.3
"Afghanistan" "MT2 - 13 - Petroleum"              2007 5.3
"Afghanistan" "MT2 - 13 - Petroleum"              2008   8
"Afghanistan" "MT2 - 13 - Petroleum"              2009   .
"Afghanistan" "MT2 - 13 - Petroleum"              2010   .
"Afghanistan" "MT2 - 13 - Petroleum"              2011   .
"Afghanistan" "MT2 - 13 - Petroleum"              2012 8.6
"Afghanistan" "MT2 - 13 - Petroleum"              2013 8.6
"Afghanistan" "MT2 - 13 - Petroleum"              2014   .
"Afghanistan" "MT2 - 13 - Petroleum"              2015   .
"Afghanistan" "MT2 - 13 - Petroleum"              2016   .
"Afghanistan" "MT2 - 13 - Petroleum"              2017   .
"Afghanistan" "MT2 - 13 - Petroleum"              2018 9.6
"Afghanistan" "MT2 - 13 - Petroleum"              2019   .
"Afghanistan" "MT2 - 13 - Petroleum"              2020   .
"Afghanistan" "MT2 - 13 - Petroleum"              2021   .
"Afghanistan" "MT2 - 14 - Chemicals"              2006 5.2
"Afghanistan" "MT2 - 14 - Chemicals"              2007 5.2
"Afghanistan" "MT2 - 14 - Chemicals"              2008   5
"Afghanistan" "MT2 - 14 - Chemicals"              2009   .
"Afghanistan" "MT2 - 14 - Chemicals"              2010   .
"Afghanistan" "MT2 - 14 - Chemicals"              2011   .
"Afghanistan" "MT2 - 14 - Chemicals"              2012   5
"Afghanistan" "MT2 - 14 - Chemicals"              2013   5
"Afghanistan" "MT2 - 14 - Chemicals"              2014   .
"Afghanistan" "MT2 - 14 - Chemicals"              2015   .
"Afghanistan" "MT2 - 14 - Chemicals"              2016   .
"Afghanistan" "MT2 - 14 - Chemicals"              2017   .
"Afghanistan" "MT2 - 14 - Chemicals"              2018 5.3
"Afghanistan" "MT2 - 14 - Chemicals"              2019   .
"Afghanistan" "MT2 - 14 - Chemicals"              2020   .
"Afghanistan" "MT2 - 14 - Chemicals"              2021   .
"Afghanistan" "MT2 - 15 - Wood, paper, etc"       2006 5.5
"Afghanistan" "MT2 - 15 - Wood, paper, etc"       2007 5.5
"Afghanistan" "MT2 - 15 - Wood, paper, etc"       2008   5
"Afghanistan" "MT2 - 15 - Wood, paper, etc"       2009   .
"Afghanistan" "MT2 - 15 - Wood, paper, etc"       2010   .
"Afghanistan" "MT2 - 15 - Wood, paper, etc"       2011   .
"Afghanistan" "MT2 - 15 - Wood, paper, etc"       2012 5.7
"Afghanistan" "MT2 - 15 - Wood, paper, etc"       2013 5.7
"Afghanistan" "MT2 - 15 - Wood, paper, etc"       2014   .
"Afghanistan" "MT2 - 15 - Wood, paper, etc"       2015   .
"Afghanistan" "MT2 - 15 - Wood, paper, etc"       2016   .
"Afghanistan" "MT2 - 15 - Wood, paper, etc"       2017   .
"Afghanistan" "MT2 - 15 - Wood, paper, etc"       2018 6.8
"Afghanistan" "MT2 - 15 - Wood, paper, etc"       2019   .
"Afghanistan" "MT2 - 15 - Wood, paper, etc"       2020   .
"Afghanistan" "MT2 - 15 - Wood, paper, etc"       2021   .
"Afghanistan" "MT2 - 16 - Textiles"               2006 4.4
"Afghanistan" "MT2 - 16 - Textiles"               2007 4.4
"Afghanistan" "MT2 - 16 - Textiles"               2008 4.4
"Afghanistan" "MT2 - 16 - Textiles"               2009   .
"Afghanistan" "MT2 - 16 - Textiles"               2010   .
"Afghanistan" "MT2 - 16 - Textiles"               2011   .
"Afghanistan" "MT2 - 16 - Textiles"               2012 4.8
"Afghanistan" "MT2 - 16 - Textiles"               2013 4.8
"Afghanistan" "MT2 - 16 - Textiles"               2014   .
"Afghanistan" "MT2 - 16 - Textiles"               2015   .
"Afghanistan" "MT2 - 16 - Textiles"               2016   .
"Afghanistan" "MT2 - 16 - Textiles"               2017   .
"Afghanistan" "MT2 - 16 - Textiles"               2018 5.1
"Afghanistan" "MT2 - 16 - Textiles"               2019   .
"Afghanistan" "MT2 - 16 - Textiles"               2020   .
"Afghanistan" "MT2 - 16 - Textiles"               2021   .
"Afghanistan" "MT2 - 17 - Clothing"               2006  10
"Afghanistan" "MT2 - 17 - Clothing"               2007  10
"Afghanistan" "MT2 - 17 - Clothing"               2008  10
"Afghanistan" "MT2 - 17 - Clothing"               2009   .
"Afghanistan" "MT2 - 17 - Clothing"               2010   .
"Afghanistan" "MT2 - 17 - Clothing"               2011   .
"Afghanistan" "MT2 - 17 - Clothing"               2012 9.9
"Afghanistan" "MT2 - 17 - Clothing"               2013 9.9
"Afghanistan" "MT2 - 17 - Clothing"               2014   .
"Afghanistan" "MT2 - 17 - Clothing"               2015   .
"Afghanistan" "MT2 - 17 - Clothing"               2016   .
"Afghanistan" "MT2 - 17 - Clothing"               2017   .
"Afghanistan" "MT2 - 17 - Clothing"               2018 9.9
"Afghanistan" "MT2 - 17 - Clothing"               2019   .
"Afghanistan" "MT2 - 17 - Clothing"               2020   .
"Afghanistan" "MT2 - 17 - Clothing"               2021   .
"Afghanistan" "MT2 - 18 - Leather, footwear, etc" 2006 6.3
"Afghanistan" "MT2 - 18 - Leather, footwear, etc" 2007 6.3
"Afghanistan" "MT2 - 18 - Leather, footwear, etc" 2008 6.3
"Afghanistan" "MT2 - 18 - Leather, footwear, etc" 2009   .
end

[Carlo Lazzaro]

Jala:
a more advisable approach would imply -ipolate-:

. encode country, g(n_country)

. encode sector, g(n_sector)

. egen wanted=group( n_country n_sector year )

. bysort n_country: ipolate tariffsec wanted, g( tariffsec_1)

. replace tariffsec_1= tariffsec_1[_n-1] if tariffsec_1==.

 . list n_country n_sector year wanted tariffsec tariffsec_1 in 1/10

     +-------------------------------------------------------------------------------------+
     |   n_country                         n_sector   year   wanted   tariff~c   tariffs~1 |
     |-------------------------------------------------------------------------------------|
  1. | Afghanistan   MT2 - 12 - Minerals and metals   2006        1        5.9   5.9000001 |
  2. | Afghanistan   MT2 - 12 - Minerals and metals   2007        2        5.9   5.9000001 |
  3. | Afghanistan   MT2 - 12 - Minerals and metals   2008        3        5.8   5.8000002 |
  4. | Afghanistan   MT2 - 12 - Minerals and metals   2009        4          .   5.8500001 |
  5. | Afghanistan   MT2 - 12 - Minerals and metals   2010        5          .   5.9000001 |
     |-------------------------------------------------------------------------------------|
  6. | Afghanistan   MT2 - 12 - Minerals and metals   2011        6          .        5.95 |
  7. | Afghanistan   MT2 - 12 - Minerals and metals   2012        7          6           6 |
  8. | Afghanistan   MT2 - 12 - Minerals and metals   2013        8          6           6 |
  9. | Afghanistan   MT2 - 12 - Minerals and metals   2014        9          .        6.04 |
 10. | Afghanistan   MT2 - 12 - Minerals and metals   2015       10          .   6.0799999 |
     +-------------------------------------------------------------------------------------+

.

[Jala Youssef]

Dear Carlo Lazzaro

Many thanks for your prompt reply and the very useful suggestions, much appreciated.

Jala