def run_agg_query(self, df, metadata, query, confidence, get_exact=True): """ Run the query using the private reader and input query Get query response back """ reader = PandasReader(df, metadata) actual = 0.0 # VAR not supported in Pandas Reader. So not needed to fetch actual on every aggregation if (get_exact): actual = reader.execute_typed(query).rows()[1:][0][0] private_reader = PrivateReader(reader, metadata, self.epsilon) query_ast = private_reader.parse_query_string(query) srs_orig = private_reader.reader.execute_ast_typed(query_ast) noisy_values = [] low_bounds = [] high_bounds = [] for idx in range(self.repeat_count): srs = TypedRowset(srs_orig.rows(), list(srs_orig.types.values())) res = private_reader._execute_ast(query_ast, True) # Disabled because confidence interval not available in report #interval = res.report[res.colnames[0]].intervals[confidence] #low_bounds.append(interval[0].low) #high_bounds.append(interval[0].high) noisy_values.append(res.rows()[1:][0][0]) return np.array(noisy_values), actual, low_bounds, high_bounds
def run_agg_query_df(self, df, metadata, query, confidence, file_name="d1"): """ Run the query using the private reader and input query Get query response back for multiple dimensions and aggregations """ # Getting exact result reader = PandasReader(df, metadata) exact = reader.execute_typed(query).rows()[1:] exact_res = [] for row in exact: exact_res.append(row) private_reader = PrivateReader(reader, metadata, self.epsilon) query_ast = private_reader.parse_query_string(query) # Distinguishing dimension and measure columns srs_orig = private_reader.reader.execute_ast_typed(query_ast) srs = TypedRowset(srs_orig.rows(), list(srs_orig.types.values())) sample_res = private_reader._execute_ast(query_ast, True) headers = sample_res.colnames dim_cols = [] num_cols = [] for col in headers: if (sample_res.types[col] == "string"): dim_cols.append(col) else: num_cols.append(col) # Repeated query and store results along with intervals res = [] for idx in range(self.repeat_count): dim_rows = [] num_rows = [] srs = TypedRowset(srs_orig.rows(), list(srs_orig.types.values())) singleres = private_reader._execute_ast(query_ast, True) values = singleres[col] for col in dim_cols: dim_rows.append(singleres[col]) for col in num_cols: values = singleres[col] #low = singleres.report[col].intervals[confidence].low #high = singleres.report[col].intervals[confidence].high #num_rows.append(list(zip(values, low, high))) num_rows.append(list(zip(values))) res.extend(list(zip(*dim_rows, *num_rows))) exact_df = pd.DataFrame(exact_res, columns=headers) noisy_df = pd.DataFrame(res, columns=headers) # Add a dummy dimension column for cases where no dimensions available for merging D1 and D2 if (len(dim_cols) == 0): dim_cols.append("__dim__") if (dim_cols[0] == "__dim__"): exact_df[dim_cols[0]] = ["key"] * len(exact_df) noisy_df[dim_cols[0]] = ["key"] * len(noisy_df) return noisy_df, exact_df, dim_cols, num_cols
import pandas as pd from opendp.smartnoise.sql import PandasReader, PrivateReader from opendp.smartnoise.metadata import CollectionMetadata pums = pd.read_csv('PUMS.csv') meta = CollectionMetadata.from_file('PUMS.yaml') query = 'SELECT married, AVG(income) AS income, COUNT(*) AS n FROM PUMS.PUMS GROUP BY married' query = 'SELECT COUNT(*) AS n, COUNT(pid) AS foo FROM PUMS.PUMS WHERE age > 80 GROUP BY educ' reader = PandasReader(meta, pums) private_reader = PrivateReader(meta, reader, 4.0) private_reader.options.censor_dims = True private_reader.options.clamp_counts = True exact = reader.execute_typed(query) print(exact) private = private_reader.execute_typed(query) print(private)