Reshape a dataset

[Nicolas Morales Alvarez]

Hello,

I have a data-set for 68 different companies which has information of their price from 2014 to 2019.
I currently have:
-a variable corresponding to the date
-68 variables corresponding to each of the different companies.

What I need is to reshape the data so that I have an observation of the price for each date and a variable identifying each company.

I'm struggling to understand if I should use the command reshape from long to wide or from wide to long.

Alternatively, if I transposed the dataset I would have:
-a variable identifying each company with 68 observations
-one variable per-month from 2014 to 2019.

In this case I would need to generate a date variable and collapse all the existing date variables into a single variable that is sorted by date.

Thank you for your help

[Nicolas Morales Alvarez]

I was checking the needed elements to use the reshape command. Referring to the transposed dataset, where I have a variable identifying each company and k variables for the observation of the price in each month, I think I found a possible way.

I am importing this dataset from Excel and the original file looks like this:

Company	1-1-15	1-2-15	1-3-15
Company A	4	2	5
Company B	4	4	3

When I import this into stata it doesn't recognize the first column as a name (because it has numeric values) so it assigns a letter to the name of each column and labels it with the date.
I would need to have the date in a format such as price_numeric_date to use the reshape from wide to long, where the stub is price and the numeric date is j (with the syntax shown in https://www.stata.com/manuals13/dreshape.pdf)

I thought of using a loop to rename the variables with the date using a loop but stata doesn't recognize the dates as values for the j.

Here's the do-file

clear
import excel "/Users/nicolasmorales/Downloads/export para stata reshape-2.xlsx", sheet("V21") firstrow

foreach v of varlist B C D E {
local x : variable label `v'
rename `v' price_`x'
}
reshape long price, i(company) j(date)

[Nicolas Morales Alvarez]

I designed a solution (not optimal but works). I went back to the excel spreadsheet and changed the column names from dates to the needed format. There were 60 periods for the price, so I renamed the columns price1, price 2, ..., price60.
Then I used the command:
reshape long price, i(company) j(numeric_date)

It worked! Now I only need to replace the values for the dates back from numeric to the dd-mm-yyyy format.

I hope this thread helps someone else.