Equivalent of Excel Pivoting in Stata

Question

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.

Whenever I see the word "intuitive", I can only translate it as "familiar". I have never used pivot tables in MS Excel, so have no idea how close my answer is to what you expect. — Nick Cox
This is spot on, thank you. I needed to merge this to larger data set so tabulating it wouldn't suffice. You're right on the intuitive point of course, this is actually much easier than learning pivot functions! — Jay

Nick Cox Nick Cox · Accepted Answer · 2020-04-21T06:58:04

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.

Equivalent of Excel Pivoting in Stata

1 Answers