Koolye
Reputation: 23
convert nested json to pandas dataframe of specific format
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.
I apologize if I asked a stupid question or the way I formatted my question.
Upvotes: 1
Views: 144
Answers (2)
Koolye
Reputation: 23
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)
Upvotes: 1
blackraven
Reputation: 5597
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
Upvotes: 0
Related Questions
- Convert Pandas Dataframe to nested JSON
- Nested Json to pandas DataFrame with specific format
- Convert multi nested Json to Pandas DataFrame
- Converting nested JSON object to pandas Dataframe
- Converting dataframe into nested json
- How to convert nested json to dataframe?
- Convert this nested JSON to pandas dataframe
- Convert nested JSON to pandas DataFrame
- How to convert nested json into python dataframe
- Convert nested json to pandas data frame