How to generate monthly time series data based on yearly time series data?

[Joonho Kim]

Hello,

I am wondering how to generate monthly time series data. For example, my current data set is constructed as follow

ccode1 ccode2 year
750 770 1947
750 770 1948
750 770 1949
750 770 1950
750 770 1951
750 770 1952
750 770 1953
750 770 1954
750 770 1955
750 770 1956
. . .
. . .
. . .
. . .
750 770 2003
750 770 2004
750 770 2005
750 770 2006
750 770 2007
750 770 2008
750 770 2009
750 770 2010
750 770 2011

From this data set, what I want to generate is as follow

ccode1 ccode2 year month date
750 770 1947 1 01jan1947
750 770 1947 2 01feb1947
750 770 1947 3 01mar1947
750 770 1947 4 01april1947
. . . . .
. . . . .
750 770 1947 11 01nov1947
750 770 1947 12 01dec1947
750 770 1948 1 01jan1948
750 770 1948 2 01feb1948
750 770 1948 3 01mar1948
750 770 1948 4 01april1948
. . . . .
. . . . .
750 770 1948 11 01nov1948
750 770 1948 12 01dec1948
. . . . .
. . . . .
750 770 2011 1 01jan2011
750 770 2011 2 01feb2011
. . . . .
. . . . .
750 770 2011 11 01nov2011
750 770 2011 12 01dec2011


Is there useful way to construct monthly time series data like the above except for making using of excel?

Thank you

[Nick Cox]

What's excel? or even Excel?

You can do this.

clear 
input ccode1 ccode2 year
750 770 1947
750 770 1948
750 770 1949
750 770 1950
end 
expand 12 
bysort year : gen month = _n 
gen mdate = ym(year, month) 
format mdate %tm 
sort mdate 

list, sepby(year) 

    +------------------------------------------+
     | ccode1   ccode2   year   month     mdate |
     |------------------------------------------|
  1. |    750      770   1947       1    1947m1 |
  2. |    750      770   1947       2    1947m2 |
  3. |    750      770   1947       3    1947m3 |
  4. |    750      770   1947       4    1947m4 |
  5. |    750      770   1947       5    1947m5 |
  6. |    750      770   1947       6    1947m6 |
  7. |    750      770   1947       7    1947m7 |
  8. |    750      770   1947       8    1947m8 |
  9. |    750      770   1947       9    1947m9 |
 10. |    750      770   1947      10   1947m10 |
 11. |    750      770   1947      11   1947m11 |
 12. |    750      770   1947      12   1947m12 |
     |------------------------------------------|
 13. |    750      770   1948       1    1948m1 |
 14. |    750      770   1948       2    1948m2 |
 15. |    750      770   1948       3    1948m3 |
 16. |    750      770   1948       4    1948m4 |
 17. |    750      770   1948       5    1948m5 |
 18. |    750      770   1948       6    1948m6 |
 19. |    750      770   1948       7    1948m7 |
 20. |    750      770   1948       8    1948m8 |
 21. |    750      770   1948       9    1948m9 |
 22. |    750      770   1948      10   1948m10 |
 23. |    750      770   1948      11   1948m11 |
 24. |    750      770   1948      12   1948m12 |
     |------------------------------------------|
 25. |    750      770   1949       1    1949m1 |
 26. |    750      770   1949       2    1949m2 |
 27. |    750      770   1949       3    1949m3 |
 28. |    750      770   1949       4    1949m4 |
 29. |    750      770   1949       5    1949m5 |
 30. |    750      770   1949       6    1949m6 |
 31. |    750      770   1949       7    1949m7 |
 32. |    750      770   1949       8    1949m8 |
 33. |    750      770   1949       9    1949m9 |
 34. |    750      770   1949      10   1949m10 |
 35. |    750      770   1949      11   1949m11 |
 36. |    750      770   1949      12   1949m12 |
     |------------------------------------------|
 37. |    750      770   1950       1    1950m1 |
 38. |    750      770   1950       2    1950m2 |
 39. |    750      770   1950       3    1950m3 |
 40. |    750      770   1950       4    1950m4 |
 41. |    750      770   1950       5    1950m5 |
 42. |    750      770   1950       6    1950m6 |
 43. |    750      770   1950       7    1950m7 |
 44. |    750      770   1950       8    1950m8 |
 45. |    750      770   1950       9    1950m9 |
 46. |    750      770   1950      10   1950m10 |
 47. |    750      770   1950      11   1950m11 |
 48. |    750      770   1950      12   1950m12 |
     +------------------------------------------+

But

1. If you want to merge with another dataset that is monthly, then just do that: merge 1:m -- or m:1 as the case may be.

2. Daily dates that are the first of each month will just prove awkward as they are variously 28, 29, 30, 31 days apart. To be useful, you'd need to convert them to monthly dates. You might as well do that at the outset.

[Joonho Kim]

It is amazing. I really appreciate for your answer and suggestion. I meant Excel.

Yes, as you guess correctly, I am trying to merge with another monthly dataset.

But, the problem is more complicated because I plan to expand my dataset from single dyad (ccode1:750 - ccode2:770) to multiple dyads.
(This is way beyond my original question, but if you give any tips and suggestions, it would be appreciated.)


For example, I have a total 319 country dyads as below

750 (India) - 770 (Pakistan)
750 (India) - 710 (China)
. .
. .
2 (US) - 20 (Canada)
2 (US) - 79 (Mexico)

My purpose is to generate a monthly dyad for all 319 dyads and to merge with another monthly dyad dataset. The another monthly dyad dataset includes a information that I need to merge, but
it has only a sporadic monthly data like this,

ccode1 ccode2 conflict_onset_month conflict_onset_year date
750 770 1 1947 01sep1947
750 770 12 1947 01dec1947
2 20 9 1950 01sep1950
2 79 4 1960 01apr1960
. . . . .
. . . . .

This is reason why I try to build a full balanced monthly dyad dataset for all 319 dyads to merge this dataset by using "merge 1:m or m:1".



If I follow your way of generating monthly dataset for a single dyad from 1947 to 2011, I can do like this,


input ccode1 ccode2 year
750 770 1947
750 770 1948
750 770 1949
750 770 1950
. . . . . .
750 770 2011
end

After this, I can do what you suggest to generate monthly dataset.

But, for 319 dyads, should I type all different ccode1, ccode2, and year by using "input" command?
It seems labor intensive, but if I have to construct a monthly dyads for 319, is it the only way of doing?

Thank you

[Nick Cox]

I can't see you need to input anything. You surely have the dataset already.

Each observation in a yearly dataset needs to be expanded 12 times.

expand 12

is always needed therefore.

In #2 all I needed for the simple data example was

bysort year: gen month = _n

For the full dataset you evidently need

bysort ccode1 ccode2 year:  gen month = _n

Once you have year and month variables, the monthly date follows.

All that said, it's usually a lot easier to merge.

[Joonho Kim]

That works perfectly. Thank you a lot!