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)
