Bookmark and Share

Notice: On March 31, it was announced that Statalist is moving from an email list to a forum. The old list will shut down on April 23, and its replacement, statalist.org is already up and running.


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

Re: st: store tabulate command results in excel


From   Anisa Shyti <anisa.shyti@gmail.com>
To   statalist@hsphsun2.harvard.edu
Subject   Re: st: store tabulate command results in excel
Date   Thu, 15 Mar 2012 14:21:35 +0100

Hi Eric,

I tried your code in several ways, but it does not give the
information I want.  I don't fully understand the code, but the
variables it ads do not count % of A according to the conditions I
need to specify.

"Row" in this case, refers to "rows" inside the questions (each
question has a set of rows - i and j - unbalanced panel).

Given the structure of my data, I am willing to build some sort of
contingency tables with frequencies of A and B.

The structure of these tables might be the following:

	Question 1						
% of A (only)												
		Subquestion- Row										
Feedback		1	2	3	4	5	6	7	8	9	10	11
1	nr.	18	18	18	14	13	8	7	7	4	4	4
	%	94.70%	94.70%	94.70%	73.70%	68.40%	42.10%	36.80%	36.80%	21.10%	21.10%	21.10%
2	nr.	10	10	10	10	10	7	3	2	1	1	1
	%	83.30%	83.30%	83.30%	83.30%	83.30%	58.30%	25%	16.70%	8.30%	8.30%	8.30%
3	nr.	9	8	8	8	8	6	1	1	1	1	1
	%	100%	88.90%	88.90%	88.90%	88.90%	66.70%	11.10%	11.10%	11.10%	11.10%	11.10%
	
