Check rows within a nested partition have the same values

0 votes

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?

Sep 26, 2018 in Tableau by Naruto
• 710 points
31 views

1 answer to this question.

0 votes

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.

answered Sep 26, 2018 by AwesomeSauce
• 860 points

Related Questions In Tableau

0 votes
1 answer

how to get the count of non zero values in a row

In first part you should solve the ...READ MORE

answered Apr 4, 2018 in Tableau by Atul
• 10,240 points
136 views
0 votes
1 answer
0 votes
1 answer

How to calculate the percent of records within a group in tableau?

 You can click the measure SUM(Number of Records) and ...READ MORE

answered May 8, 2018 in Tableau by Atul
• 5,530 points
173 views
0 votes
1 answer

Creating a calculated field for summing up the values

Well you can use this in your ...READ MORE

answered May 17, 2018 in Tableau by Atul
• 5,530 points
39 views
+1 vote
1 answer

Is it possible to access or install tableau desktop insde tableau server?

Try to understand this, Tableau Server and ...READ MORE

answered Mar 29, 2018 in Tableau by xyz
• 1,550 points

edited Mar 29, 2018 by xyz 53 views
0 votes
1 answer

Hover text shows numbers in decimal points

Do this: 1. Right click on the calculated ...READ MORE

answered Apr 2, 2018 in Tableau by QueenBee
• 1,810 points
28 views
0 votes
1 answer

Dropdown to change x-axis in Tableau

You need to create a  str datatype parameter ...READ MORE

answered Apr 3, 2018 in Tableau by QueenBee
• 1,810 points
51 views
0 votes
1 answer

Tableau Desktop- Tableau Server

No, you don't necessarily need to purchase ...READ MORE

answered Apr 3, 2018 in Tableau by QueenBee
• 1,810 points
47 views
0 votes
1 answer

Want to hide the Bar for Grand Total in the bar chart in Tableau

Tableau treats the whole visualization uniformly.  If you ...READ MORE

answered Aug 10, 2018 in Tableau by AwesomeSauce
• 860 points
547 views
0 votes
1 answer

How to Split a Measure in Tableau?

Have you tried using an IF statement: IF ...READ MORE

answered Aug 14, 2018 in Tableau by AwesomeSauce
• 860 points
91 views