Great Expectations expect column to contain only integers fails for all rows when only one is bad

user_1011724 0
Viewed 4017

I want to use the great expectations package to validate that a column in a .csv file only contains integers.

The file I am using has only integers in the age column except for one row which has a '`' character instead. This is what I want the expectation to catch. I have also checked the .csv file in a text editor and can confirm that the ages in the age column are not enclosed in quotes.

However, the expectation fails one 100% of the data. I think it is because pandas is reading the column in as the object type (so a string) because of the one incorrect row. I can preprocess this using something like .astype(int) becauseit will fail on that row. And wrapping .astype(int) in a try block would completely defeat the purpose of using great expectations for this.

Here is a minimal working example:

good.csv:

age,name
34,Fred
22,Bob
54,Mary

bad.csv:

age,name
34,Fred
`,Bob
54,Mary

Code:

import great_expectations as ge

df = ge.read_csv("./good.csv");
my_df.expect_column_values_to_be_of_type('age','int')

df = ge.read_csv("./bad.csv");
my_df.expect_column_values_to_be_of_type('age','int')

The first case returns

{'success': True,
 'result': {'element_count': 3,
  'missing_count': 0,
  'missing_percent': 0.0,
  'unexpected_count': 0,
  'unexpected_percent': 0.0,
  'unexpected_percent_nonmissing': 0.0,
  'partial_unexpected_list': []}}

So all the ages are ints and it succeeds on every row. I expect the second case to fail, but only on the second row. However it fails on all the rows:

{'success': False,
 'result': {'element_count': 3,
  'missing_count': 0,
  'missing_percent': 0.0,
  'unexpected_count': 3,
  'unexpected_percent': 1.0,
  'unexpected_percent_nonmissing': 1.0,
  'partial_unexpected_list': ['34', '`', '54']}}

So I guess I expect something like this:

{'success': False,
 'result': {'element_count': 3,
  'missing_count': 0,
  'missing_percent': 0.0,
  'unexpected_count': 1,
  'unexpected_percent': 0.33,
  'unexpected_percent_nonmissing': 1.0,
  'partial_unexpected_list': ['`']}}

Is there something I am doing wrong, or is the package just not capable of this?

great-expectations pandas validation python types
4 Answers

This appears to be an error that is known but not yet resolved (as of 9/2018):

https://github.com/great-expectations/great_expectations/issues/110

Developers are actively working on improving behavior:

  1. expect_column_values_to_be_of_type (the current expectation) has been cleaned up to now be closer to what we think people expect, and we are planning to rename it expect_column_values_to_be_stored_as_type

  2. We plan to introduce a new expectation expect_column_values_to_be_parseasble_as_type that accepts: string, float, int, bool (others?) and focuses on a more semantically meaningful understanding of the type (i.e. if a value is a string but the string is "1.0" then it will pass for string, float, and int).

And hopefully will have results soon:

Implementation on 1 and 2 is about to start, but we're still open to suggestions...

user_20749810

The problem is that your values aren't actually integers, they are strings. When you read your csv with great_expectations.read_csv() it uses pandas.read_csv() internally which will default the data in your age column to strings because of the ´.

The method expect_column_values_to_be_of_type() is very basic meaning that it will only check if your value is the type your looking for (isinstance()), not if it "could" be that type.

user_34718810

As a workaround until the new expect_column_values_to_be_parseasble_as_type is implemented, I can achieve the same result using a regex expectation instead:

my_df = ge.read_csv("bad.csv")
pattern = r'^\d+$'
result  = my_df.expect_column_values_to_match_regex('age',
                                                    pattern,
                                                    result_format={'result_format': 'COMPLETE'})

result
# In my case I'm only interested in where it went wrong
# print(result['result']['unexpected_list'])
# print(result['result']['unexpected_index_list'])

which gives:

{'success': False,
 'result': {'element_count': 3,
  'missing_count': 0,
  'missing_percent': 0.0,
  'unexpected_count': 1,
  'unexpected_percent': 0.3333333333333333,
  'unexpected_percent_nonmissing': 0.3333333333333333,
  'partial_unexpected_list': ['`'],
  'partial_unexpected_index_list': [1],
  'partial_unexpected_counts': [{'value': '`', 'count': 1}],
  'unexpected_list': ['`'],
  'unexpected_index_list': [1]}}

Note if you want to allow a leading + or - you need to change the pattern to:

pattern = r'^[+-]?\d+$'
user_10117240

I am not aware of great_expectations, But you can solve this in pandas simply using this,

print df[pd.to_numeric(df['age'].fillna(0),errors='coercs').isnull()]['age'].tolist()

Output for good.csv

[]

Output for bad.csv

['`']
user_46848610
Related