Invert panels and variables

[Steven Koller]

Hi all. First post. I've got a simple dataset (screenshotted), which currently has 6 observations and 40 variables. I would like each year to be a panel, and each panel to be a variable, such that the set has 6 variables and 40 observations. Any suggestions? "Reshape" hasn't been working for me.

[William Lisowski]

Welcome to Statalist, Steven.

Reshape is the tool you need, perhaps reshape long followed by reshape wide. However, experienced users here generally agree that, with few exceptions, Stata makes it much more straightforward to accomplish complex analyses using a long layout of your data rather than a wide layout of the same data, so you'd likely be better off with 240 observations of crop, year, and value for whatever the quantity is that you're measuring. That's certainly what Stata's commands for dealing with longitudinal and panel data expect. (See the Stata Longitudinal Data/Panel Data Reference Manual PDF included in your Stata documentation.)

Please review the Statalist FAQ linked to from the top of the page, as well as from the Advice on Posting link on the page you used to create your post. Note especially sections 9-12 on how to best pose your question. The more you help others understand your problem, the more likely others are to be able to help you solve your problem.

Section 12.1 is particularly pertinent

12.1 What to say about your commands and your problem

Say exactly what you typed and exactly what Stata typed (or did) in response. N.B. exactly!
...
Never say just that something "doesn't work" or "didn't work", but explain precisely in what sense you didn't get what you wanted.

Also relevant is section 12.5

12.5 Posting attachments: please don't...

There are several "please don't" requests here, but good reasons for them all.
...
In particular, please do not post screenshots. Many members will not be able to read them at all; they usually can't be read easily; and they do not allow copy and paste of data or code, which is highly desirable to allow experienced members to make precise suggestions for your questions.

and section 12.2

12.2 What to say about your data

We can understand your dataset only to the extent that you explain it clearly.

The best way to explain it is to show an example. The user-written command dataex makes it easy to give simple example datasets in postings. It was written to support Statalist and its use is strongly recommended. Usually a copy of 20 or so observations from your dataset is enough to show your problem.

In your case, posting, say, 5 years of data for your 6 crops would be adequate, no need to post all 40 years.

[Steven Koller]

Hi William, thank you for your comments and for letting me know about the forum's posting etiquette. I will be sure to keep them in mind before posting again in the future.

Below is the data structure I started out with:

crop	year_1991	year_1992	year_1993	year_1994	year_1995
maize	xx	xx	xx	xx	xx
wheat	xx	xx	xx	xx	xx
barley	xx	xx	xx	xx	xx

Then I ran the following code:

reshape long year_, i(crop) j(yr)

Which outputted something similar to:

crop	year	year_	v39
maize	1991	xx	xx
maize	1992	xx	xx
maize	1993	xx	xx
maize	1994	xx	xx
maize	1995	xx	xx
wheat	1991	xx	xx
wheat	1992	xx	xx
wheat	1993	xx	xx
wheat	1994	xx	xx
wheat	1995	xx	xx
barley	1991	xx	xx
barley	1992	xx	xx
barley	1993	xx	xx
barley	1994	xx	xx
barley	1995	xx	xx

And this is the data structure I would like:

year	maize	wheat	barley
1991	xx	xx	xx
1992	xx	xx	xx
1993	xx	xx	xx
1994	xx	xx	xx
1995	xx	xx	xx

However using the code below does not generate the format I am going for, as there are too many observations in the output:

reshape wide crop, i(price) j(year)


While I understand data in long form are typically preferred for longitudinal panel data, the current analysis I'm doing requires the data to be in the structure I've mentioned above in order to conduct a merge. Thanks!

[Andrew Musau]

It is much easier to use dataex than to present your data as above. After re-reading the FAQs,

ssc install dataex
help dataex

The reshape commands should be

reshape long year_, i(crop)
reshape wide year_, i(_j) j( crop, string)

Drop "string" if you have destringed your variables. The screenshot shows that you have not. Assuming that your years range from 1981-1995

forval i = 1981/1995{
destring year_`i' , replace
}

[Marcos Almeida]

You may also wish to select the - xpose - command.

Unfortunately, you didn't share data as recommended in the FAQ. As a consequence, I needed to type so as to create a mock dataset, hence I decided to make it shorter than yours.

Below, an example:

. input str15 product year_2000 year_2001 year_2002

             product  year_2000  year_2001  year_2002
  1. Wheat 172.3 176.4 167.4
  2. Maize 165.4 123.5 156.5
  3. Barley 123.3 125.4 176.5
  4. Rice 125.4 145.3 167.6
  5. end

. list

     +------------------------------------------+
     | product   yea~2000   yea~2001   yea~2002 |
     |------------------------------------------|
  1. |   Wheat      172.3      176.4      167.4 |
  2. |   Maize      165.4      123.5      156.5 |
  3. |   Barley      123.3      125.4      176.5 |
  4. |   Rice      125.4      145.3      167.6 |
     +------------------------------------------+

. xpose, clear varname

. list

     +-------------------------------------------+
     |    v1      v2      v3      v4    _varname |
     |-------------------------------------------|
  1. |     .       .       .       .     product |
  2. | 172.3   165.4   123.3   125.4   year_2000 |
  3. | 176.4   123.5   125.4   145.3   year_2001 |
  4. | 167.4   156.5   176.5   167.6   year_2002 |
     +-------------------------------------------+

. rename v1 Wheat

. rename v2 Maize

. rename v3 Barley

. rename v4 Rice

. drop in 1
(1 observation deleted)

. gen year = substr( _varname, 6,4)

. drop _varname

. destring year, replace
year: all characters numeric; replaced as int

. order year

. list

     +--------------------------------------+
     | year   Wheat   Maize  Barley    Rice |
     |--------------------------------------|
  1. | 2000   172.3   165.4   123.3   125.4 |
  2. | 2001   176.4   123.5   125.4   145.3 |
  3. | 2002   167.4   156.5   176.5   167.6 |
     +--------------------------------------+

Hopefully that helps.