Announcement

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

  • interpolate between two decades using expand

    Hi All,


    I have two unbalanced panel dataset with different variables in different frequencies. The first one has annual data with time periods 1980, 1990, 2000, and 2010, as shown below.

    Code:
    * Example generated by -dataex-. For more info, type help dataex
    clear
    input long fipsmerg int year long totpop float reltrad double adherent
    1001 1980 32259 1 15796
    1001 1980 32259 3    43
    1001 1980 32259 4   200
    1001 1990 34222 3  1025
    1001 1990 34222 1 22361
    1001 1990 34222 4   323
    1001 2000 43671 3  1490
    1001 2000 43671 4   465
    1001 2000 43671 1 26476
    1001 2010 54571 1 38480
    1001 2010 54571 4   856
    1001 2010 54571 3  1766
    end
    label values reltrad reltrad
    label def reltrad 1 "Protestant", modify
    label def reltrad 3 "Catholic", modify
    label def reltrad 4 "Other", modify


    The second has annual data, as shown below. This time range is 1975-2015

    Code:
    * Example generated by -dataex-. For more info, type help dataex
    clear
    input str6 gvkey double fyear
    "001001" 1983
    "001001" 1984
    "001001" 1985
    "001003" 1982
    "001003" 1983
    "001003" 1984
    "001003" 1985
    "001003" 1986
    "001003" 1987
    "001003" 1988
    "001003" 1989
    "001004" 1975
    "001004" 1976
    "001004" 1977
    "001004" 1978
    "001004" 1979
    "001004" 1980
    "001004" 1981
    "001004" 1982
    "001004" 1983
    end

    I first aim to generate years between the decades (linearly interpolate), such that i can merge it with the second dataset.
    Can someone please help me with the code? I aim for something like the following, which I found here .

    Code:
    expand 10
    bys fipsmerg year: replace totpop=. if _n!=_N
    by fipsmerg: gen year_new=_n
    by fipsmerg: ipolate totpop year_new, gen(y_pop) epolate
    by fipsmerg year: replace year_new=_n
    Yet, i need interpolated years, totpop, and adherent, rather than only totpop as is in the code above.

    Thanks in advance!
    Last edited by tom berk; 18 May 2021, 05:41.

  • #2
    What you're copying is about interpolating with quarterly data which is not at all what you want here. Also, your data structure repeats each county and year according to Protestant, Catholic, other which complicates interpolation mightily unless you change the data structure. I can offer code but first please follow our advice and give data examples using dataex as explained in https://www.statalist.org/forums/help#stata

    Comment


    • #3
      Thank you for pointing that out, Nick Cox. I have edited the post accordingly. I furthermore hope that I have been concise, precise, and detailed enough to translate the core of my problem

      Last edited by tom berk; 18 May 2021, 05:52.

      Comment


      • #4
        Thanks. That helps. Here is some technique. Readers should note that this is geometric interpolation based on linear interpolation of logarithms.

        Code:
        * Example generated by -dataex-. For more info, type help dataex
        clear
        input long fipsmerg int year long totpop float reltrad double adherent
        1001 1980 32259 1 15796
        1001 1980 32259 3    43
        1001 1980 32259 4   200
        1001 1990 34222 3  1025
        1001 1990 34222 1 22361
        1001 1990 34222 4   323
        1001 2000 43671 3  1490
        1001 2000 43671 4   465
        1001 2000 43671 1 26476
        1001 2010 54571 1 38480
        1001 2010 54571 4   856
        1001 2010 54571 3  1766
        end
        label values reltrad reltrad
        label def reltrad 1 "Protestant", modify
        label def reltrad 3 "Catholic", modify
        label def reltrad 4 "Other", modify
        
        reshape wide adherent, i(fipsmerg year) j(reltrad)
        expand 10 
        bysort fipsmerg year : replace year = year + _n - 1 
        gen logtotpop = log(totpop) if mod(year, 10) == 0 
        ipolate logtotpop year, by(fipsmerg) gen(ipolated)
        replace ipolated = round(exp(ipolated)) 
        
        list year totpop ipolated 
        
        
            +--------------------------+
             | year   totpop   ipolated |
             |--------------------------|
          1. | 1980    32259      32259 |
          2. | 1981    32259      32450 |
          3. | 1982    32259      32642 |
          4. | 1983    32259      32836 |
          5. | 1984    32259      33030 |
             |--------------------------|
          6. | 1985    32259      33226 |
          7. | 1986    32259      33423 |
          8. | 1987    32259      33621 |
          9. | 1988    32259      33820 |
         10. | 1989    32259      34020 |
             |--------------------------|
         11. | 1990    34222      34222 |
         12. | 1991    34222      35067 |
         13. | 1992    34222      35932 |
         14. | 1993    34222      36819 |
         15. | 1994    34222      37728 |
             |--------------------------|
         16. | 1995    34222      38659 |
         17. | 1996    34222      39613 |
         18. | 1997    34222      40591 |
         19. | 1998    34222      41593 |
         20. | 1999    34222      42619 |
             |--------------------------|
         21. | 2000    43671      43671 |
         22. | 2001    43671      44655 |
         23. | 2002    43671      45661 |
         24. | 2003    43671      46690 |
         25. | 2004    43671      47742 |
             |--------------------------|
         26. | 2005    43671      48818 |
         27. | 2006    43671      49918 |
         28. | 2007    43671      51042 |
         29. | 2008    43671      52193 |
         30. | 2009    43671      53369 |
             |--------------------------|
         31. | 2010    54571      54571 |
         32. | 2011    54571          . |
         33. | 2012    54571          . |
         34. | 2013    54571          . |
         35. | 2014    54571          . |
             |--------------------------|
         36. | 2015    54571          . |
         37. | 2016    54571          . |
         38. | 2017    54571          . |
         39. | 2018    54571          . |
         40. | 2019    54571          . |
             +--------------------------+
        .

        Comment


        • #5
          That works! Thank you, Nick Cox

          Comment

          Working...
          X