1

I pulled this nested json data from database and I think it's a list of dictionary. (I am not sure, I am new to python)

I tried many codes posted on stack overflow but none of them addressed my particular problem, I always ran into errors...

The data is quite large, there are in total more than 100 usageId. I'm only showing the first one, which looks like this:

[{'usageId': 'e83f43f8-ec4a-402d-a64e-d74b6f1df4a7',
  'assessment_status_date': '2022-03-28',
  'assessment_date': '2020-12-07',
  'usage_assessment': 'Level 1',
  'has_l3test': None,
  'compensating_control': None,
  'recommendations': None,
  'test_category': {'Usage Reconciliation': {'test_category_assessment_date': None,
    'last_updated_by': None,
    'test_execution': {'Health and Welfare Plan + Usage Reconciliation': {'evidence_capture': None,
      'test_result_justification': 'Test out of scope',
      'latest_test_result_date': '2019-10-02',
      'last_updated_by': None,
      'test_execution_status': 'In Progress',
      'test_result': None}},
    'test_category_assessment': None,
    'test_category_status': None,
    'test_category_assessment_justification': None},
   'Data Agreements': {'test_category_assessment_date': None,
    'last_updated_by': None,
    'test_execution': {'Health and Welfare Plan + Data Agreements': {'evidence_capture': None,
      'test_result_justification': 'xxx',
      'latest_test_result_date': '2019-10-02',
      'last_updated_by': None,
      'test_execution_status': 'In Progress',
      'test_result': None}},
    'test_category_assessment': None,
    'test_category_status': None,
    'test_category_assessment_justification': None},
   'Data Elements': {'test_category_assessment_date': None,
    'last_updated_by': None,
    'test_execution': {'Health and Welfare Plan + Data Elements': {'evidence_capture': None,
      'test_result_justification': 'The rationale provided for why the Usage contains no HPDEs appears valid',
      'latest_test_result_date': '2019-10-02',
      'last_updated_by': None,
      'test_execution_status': 'In Progress',
      'test_result': None}},
    'test_category_assessment': None,
    'test_category_status': None,
    'test_category_assessment_justification': None},
   'Computations': {'test_category_assessment_date': None,
    'last_updated_by': None,
    'test_execution': {'Health and Welfare Plan + Computations': {'evidence_capture': None,
      'test_result_justification': None,
      'latest_test_result_date': None,
      'last_updated_by': None,
      'test_execution_status': 'Not Started',
      'test_result': None}},
    'test_category_assessment': None,
    'test_category_status': None,
    'test_category_assessment_justification': None},
   'Lineage': {'test_category_assessment_date': None,
    'last_updated_by': None,
    'test_execution': {'Health and Welfare Plan + Lineage': {'evidence_capture': None,
      'test_result_justification': None,
      'latest_test_result_date': None,
      'last_updated_by': None,
      'test_execution_status': 'Not Started',
      'test_result': None}},
    'test_category_assessment': None,
    'test_category_status': None,
    'test_category_assessment_justification': None},
   'Metadata': {'test_category_assessment_date': None,
    'last_updated_by': None,
    'test_execution': {'Health and Welfare Plan + Metadata': {'evidence_capture': None,
      'test_result_justification': 'Valid',
      'latest_test_result_date': '2020-07-02',
      'last_updated_by': None,
      'test_execution_status': 'Completed',
      'test_result': 'Pass without Compensating Controls'}},
    'test_category_assessment': None,
    'test_category_status': None,
    'test_category_assessment_justification': None},
   'Data Quality Monitoring': {'test_category_assessment_date': None,
    'last_updated_by': None,
    'test_execution': {'Health and Welfare Plan + Data Quality Monitoring': {'evidence_capture': None,
      'test_result_justification': 'xxx',
      'latest_test_result_date': '2019-08-09',
      'last_updated_by': None,
      'test_execution_status': 'Completed',
      'test_result': 'Pass without Compensating Controls'}},
    'test_category_assessment': None,
    'test_category_status': None,
    'test_category_assessment_justification': None},
   'HPU Source Reliability': {'test_category_assessment_date': None,
    'last_updated_by': None,
    'test_execution': {'Health and Welfare Plan + HPU Source Reliability': {'evidence_capture': None,
      'test_result_justification': 'xxx',
      'latest_test_result_date': '2019-10-02',
      'last_updated_by': None,
      'test_execution_status': 'In Progress',
      'test_result': None}},
    'test_category_assessment': None,
    'test_category_status': None,
    'test_category_assessment_justification': None},
   'Change Notification': {'test_category_assessment_date': None,
    'last_updated_by': None,
    'test_execution': {'Health and Welfare Plan + Change Notification': {'evidence_capture': None,
      'test_result_justification': None,
      'latest_test_result_date': None,
      'last_updated_by': None,
      'test_execution_status': 'Not Started',
      'test_result': None}},
    'test_category_assessment': None,
    'test_category_status': None,
    'test_category_assessment_justification': None}},
  'assessment_status': 'In Progress',
  'recommendation_indicator': None,
  'assessment_justification': None,
  'revalidation_justification': None},
 {'usageId': 'b3c9cbbd-fb72-46df-a4a3-6dd1e1edce64',
  'assessment_status_date': '2022-03-28',
  'assessment_date': '2020-12-07',
  'usage_assessment': 'Level 1',
  'has_l3test': None,
  'compensating_control': None,
  'recommendations': None,
  'test_category': {'Usage Reconciliation': {'test_category_assessment_date': None,
    'last_updated_by': None,
    'test_execution': {'New or Changed Usage Reconciles with Prior Usage': {'evidence_capture': None,
      'test_result_justification': 'xxx',
      'latest_test_result_date': '2020-10-23',
      'last_updated_by': None,
      'test_execution_status': 'Completed',
      'test_result': 'Pass without Compensating Controls'}},
    'test_category_assessment': None,
    'test_category_status': None,
    'test_category_assessment_justification': None},
   'Data Agreements': {'test_category_assessment_date': None,
    'last_updated_by': None,
    'test_execution': {'Data Agreement Reviewed and Approved in Last Year': {'evidence_capture': None,
      'test_result_justification': 'xxx',
      'latest_test_result_date': '2020-07-21',
      'last_updated_by': None,
      'test_execution_status': 'Completed',
      'test_result': 'Pass without Compensating Controls'}},
    'test_category_assessment': None,
    'test_category_status': None,
    'test_category_assessment_justification': None},
   'Data Elements': {'test_category_assessment_date': None,
    'last_updated_by': None,
    'test_execution': {'HPDEs Identified': {'evidence_capture': None,
      'test_result_justification': 'Valid',
      'latest_test_result_date': '2020-07-02',
      'last_updated_by': None,
      'test_execution_status': 'Completed',
      'test_result': 'Pass without Compensating Controls'},
     'HPDE Justification is Documented and Reasonable': {'evidence_capture': None,
      'test_result_justification': 'xxx',
      'latest_test_result_date': '2020-02-28',
      'last_updated_by': None,
      'test_execution_status': 'Completed',
      'test_result': 'Pass without Compensating Controls'},
     'HPDE Identification Rationale is Valid': {'evidence_capture': None,
      'test_result_justification': 'xxx',
      'latest_test_result_date': '2020-02-28',
      'last_updated_by': None,
      'test_execution_status': 'Completed',
      'test_result': 'Pass without Compensating Controls'},
     'Usage Output is Documented and Metadata is Registered': {'evidence_capture': None,
      'test_result_justification': 'xxx',
      'latest_test_result_date': '2020-08-07',
      'last_updated_by': None,
      'test_execution_status': 'Completed',
      'test_result': 'Pass without Compensating Controls'},
     'Data Element Metadata is in Curated State': {'evidence_capture': None,
      'test_result_justification': 'xxx',
      'latest_test_result_date': None,
      'last_updated_by': 'tat00000',
      'test_execution_status': 'In Progress',
      'test_result': None},
     'Secured Data Indicator Consistency': {'evidence_capture': None,
      'test_result_justification': None,
      'latest_test_result_date': None,
      'last_updated_by': None,
      'test_execution_status': 'Not Started',
      'test_result': None},
     'HPDE Metadata is in Curated State': {'evidence_capture': None,
      'test_result_justification': 'Valid',
      'latest_test_result_date': '2020-07-02',
      'last_updated_by': None,
      'test_execution_status': 'Completed',
      'test_result': 'Pass without Compensating Controls'}},
    'test_category_assessment': None,
    'test_category_status': None,
    'test_category_assessment_justification': None},
   'Computations': {'test_category_assessment_date': None,
    'last_updated_by': None,
    'test_execution': {'Usage Outcome is Accurate': {'evidence_capture': None,
      'test_result_justification': None,
      'latest_test_result_date': None,
      'last_updated_by': None,
      'test_execution_status': 'Not Started',
      'test_result': None}},
    'test_category_assessment': None,
    'test_category_status': None,
    'test_category_assessment_justification': None},
   'Lineage': {'test_category_assessment_date': None,
    'last_updated_by': None,
    'test_execution': {'Lineage is Accurate Reflection of Run': {'evidence_capture': None,
      'test_result_justification': None,
      'latest_test_result_date': None,
      'last_updated_by': None,
      'test_execution_status': 'Not Started',
      'test_result': None},
     'Partial Lineage from Authoritiative or Acceptable Source to Originating Source Exists and is Reasonable': {'evidence_capture': None,
      'test_result_justification': None,
      'latest_test_result_date': None,
      'last_updated_by': None,
      'test_execution_status': 'Not Started',
      'test_result': None},
     'Lineage from Usage to Authoritative or Acceptable Source is Complete': {'evidence_capture': None,
      'test_result_justification': None,
      'latest_test_result_date': None,
      'last_updated_by': None,
      'test_execution_status': 'Not Started',
      'test_result': None},
     'Quality is Sufficient': {'evidence_capture': None,
      'test_result_justification': None,
      'latest_test_result_date': None,
      'last_updated_by': None,
      'test_execution_status': 'Not Started',
      'test_result': None}},
    'test_category_assessment': None,
    'test_category_status': None,
    'test_category_assessment_justification': None},
   'Metadata': {'test_category_assessment_date': None,
    'last_updated_by': None,
    'test_execution': {'Usage Description is Valid': {'evidence_capture': None,
      'test_result_justification': 'xx',
      'latest_test_result_date': '2020-02-28',
      'last_updated_by': None,
      'test_execution_status': 'Completed',
      'test_result': 'Pass without Compensating Controls'},
     "Usage SME's EID Status is Valid": {'evidence_capture': None,
      'test_result_justification': 'Valid',
      'latest_test_result_date': '2020-07-02',
      'last_updated_by': None,
      'test_execution_status': 'Completed',
      'test_result': 'Pass without Compensating Controls'},
     'Usage AE': {'evidence_capture': None,
      'test_result_justification': 'Valid',
      'latest_test_result_date': '2020-07-02',
      'last_updated_by': None,
      'test_execution_status': 'Completed',
      'test_result': 'Pass without Compensating Controls'}},
    'test_category_assessment': None,
    'test_category_status': None,
    'test_category_assessment_justification': None},
   'Data Quality Monitoring': {'test_category_assessment_date': None,
    'last_updated_by': None,
    'test_execution': {'Data Defect Tracking Process is Reasonable': {'evidence_capture': None,
      'test_result_justification': 'xxx',
      'latest_test_result_date': '2020-02-28',
      'last_updated_by': None,
      'test_execution_status': 'Completed',
      'test_result': 'Pass without Compensating Controls'},
     'Data Movement is Reasonable': {'evidence_capture': None,
      'test_result_justification': 'xxx',
      'latest_test_result_date': '2020-10-23',
      'last_updated_by': None,
      'test_execution_status': 'Completed',
      'test_result': 'Fail'},
     'DQ Rules and Thresholds are Comprehensive': {'evidence_capture': None,
      'test_result_justification': None,
      'latest_test_result_date': None,
      'last_updated_by': None,
      'test_execution_status': 'In Progress',
      'test_result': 'Fail'},
     'Data Defect Tracking Process is Operating Effectively': {'evidence_capture': None,
      'test_result_justification': None,
      'latest_test_result_date': None,
      'last_updated_by': None,
      'test_execution_status': 'Not Started',
      'test_result': None},
     'DQ Monitoring Plan is Reasonable': {'evidence_capture': None,
      'test_result_justification': 'xxx',
      'latest_test_result_date': '2020-10-22',
      'last_updated_by': None,
      'test_execution_status': 'Completed',
      'test_result': 'Pass without Compensating Controls'}},
    'test_category_assessment': None,
    'test_category_status': None,
    'test_category_assessment_justification': None},
   'HPU Source Reliability': {'test_category_assessment_date': None,
    'last_updated_by': None,
    'test_execution': {'Usage Consumes from Acceptable Source': {'evidence_capture': None,
      'test_result_justification': 'xxx',
      'latest_test_result_date': '2020-10-23',
      'last_updated_by': None,
      'test_execution_status': 'Completed',
      'test_result': 'Fail'},
     'Usage Consumes from Approved Source': {'evidence_capture': None,
      'test_result_justification': None,
      'latest_test_result_date': None,
      'last_updated_by': None,
      'test_execution_status': 'Not Started',
      'test_result': None}},
    'test_category_assessment': None,
    'test_category_status': None,
    'test_category_assessment_justification': None},
   'Change Notification': {'test_category_assessment_date': None,
    'last_updated_by': None,
    'test_execution': {'Change Notification Process is Reasonable': {'evidence_capture': None,
      'test_result_justification': 'xxx',
      'latest_test_result_date': '2020-07-21',
      'last_updated_by': None,
      'test_execution_status': 'Completed',
      'test_result': 'Pass without Compensating Controls'},
     'Change Notification Process is Operating Effectively': {'evidence_capture': None,
      'test_result_justification': None,
      'latest_test_result_date': None,
      'last_updated_by': None,
      'test_execution_status': 'Not Started',
      'test_result': None}},
    'test_category_assessment': None,
    'test_category_status': None,
    'test_category_assessment_justification': None}},
  'assessment_status': 'In Progress',
  'recommendation_indicator': None,
  'assessment_justification': None,
  'revalidation_justification': None},
 {'usageId': 'c67a1567-2de3-4826-97bb-99838b405acd',
  'assessment_status_date': '2022-03-28',
  'assessment_date': '2020-12-07',
  'usage_assessment': 'Level 1',
  'has_l3test': None,
  'compensating_control': None,
  'recommendations': None,
  'test_category': {'Usage Reconciliation': {'test_category_assessment_date': None,
    'last_updated_by': None,
    'test_execution': {'Provided Health Insur Offer & Coverage Information Returns (1094C) + Usage Reconciliation': {'evidence_capture': None,
      'test_result_justification': 'Test out of scope',
      'latest_test_result_date': '2019-10-02',
      'last_updated_by': None,
      'test_execution_status': 'In Progress',
      'test_result': None}},
    'test_category_assessment': None,
    'test_category_status': None,
    'test_category_assessment_justification': None},
   'Data Agreements': {'test_category_assessment_date': None,
    'last_updated_by': None,
    'test_execution': {'Provided Health Insur Offer & Coverage Information Returns (1094C) + Data Agreements': {'evidence_capture': None,
      'test_result_justification': 'xxx',
      'latest_test_result_date': '2019-10-21',
      'last_updated_by': None,
      'test_execution_status': 'Completed',
      'test_result': 'Pass without Compensating Controls'}},
    'test_category_assessment': None,
    'test_category_status': None,
    'test_category_assessment_justification': None},
   'Data Elements': {'test_category_assessment_date': None,
    'last_updated_by': None,
    'test_execution': {'Provided Health Insur Offer & Coverage Information Returns (1094C) + Data Elements': {'evidence_capture': None,
      'test_result_justification': 'Valid',
      'latest_test_result_date': '2020-07-02',
      'last_updated_by': None,
      'test_execution_status': 'Completed',
      'test_result': 'Pass without Compensating Controls'}},
    'test_category_assessment': None,
    'test_category_status': None,
    'test_category_assessment_justification': None},
   'Computations': {'test_category_assessment_date': None,
    'last_updated_by': None,
    'test_execution': {'Provided Health Insur Offer & Coverage Information Returns (1094C) + Computations': {'evidence_capture': None,
      'test_result_justification': None,
      'latest_test_result_date': None,
      'last_updated_by': None,
      'test_execution_status': 'Not Started',
      'test_result': None}},
    'test_category_assessment': None,
    'test_category_status': None,
    'test_category_assessment_justification': None},
   'Lineage': {'test_category_assessment_date': None,
    'last_updated_by': None,
    'test_execution': {'Provided Health Insur Offer & Coverage Information Returns (1094C) + Lineage': {'evidence_capture': None,
      'test_result_justification': None,
      'latest_test_result_date': None,
      'last_updated_by': None,
      'test_execution_status': 'Not Started',
      'test_result': None}},
    'test_category_assessment': None,
    'test_category_status': None,
    'test_category_assessment_justification': None},
   'Metadata': {'test_category_assessment_date': None,
    'last_updated_by': None,
    'test_execution': {'Provided Health Insur Offer & Coverage Information Returns (1094C) + Metadata': {'evidence_capture': None,
      'test_result_justification': 'Valid',
      'latest_test_result_date': '2020-07-02',
      'last_updated_by': None,
      'test_execution_status': 'Completed',
      'test_result': 'Pass without Compensating Controls'}},
    'test_category_assessment': None,
    'test_category_status': None,
    'test_category_assessment_justification': None},
   'Data Quality Monitoring': {'test_category_assessment_date': None,
    'last_updated_by': None,
    'test_execution': {'Provided Health Insur Offer & Coverage Information Returns (1094C) + Data Quality Monitoring': {'evidence_capture': None,
      'test_result_justification': 'xxx',
      'latest_test_result_date': '2019-10-21',
      'last_updated_by': None,
      'test_execution_status': 'Completed',
      'test_result': 'Pass without Compensating Controls'}},
    'test_category_assessment': None,
    'test_category_status': None,
    'test_category_assessment_justification': None},
   'HPU Source Reliability': {'test_category_assessment_date': None,
    'last_updated_by': None,
    'test_execution': {'Provided Health Insur Offer & Coverage Information Returns (1094C) + HPU Source Reliability': {'evidence_capture': None,
      'test_result_justification': None,
      'latest_test_result_date': None,
      'last_updated_by': None,
      'test_execution_status': 'Not Started',
      'test_result': None}},
    'test_category_assessment': None,
    'test_category_status': None,
    'test_category_assessment_justification': None},
   'Change Notification': {'test_category_assessment_date': None,
    'last_updated_by': None,
    'test_execution': {'Provided Health Insur Offer & Coverage Information Returns (1094C) + Change Notification': {'evidence_capture': None,
      'test_result_justification': None,
      'latest_test_result_date': None,
      'last_updated_by': None,
      'test_execution_status': 'Not Started',
      'test_result': None}},
    'test_category_assessment': None,
    'test_category_status': None,
    'test_category_assessment_justification': None}},
  'assessment_status': 'In Progress',
  'recommendation_indicator': None,
  'assessment_justification': None,
  'revalidation_justification': None}]

