Announcement

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

    Arranging data using reshape

    Dear all,
    I have district level monthly rainfall data from 1980 to 2020. I want to arrange it in a panel where the distrcit code is the panel variable year is the time variable followed by monthly rainfall as variables. I believe that the reshape command can do this. However I am not sure how.

    An example of my data is below.

    Code:
    * Example generated by -dataex-. To install: ssc install dataex
clear
input double District float(X1980_01_0 X1980_02_0 X1980_03_0 X1981_01_0 X1981_02_0 X1981_03_0 X1981_04_0 X1981_05_0 X1981_06_0 X1981_07_0 X1981_08_0 X1981_09_0 X1981_10_0 X1981_11_0 X1981_12_0)
   1 1.555923 54.6696  49.2945   43.4244  42.0099   90.7386  268.1847  349.092 158.7042    307.65 223.9824  161.5332  10.53786 .1414476  48.2337
   2 1.555923 54.6696  49.2945   43.4244  42.0099   90.7386  268.1847  349.092 158.7042    307.65 223.9824  161.5332  10.53786 .1414476  48.2337
   3 1.555923 54.6696  49.2945   43.4244  42.0099   90.7386  268.1847  349.092 158.7042    307.65 223.9824  161.5332  10.53786 .1414476  48.2337
   4 1.555923 54.6696  49.2945   43.4244  42.0099   90.7386  268.1847  349.092 158.7042    307.65 223.9824  161.5332  10.53786 .1414476  48.2337
   5 1.555923 54.6696  49.2945   43.4244  42.0099   90.7386  268.1847  349.092 158.7042    307.65 223.9824  161.5332  10.53786 .1414476  48.2337
   6 1.555923 54.6696  49.2945   43.4244  42.0099   90.7386  268.1847  349.092 158.7042    307.65 223.9824  161.5332  10.53786 .1414476  48.2337
   7 1.555923 54.6696  49.2945   43.4244  42.0099   90.7386  268.1847  349.092 158.7042    307.65 223.9824  161.5332  10.53786 .1414476  48.2337
   8 1.555923 54.6696  49.2945   43.4244  42.0099   90.7386  268.1847  349.092 158.7042    307.65 223.9824  161.5332  10.53786 .1414476  48.2337
   9 1.555923 54.6696  49.2945   43.4244  42.0099   90.7386  268.1847  349.092 158.7042    307.65 223.9824  161.5332  10.53786 .1414476  48.2337
10.1 1.555923 54.6696  49.2945   43.4244  42.0099   90.7386  268.1847  349.092 158.7042    307.65 223.9824  161.5332  10.53786 .1414476  48.2337
10.2 1.555923 54.6696  49.2945   43.4244  42.0099   90.7386  268.1847  349.092 158.7042    307.65 223.9824  161.5332  10.53786 .1414476  48.2337
  11 1.555923 54.6696  49.2945   43.4244  42.0099   90.7386  268.1847  349.092 158.7042    307.65 223.9824  161.5332  10.53786 .1414476  48.2337
  12 1.555923 54.6696  49.2945   43.4244  42.0099   90.7386  268.1847  349.092 158.7042    307.65 223.9824  161.5332  10.53786 .1414476  48.2337
  13 1.555923 54.6696  49.2945   43.4244  42.0099   90.7386  268.1847  349.092 158.7042    307.65 223.9824  161.5332  10.53786 .1414476  48.2337
  14 1.555923 54.6696  49.2945   43.4244  42.0099   90.7386  268.1847  349.092 158.7042    307.65 223.9824  161.5332  10.53786 .1414476  48.2337
  15 1.555923 54.6696  49.2945   43.4244  42.0099   90.7386  268.1847  349.092 158.7042    307.65 223.9824  161.5332  10.53786 .1414476  48.2337
16.1 1.555923 54.6696  49.2945   43.4244  42.0099   90.7386  268.1847  349.092 158.7042    307.65 223.9824  161.5332  10.53786 .1414476  48.2337
16.2 1.555923 54.6696  49.2945   43.4244  42.0099   90.7386  268.1847  349.092 158.7042    307.65 223.9824  161.5332  10.53786 .1414476  48.2337
  17 1.555923 54.6696  49.2945   43.4244  42.0099   90.7386  268.1847  349.092 158.7042    307.65 223.9824  161.5332  10.53786 .1414476  48.2337
