Create a column from a different column and combine multiple datasets

[Jaeyong Yoo]

I have 50 U.S. state daily max temperature datasets in the CSV form from 1990 to 2019.

This is a part of one dataset from Georgia.

Date	TMax at Georgia, 1990-01-01 to 2019-12-31
1/1/1990	32
1/2/1990	42
1/3/1990	50
...
...
...
12/29/2019	56
12/30/2019	35
12/31/2019	35

In the first row, there are the name of variables. I want to create a column for a state variable and combine all datasets for 50 states.

Date	TMax	State
1/1/1990	32	Georgia
1/2/1990	42	Georgia
...
12/31/2019	35	Georgia
1/1/1990	32	Idaho
1/2/1990	26	Idaho
...
12/31/2019	30	Idaho
....

All dataset files are located in the same folder.
Please help me solve this problem.

Thank you!

[Ken Chui]

Should not be too hard but the question is formatted in a way that it needs a lot of back-and-forth follow-up, this likely would lower the chance of it being answered. It'd be great if you can use -dataex- to provide at least two files of two different states. That way users here can set up the data, understand how the variables are named, and be able to draft and test the code. It's very easy, suppose the variable are date and tmax, to paste 50 cases, just use:

dataex date tmax, count(50)

Do that once for state 1, and then once more for state 2. Without that, it's kinda impossible to give concrete recommendation.

[Jaeyong Yoo]

Thanks, Ken.

This is for Arizona.

* Example generated by -dataex-. For more info, type help dataex
clear
input str10 datetime float maxtemperaturearizona
"1990-01-01" 54.4507
"1990-01-02" 49.4202
"1990-01-03" 40.7127
"1990-01-04" 42.9121
"1990-01-05" 46.5906
"1990-01-06" 49.8268
"1990-01-07" 53.5669
"1990-01-08" 60.0233
"1990-01-09" 65.6038
"1990-01-10" 69.2616
"1990-01-11" 68.3458
"1990-01-12" 64.3309
"1990-01-13" 59.3557
"1990-01-14" 55.3658
"1990-01-15" 50.6838
"1990-01-16" 49.5187
"1990-01-17" 45.9532
"1990-01-18"  42.847
"1990-01-19" 42.9129
"1990-01-20" 46.2781
"1990-01-21" 52.0511
"1990-01-22" 52.5796
"1990-01-23" 58.5887
"1990-01-24"  53.002
"1990-01-25" 56.7217
"1990-01-26" 60.5624
"1990-01-27" 52.9754
"1990-01-28" 54.6048
"1990-01-29" 58.0179
"1990-01-30" 60.3799
"1990-01-31" 51.4614
"1990-02-01" 44.1635
"1990-02-02" 43.7695
"1990-02-03" 49.3021
"1990-02-04" 53.6952
"1990-02-05" 50.5362
"1990-02-06" 56.7709
"1990-02-07" 57.1341
"1990-02-08" 48.6385
"1990-02-09" 53.3377
"1990-02-10"  60.046
"1990-02-11"  66.155
"1990-02-12" 63.2863
"1990-02-13" 59.5702
"1990-02-14" 40.4872
"1990-02-15" 35.6637
"1990-02-16" 45.2602
"1990-02-17" 53.4385
"1990-02-18" 53.6209
"1990-02-19" 42.3203
end

This is for Arkansas.

* Example generated by -dataex-. For more info, type help dataex
clear
input str10 datetime float maxtemperaturearkansas
"1990-01-01" 45.8019
"1990-01-02" 50.1368
"1990-01-03" 57.1431
"1990-01-04" 52.0198
"1990-01-05" 44.4042
"1990-01-06" 49.2847
"1990-01-07" 47.2051
"1990-01-08" 58.6026
"1990-01-09" 65.5093
"1990-01-10" 66.7774
"1990-01-11" 65.4109
"1990-01-12"  46.283
"1990-01-13" 45.2511
"1990-01-14" 56.7235
"1990-01-15" 66.7071
"1990-01-16"  64.854
"1990-01-17" 64.5175
"1990-01-18" 53.9411
"1990-01-19" 53.6355
"1990-01-20" 53.5782
"1990-01-21" 53.5888
"1990-01-22" 65.8918
"1990-01-23" 64.0008
"1990-01-24" 63.8965
"1990-01-25" 48.5692
"1990-01-26" 61.1305
"1990-01-27" 63.7414
"1990-01-28" 43.2653
"1990-01-29"  54.003
"1990-01-30" 60.9759
"1990-01-31" 62.7778
"1990-02-01" 64.0328
"1990-02-02" 62.1903
"1990-02-03" 54.7124
"1990-02-04" 51.5547
"1990-02-05" 58.5541
"1990-02-06" 60.1461
"1990-02-07" 64.4056
"1990-02-08" 66.2659
"1990-02-09" 66.6963
"1990-02-10" 60.0525
"1990-02-11" 70.9396
"1990-02-12" 73.9891
"1990-02-13" 72.0396
"1990-02-14" 68.0661
"1990-02-15" 66.7773
"1990-02-16" 51.2602
"1990-02-17" 46.9697
"1990-02-18" 50.3346
"1990-02-19" 59.4847
end