I want to convert it into 2 tables. I've created 2 tables in excel, it'll be something like that.

first table

second table

I apologize if I asked a stupid question or the way I formatted my question.

Improve this question

2 Answers 2

Reset to default
1

I solved it finally with the below code

for usage_json in assessment_json:
    if 'test_category' in usage_json:
        for tc in usage_json['test_category'].keys():
            if 'test_execution' in usage_json['test_category'][tc]:
                sub_te_df = pd.DataFrame(usage_json['test_category'][tc]['test_execution']).T
                sub_te_df.reset_index(inplace=True)
                sub_te_df = sub_te_df.rename(columns = {'index':'test_execution'})
                sub_te_df['usageId'] = usage_json['usageId']
                sub_te_df['test_category'] = tc
                usage_te_df_list.append(sub_te_df)

df_execution = pd.concat(usage_te_df_list).reset_index(drop=True)
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

0

Edit: Using the updated data with multiple usageId (there are 3 of them)，I did the following processing steps and output df4 with the relevant columns of interest, please have a look:

import pandas as pd

d = [{'usageId': 'e83f43f8-ec4a-402d-a64e-d74b6f1df4a7',
  'assessment_status_date': '2022-03-28',
  'assessment_date': '2020-12-07',
  'usage_assessment': <truncated>  ]

df4 = pd.DataFrame()
df1 = pd.DataFrame(d)    #df1 = from dictionary d
for usageId in set(df1['usageId']):
    df2 = df1[df1['usageId'] == usageId]    #df2 = filtered based on each usageId, total more than 100 usageId
    test_category = list({k.split('+')[0].strip(): v for d in lis for k, v in d.items()}.keys())[0]
    lis = pd.DataFrame(df2['test_category'].values.tolist()[0]).T['test_execution'].values.tolist()
    df3 = pd.DataFrame({k.split('+')[-1].strip(): v for d in lis for k, v in d.items()}).T    #df3 = extracted column 'test_execution'
    df3['test_category'] = test_category
    df3['usageId'] = usageId
    df4 = pd.concat([df4, df3], axis=0)    #df4 = compiled test_execution for all usageId
    df1['test_category'][df1['usageId']==usageId] = test_category
print(df4)

Output:

                               evidence_capture      test_result_justification latest_test_result_date last_updated_by test_execution_status                    test_result                  test_category                        usageId
Usage Reconciliation                       None              Test out of scope              2019-10-02            None           In Progress                           None        Health and Welfare Plan  e83f43f8-ec4a-402d-a64e-d7...
Data Agreements                            None                            xxx              2019-10-02            None           In Progress                           None        Health and Welfare Plan  e83f43f8-ec4a-402d-a64e-d7...
Data Elements                              None  The rationale provided for...              2019-10-02            None           In Progress                           None        Health and Welfare Plan  e83f43f8-ec4a-402d-a64e-d7...
Computations                               None                           None                    None            None           Not Started                           None        Health and Welfare Plan  e83f43f8-ec4a-402d-a64e-d7...
Lineage                                    None                           None                    None            None           Not Started                           None        Health and Welfare Plan  e83f43f8-ec4a-402d-a64e-d7...
Metadata                                   None                          Valid              2020-07-02            None             Completed  Pass without Compensating ...        Health and Welfare Plan  e83f43f8-ec4a-402d-a64e-d7...
Data Quality Monitoring                    None                            xxx              2019-08-09            None             Completed  Pass without Compensating ...        Health and Welfare Plan  e83f43f8-ec4a-402d-a64e-d7...
HPU Source Reliability                     None                            xxx              2019-10-02            None           In Progress                           None        Health and Welfare Plan  e83f43f8-ec4a-402d-a64e-d7...
Change Notification                        None                           None                    None            None           Not Started                           None        Health and Welfare Plan  e83f43f8-ec4a-402d-a64e-d7...
New or Changed Usage Reconc...             None                            xxx              2020-10-23            None             Completed  Pass without Compensating ...  New or Changed Usage Recon...  b3c9cbbd-fb72-46df-a4a3-6d...
Data Agreement Reviewed and...             None                            xxx              2020-07-21            None             Completed  Pass without Compensating ...  New or Changed Usage Recon...  b3c9cbbd-fb72-46df-a4a3-6d...
HPDEs Identified                           None                          Valid              2020-07-02            None             Completed  Pass without Compensating ...  New or Changed Usage Recon...  b3c9cbbd-fb72-46df-a4a3-6d...
HPDE Justification is Docum...             None                            xxx              2020-02-28            None             Completed  Pass without Compensating ...  New or Changed Usage Recon...  b3c9cbbd-fb72-46df-a4a3-6d...
HPDE Identification Rationa...             None                            xxx              2020-02-28            None             Completed  Pass without Compensating ...  New or Changed Usage Recon...  b3c9cbbd-fb72-46df-a4a3-6d...
Usage Output is Documented ...             None                            xxx              2020-08-07            None             Completed  Pass without Compensating ...  New or Changed Usage Recon...  b3c9cbbd-fb72-46df-a4a3-6d...
Data Element Metadata is in...             None                            xxx                    None        tat00000           In Progress                           None  New or Changed Usage Recon...  b3c9cbbd-fb72-46df-a4a3-6d...
Secured Data Indicator Cons...             None                           None                    None            None           Not Started                           None  New or Changed Usage Recon...  b3c9cbbd-fb72-46df-a4a3-6d...
HPDE Metadata is in Curated...             None                          Valid              2020-07-02            None             Completed  Pass without Compensating ...  New or Changed Usage Recon...  b3c9cbbd-fb72-46df-a4a3-6d...
Usage Outcome is Accurate                  None                           None                    None            None           Not Started                           None  New or Changed Usage Recon...  b3c9cbbd-fb72-46df-a4a3-6d...
Lineage is Accurate Reflect...             None                           None                    None            None           Not Started                           None  New or Changed Usage Recon...  b3c9cbbd-fb72-46df-a4a3-6d...
Partial Lineage from Author...             None                           None                    None            None           Not Started                           None  New or Changed Usage Recon...  b3c9cbbd-fb72-46df-a4a3-6d...
Lineage from Usage to Autho...             None                           None                    None            None           Not Started                           None  New or Changed Usage Recon...  b3c9cbbd-fb72-46df-a4a3-6d...
Quality is Sufficient                      None                           None                    None            None           Not Started                           None  New or Changed Usage Recon...  b3c9cbbd-fb72-46df-a4a3-6d...
Usage Description is Valid                 None                             xx              2020-02-28            None             Completed  Pass without Compensating ...  New or Changed Usage Recon...  b3c9cbbd-fb72-46df-a4a3-6d...
Usage SME's EID Status is V...             None                          Valid              2020-07-02            None             Completed  Pass without Compensating ...  New or Changed Usage Recon...  b3c9cbbd-fb72-46df-a4a3-6d...
Usage AE                                   None                          Valid              2020-07-02            None             Completed  Pass without Compensating ...  New or Changed Usage Recon...  b3c9cbbd-fb72-46df-a4a3-6d...
Data Defect Tracking Proces...             None                            xxx              2020-02-28            None             Completed  Pass without Compensating ...  New or Changed Usage Recon...  b3c9cbbd-fb72-46df-a4a3-6d...
Data Movement is Reasonable                None                            xxx              2020-10-23            None             Completed                           Fail  New or Changed Usage Recon...  b3c9cbbd-fb72-46df-a4a3-6d...
DQ Rules and Thresholds are...             None                           None                    None            None           In Progress                           Fail  New or Changed Usage Recon...  b3c9cbbd-fb72-46df-a4a3-6d...
Data Defect Tracking Proces...             None                           None                    None            None           Not Started                           None  New or Changed Usage Recon...  b3c9cbbd-fb72-46df-a4a3-6d...
DQ Monitoring Plan is Reaso...             None                            xxx              2020-10-22            None             Completed  Pass without Compensating ...  New or Changed Usage Recon...  b3c9cbbd-fb72-46df-a4a3-6d...
Usage Consumes from Accepta...             None                            xxx              2020-10-23            None             Completed                           Fail  New or Changed Usage Recon...  b3c9cbbd-fb72-46df-a4a3-6d...
Usage Consumes from Approve...             None                           None                    None            None           Not Started                           None  New or Changed Usage Recon...  b3c9cbbd-fb72-46df-a4a3-6d...
Change Notification Process...             None                            xxx              2020-07-21            None             Completed  Pass without Compensating ...  New or Changed Usage Recon...  b3c9cbbd-fb72-46df-a4a3-6d...
Change Notification Process...             None                           None                    None            None           Not Started                           None  New or Changed Usage Recon...  b3c9cbbd-fb72-46df-a4a3-6d...
Usage Reconciliation                       None              Test out of scope              2019-10-02            None           In Progress                           None  Provided Health Insur Offe...  c67a1567-2de3-4826-97bb-99...
Data Agreements                            None                            xxx              2019-10-21            None             Completed  Pass without Compensating ...  Provided Health Insur Offe...  c67a1567-2de3-4826-97bb-99...
Data Elements                              None                          Valid              2020-07-02            None             Completed  Pass without Compensating ...  Provided Health Insur Offe...  c67a1567-2de3-4826-97bb-99...
Computations                               None                           None                    None            None           Not Started                           None  Provided Health Insur Offe...  c67a1567-2de3-4826-97bb-99...
Lineage                                    None                           None                    None            None           Not Started                           None  Provided Health Insur Offe...  c67a1567-2de3-4826-97bb-99...
Metadata                                   None                          Valid              2020-07-02            None             Completed  Pass without Compensating ...  Provided Health Insur Offe...  c67a1567-2de3-4826-97bb-99...
Data Quality Monitoring                    None                            xxx              2019-10-21            None             Completed  Pass without Compensating ...  Provided Health Insur Offe...  c67a1567-2de3-4826-97bb-99...
HPU Source Reliability                     None                           None                    None            None           Not Started                           None  Provided Health Insur Offe...  c67a1567-2de3-4826-97bb-99...
Change Notification                        None                           None                    None            None           Not Started                           None  Provided Health Insur Offe...  c67a1567-2de3-4826-97bb-99...

The original df1 is also updated; this table is linked to df4 by usageId and test_category

                         usageId assessment_status_date assessment_date usage_assessment has_l3test compensating_control recommendations                  test_category assessment_status recommendation_indicator assessment_justification revalidation_justification
0  e83f43f8-ec4a-402d-a64e-d7...             2022-03-28      2020-12-07          Level 1       None                 None            None        Health and Welfare Plan       In Progress                     None                     None                       None
1  b3c9cbbd-fb72-46df-a4a3-6d...             2022-03-28      2020-12-07          Level 1       None                 None            None  New or Changed Usage Recon...       In Progress                     None                     None                       None
2  c67a1567-2de3-4826-97bb-99...             2022-03-28      2020-12-07          Level 1       None                 None            None  Provided Health Insur Offe...       In Progress                     None                     None                       None
Improve this answer

14 Comments

Hi, thank you for the answer. I got an error message KeyError: 0 when running your code. Any idea why this might happen?
may I know which line is the error? (there are 2 lines with key 0)
do all the 'test_execution' look like this with ' + ' in between? 'test_execution': {'Health and Welfare Plan + Change Notification': ...<trancated>
df2['test_category'][0] this line is highlighted. Yes, all the 'test_execution' look like this.
there is always a '+' in between, and follow by the name of the category name (e.g. Change Notification), but the first part varies. Sorry for the confusion.
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.