From Pivot Table to Variables

[Alper Demirdogen]

Hello,

I want to transform my data set. I have a data set looking something like that:

Using the table command in Stata, I would be able to get these results (similar pivot tables in Excel):

However I want to have these results as variables which would look like:

Shortly, how could I save the pivot table values as variables?

Thank you so much
Alper Demirdogen

[William Lisowski]

Welcome to Statalist.

What you want is the reshape wide command, perhaps. From the data you show in your first picture, I think the command you want might be

reshape wide Value, i(Location) j(Input) string

I would post an example demonstrating this on your example data, but unfortunately you posted a picture of data, and I cannot read your picture into Stata.

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, looking especially at sections 9-12 on how to best pose your question. In particular, please read FAQ #12 and use dataex to post sample data and CODE delimiters when posting commands and results.

Having said all this, what you will be doing is translating your data from a "long" layout to a "wide" layout. Depending on what you plan to do with the data, a wide layout may increase the difficulty of accomplishing what you want. The 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. In particular, if you find yourself wanting to loop over the variables in your wide layout, you probably could accomplish your objective more easily by working in a long layout.

[Alper Demirdogen]

Dear William,

Thank you so much for your post. Reshape code solves the problem.


Alper

[Alper Demirdogen]

Hello again,

I have faced with a similar problem. Instead of posting a new topic, I simply summarize here;

I have a data set like:

clear
input str1 Location str1 Input long Total
A X 10
A X 40
A Y 20
B X 20
B Y 30
B Q 10
B Q 50
C Z 60
C X 40
C W 60
end

I like to have this data set like:

clear
input str1 Location long InputQ InputW InputX InputY InputZ
A . . 50 20 .
B 60 . 20 30 .
C . 60 40 . 60
end

It is like using

table Location Input, c(sum Total)

command, however, having results as variables


P.S. Using reshape command works only I have uniqe Input variables. However, my data set contains same Input observations within Location groups.

[William Lisowski]

Thank you for the very clear presentation of your problem, including sample data and expected results.

In this case, you need to precede your reshape wide command with the collapse command to add up the observations.

clear
input str1 Location str1 Input long Total
A X 10
A X 40
A Y 20
B X 20
B Y 30
B Q 10
B Q 50
C Z 60
C X 40
C W 60
end
collapse (sum) Total, by(Location Input)
list, sepby(Location)
reshape wide Total, i(Location) j(Input) string
list

. collapse (sum) Total, by(Location Input)

. list, sepby(Location)

     +--------------------------+
     | Location   Input   Total |
     |--------------------------|
  1. |        A       X      50 |
  2. |        A       Y      20 |
     |--------------------------|
  3. |        B       Q      60 |
  4. |        B       X      20 |
  5. |        B       Y      30 |
     |--------------------------|
  6. |        C       W      60 |
  7. |        C       X      40 |
  8. |        C       Z      60 |
     +--------------------------+

. reshape wide Total, i(Location) j(Input) string
(note: j = Q W X Y Z)

Data                               long   ->   wide
-----------------------------------------------------------------------------
Number of obs.                        8   ->       3
Number of variables                   3   ->       6
j variable (5 values)             Input   ->   (dropped)
xij variables:
                                  Total   ->   TotalQ TotalW ... TotalZ
-----------------------------------------------------------------------------

. list

     +-------------------------------------------------------+
     | Location   TotalQ   TotalW   TotalX   TotalY   TotalZ |
     |-------------------------------------------------------|
  1. |        A        .        .       50       20        . |
  2. |        B       60        .       20       30        . |
  3. |        C        .       60       40        .       60 |
     +-------------------------------------------------------+

.

[Alper Demirdogen]

Dear William,

Thank you so much for your post.

Best Regards
Alper