pastbin

 https://pastebin.com/uBcFUXCA

1. import pandas as pd
2. dataset = %env WORKSPACE_CDR
3. query = """
4. SELECT
5. p.person_id AS person_id,
6. c.concept_name AS state_name
7. FROM `{dataset}.person` AS p
8. LEFT JOIN `{dataset}.concept` AS c ON p.state_of_residence_concept_id = c.concept_id
9. WHERE c.concept_name LIKE 'PII State: %'
10. """
11.  
12. state_df = pd.read_gbq(query.format(dataset = dataset))
13.  
14. state_df['state_of_residence'] = state_df['state_name'].str.replace('PII State: ', '')
15.  
16. state_df.head()
17. state_df.shape
18.  
19. homeless_state_df=state_df[state_df['person_id'].isin(homeless_respiratory_status['person_id'])]
20. homeless_state_df['state_of_residence'].value_counts()