Need a matrix with counts of intersecting values

Question

I have a data frame which looks like this:

Fruit    Colour
Apple      Red
Apple      Green   
Cherry     Red
Lemon      Yellow 
Banana     Yellow
Blueberry  Purple
Grapes     Purple
Grapes     Green

And would like a matrix which looks like this:

           Apple    Cherry    Lemon    Banana    Blueberry    Grapes
Apple       0         1         0         0         0            1            
Cherry      1         0         0         0         0            0
Lemon       0         0         0         1         0            0
Banana      0         0         1         0         0            0
Blueberry   0         0         0         0         0            1         
Grapes      1         0         0         0         1            0

Corresponding to a the number of shared values between the rows in the colour column.

I've tried something like this:

df1 <- dcast(fruit_frame, Fruit~Colour)

Which gives me a dataframe with the colour as the columns and fruit as rows and number of occurrences of each colour but it isn't quite what I am looking for. Is there an easy way to do this in R or python?

Thanks you in advance.

If it's python, it has to be with pandas. Is that what you're using (or open to)? — cs95, May 27 '19 at 04:38
Please also complete the table and show your expected result. — cs95, May 27 '19 at 04:41
@akrun this is giving me a product of the values, rather than the number of shared occurrences? — epigeneticist, May 27 '19 at 04:46
OK, how does `u = pd.crosstab(df.Fruit, df.Colour); v = u.dot(u.T); np.fill_diagonal(v.values, 0); print(v)` work? — cs95, May 27 '19 at 04:47
@epigeneticist - it gives the shared occurrences. The diagonal is the raw frequency count, or how often the value occurs in combination with itself. — thelatemail, May 27 '19 at 04:53
@epigeneticist Wouldn't Apple and Grapes share 'Green' ? (based on the input example) — akrun, May 27 '19 at 05:01
@epigeneticist In the first column also, there should be one more 1 — akrun, May 27 '19 at 05:06

score 3 · Accepted Answer · answered May 27 '19 at 04:42

3

An option in R would be

out <- tcrossprod(table(fruit_frame))
diag(out) <- 0

answered May 27 '19 at 04:42

akrun

874,273
37
540
662

Need a matrix with counts of intersecting values

1 Answers1