Announcement

Collapse
No announcement yet.
X
Collapse
  • Page of 1
  • Filter
  • Time
  • Show
Clear All
new posts
  • #1

    Data Configuration Problem - Data Range to Time Series Help

    Hello Statalist,

    I have what seems to be probably an easy issue to solve, but I can't figure out how to do it efficiently in stata code.

    I am currently working with a dataset of ethnic group power relationships. The unit of analysis in the dataset is the ethnic group. However, I need to integrate parts of the data into my current dataset that has as its unit of analysis countries.

    The ethnic group data are currently formatted with a date range instead of individual lines that would make it a panel/time series dataset. So this is an example of the data and its current format:


    gwid statename from to group groupid gwgroupid umbrella size status reg_aut
    2 United States of America 1946 1965 Whites 1000 201000 0.69099998 MONOPOLY
    2 United States of America 1946 1965 African Americans 3000 203000 0.124 DISCRIMINATED FALSE
    2 United States of America 1946 1965 American Indians 5000 205000 0.0078 POWERLESS TRUE

    So the key area for my interest is the from and to columns. I need to be able to transform the data into time series for each of these groups. This is what I'd like to do with the data

    gwid statename year group groupid gwgroupid umbrella size status reg_aut
    2 United States of America 1946 Whites 1000 201000 0.69099998 MONOPOLY
    2 United States of America 1947 Whites 1000 201000 0.69099998 MONOPOLY
    2 United States of America 1948 Whites 1000 201000 0.69099998 MONOPOLY
    2 United States of America 1949 Whites 1000 201000 0.69099998 MONOPOLY
    2 United States of America 1950 Whites 1000 201000 0.69099998 MONOPOLY
    2 United States of America 1951 Whites 1000 201000 0.69099998 MONOPOLY
    2 United States of America 1952 Whites 1000 201000 0.69099998 MONOPOLY
    2 United States of America 1953 Whites 1000 201000 0.69099998 MONOPOLY
    2 United States of America 1954 Whites 1000 201000 0.69099998 MONOPOLY
    2 United States of America 1955 Whites 1000 201000 0.69099998 MONOPOLY
    2 United States of America 1956 Whites 1000 201000 0.69099998 MONOPOLY
    2 United States of America 1957 Whites 1000 201000 0.69099998 MONOPOLY
    2 United States of America 1958 Whites 1000 201000 0.69099998 MONOPOLY
    2 United States of America 1959 Whites 1000 201000 0.69099998 MONOPOLY
    2 United States of America 1960 Whites 1000 201000 0.69099998 MONOPOLY
    2 United States of America 1961 Whites 1000 201000 0.69099998 MONOPOLY
    2 United States of America 1962 Whites 1000 201000 0.69099998 MONOPOLY
    2 United States of America 1963 Whites 1000 201000 0.69099998 MONOPOLY
    2 United States of America 1964 Whites 1000 201000 0.69099998 MONOPOLY
    2 United States of America 1965 Whites 1000 201000 0.69099998 MONOPOLY
    2 United States of America 1946 African Americans 3000 203000 0.124 DISCRIMINATED FALSE
    2 United States of America 1947 African Americans 3000 203000 0.124 DISCRIMINATED FALSE
    2 United States of America 1948 African Americans 3000 203000 0.124 DISCRIMINATED FALSE
    2 United States of America 1949 African Americans 3000 203000 0.124 DISCRIMINATED FALSE
    2 United States of America 1950 African Americans 3000 203000 0.124 DISCRIMINATED FALSE
    2 United States of America 1951 African Americans 3000 203000 0.124 DISCRIMINATED FALSE
    2 United States of America 1952 African Americans 3000 203000 0.124 DISCRIMINATED FALSE
    2 United States of America 1953 African Americans 3000 203000 0.124 DISCRIMINATED FALSE
    2 United States of America 1954 African Americans 3000 203000 0.124 DISCRIMINATED FALSE
    2 United States of America 1955 African Americans 3000 203000 0.124 DISCRIMINATED FALSE
    2 United States of America 1956 African Americans 3000 203000 0.124 DISCRIMINATED FALSE
    2 United States of America 1957 African Americans 3000 203000 0.124 DISCRIMINATED FALSE
    2 United States of America 1958 African Americans 3000 203000 0.124 DISCRIMINATED FALSE
    2 United States of America 1959 African Americans 3000 203000 0.124 DISCRIMINATED FALSE
    2 United States of America 1960 African Americans 3000 203000 0.124 DISCRIMINATED FALSE
    2 United States of America 1961 African Americans 3000 203000 0.124 DISCRIMINATED FALSE
    2 United States of America 1962 African Americans 3000 203000 0.124 DISCRIMINATED FALSE
    2 United States of America 1963 African Americans 3000 203000 0.124 DISCRIMINATED FALSE
    2 United States of America 1964 African Americans 3000 203000 0.124 DISCRIMINATED FALSE
    2 United States of America 1965 African Americans 3000 203000 0.124 DISCRIMINATED FALSE
    2 United States of America 1946 American Indians 5000 205000 0.0078 POWERLESS TRUE
    2 United States of America 1947 American Indians 5000 205000 0.0078 POWERLESS TRUE
    2 United States of America 1948 American Indians 5000 205000 0.0078 POWERLESS TRUE
    2 United States of America 1949 American Indians 5000 205000 0.0078 POWERLESS TRUE
    2 United States of America 1950 American Indians 5000 205000 0.0078 POWERLESS TRUE
    2 United States of America 1951 American Indians 5000 205000 0.0078 POWERLESS TRUE
    2 United States of America 1952 American Indians 5000 205000 0.0078 POWERLESS TRUE
    2 United States of America 1953 American Indians 5000 205000 0.0078 POWERLESS TRUE
    2 United States of America 1954 American Indians 5000 205000 0.0078 POWERLESS TRUE
    2 United States of America 1955 American Indians 5000 205000 0.0078 POWERLESS TRUE
    2 United States of America 1956 American Indians 5000 205000 0.0078 POWERLESS TRUE
    2 United States of America 1957 American Indians 5000 205000 0.0078 POWERLESS TRUE
    2 United States of America 1958 American Indians 5000 205000 0.0078 POWERLESS TRUE
    2 United States of America 1959 American Indians 5000 205000 0.0078 POWERLESS TRUE
    2 United States of America 1960 American Indians 5000 205000 0.0078 POWERLESS TRUE
    2 United States of America 1961 American Indians 5000 205000 0.0078 POWERLESS TRUE
    2 United States of America 1962 American Indians 5000 205000 0.0078 POWERLESS TRUE
    2 United States of America 1963 American Indians 5000 205000 0.0078 POWERLESS TRUE
    2 United States of America 1964 American Indians 5000 205000 0.0078 POWERLESS TRUE
    2 United States of America 1965 American Indians 5000 205000 0.0078 POWERLESS TRUE

    Of course, this was relatively easy to do in a spreadsheet where the data currently reside. However, there are over 8000 groups that I need to do this for.

    I was wondering if anyone might have an idea of how I could use stata code to transform the date ranges to yearly time series data. The data will be kept in the same dataset, so I'm not breaking out the individual panels into their own datasets. The observations will, of course, increase substantially, but it will make my integration of the data into my primary dataset much easier. I've tried to think how I can do this for a few days, but I've yet to come up with any ideas on even how to approach this problem as I've never encountered this type of transformation before.

    Thanks for any suggestions. I look forward to your ideas!

    Best,
    Bob

    Tags: None
  • #2
    Code:
    * Example generated by -dataex-. For more info, type help dataex
