Check rows within a nested partition have the same values

Question

I have a table with two IDs, and I need to check that for a particular ID1 and ID2, all the products are the same and the same number of products.

For example in the table below, I have 10001 which has 123 and 234, and there's a line missing which is 123 having Product 2, and

for 20002, 345 and 456 both have Product 3 and 4 but, there's a difference in the last product. I need to find such cases in my data.

ID1     ID2     Product
10001   123     Product 1
10001   234     Product 1
10001   234     Product 2
20002   345     Product 3
20002   345     Product 4
20002   345     Product 5
20002   456     Product 3
20002   456     Product 4
20002   456     Product 6

The perfect scenario will be, which will be correct.

ID1     ID2     Product
10001   123     Product 1
10001   123     Product 2
10001   234     Product 1
10001   234     Product 2
20002   345     Product 3
20002   345     Product 4
20002   345     Product 5
20002   456     Product 3
20002   456     Product 4
20002   456     Product 5

Basically I need to find all the cases in my data where in a particular ID1, all the ID2's don't have consistent products, by consistent products I mean all ID2s should have the same products within an ID1.

Any suggestions on a way to find the cases in the first table?

Answer 1

Imagine you've loaded your data into a dict, and product list is a set (this would help you guarantee that products aren't duplicated for an id1, id2, by the way):

data = {
    10001: {
        123: set([1]),
        234: set([1,2])
    },
    20002: {
        345: set([3,4,6]),
        456: set([3,4,6])
    }
}

Then you can check if two values for id2 have the same items by using the '^' operator on sets. Check https://docs.python.org/3/library/stdtypes.html#set. For example:

a = data[10001][123]
b = data[10001][234]
c = a ^ b # len(c) will be >0 !!

'^' calculatesthe symmetric difference between both sets, so it will return the empty set if and only if both sets are equal.

So you can iterate over all id2 keys for a given id1 and break with a message once '^' of it and the previous one hasn't got zero len. Example:

for id1 in data:
    last_seen = None
    for id2 in data[id1]:
        actual = data[id1][id2]
        if last_seen != None and len(last_seen ^ actual) != 0:
                print('Items for id1 {} are not equal'.format(id1))
                break
        last_seen = actual

This is supposing your csv file isn't necessarly ordered so you needed to load it into a dict... If your file is ordered by ids then you can read the file and do the job at once, of course, i'm sure you can adapt this.