CODEWITHSUNDEEP
pivot-tablestatareshape
Jay
Jay

Reputation: 15

Equivalent of Excel Pivoting in Stata

I have been working with country-level survey data in Stata that I needed to reshape. I ended up exporting the .dta to a .csv and making a pivot table in in Excel but I am curious to know how to do this in Stata, as I couldn't figure it out.

Suppose we have the following data:

country response
A 1
A 1
A 2
A 2
A 1
B 1
B 2
B 2
B 1
B 1
A 2
A 2
A 1

I would like the data to be reformatted as such:

country sum_1 sum_2
A 4 4
B 3 2

First I tried a simple reshape wide command but got the error that "values of variable response not unique within country" before realizing reshape without additional steps wouldn't work anyway.

Then I tried generating new variables conditional on the value of response and trying to use reshape following that... the whole thing turned into kind of a mess so I just used Excel.

Just curious if there is a more intuitive way of doing that transformation.

Upvotes: 1

Views: 2020

Answers (1)

Nick Cox
Nick Cox

Reputation: 37288

If you just want a table, then just ask for one: 

clear 
input str1 country response
A 1
A 1
A 2
A 2
A 1
B 1
B 2
B 2
B 1
B 1
A 2
A 2
A 1
end 

tabulate country response 

           |       response
   country |         1          2 |     Total
-----------+----------------------+----------
         A |         4          4 |         8 
         B |         3          2 |         5 
-----------+----------------------+----------
     Total |         7          6 |        13

If you want the data to be changed to this, reshape is part of the answer, but you should contract first. collapse is in several ways more versatile, but your "sum" is really a count or frequency, so contract is more direct. 

contract country response, freq(sum_)

reshape wide sum_, i(country) j(response)

list 

     +-------------------------+
     | country   sum_1   sum_2 |
     |-------------------------|
  1. |       A       4       4 |
  2. |       B       3       2 |
     +-------------------------+

In Stata 16 up, help frames introduces frames as a way to work with multiple datasets in the same session.

Upvotes: 1

Related Questions