18.1 1.555923 54.6696  49.2945   43.4244  42.0099   90.7386  268.1847  349.092 158.7042    307.65 223.9824  161.5332  10.53786 .1414476  48.2337
18.2 1.555923 54.6696  49.2945   43.4244  42.0099   90.7386  268.1847  349.092 158.7042    307.65 223.9824  161.5332  10.53786 .1414476  48.2337
  19 1.555923 54.6696  49.2945   43.4244  42.0099   90.7386  268.1847  349.092 158.7042    307.65 223.9824  161.5332  10.53786 .1414476  48.2337
  20 1.555923 54.6696  49.2945   43.4244  42.0099   90.7386  268.1847  349.092 158.7042    307.65 223.9824  161.5332  10.53786 .1414476  48.2337
  21 1.555923 54.6696  49.2945   43.4244  42.0099   90.7386  268.1847  349.092 158.7042    307.65 223.9824  161.5332  10.53786 .1414476  48.2337
  22 1.555923 54.6696  49.2945   43.4244  42.0099   90.7386  268.1847  349.092 158.7042    307.65 223.9824  161.5332  10.53786 .1414476  48.2337
  23 1.343751 64.5708  49.5066 35.856903  46.7484   78.1497  250.9989    339.9 150.0759 288.76532 229.2867  149.5809   5.23356 .0707238  51.5577
  24 1.555923 54.6696  49.2945   43.4244  42.0099   90.7386  268.1847  349.092 158.7042    307.65 223.9824  161.5332  10.53786 .1414476  48.2337
  25 1.555923 54.6696  49.2945   43.4244  42.0099   90.7386  268.1847  349.092 158.7042    307.65 223.9824  161.5332  10.53786 .1414476  48.2337
  26 1.555923 54.6696  49.2945   43.4244  42.0099   90.7386  268.1847  349.092 158.7042    307.65 223.9824  161.5332  10.53786 .1414476  48.2337
  27 1.343751 64.5708  49.5066 35.856903  46.7484   78.1497  250.9989    339.9 150.0759 288.76532 229.2867  149.5809   5.23356 .0707238  51.5577
  28 1.555923 54.6696  49.2945   43.4244  42.0099   90.7386  268.1847  349.092 158.7042    307.65 223.9824  161.5332  10.53786 .1414476  48.2337
  29 1.555923 54.6696  49.2945   43.4244  42.0099   90.7386  268.1847  349.092 158.7042    307.65 223.9824  161.5332  10.53786 .1414476  48.2337
