Reformatting datafile

[Tom Scott]

Hello,

I have a datafile with 188 neighborhoods, where each neighborhood has 7 yearly values (2015-21) of 8 unique crime types (Category variable). I showed below what the datafile looks like for the first 3 neighborhoods. I'd like to reformat this datafile to swap the Category variable with the YR variables so that the first column measures the year (values equal 2015 through 2021) and each value of Category is its own variable/column. Does anyone know the code or command to do this? Thank you very much.

[Clyde Schechter]

You've been around Statalist for a while, Tom, so I'm very surprised that you chose to show your data example with a screenshot, which is about as unhelpful a way to do that as possible, instead of with the very helpful -dataex- command. The latter would have made it easy for somebody to work with your data and write some code.

Since you didn't do that, I'll just outline the steps. Basically this is going to be done with two -reshape- commands. The first will be to -reshape long YR, i(neighborhood category) j(year)- Before you do the second -reshape-, there is an obstacle to over come. You want the values of the variable category to become the names of the variables in the columns originally headed by the years. The problem is that they are not possible Stata variable names. They contain disallowed characters (spaces, dashes) and some of them are also too long. You can overcome that with -replace category = strtoname(category)- followed by -replace category = subinstr(category, "___", "_", .)- The final step is then to -rename YR _- and -reshape wide _, i(neighborhood year) j(category) string-. Finally, for a cosmetic finishing touch, -rename _* *-.

In the future, when showing data examples, please use the -dataex- command to do so. If you are running version 18, 17, 16 or a fully updated version 15.1 or 14.2, -dataex- is already part of your official Stata installation. If not, run -ssc install dataex- to get it. Either way, run -help dataex- to read the simple instructions for using it. -dataex- will save you time; it is easier and quicker than typing out tables. It includes complete information about aspects of the data that are often critical to answering your question but cannot be seen from tabular displays or screenshots. It also makes it possible for those who want to help you to create a faithful representation of your example to try out their code, which in turn makes it more likely that their answer will actually work in your data.

[Tom Scott]

Thank you very much Clyde and I'm sorry I made that difficult for you. Thank you for the reminder about dataex. I appreciate your time and help very much. Best.