Under feedback 1 and subquestion 1 -> the nr 18 means that given the
pool of subjects, 18 of them did choose A (and I know that under
feedback 1 I have 19 subjects, so 18/19 = 94.7% picked A, and so on.

I did this by splitting the sample according to feedback, and then
applied your previous code.  It works, but it's still a lot of manual
work, not free of errors.  The precision of these tables is crucial
for the testing step..

I do not know if all this can be done by if conditions, but I still
need to tabulate and have an excel output.

Thanks a lot for your support.
Cheers,
Anisa






On Wed, Mar 14, 2012 at 9:14 PM, Eric Booth <eric.a.booth@gmail.com> wrote:
> <>
>
>
> Hi Anisa:
> Just to be clear then, you want the freq and Row % of answer A (vs. B) for each 'Row' (the variable 'Row', not the row element of a table)  within each Condition within each Question.
>
> You can get this table with the code example below.
>
> Caveat emptor: This assumes you have Stata version 12 (which you did not specify) -- if you have previous versions, you should change the -export- commands (to something like -outsheet-).  Also, the code builds the elements of the table manually - this could probably be shortened - but you should be able to better adapt this code if the table still isnt formatted quite right.
>
> Finally, you indicate that this whole exercise is to reduce the complexity of the dataset. You might consider a graph command to inspect the freq/% of A's in each answer/'Row'/condition/question - it may make patterns in this data more clear (since there are so many nested elements in your tables).
>
>
> ********************************!
> **watch for wrapping below:
>
> clear
> inp user_id questionid  row   str2  answer str12 condition
> 1       1       1       "A"       "- Feedback 1"
> 1       1       2       "A"       "- Feedback 1"
> 1       1       3       "A"       "- Feedback 1"
> 1       1       4       "B"       "- Feedback 1"
> 1       1       5       "B"       "- Feedback 1"
> 1       1       6       "B"       "- Feedback 1"
> 1       1       7       "B"       "- Feedback 1"
> 1       1       8       "B"       "- Feedback 1"
> 1       1       9       "B"       "- Feedback 1"
> 1       1       10      "B"       "- Feedback 1"
> 1       1       11      "B"       "- Feedback 1"
> 2       1       1       "A"       "- Feedback 1"
> 2       1       2       "A"       "- Feedback 1"
> 2       1       3       "A"       "- Feedback 1"
> 2       1       4       "A"       "- Feedback 1"
> 2       1       5       "A"       "- Feedback 1"
> 2       1       6       "A"       "- Feedback 1"
> 2       1       7       "A"       "- Feedback 1"
> 2       1       8       "A"       "- Feedback 1"
> 2       1       9       "A"       "- Feedback 1"
> 2       1       10      "A"       "- Feedback 1"
> 2       1       11      "A"       "- Feedback 1"
> 2       2       1       "A"       "- Feedback 1"
> 2       2       2       "A"       "- Feedback 1"
> 2       2       3       "A"       "- Feedback 1"
> 2       2       4       "A"       "- Feedback 1"
> 2       2       5       "A"       "- Feedback 1"
> 2       2       6       "A"       "- Feedback 1"
> 2       2       7       "A"       "- Feedback 1"
> 2       2       8       "A"       "- Feedback 1"
> 2       2       9       "A"       "- Feedback 1"
> 2       2       10      "A"       "- Feedback 1"
> 2       2       11      "A"       "- Feedback 1"
> 2       2       12      "A"       "- Feedback 1"
> 2       2       13      "A"       "- Feedback 1"
> 2       2       14      "A"       "- Feedback 1"
> 2       2       15      "A"       "- Feedback 1"
> 2       2       16      "A"       "- Feedback 1"
> 2       2       17      "A"       "- Feedback 1"
> 2       2       18      "A"       "- Feedback 1"
> 2       2       19      "A"       "- Feedback 1"
> 2       2       20      "A"       "- Feedback 1"
> 2       2       21      "A"       "- Feedback 1"
> 2       2       22      "A"       "- Feedback 1"
> 2       2       23      "A"       "- Feedback 1"
> 2       2       24      "A"       "- Feedback 1"
> 2       2       25      "A"       "- Feedback 1"
> 2       2       26      "A"       "- Feedback 1"
> 12      1       1       "B"       "- Feedback 2"
> 12      1       2       "B"       "- Feedback 2"
> 12      1       3       "B"       "- Feedback 2"
> 12      1       4       "B"       "- Feedback 2"
> 12      1       5       "B"       "- Feedback 2"
> 12      1       6       "B"       "- Feedback 2"
> 12      1       7       "B"       "- Feedback 2"
> 12      1       8       "B"       "- Feedback 2"
> 12      1       9       "B"       "- Feedback 2"
> 12      1       10      "B"       "- Feedback 2"
> 12      1       11      "B"       "- Feedback 2"
> 12      2       1       "B"       "- Feedback 2"
> 12      2       2       "B"       "- Feedback 2"
> 12      2       3       "B"       "- Feedback 2"
> 12      2       4       "B"       "- Feedback 2"
> 12      2       5       "B"       "- Feedback 2"
> 12      2       6       "B"       "- Feedback 2"
> 12      2       7       "B"       "- Feedback 2"
> 12      2       8       "B"       "- Feedback 2"
> 12      2       9       "B"       "- Feedback 2"
> 12      2       10      "B"       "- Feedback 2"
> 12      2       11      "B"       "- Feedback 2"
> 12      2       12      "B"       "- Feedback 2"
> 12      2       13      "B"       "- Feedback 2"
> 12      2       14      "B"       "- Feedback 2"
> 12      2       15      "B"       "- Feedback 2"
> 12      2       16      "B"       "- Feedback 2"
> 12      2       17      "B"       "- Feedback 2"
> 12      2       18      "B"       "- Feedback 2"
> 12      2       19      "B"       "- Feedback 2"
> 12      2       20      "B"       "- Feedback 2"
> 12      2       21      "B"       "- Feedback 2"
> 12      2       22      "B"       "- Feedback 2"
> 12      2       23      "B"       "- Feedback 2"
> 12      2       24      "B"       "- Feedback 2"
> 12      2       25      "B"       "- Feedback 2"
> 12      2       26      "A"       "- Feedback 2"
> 14      12      1       "A"       "- Feedback 2"
> 14      12      2       "A"       "- Feedback 2"
> 14      12      3       "A"       "- Feedback 2"
> 14      12      4       "A"       "- Feedback 2"
> 14      12      5       "A"       "- Feedback 2"
> 14      12      6       "A"       "- Feedback 2"
> 14      12      7       "A"       "- Feedback 2"
> 14      12      8       "A"       "- Feedback 2"
> 14      12      9       "A"       "- Feedback 2"
> 14      12      10      "A"       "- Feedback 2"
> 14      12      11      "A"       "- Feedback 2"
> 14      12      12      "A"       "- Feedback 2"
> 14      12      13      "A"       "- Feedback 2"
> 14      12      14      "B"       "- Feedback 2"
> 14      12      15      "B"       "- Feedback 2"
> 14      12      16      "B"       "- Feedback 2"
> 14      12      17      "B"       "- Feedback 2"
> 14      12      18      "B"       "- Feedback 2"
> 14      12      19      "B"       "- Feedback 2"
> 14      12      20      "B"       "- Feedback 2"
> 14      12      21      "B"       "- Feedback 2"
> 14      12      22      "B"       "- Feedback 2"
> 14      12      23      "B"       "- Feedback 2"
> 14      12      24      "B"       "- Feedback 2"
> 14      12      25      "B"       "- Feedback 2"
> 14      12      26      "B"       "- Feedback 2"
> end
>
>
> loc UoI `"questionid condition row"' //unit of interest
> encode answer, g(a2)
> foreach u in A B {
> bys `UoI' : egen freq_`u'2 = ///
>        total(a2)       if answer=="`u'"
> bys `UoI': egen freq_`u' = max(freq_`u'2)
> drop freq_`u'2
> recode freq_`u' (.=0)
> }
> g pct_A = freq_A/(freq_A + freq_B)*100
> preserve
>  bys `UoI': g i = 1==_n
>  drop if i!=1
>  drop i freq_B user_id answer a2
>        **aesthetics:
>        tostring pct_A, replace force use
>        replace pct_A = pct_A + " %"
>        rename freq_A freqA_Row
>        rename pct_A pctA_Row
>  reshape wide freqA_Row pctA_Row, ///
>        i(questionid condition) j(row)
>  export  excel using "table.xlsx", ///
>        replace first(variable)
> restore
> ********************************!
>
> - Eric
> __
> Eric A. Booth
> Public Policy Research Institute
> Texas A&M University
> ebooth@ppri.tamu.edu
> +979.845.6754
>
>
>
>
>
> On Mar 13, 2012, at 11:34 AM, Anisa Shyti wrote:
>
>> Hi Eric,
>>
>> Apologies for the variable mismatch - feedback and condition are the
>> same - that's accurate.  Below you find the exact structure of my
>> data.  I have the row as an existing variable already, since questions
>> have subquestions (in rows):
>>
>> I need a nested structure of the tab to refine the analysis, to see
>> for each row the % A and B and the evolution on answers according to
>> Condition.
>>
>> The structure of the output needs to be something like:
>>
>> QuestionID i
>> ...........................row1........row2.........row3........row4........................rowN
>> Feedback 1........ nr (%)A.....nr(%)A.....nr(%)A......nr(%)A................
>> Feedback 2........ nr (%)A.....nr(%)A.....nr(%)A......nr(%)A................
>> Feedback 3........ nr (%)A.....nr(%)A.....nr(%)A......nr(%)A................
>>
>> It's like zooming in in each of the previous cells (instead of having
>> the total nr and % of A/B answers).
>>
>> Is this more clear?
>>
>> I tried what you suggest, but it generates Condition+row without
>> keeping the distinction by user and questionID.  How can I compose
>> Condtion+Row given the structure of my data, preserving the rest of
>> the information?
>>
>
>
>
>
> *
> *   For searches and help try:
> *   http://www.stata.com/help.cgi?search
> *   http://www.stata.com/support/statalist/faq
> *   http://www.ats.ucla.edu/stat/stata/

*
*   For searches and help try:
*   http://www.stata.com/help.cgi?search
*   http://www.stata.com/support/statalist/faq
*   http://www.ats.ucla.edu/stat/stata/


© Copyright 1996–2014 StataCorp LP   |   Terms of use   |   Privacy   |   Contact us   |   Site index