[Andrew Musau]

You can loop appending. Or merge on datetime and then reshape long.

* Example generated by -dataex-. For more info, type help dataex
clear
input str10 datetime float maxtemperaturearizona
"1990-01-01" 54.4507
"1990-01-02" 49.4202
"1990-01-03" 40.7127
"1990-01-04" 42.9121
"1990-01-05" 46.5906
"1990-01-06" 49.8268
"1990-01-07" 53.5669
"1990-01-08" 60.0233
"1990-01-09" 65.6038
"1990-01-10" 69.2616
"1990-01-11" 68.3458
"1990-01-12" 64.3309
"1990-01-13" 59.3557
"1990-01-14" 55.3658
"1990-01-15" 50.6838
"1990-01-16" 49.5187
"1990-01-17" 45.9532
"1990-01-18"  42.847
"1990-01-19" 42.9129
"1990-01-20" 46.2781
"1990-01-21" 52.0511
"1990-01-22" 52.5796
"1990-01-23" 58.5887
"1990-01-24"  53.002
"1990-01-25" 56.7217
"1990-01-26" 60.5624
"1990-01-27" 52.9754
"1990-01-28" 54.6048
"1990-01-29" 58.0179
"1990-01-30" 60.3799
"1990-01-31" 51.4614
"1990-02-01" 44.1635
"1990-02-02" 43.7695
"1990-02-03" 49.3021
"1990-02-04" 53.6952
"1990-02-05" 50.5362
"1990-02-06" 56.7709
"1990-02-07" 57.1341
"1990-02-08" 48.6385
"1990-02-09" 53.3377
"1990-02-10"  60.046
"1990-02-11"  66.155
"1990-02-12" 63.2863
"1990-02-13" 59.5702
"1990-02-14" 40.4872
"1990-02-15" 35.6637
"1990-02-16" 45.2602
"1990-02-17" 53.4385
"1990-02-18" 53.6209
"1990-02-19" 42.3203
end
tempfile state
ds datetime, not
gen state= ustrregexra("`r(varlist)'", ".*ture(\w+)", "$1")
rename maxtemp* maxtemp
save `state', replace

* Example generated by -dataex-. For more info, type help dataex
clear
input str10 datetime float maxtemperaturearkansas
"1990-01-01" 45.8019
"1990-01-02" 50.1368
"1990-01-03" 57.1431
"1990-01-04" 52.0198
"1990-01-05" 44.4042
"1990-01-06" 49.2847
"1990-01-07" 47.2051
"1990-01-08" 58.6026
"1990-01-09" 65.5093
"1990-01-10" 66.7774
"1990-01-11" 65.4109
"1990-01-12"  46.283
"1990-01-13" 45.2511
"1990-01-14" 56.7235
"1990-01-15" 66.7071
"1990-01-16"  64.854
"1990-01-17" 64.5175
"1990-01-18" 53.9411
"1990-01-19" 53.6355
"1990-01-20" 53.5782
"1990-01-21" 53.5888
"1990-01-22" 65.8918
"1990-01-23" 64.0008
"1990-01-24" 63.8965
"1990-01-25" 48.5692
"1990-01-26" 61.1305
"1990-01-27" 63.7414
"1990-01-28" 43.2653
"1990-01-29"  54.003
"1990-01-30" 60.9759
"1990-01-31" 62.7778
"1990-02-01" 64.0328
"1990-02-02" 62.1903
"1990-02-03" 54.7124
"1990-02-04" 51.5547
"1990-02-05" 58.5541
"1990-02-06" 60.1461
"1990-02-07" 64.4056
"1990-02-08" 66.2659
"1990-02-09" 66.6963
"1990-02-10" 60.0525
"1990-02-11" 70.9396
"1990-02-12" 73.9891
"1990-02-13" 72.0396
"1990-02-14" 68.0661
"1990-02-15" 66.7773
"1990-02-16" 51.2602
"1990-02-17" 46.9697
"1990-02-18" 50.3346
"1990-02-19" 59.4847
end

ds datetime, not
gen state= ustrregexra("`r(varlist)'", ".*ture(\w+)", "$1")
rename maxtemp* maxtemp
append using `state'
save `state', replace

and so on.

Res.:

. l, sepby(state)

     +---------------------------------+
     |   datetime   maxtemp      state |
     |---------------------------------|
  1. | 1990-01-01   45.8019   arkansas |
  2. | 1990-01-02   50.1368   arkansas |
  3. | 1990-01-03   57.1431   arkansas |
  4. | 1990-01-04   52.0198   arkansas |
  5. | 1990-01-05   44.4042   arkansas |
  6. | 1990-01-06   49.2847   arkansas |
  7. | 1990-01-07   47.2051   arkansas |
  8. | 1990-01-08   58.6026   arkansas |
  9. | 1990-01-09   65.5093   arkansas |
 10. | 1990-01-10   66.7774   arkansas |
 11. | 1990-01-11   65.4109   arkansas |
 12. | 1990-01-12    46.283   arkansas |
 13. | 1990-01-13   45.2511   arkansas |
 14. | 1990-01-14   56.7235   arkansas |
 15. | 1990-01-15   66.7071   arkansas |
 16. | 1990-01-16    64.854   arkansas |
 17. | 1990-01-17   64.5175   arkansas |
 18. | 1990-01-18   53.9411   arkansas |
 19. | 1990-01-19   53.6355   arkansas |
 20. | 1990-01-20   53.5782   arkansas |
 21. | 1990-01-21   53.5888   arkansas |
 22. | 1990-01-22   65.8918   arkansas |
 23. | 1990-01-23   64.0008   arkansas |
 24. | 1990-01-24   63.8965   arkansas |
 25. | 1990-01-25   48.5692   arkansas |
 26. | 1990-01-26   61.1305   arkansas |
 27. | 1990-01-27   63.7414   arkansas |
 28. | 1990-01-28   43.2653   arkansas |
 29. | 1990-01-29    54.003   arkansas |
 30. | 1990-01-30   60.9759   arkansas |
 31. | 1990-01-31   62.7778   arkansas |
 32. | 1990-02-01   64.0328   arkansas |
 33. | 1990-02-02   62.1903   arkansas |
 34. | 1990-02-03   54.7124   arkansas |
 35. | 1990-02-04   51.5547   arkansas |
 36. | 1990-02-05   58.5541   arkansas |
 37. | 1990-02-06   60.1461   arkansas |
 38. | 1990-02-07   64.4056   arkansas |
 39. | 1990-02-08   66.2659   arkansas |
 40. | 1990-02-09   66.6963   arkansas |
 41. | 1990-02-10   60.0525   arkansas |
 42. | 1990-02-11   70.9396   arkansas |
 43. | 1990-02-12   73.9891   arkansas |
 44. | 1990-02-13   72.0396   arkansas |
 45. | 1990-02-14   68.0661   arkansas |
 46. | 1990-02-15   66.7773   arkansas |
 47. | 1990-02-16   51.2602   arkansas |
 48. | 1990-02-17   46.9697   arkansas |
 49. | 1990-02-18   50.3346   arkansas |
 50. | 1990-02-19   59.4847   arkansas |
     |---------------------------------|
 51. | 1990-01-01   54.4507    arizona |
 52. | 1990-01-02   49.4202    arizona |
 53. | 1990-01-03   40.7127    arizona |
 54. | 1990-01-04   42.9121    arizona |
 55. | 1990-01-05   46.5906    arizona |
 56. | 1990-01-06   49.8268    arizona |
 57. | 1990-01-07   53.5669    arizona |
 58. | 1990-01-08   60.0233    arizona |
 59. | 1990-01-09   65.6038    arizona |
 60. | 1990-01-10   69.2616    arizona |
 61. | 1990-01-11   68.3458    arizona |
 62. | 1990-01-12   64.3309    arizona |
 63. | 1990-01-13   59.3557    arizona |
 64. | 1990-01-14   55.3658    arizona |
 65. | 1990-01-15   50.6838    arizona |
 66. | 1990-01-16   49.5187    arizona |
 67. | 1990-01-17   45.9532    arizona |
 68. | 1990-01-18    42.847    arizona |
 69. | 1990-01-19   42.9129    arizona |
 70. | 1990-01-20   46.2781    arizona |
 71. | 1990-01-21   52.0511    arizona |
 72. | 1990-01-22   52.5796    arizona |
 73. | 1990-01-23   58.5887    arizona |
 74. | 1990-01-24    53.002    arizona |
 75. | 1990-01-25   56.7217    arizona |
 76. | 1990-01-26   60.5624    arizona |
 77. | 1990-01-27   52.9754    arizona |
 78. | 1990-01-28   54.6048    arizona |
 79. | 1990-01-29   58.0179    arizona |
 80. | 1990-01-30   60.3799    arizona |
 81. | 1990-01-31   51.4614    arizona |
 82. | 1990-02-01   44.1635    arizona |
 83. | 1990-02-02   43.7695    arizona |
 84. | 1990-02-03   49.3021    arizona |
 85. | 1990-02-04   53.6952    arizona |
 86. | 1990-02-05   50.5362    arizona |
 87. | 1990-02-06   56.7709    arizona |
 88. | 1990-02-07   57.1341    arizona |
 89. | 1990-02-08   48.6385    arizona |
 90. | 1990-02-09   53.3377    arizona |
 91. | 1990-02-10    60.046    arizona |
 92. | 1990-02-11    66.155    arizona |
 93. | 1990-02-12   63.2863    arizona |
 94. | 1990-02-13   59.5702    arizona |
 95. | 1990-02-14   40.4872    arizona |
 96. | 1990-02-15   35.6637    arizona |
 97. | 1990-02-16   45.2602    arizona |
 98. | 1990-02-17   53.4385    arizona |
 99. | 1990-02-18   53.6209    arizona |
100. | 1990-02-19   42.3203    arizona |
     +---------------------------------+

.