Notice: On April 23, 2014, Statalist moved from an email list to a forum, based at statalist.org.
[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
Re: st: store tabulate command results in excel
From
Anisa Shyti <[email protected]>
To
[email protected]
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 <[email protected]> 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
> [email protected]
> +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/