[Katherine Adams]

Hello,

I have hourly household consumption (and outdoor temperature) data for two years which I need to import into Stata. The example of my data set:

* Example generated by -dataex-. To install: ssc install dataex
clear
input int year str2(month day hour) float(outdoor_temp hh1 hh2 hh3 hh4)
2017 "01" "01" "00"  .7 .34 .75 .24 .25
2017 "01" "01" "01"  .1 .28 .77  .7 .24
2017 "01" "01" "02" .16 .73 .79  .6 .32
2017 "01" "01" "03" .75  .7 .51  .8 .88
2017 "01" "01" "04" .41 .16 .55 .28 .41
2017 "01" "01" "05" .58 .48  .2 .39 .24
2017 "01" "01" "06" .42 .62 .69 .08  .1
2017 "01" "01" "07" .28 .16 .96 .31 .65
2017 "01" "01" "08" .91 .09 .77 .45 .71
2017 "01" "01" "09"  .7  .5 .07 .46 .38
2017 "01" "01" "10" .08 .33 .95 .94   0
2017 "01" "01" "11" .38 .01 .43 .34 .13
2017 "01" "01" "12" .87  .6 .82  .2 .69
2017 "01" "01" "13" .89 .03 .11 .72 .31
2017 "01" "01" "14" .14 .73 .09 .51 .13
2017 "01" "01" "15" .45 .27 .23 .21 .52
2017 "01" "01" "16" .32 .38 .07  .1 .06
2017 "01" "01" "17"   1 .48 .16 .77 .44
2017 "01" "01" "18" .78 .49   1 .76 .55
2017 "01" "01" "19" .24 .08 .36 .89  .9
2017 "01" "01" "20" .54 .42 .69 .73 .45
2017 "01" "01" "21" .82 .36 .33  .3 .24
2017 "01" "01" "22" .94 .72 .65 .26 .36
2017 "01" "01" "23" .83 .82 .66 .64 .51
end

However, Stata requires a different format of the data. So, my data set should look like this:

