Generating a contingency table from binomial data

[David Crow]

I have data in the following format (which I'll call "binomial"):

agency drugrelated appeals petitions
A 0 10 50
A 1 30 80
B 0 20 100
B 1 20 30
C 0 30 70
C 1 40 60

where "agency" is a some governmental agency, "petitions" is the number of freedom of information requests made to the agency, "appeals" is the number of responses to those requests that were appealed by petitioner, and "drugrelated" is whether or not the FOI request involved drug policy.

Combining all agencies would give this 2 x 2 contingency table (represented in database form):

drugrelated appeals F
0 0 160
0 1 60
1 0 80
1 1 90

This should be easy, but how do I get from binomial to contingency table format? With "collapse," "contract," or some other command?

I'd greatly appreciate any help.

Thanks,
David

[William Lisowski]

I thought I understood what you wanted, but realize now that your data shows values for appeals of 10, 20, 30, and 40, but your "contingency table" shows values of 0 and 1. So I don't know what you want, but perhaps looking at help table will point you in the right direction.

[David Crow]

Thanks for the reply. Let me try to be clearer. Let's focus on just Agency A, in non-drug related cases, 10 of 50 petitions resulted in appeals. This means that 40 of 50 non-drug-related petitions didn't result in an appeal. In drug-related cases, 30 of 80 petitions resulted in an appeal, and 50 of 80 didn't. I have the data in this form:

Present Format

Agency drugrelated appeals petitions
A 0 10 50
A 1 30 80

Desired Contingency Table

What I want to get to is this contingency table:

tab drugrelated appeals [fw=F]

drugrelate |        appeals
         d |         0          1 |     Total
-----------+----------------------+----------
         0 |        40         10 |        50 
         1 |        50         30 |        80 
-----------+----------------------+----------
     Total |        90         40 |       130 

Possible Data Representation of Congintency Table

This contingency table could be also be represented like this dataset with four observations

drugrelated appeal F
0 0 40
0 1 10
1 0 50
1 1 30

where "F" is the frequency in each of the cells of the contingency table.

If the data were in this form, I would simply do this to get the contingency table:

tab drugrelated appeal [fw=F]


My question is: with my data in the present, first format, how can I get the data into the second format and then use tab (with frequency weights) to get a 2 x 2 table that i could then use for Chi-squared analysis, calculate the row percentages, get the odds ratio, etc.

Is this clearer?

[Joseph Coveney]

Try something like that below (start at "Begin here").

.ÿversionÿ14.0

.ÿ
.ÿclearÿ*

.ÿsetÿmoreÿoff

.ÿinputÿstr1ÿagencyÿbyteÿdrugrelatedÿint(appealsÿpetitions)

ÿÿÿÿÿÿÿÿagencyÿÿdrugre~dÿÿÿappealsÿÿpetiti~s
ÿÿ1.ÿAÿ0ÿ10ÿ50
ÿÿ2.ÿAÿ1ÿ30ÿ80
ÿÿ3.ÿBÿ0ÿ20ÿ100
ÿÿ4.ÿBÿ1ÿ20ÿ30
ÿÿ5.ÿCÿ0ÿ30ÿ70
ÿÿ6.ÿCÿ1ÿ40ÿ60
ÿÿ7.ÿend

.ÿ
.ÿ*
.ÿ*ÿBeginÿhere
.ÿ*
.ÿrenameÿappealsÿF1

.ÿgenerateÿintÿF0ÿ=ÿpetitionsÿ-ÿF1

.ÿdropÿpetitions

.ÿ
.ÿquietlyÿreshapeÿlongÿF,ÿi(agencyÿdrugrelated)ÿj(appeals)

.ÿsortÿagencyÿdrugrelatedÿappeals

.ÿlist,ÿnoobsÿsepby(agency)ÿabbreviate(20)

ÿÿ+-------------------------------------+
ÿÿ|ÿagencyÿÿÿdrugrelatedÿÿÿappealsÿÿÿÿFÿ|
ÿÿ|-------------------------------------|
ÿÿ|ÿÿÿÿÿÿAÿÿÿÿÿÿÿÿÿÿÿÿÿ0ÿÿÿÿÿÿÿÿÿ0ÿÿÿ40ÿ|
ÿÿ|ÿÿÿÿÿÿAÿÿÿÿÿÿÿÿÿÿÿÿÿ0ÿÿÿÿÿÿÿÿÿ1ÿÿÿ10ÿ|
ÿÿ|ÿÿÿÿÿÿAÿÿÿÿÿÿÿÿÿÿÿÿÿ1ÿÿÿÿÿÿÿÿÿ0ÿÿÿ50ÿ|
ÿÿ|ÿÿÿÿÿÿAÿÿÿÿÿÿÿÿÿÿÿÿÿ1ÿÿÿÿÿÿÿÿÿ1ÿÿÿ30ÿ|
ÿÿ|-------------------------------------|
ÿÿ|ÿÿÿÿÿÿBÿÿÿÿÿÿÿÿÿÿÿÿÿ0ÿÿÿÿÿÿÿÿÿ0ÿÿÿ80ÿ|
ÿÿ|ÿÿÿÿÿÿBÿÿÿÿÿÿÿÿÿÿÿÿÿ0ÿÿÿÿÿÿÿÿÿ1ÿÿÿ20ÿ|
ÿÿ|ÿÿÿÿÿÿBÿÿÿÿÿÿÿÿÿÿÿÿÿ1ÿÿÿÿÿÿÿÿÿ0ÿÿÿ10ÿ|
ÿÿ|ÿÿÿÿÿÿBÿÿÿÿÿÿÿÿÿÿÿÿÿ1ÿÿÿÿÿÿÿÿÿ1ÿÿÿ20ÿ|
ÿÿ|-------------------------------------|
ÿÿ|ÿÿÿÿÿÿCÿÿÿÿÿÿÿÿÿÿÿÿÿ0ÿÿÿÿÿÿÿÿÿ0ÿÿÿ40ÿ|
ÿÿ|ÿÿÿÿÿÿCÿÿÿÿÿÿÿÿÿÿÿÿÿ0ÿÿÿÿÿÿÿÿÿ1ÿÿÿ30ÿ|
ÿÿ|ÿÿÿÿÿÿCÿÿÿÿÿÿÿÿÿÿÿÿÿ1ÿÿÿÿÿÿÿÿÿ0ÿÿÿ20ÿ|
ÿÿ|ÿÿÿÿÿÿCÿÿÿÿÿÿÿÿÿÿÿÿÿ1ÿÿÿÿÿÿÿÿÿ1ÿÿÿ40ÿ|
ÿÿ+-------------------------------------+

.ÿ
.ÿexit

endÿofÿdo-file


.

[David Crow]

Thanks. Worked like a charm. Reshape was the ticket!