clear
input byte gwid str24 statename int(from to) str17 group int groupid long gwgroupid byte umbrella float size str13 status str5 reg_aut
2 "United States of America" 1946 1965 "Whites"            1000 201000 .  .691 "MONOPOLY"      ""     
2 "United States of America" 1946 1965 "African Americans" 3000 203000 .  .124 "DISCRIMINATED" "FALSE"
2 "United States of America" 1946 1965 "American Indians"  5000 205000 . .0078 "POWERLESS"     "TRUE" 
end

gen duration= to-from+1
expand duration
bys gwid statename group: gen year= from+_n-1, after(to)
drop from to duration
    Res.:

    Code:
    . l, sepby(gwid statename group)

     +------------------------------------------------------------------------------------------------------------------------------+
     | gwid                  statename   year               group   groupid   gwgrou~d   umbrella    size          status   reg_aut |
     |------------------------------------------------------------------------------------------------------------------------------|
  1. |    2   United States of America   1946   African Americans      3000     203000          .    .124   DISCRIMINATED     FALSE |
  2. |    2   United States of America   1947   African Americans      3000     203000          .    .124   DISCRIMINATED     FALSE |
  3. |    2   United States of America   1948   African Americans      3000     203000          .    .124   DISCRIMINATED     FALSE |
  4. |    2   United States of America   1949   African Americans      3000     203000          .    .124   DISCRIMINATED     FALSE |
  5. |    2   United States of America   1950   African Americans      3000     203000          .    .124   DISCRIMINATED     FALSE |
  6. |    2   United States of America   1951   African Americans      3000     203000          .    .124   DISCRIMINATED     FALSE |
  7. |    2   United States of America   1952   African Americans      3000     203000          .    .124   DISCRIMINATED     FALSE |
  8. |    2   United States of America   1953   African Americans      3000     203000          .    .124   DISCRIMINATED     FALSE |
  9. |    2   United States of America   1954   African Americans      3000     203000          .    .124   DISCRIMINATED     FALSE |
 10. |    2   United States of America   1955   African Americans      3000     203000          .    .124   DISCRIMINATED     FALSE |
 11. |    2   United States of America   1956   African Americans      3000     203000          .    .124   DISCRIMINATED     FALSE |
 12. |    2   United States of America   1957   African Americans      3000     203000          .    .124   DISCRIMINATED     FALSE |
 13. |    2   United States of America   1958   African Americans      3000     203000          .    .124   DISCRIMINATED     FALSE |
 14. |    2   United States of America   1959   African Americans      3000     203000          .    .124   DISCRIMINATED     FALSE |
 15. |    2   United States of America   1960   African Americans      3000     203000          .    .124   DISCRIMINATED     FALSE |
 16. |    2   United States of America   1961   African Americans      3000     203000          .    .124   DISCRIMINATED     FALSE |
 17. |    2   United States of America   1962   African Americans      3000     203000          .    .124   DISCRIMINATED     FALSE |
 18. |    2   United States of America   1963   African Americans      3000     203000          .    .124   DISCRIMINATED     FALSE |
 19. |    2   United States of America   1964   African Americans      3000     203000          .    .124   DISCRIMINATED     FALSE |
 20. |    2   United States of America   1965   African Americans      3000     203000          .    .124   DISCRIMINATED     FALSE |
     |------------------------------------------------------------------------------------------------------------------------------|
 21. |    2   United States of America   1946    American Indians      5000     205000          .   .0078       POWERLESS      TRUE |
 22. |    2   United States of America   1947    American Indians      5000     205000          .   .0078       POWERLESS      TRUE |
 23. |    2   United States of America   1948    American Indians      5000     205000          .   .0078       POWERLESS      TRUE |
 24. |    2   United States of America   1949    American Indians      5000     205000          .   .0078       POWERLESS      TRUE |
 25. |    2   United States of America   1950    American Indians      5000     205000          .   .0078       POWERLESS      TRUE |
 26. |    2   United States of America   1951    American Indians      5000     205000          .   .0078       POWERLESS      TRUE |
 27. |    2   United States of America   1952    American Indians      5000     205000          .   .0078       POWERLESS      TRUE |
 28. |    2   United States of America   1953    American Indians      5000     205000          .   .0078       POWERLESS      TRUE |
 29. |    2   United States of America   1954    American Indians      5000     205000          .   .0078       POWERLESS      TRUE |
 30. |    2   United States of America   1955    American Indians      5000     205000          .   .0078       POWERLESS      TRUE |
 31. |    2   United States of America   1956    American Indians      5000     205000          .   .0078       POWERLESS      TRUE |
 32. |    2   United States of America   1957    American Indians      5000     205000          .   .0078       POWERLESS      TRUE |
 33. |    2   United States of America   1958    American Indians      5000     205000          .   .0078       POWERLESS      TRUE |
 34. |    2   United States of America   1959    American Indians      5000     205000          .   .0078       POWERLESS      TRUE |
 35. |    2   United States of America   1960    American Indians      5000     205000          .   .0078       POWERLESS      TRUE |
 36. |    2   United States of America   1961    American Indians      5000     205000          .   .0078       POWERLESS      TRUE |
 37. |    2   United States of America   1962    American Indians      5000     205000          .   .0078       POWERLESS      TRUE |
 38. |    2   United States of America   1963    American Indians      5000     205000          .   .0078       POWERLESS      TRUE |
 39. |    2   United States of America   1964    American Indians      5000     205000          .   .0078       POWERLESS      TRUE |
 40. |    2   United States of America   1965    American Indians      5000     205000          .   .0078       POWERLESS      TRUE |
     |------------------------------------------------------------------------------------------------------------------------------|
 41. |    2   United States of America   1946              Whites      1000     201000          .    .691        MONOPOLY           |
 42. |    2   United States of America   1947              Whites      1000     201000          .    .691        MONOPOLY           |
 43. |    2   United States of America   1948              Whites      1000     201000          .    .691        MONOPOLY           |
 44. |    2   United States of America   1949              Whites      1000     201000          .    .691        MONOPOLY           |
 45. |    2   United States of America   1950              Whites      1000     201000          .    .691        MONOPOLY           |
 46. |    2   United States of America   1951              Whites      1000     201000          .    .691        MONOPOLY           |
 47. |    2   United States of America   1952              Whites      1000     201000          .    .691        MONOPOLY           |
 48. |    2   United States of America   1953              Whites      1000     201000          .    .691        MONOPOLY           |
 49. |    2   United States of America   1954              Whites      1000     201000          .    .691        MONOPOLY           |
 50. |    2   United States of America   1955              Whites      1000     201000          .    .691        MONOPOLY           |
 51. |    2   United States of America   1956              Whites      1000     201000          .    .691        MONOPOLY           |
 52. |    2   United States of America   1957              Whites      1000     201000          .    .691        MONOPOLY           |
 53. |    2   United States of America   1958              Whites      1000     201000          .    .691        MONOPOLY           |
 54. |    2   United States of America   1959              Whites      1000     201000          .    .691        MONOPOLY           |
 55. |    2   United States of America   1960              Whites      1000     201000          .    .691        MONOPOLY           |
 56. |    2   United States of America   1961              Whites      1000     201000          .    .691        MONOPOLY           |
 57. |    2   United States of America   1962              Whites      1000     201000          .    .691        MONOPOLY           |
 58. |    2   United States of America   1963              Whites      1000     201000          .    .691        MONOPOLY           |
 59. |    2   United States of America   1964              Whites      1000     201000          .    .691        MONOPOLY           |
 60. |    2   United States of America   1965              Whites      1000     201000          .    .691        MONOPOLY           |
     +------------------------------------------------------------------------------------------------------------------------------+

    Comment

    • #3
      Andrew, thanks so much for putting this out there. I will give this a shot later tonight and let you know how it went! Thanks again. - Bob

      Comment

      • #4
        Andrew gives good advice in #2. There is also another approach you can use for this. You can leave your ethnic data as it is. Then you can do this:
        Code:
        use ethnic_data, clear
rangejoin year from to using other_panel_data_set, by(gwid)
        -rangejoin- is written by Robert Picard and is available from SSC. To use it, you must also install -rangestat-, by Robert Picard, Nick Cox, and Roberto Ferrer, also available from SSC.

        Comment

        • #5
          Andrew and Clyde,
          Thanks so much for posting these. Sorry it took so long to get back with the update; I got caught up in deadlines as usual.

          Both solutions worked amazingly well. I had a small glitch with the code Andrew kindly provided as it skipped the initial years in the larger dataset (so, instead of including, for example 1946 it bypassed it and started at 1947). But this was not a big problem and was easily dealt with. Both of you saved me hours of work and sanity. Very much appreciated.

          Best,
          Bob

          Comment

          Previous Next
          Working...
          X