30.1 1.343751 64.5708  49.5066 35.856903  46.7484   78.1497  250.9989    339.9 150.0759 288.76532 229.2867  149.5809   5.23356 .0707238  51.5577
30.2 1.555923 54.6696  49.2945   43.4244  42.0099   90.7386  268.1847  349.092 158.7042    307.65 223.9824  161.5332  10.53786 .1414476  48.2337
  31 1.555923 54.6696  49.2945   43.4244  42.0099   90.7386  268.1847  349.092 158.7042    307.65 223.9824  161.5332  10.53786 .1414476  48.2337
  32 1.555923 54.6696  49.2945   43.4244  42.0099   90.7386  268.1847  349.092 158.7042    307.65 223.9824  161.5332  10.53786 .1414476  48.2337
  33 1.555923 54.6696  49.2945   43.4244  42.0099   90.7386  268.1847  349.092 158.7042    307.65 223.9824  161.5332  10.53786 .1414476  48.2337
  34 1.555923 54.6696  49.2945   43.4244  42.0099   90.7386  268.1847  349.092 158.7042    307.65 223.9824  161.5332  10.53786 .1414476  48.2337
  35 1.555923 54.6696  49.2945   43.4244  42.0099   90.7386  268.1847  349.092 158.7042    307.65 223.9824  161.5332  10.53786 .1414476  48.2337
  36 1.555923 54.6696  49.2945   43.4244  42.0099   90.7386  268.1847  349.092 158.7042    307.65 223.9824  161.5332  10.53786 .1414476  48.2337
  37 1.555923 54.6696  49.2945   43.4244  42.0099   90.7386  268.1847  349.092 158.7042    307.65 223.9824  161.5332  10.53786 .1414476  48.2337
  38 1.343751 64.5708  49.5066 35.856903  46.7484   78.1497  250.9989    339.9 150.0759 288.76532 229.2867  149.5809   5.23356 .0707238  51.5577
  39 1.555923 54.6696  49.2945   43.4244  42.0099   90.7386  268.1847  349.092 158.7042    307.65 223.9824  161.5332  10.53786 .1414476  48.2337
  40 1.555923 54.6696  49.2945   43.4244  42.0099   90.7386  268.1847  349.092 158.7042    307.65 223.9824  161.5332  10.53786 .1414476  48.2337
  41 1.555923 53.2551  51.7698  27.51156  36.1398  120.3012   330.138  350.649 205.8063   338.838 228.2964  195.8343  13.72041 .1414476  44.4852
  42 1.555923 53.2551  51.7698  27.51156  36.1398  120.3012   330.138  350.649 205.8063   338.838 228.2964  195.8343  13.72041 .1414476  44.4852
  43 1.555923 53.2551  51.7698  27.51156  36.1398  120.3012   330.138  350.649 205.8063   338.838 228.2964  195.8343  13.72041 .1414476  44.4852
  44 1.555923 53.2551  51.7698  27.51156  36.1398  120.3012   330.138  350.649 205.8063   338.838 228.2964  195.8343  13.72041 .1414476  44.4852
  45 1.555923 53.2551  51.7698  27.51156  36.1398  120.3012   330.138  350.649 205.8063   338.838 228.2964  195.8343  13.72041 .1414476  44.4852
  46 1.555923 53.2551  51.7698  27.51156  36.1398  120.3012   330.138  350.649 205.8063   338.838 228.2964  195.8343  13.72041 .1414476  44.4852
  47 1.555923 53.2551  51.7698  27.51156  36.1398  120.3012   330.138  350.649 205.8063   338.838 228.2964  195.8343  13.72041 .1414476  44.4852
  48 1.555923 53.2551  51.7698  27.51156  36.1398  120.3012   330.138  350.649 205.8063   338.838 228.2964  195.8343  13.72041 .1414476  44.4852
  49 1.555923 53.2551  51.7698  27.51156  36.1398  120.3012   330.138  350.649 205.8063   338.838 228.2964  195.8343  13.72041 .1414476  44.4852
  50 1.555923 53.2551  51.7698  27.51156  36.1398  120.3012   330.138  350.649 205.8063   338.838 228.2964  195.8343  13.72041 .1414476  44.4852
  51 1.555923 53.2551  51.7698  27.51156  36.1398  120.3012   330.138  350.649 205.8063   338.838 228.2964  195.8343  13.72041 .1414476  44.4852
  52 1.555923 53.2551  51.7698  27.51156  36.1398  120.3012   330.138  350.649 205.8063   338.838 228.2964  195.8343  13.72041 .1414476  44.4852
  53 1.555923 53.2551  51.7698  27.51156  36.1398  120.3012   330.138  350.649 205.8063   338.838 228.2964  195.8343  13.72041 .1414476  44.4852
  54 1.555923 53.2551  51.7698  27.51156  36.1398  120.3012   330.138  350.649 205.8063   338.838 228.2964  195.8343  13.72041 .1414476  44.4852
  55 1.555923 53.2551  51.7698  27.51156  36.1398  120.3012   330.138  350.649 205.8063   338.838 228.2964  195.8343  13.72041 .1414476  44.4852
  56 1.555923 53.2551  51.7698  27.51156  36.1398  120.3012   330.138  350.649 205.8063   338.838 228.2964  195.8343  13.72041 .1414476  44.4852
  57 1.555923 53.2551  51.7698  27.51156  36.1398  120.3012   330.138  350.649 205.8063   338.838 228.2964  195.8343  13.72041 .1414476  44.4852
  58 1.555923 53.2551  51.7698  27.51156  36.1398  120.3012   330.138  350.649 205.8063   338.838 228.2964  195.8343  13.72041 .1414476  44.4852
  59 1.555923 53.2551  51.7698  27.51156  36.1398  120.3012   330.138  350.649 205.8063   338.838 228.2964  195.8343  13.72041 .1414476  44.4852
  60 1.555923 53.2551  51.7698  27.51156  36.1398  120.3012   330.138  350.649 205.8063   338.838 228.2964  195.8343  13.72041 .1414476  44.4852
  61 1.980267 47.1021  56.7912  18.67107   43.566 139.96231 296.82782  337.566 221.2947   400.227 279.4296  226.3161 18.600359 .1414476  44.6973
  62 1.980267 47.1021  56.7912  18.67107   43.566 139.96231 296.82782  337.566 221.2947   400.227 279.4296  226.3161 18.600359 .1414476  44.6973
  63 1.980267 47.1021  56.7912  18.67107   43.566 139.96231 296.82782  337.566 221.2947   400.227 279.4296  226.3161 18.600359 .1414476  44.6973
  64 1.980267 47.1021  56.7912  18.67107   43.566 139.96231 296.82782  337.566 221.2947   400.227 279.4296  226.3161 18.600359 .1414476  44.6973
  65 1.980267 47.1021  56.7912  18.67107   43.566 139.96231 296.82782  337.566 221.2947   400.227 279.4296  226.3161 18.600359 .1414476  44.6973
  66 1.980267 47.1021  56.7912  18.67107   43.566 139.96231 296.82782  337.566 221.2947   400.227 279.4296  226.3161 18.600359 .1414476  44.6973
  67 1.980267 47.1021  56.7912  18.67107   43.566 139.96231 296.82782  337.566 221.2947   400.227 279.4296  226.3161 18.600359 .1414476  44.6973
  68 1.980267 47.1021  56.7912  18.67107   43.566 139.96231 296.82782  337.566 221.2947   400.227 279.4296  226.3161 18.600359 .1414476  44.6973
  69 1.980267 47.1021  56.7912  18.67107   43.566 139.96231 296.82782  337.566 221.2947   400.227 279.4296  226.3161 18.600359 .1414476  44.6973
  70 1.980267 47.1021  56.7912  18.67107   43.566 139.96231 296.82782  337.566 221.2947   400.227 279.4296  226.3161 18.600359 .1414476  44.6973
  71 1.980267 47.1021  56.7912  18.67107   43.566 139.96231 296.82782  337.566 221.2947   400.227 279.4296  226.3161 18.600359 .1414476  44.6973
  72 1.980267 47.1021  56.7912  18.67107   43.566 139.96231 296.82782  337.566 221.2947   400.227 279.4296  226.3161 18.600359 .1414476  44.6973
  73 1.980267 47.1021  56.7912  18.67107   43.566 139.96231 296.82782  337.566 221.2947   400.227 279.4296  226.3161 18.600359 .1414476  44.6973
  74 1.980267 47.1021  56.7912  18.67107   43.566 139.96231 296.82782  337.566 221.2947   400.227 279.4296  226.3161 18.600359 .1414476  44.6973
  75 1.980267 47.1021  56.7912  18.67107   43.566 139.96231 296.82782  337.566 221.2947   400.227 279.4296  226.3161 18.600359 .1414476  44.6973
  76 1.980267 47.1021  56.7912  18.67107   43.566 139.96231 296.82782  337.566 221.2947   400.227 279.4296  226.3161 18.600359 .1414476  44.6973
  77 1.980267 47.1021  56.7912  18.67107   43.566 139.96231 296.82782  337.566 221.2947   400.227 279.4296  226.3161 18.600359 .1414476  44.6973
  78 1.980267 47.1021  56.7912  18.67107   43.566 139.96231 296.82782  337.566 221.2947   400.227 279.4296  226.3161 18.600359 .1414476  44.6973
  79 1.980267 47.1021  56.7912  18.67107   43.566 139.96231 296.82782  337.566 221.2947   400.227 279.4296  226.3161 18.600359 .1414476  44.6973
  80 1.980267 47.1021  56.7912  18.67107   43.566 139.96231 296.82782  337.566 221.2947   400.227 279.4296  226.3161 18.600359 .1414476  44.6973
  81   .91941 7.21383 19.94412  12.16449 10.89147  18.24675  282.2586 126.8079  323.844   370.593 212.1714 285.44128  10.82073  86.7075 18.45891
  82   .91941 7.21383 19.94412  12.16449 10.89147  18.24675  282.2586 126.8079  323.844   370.593 212.1714 285.44128  10.82073  86.7075 18.45891
  83   .91941 7.21383 19.94412  12.16449 10.89147  18.24675  282.2586 126.8079  323.844   370.593 212.1714 285.44128  10.82073  86.7075 18.45891
  84   .91941 7.21383 19.94412  12.16449 10.89147  18.24675  282.2586 126.8079  323.844   370.593 212.1714 285.44128  10.82073  86.7075 18.45891
  85   .91941 7.21383 19.94412  12.16449 10.89147  18.24675  282.2586 126.8079  323.844   370.593 212.1714 285.44128  10.82073  86.7075 18.45891
  86   .91941 7.21383 19.94412  12.16449 10.89147  18.24675  282.2586 126.8079  323.844   370.593 212.1714 285.44128  10.82073  86.7075 18.45891
  87   .91941 7.21383 19.94412  12.16449 10.89147  18.24675  282.2586 126.8079  323.844   370.593 212.1714 285.44128  10.82073  86.7075 18.45891
  88   .91941 7.21383 19.94412  12.16449 10.89147  18.24675  282.2586 126.8079  323.844   370.593 212.1714 285.44128  10.82073  86.7075 18.45891
  89   .91941 7.21383 19.94412  12.16449 10.89147  18.24675  282.2586 126.8079  323.844   370.593 212.1714 285.44128  10.82073  86.7075 18.45891
  90   .91941 7.21383 19.94412  12.16449 10.89147  18.24675  282.2586 126.8079  323.844   370.593 212.1714 285.44128  10.82073  86.7075 18.45891
  91   .91941 7.21383 19.94412  12.16449 10.89147  18.24675  282.2586 126.8079  323.844   370.593 212.1714 285.44128  10.82073  86.7075 18.45891
  92   .91941 7.21383 19.94412  12.16449 10.89147  18.24675  282.2586 126.8079  323.844   370.593 212.1714 285.44128  10.82073  86.7075 18.45891
  93   .91941 7.21383 19.94412  12.16449 10.89147  18.24675  282.2586 126.8079  323.844   370.593 212.1714 285.44128  10.82073  86.7075 18.45891
  94   .91941 7.21383 19.94412  12.16449 10.89147  18.24675  282.2586 126.8079  323.844   370.593 212.1714 285.44128  10.82073  86.7075 18.45891
  95   .91941 7.21383 19.94412  12.16449 10.89147  18.24675  282.2586 126.8079  323.844   370.593 212.1714 285.44128  10.82073  86.7075 18.45891
  96   .91941 7.21383 19.94412  12.16449 10.89147  18.24675  282.2586 126.8079  323.844   370.593 212.1714 285.44128  10.82073  86.7075 18.45891
end

    Any suggestions would be welcome.
    Tags: None
  • #2
    This cannot be done with just a single -reshape- command. It can be done with a series of -reshape long- commands. But there is actually a simpler way still:

    Code:
    reshape long X, i(District) j(_j) string
split _j, parse("_") destring gen(t)
rename t1 year
rename t2 month
assert t3 == 0
drop t3 _j
    Added: Looking beyond just this transformation of the data, to make good use of the month and year in Stata you will probably need to combine them into a Stata internal format monthly date:
    Code:
    gen int mdate = ym(year, month)
format mdate %tm
    Note on the first code shown here: All of the X* variables in the example data end in _0. This results in the code creating a variable t3 which is always 0. Since that is an uninformative waste of space, the code drops it at the end. Perhaps in your full data set, the ending of the X* variables is not always _0. In that case, remove the -assert t3 == 0- command, and change -drop t3 _j- to -drop _j- so that the information in that suffix will be retained.
    Last edited by Clyde Schechter; 16 Jun 2022, 12:51.

    Comment

    • #3
      Dear Clyde Schechter the code worked very well. Thank you.
      I added the below to get monthly rainfall as separate variables.

      Code:
      reshape wide X, i(District year ) j(month)

      Comment

      Previous Next
      Working...
      X