Reshape Long Wide Data with Numerous Columns that Represent Intervals

[Matthew Boyce]

Hi All,

I would like to reshape long data that is in a wide format. Each column is a 15 minute increment for a month (over 2,000 columns). It is in the format of _201707010915 for July 1, 2017 at 9:15 am. Any suggestions would be greatly appreciated. I have tried a few different permutations without success, and my go-to person is also stumped.

Thank you,

Matthew

[David Benson]

Hi Matthew,

Welcome to Statalist!

Your problem sounds tough! (Although I am sure someone here can solve it).

I don't know if I can solve it, but hopefully my comments can help you get started:

1. You will probably need to add a prefix (like var_) to all of your variables (using either the rename or renvars command (user-generated so you will need to install it (Search for it within Stata). This will let you use Stata's reshape commands.

* Either of the below will add the prefix var_ to all variables.  
rename * var_=   // The "var_=" means "Add prefix var_"

search renvars  // And install it
renvars,  prefix(var_)   // renvars defaults to all variables. You will need to change any other variables (like id) back
// i.e so they don't have the prefix

Once you do that, you can use Stata's reshape machinery:

* This won't work exactly, but you can modify it to your needs
reshape long var_, i(id) j(date_time)

That will get of your date_time columns (_201707010915, _201707010930, etc) into a single variable. You can then use the various date and time commands or the nsplit command (SSC) to split your date_time variable into year, month, day, etc parts as desired. nsplit stands for "Numeric split" and allows you to easily split numbers into various parts.)

ssc install nsplit
nsplit date_time, digits(4 2 2 4) generate(year month day time)

NOTE: I haven't tested any of the above code, so check for typos (and make sure I have got the syntax right).
You may want to post a small sample of your data here using the -dataex- command. That will probably be super helpful in getting others to help you get this to work.

Hope that helps!
--David