<jupyter_start><jupyter_text>pyAnVIL dashboard  installation> Ensure latest version installed<jupyter_code># Install a pip package in the current Jupyter kernel
import sys
# !{sys.executable} -m pip install   pyanvil --upgrade
!{sys.executable} -m pip show pyanvil<jupyter_output>Name: pyAnVIL
Version: 0.0.2rc16
Summary: AnVIL client library. Combines gen3, terra client APIs with single signon and data harmonization use cases.
Home-page: https://github.com/anvilproject/client-apis
Author: The AnVIL project
Author-email: <EMAIL>
License: UNKNOWN
Location: /home/jupyter-user/notebooks/packages
Requires: google-cloud-storage, Click, firecloud, gen3, attrdict, xmltodict
Required-by:<jupyter_text>validation> Note:  As a workaround, the tracking spreadsheet is embedded in the python package.  This spreadsheet provides a list of workspaces that should exist and associated dbGap accessions<jupyter_code># check installation, import should work
import os
import time
# embedded data tracking spreadsheet should exist    
# see https://docs.google.com/spreadsheets/d/1UvQimGHggygeJeTIPjIi6Ze3ryxsUdVjjn8BoIFkyho/edit#gid=552844485
from anvil.dbgap.api import DEFAULT_OUTPUT_PATH
assert os.path.isfile(DEFAULT_OUTPUT_PATH), "embedded data tracking spreadsheet should exist"<jupyter_output><empty_output><jupyter_text>extract> Extract all meta data, reconcile with google bucket and dbGap data<jupyter_code>import json
import logging
from anvil.util.reconciler import aggregate, DEFAULT_NAMESPACE

logging.basicConfig(level=logging.INFO, format='%(asctime)s %(levelname)-8s %(message)s')

# store aggregated data locally
DASHBOARD_OUTPUT_PATH = '/tmp/data_dashboard.json'


def reconcile_all(user_project, consortiums,  namespace=DEFAULT_NAMESPACE, output_path=DASHBOARD_OUTPUT_PATH):
    """Reconcile and aggregate results.

    e.g. bin/reconciler --user_project <your-billing-project>  --consortium CMG AnVIL_CMG.* --consortium CCDG AnVIL_CCDG.* --consortium GTEx ^AnVIL_GTEx_V8_hg38$ --consortium ThousandGenomes ^1000G-high-coverage-2019$
    """
    with open(output_path, 'w') as outs:
        json.dump({'projects': [v for v in aggregate(namespace, user_project, consortiums)]}, outs)


logging.info("Starting aggregation for all AnVIL workspaces, this will take several minutes.")        
reconcile_all(
    user_project = os.environ['GOOGLE_PROJECT'],
    consortiums = (
        ('CMG', 'AnVIL_CMG.*'),
        ('CCDG', 'AnVIL_CCDG.*'),
        ('GTEx', '^AnVIL_GTEx_V8_hg38$'),
        ('ThousandGenomes', '^1000G-high-coverage-2019$')
    )
)<jupyter_output>2020-09-10 19:34:04,757 INFO     Starting aggregation for all AnVIL workspaces, this will take several minutes.
2020-09-10 19:35:11,890 WARNING  AnVIL_CMG_Broad_Brain_Gleeson_WGS missing dbGap accession
2020-09-10 19:35:17,522 WARNING  AnVIL_CMG_Broad_Brain_Engle_WGS missing dbGap accession
2020-09-10 19:35:20,968 WARNING  AnVIL_CMG_Broad_Kidney_Pollak_WES missing dbGap accession
2020-09-10 19:35:24,529 WARNING  AnVIL_CMG_Broad_Orphan_Scott_WES missing dbGap accession
2020-09-10 19:35:28,651 WARNING  AnVIL_CMG_Broad_Blood_Fleming_WES missing dbGap accession
2020-09-10 19:35:31,041 WARNING  anvil_ccdg_broad_ai_ibd_daly_niddk_cho_wes missing dbGap accession
2020-09-10 19:35:32,566 WARNING  phs001155/ error: No qualified study for phs001155
2020-09-10 19:35:32,567 WARNING  No study found AnVIL_CCDG_WashU_CVD_EOCAD_BioMe_WGS accession: phs001155
2020-09-10 19:35:32,938 WARNING  AnVIL_ccdg_asc_ndd_daly_talkowski_chung_asd_exome missing dbGap accession
2020-09-10 19:35:33,649 WARNING  Study [...]<jupyter_text>validate<jupyter_code># validate output
import json
import os
DASHBOARD_OUTPUT_PATH = '/tmp/data_dashboard.json'
assert os.path.isfile(DASHBOARD_OUTPUT_PATH), "dashboard should exist"
with open(DASHBOARD_OUTPUT_PATH, 'r') as inputs:
    dashboard_data = json.load(inputs)<jupyter_output><empty_output><jupyter_text>transform> Flatten the results into a table<jupyter_code>from anvil.util.reconciler import flatten
import pandas as pd

(flattened, column_names) = flatten(dashboard_data['projects'])
df = pd.DataFrame(flattened)  
df.columns = column_names
# Print the data  (all rows, all columns)
pd.set_option('display.max_rows', None)
pd.set_option('display.max_columns', None)
df<jupyter_output><empty_output><jupyter_text>summarize> problems with comma separated list of workspaces<jupyter_code># explore
flattened = []
problems = set([problem for project in dashboard_data['projects'] for problem in project['problems']])
for problem in problems:
    projects = [project['project_id'] for project in dashboard_data['projects'] if problem in project['problems']]
    flattened.append([problem, ','.join(projects)])

# Print the data  (all rows, all columns)
pd.set_option('display.max_rows', None)
pd.set_option('display.max_columns', None)
pd.set_option('display.max_colwidth', None)
pd.set_option('display.colheader_justify', 'left')

df = pd.DataFrame(flattened)  
df.columns = ['problem', 'affected_workspaces']
df = df.style.set_properties(**{'text-align': 'left'})
df 
# print (df.to_string (justify='left', index=False))<jupyter_output><empty_output><jupyter_text>"load" > Copy results to bucket<jupyter_code># copy json results to bucket
!gsutil cp /tmp/data_dashboard.json  $WORKSPACE_BUCKET
# create a tsv from dataframe
df.to_csv("/tmp/data_dashboard.tsv", sep="\t")
# copy json results to bucket
!gsutil cp /tmp/data_dashboard.tsv  $WORKSPACE_BUCKET
!gsutil ls  $WORKSPACE_BUCKET<jupyter_output>gs://fc-secure-d8ae6fb6-76be-43a4-87a5-2ab255fc8d7d/data_dashboard.json
gs://fc-secure-d8ae6fb6-76be-43a4-87a5-2ab255fc8d7d/data_dashboard.tsv
gs://fc-secure-d8ae6fb6-76be-43a4-87a5-2ab255fc8d7d/notebooks/
{'fraction_non_alphanumeric': 0.10475690800979363, 'fraction_numerical': 0.05229101084295208, 'mean_word_length': 6.157697121401752, 'pattern_counts': {'":': 0, '<': 28, '<?xml version=': 0, '>': 34, 'https://': 2, 'lorem ipsum': 0, 'www.': 0, 'xml': 1}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '7', 'dir': 'jupyter-structured-clean-dedup', 'id': '98384', 'license': 'Apache-2.0', 'n_tokens_mistral': 2263, 'n_tokens_neox': 2050, 'n_words': 452, 'path': 'pyAnVIL/docs/_static/0.0.2.ipynb', 'repo_name': 'anvilproject/client-apis', 'size': '5726'}