Announcement

Collapse
No announcement yet.
X
  • Filter
  • Time
  • Show
Clear All
new posts

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

    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





    Code:
    * 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

  • #2
    Jala:
    a more advisable approach would imply -ipolate-:
    Code:
    . 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 |
         +-------------------------------------------------------------------------------------+
    
    .
    Kind regards,
    Carlo
    (Stata 19.0)

    Comment


    • #3
      Dear Carlo Lazzaro

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

      Jala

      Comment

      Working...
      X