# How to reconstruct database from cross-tabulated data?

I am given a set of almost 100 crosstabulations (most 2D, but some three dimensional) based on a single, yet unavailable dataset. My task is to perform basic statistical tests ($\chi^2$, Mann-Whitney, Kruskal-Wallis, etc.). I could calculate them by hand, by it would be over one order of magnitude faster to use my "streamlined" procedures which automatically make beautiful Excel figures, tables and stuff. Unfortunately my tools use database as input and I cannot feed them with summaries. The crosstabs do overlap, i.e. if one crosstab is between var1 and var2, the could well be between $var1 \times var3$.

Is there any existing software (e.g. in R) that I can use to create any database (dataset) which agrees with all given crosstabulations?

(I know, that reconstructing database from cross tabulations is not a unique problem. I know that everyone will be better off having a proper database to start with.)

                                         O  O  O


I suspect, that there is no ready solution, so I set writing one:

Every crosstab can be written as a set of linear equations (one equation for every known frequency, coefficients equal to 1 or 0), so from mathematical point of view I see the problem equivalent to finding integer solution to set of linear equations. It is a problem from integer programming domain, just like mr. whuber suggested. Unfortunately there is a problem in prohibitive size of the equations; the only way I can think up is by partition the dataset into $\text{no}(var1) \times \text{no}(var2) \times \text{no}(var3)$ groups, where $\text{no}(var)$ denotes number of distinct levels (groups) of variable var. It might be fine for this simple example, but in my case, when I multiply all the numbers of levels of each variable I come up with astronomical number in order of $10^{25}$. And I suspect that sparse matrices optimization wouldn't help, so all I wrote might just be the dead end.

Does anyone have any idea about how to solve this problem?

• This looks like a programmic not statistical question – ttnphns Jul 21 '12 at 13:19
• True. But I'm not that interested in algorithm. The question is targeted on discovery of existing, yet arcane tools, so I will not reinvent the wheel writing one. – Adam Ryczkowski Jul 21 '12 at 13:30
• Do you have access to any linear programming software that can solve integer programming problems? – whuber Jul 21 '12 at 16:32
• Will Mathematica (Wolfram Research) do? – Adam Ryczkowski Jul 22 '12 at 16:06
• Yes, Adam, it has that capability. I demonstrate an application here: mathematica.stackexchange.com/a/6888/91 – whuber Aug 14 '12 at 20:51

## 2 Answers

In R you can use the as.data.frame.table and rep commands to get back to original data from a crosstabs type object:

> mydf <- data.frame( one= sample( letters[1:5], 100, TRUE),
+ two= sample(LETTERS[1:5], 100, TRUE) )
>
> tab1 <- table(one=mydf$one, two=mydf$two)
> tab1
two
one A B C D E
a 5 7 3 2 0
b 6 6 7 7 3
c 6 5 3 1 5
d 3 9 4 3 4
e 5 0 2 1 3
>
> mydf2 <- as.data.frame.table(tab1)
> head(mydf2)
one two Freq
1   a   A    5
2   b   A    6
3   c   A    6
4   d   A    3
5   e   A    5
6   a   B    7
>
> mydf3 <- mydf2[ rep( 1:nrow(mydf2), mydf2$Freq ), -3 ] > head(mydf3) one two 1 a A 1.1 a A 1.2 a A 1.3 a A 1.4 a A 2 b A > > rownames(mydf3) <- NULL > mydf <- mydf[ order( mydf$two, mydf$one ), ] > rownames(mydf) <- NULL > > all.equal(mydf, mydf3)  TRUE  • That certeinly works, but only if one has one crosstab or at least set of crosstabs which doesn't overlap. But what if I have one crosstab var1 x var2, and second crosstab var1 x var3? I can't see any easy way of generalizing the proposed approach in this scenerio. – Adam Ryczkowski Jul 21 '12 at 19:48 • You will need to make some assumptions about the relationship between var2 and var3, conditional independence would be simplest. First create the data frame for var1 and var2, then within the levels of var1 use rep to give the values for var3 and sample to permute them (independence). You could use a loop or the plyr package may help. – Greg Snow Jul 21 '12 at 20:41 • Actually it is probably simpler than the above comment, just create the var1*var2 data frame, then create the var1*var3 data frame, sort both by var1 and use cbind to attach the var3 column from the 2nd df to the var1*var2 data frame. If the marginals of var1 match (which they should if they came from the same original data set) then everything will now work out (possibly need to randomize df2 before sorting). – Greg Snow Jul 22 '12 at 0:52 • Ok. It is a smart idea, but still, I can't see a way to generalize it. My problem consist of exactly 48 distinct crosstabs, which are based on only 45 unique variables. Most cross tabulations are 2D, but some are 3D. – Adam Ryczkowski Jul 22 '12 at 16:14 You say you have about 100 cross-tabulations? Is it from one dataset? How many variables in total? From$p$variables there are$p\choose 2\$ 2-dim cross-tabulations, if you have all of those 2-dim cross-tabulations, this becomes a kind of discrete Radon transform. Maybe it is possible to get some information from this, for instance by making an algorithm for simulating from the (a) conditional distribution of the table given all those cross-sections. Maybe something is written about this lines? try to google for "radon transform contingency table" (I do get some hits).

• My problem consist of exactly 48 distinct crosstabs, which are based on exactly 45 unique variables. Most cross tabulations are 2D, but some are 3D. Definitely I don't have all the margins. – Adam Ryczkowski Jul 22 '12 at 16:15
• Some variables have 2 levels, some more. And deifinitely I don't have all possible cross tabulations on this dataset. I know the problem does not have unique solution, I juest don't care which one I would get. – Adam Ryczkowski Jul 22 '12 at 18:50