* Example generated by -dataex-. To install: ssc install dataex
clear
input byte hh int year str2(month day hour) float(consumption outdoor_temp)
1 2017 "01" "01" "00" .34  .7
1 2017 "01" "01" "01" .28  .1
1 2017 "01" "01" "02" .73 .16
1 2017 "01" "01" "03"  .7 .75
1 2017 "01" "01" "04" .16 .41
1 2017 "01" "01" "05" .48 .58
1 2017 "01" "01" "06" .62 .42
1 2017 "01" "01" "07" .16 .28
1 2017 "01" "01" "08" .09 .91
1 2017 "01" "01" "09"  .5  .7
1 2017 "01" "01" "10" .33 .08
1 2017 "01" "01" "11" .01 .38
1 2017 "01" "01" "12"  .6 .87
1 2017 "01" "01" "13" .03 .89
1 2017 "01" "01" "14" .73 .14
1 2017 "01" "01" "15" .27 .45
1 2017 "01" "01" "16" .38 .32
1 2017 "01" "01" "17" .48   1
1 2017 "01" "01" "18" .49 .78
1 2017 "01" "01" "19" .08 .24
1 2017 "01" "01" "20" .42 .54
1 2017 "01" "01" "21" .36 .82
1 2017 "01" "01" "22" .72 .94
1 2017 "01" "01" "23" .82 .83
2 2017 "01" "01" "00" .75  .7
2 2017 "01" "01" "01" .77  .1
2 2017 "01" "01" "02" .79 .16
2 2017 "01" "01" "03" .51 .75
2 2017 "01" "01" "04" .55 .41
2 2017 "01" "01" "05"  .2 .58
2 2017 "01" "01" "06" .69 .42
2 2017 "01" "01" "07" .96 .28
2 2017 "01" "01" "08" .77 .91
2 2017 "01" "01" "09" .07  .7
2 2017 "01" "01" "10" .95 .08
2 2017 "01" "01" "11" .43 .38
2 2017 "01" "01" "12" .82 .87
2 2017 "01" "01" "13" .11 .89
2 2017 "01" "01" "14" .09 .14
2 2017 "01" "01" "15" .23 .45
2 2017 "01" "01" "16" .07 .32
2 2017 "01" "01" "17" .16   1
2 2017 "01" "01" "18"   1 .78
2 2017 "01" "01" "19" .36 .24
2 2017 "01" "01" "20" .69 .54
2 2017 "01" "01" "21" .33 .82
2 2017 "01" "01" "22" .65 .94
2 2017 "01" "01" "23" .66 .83
3 2017 "01" "01" "00" .24  .7
3 2017 "01" "01" "01"  .7  .1
3 2017 "01" "01" "02"  .6 .16
3 2017 "01" "01" "03"  .8 .75
3 2017 "01" "01" "04" .28 .41
3 2017 "01" "01" "05" .39 .58
3 2017 "01" "01" "06" .08 .42
3 2017 "01" "01" "07" .31 .28
3 2017 "01" "01" "08" .45 .91
3 2017 "01" "01" "09" .46  .7
3 2017 "01" "01" "10" .94 .08
3 2017 "01" "01" "11" .34 .38
3 2017 "01" "01" "12"  .2 .87
3 2017 "01" "01" "13" .72 .89
3 2017 "01" "01" "14" .51 .14
3 2017 "01" "01" "15" .21 .45
3 2017 "01" "01" "16"  .1 .32
3 2017 "01" "01" "17" .77   1
3 2017 "01" "01" "18" .76 .78
3 2017 "01" "01" "19" .89 .24
3 2017 "01" "01" "20" .73 .54
3 2017 "01" "01" "21"  .3 .82
3 2017 "01" "01" "22" .26 .94
3 2017 "01" "01" "23" .64 .83
4 2017 "01" "01" "00" .25  .7
4 2017 "01" "01" "01" .24  .1
4 2017 "01" "01" "02" .32 .16
4 2017 "01" "01" "03" .88 .75
4 2017 "01" "01" "04" .41 .41
4 2017 "01" "01" "05" .24 .58
4 2017 "01" "01" "06"  .1 .42
4 2017 "01" "01" "07" .65 .28
4 2017 "01" "01" "08" .71 .91
4 2017 "01" "01" "09" .38  .7
4 2017 "01" "01" "10"   0 .08
4 2017 "01" "01" "11" .13 .38
4 2017 "01" "01" "12" .69 .87
4 2017 "01" "01" "13" .31 .89
4 2017 "01" "01" "14" .13 .14
4 2017 "01" "01" "15" .52 .45
4 2017 "01" "01" "16" .06 .32
4 2017 "01" "01" "17" .44   1
4 2017 "01" "01" "18" .55 .78
4 2017 "01" "01" "19"  .9 .24
4 2017 "01" "01" "20" .45 .54
4 2017 "01" "01" "21" .24 .82
4 2017 "01" "01" "22" .36 .94
4 2017 "01" "01" "23" .51 .83
end

The problem is that I do not know how to reshape the data. I tried the “reshape” and “stack” commands in Stata, but it did not help. I cannot find any Excel functions that transform the data into the format I need either. The point is that I basically need to combine multiple columns into one, but the functions/commands I have found so far generally do “rows-to-columns” or “columns-to-rows” transformation.

So, I was wondering if anyone here knew how to do the transformation above. I will appreciate any help.

Thank you.

[Clyde Schechter]

-reshape- is a difficult command to learn to use, and it takes a fair amount of practice before you develop intuition about how to use it flexibly.

rename hh* consumption*
reshape long consumption, i(year month day hour) j(hh)
sort hh year month day hour

does what you want.

[Katherine Adams]

Clyde,

Thank you very much!!