I'm trying to convert a column containing list of dictioneries to multiple columns in Pandas.
Here's how the dataframe looks like:
id | name | servicePlans
------------------------
1 | xyz | [{'servicePlanId': '1',
'servicePlanName': 'A',
'provisioningStatus': 'X',
'appliesTo': 'User'},
{'servicePlanId': '2',
'servicePlanName': 'B',
'provisioningStatus': 'Y',
'appliesTo': 'User'},
{'servicePlanId': '3',
'servicePlanName': 'C',
'provisioningStatus': 'Z',
'appliesTo': 'User'}]
...
Now, the servicePlans column contains many dictionary, just a few shown in example.
I want to convert it to something like this:
id | name | servicePlanId | servicePlanName | provisioningStatus | appliesTo
-----------------------------------------------------------------------------
1 | xyz | 1 | A | X | User
1 | xyz | 2 | B | Y | User
1 | xyz | 3 | C | Z | User
I've tried to use explode() method in pandas but no success.
Here's what I've tried:
pd.DataFrame(df['servicePlans'].explode().tolist()).drop_duplicates(subset=['servicePlanId'])
This is how the dataframe was created:
I had a huge list of dictionaries(rows) like this.
[{'id': 'd9f16477-89e5-4b8e-9246-b8433352d2c7',
'Name': 'Adele Vance',
'Principle_Name': '[email protected]',
'skuId': 'c42b9cae-ea4f-4ab7-9717-81576235ccac',
'servicePlans': [{'servicePlanId': 'b76fb638-6ba6-402a-b9f9-83d28acb3d86',
'servicePlanName': 'VIVA_LEARNING_SEEDED',
'provisioningStatus': 'Success',
'appliesTo': 'User'},
{'servicePlanId': 'db4d623d-b514-490b-b7ef-8885eee514de',
'servicePlanName': 'Nucleus',
'provisioningStatus': 'Success',
'appliesTo': 'Company'},
{'servicePlanId': 'cd31b152-6326-4d1b-ae1b-997b625182e6',
'servicePlanName': 'MIP_S_Exchange',
'provisioningStatus': 'Success',
'appliesTo': 'User'},
{'servicePlanId': 'a413a9ff-720c-4822-98ef-2f37c2a21f4c',
'servicePlanName': 'MICROSOFT_COMMUNICATION_COMPLIANCE',
'provisioningStatus': 'Success',
'appliesTo': 'User'},
{'servicePlanId': 'a6520331-d7d4-4276-95f5-15c0933bc757',
'servicePlanName': 'GRAPH_CONNECTORS_SEARCH_INDEX',
'provisioningStatus': 'Success',
'appliesTo': 'User'},
{'servicePlanId': 'd9fa6af4-e046-4c89-9226-729a0786685d',
'servicePlanName': 'Content_Explorer',
'provisioningStatus': 'Success',
'appliesTo': 'Company'},
{'servicePlanId': 'ded3d325-1bdc-453e-8432-5bac26d7a014',
'servicePlanName': 'POWER_VIRTUAL_AGENTS_O365_P3',
'provisioningStatus': 'Success',
'appliesTo': 'User'},
{'servicePlanId': 'afa73018-811e-46e9-988f-f75d2b1b8430',
'servicePlanName': 'CDS_O365_P3',
'provisioningStatus': 'Success',
'appliesTo': 'User'},
{'servicePlanId': 'b21a6b06-1988-436e-a07b-51ec6d9f52ad',
'servicePlanName': 'PROJECT_O365_P3',
'provisioningStatus': 'Success',
'appliesTo': 'User'},
{'servicePlanId': 'd587c7a3-bda9-4f99-8776-9bcf59c84f75',
'servicePlanName': 'INSIDER_RISK',
'provisioningStatus': 'Success',
'appliesTo': 'User'},
{'servicePlanId': '531ee2f8-b1cb-453b-9c21-d2180d014ca5',
'servicePlanName': 'EXCEL_PREMIUM',
'provisioningStatus': 'Success',
'appliesTo': 'User'},
{'servicePlanId': 'bf28f719-7844-4079-9c78-c1307898e192',
'servicePlanName': 'MTP',
'provisioningStatus': 'Success',
'appliesTo': 'User'},
{'servicePlanId': '94065c59-bc8e-4e8b-89e5-5138d471eaff',
'servicePlanName': 'MICROSOFT_SEARCH',
'provisioningStatus': 'Success',
'appliesTo': 'Company'},
{'servicePlanId': '28b0fa46-c39a-4188-89e2-58e979a6b014',
'servicePlanName': 'DYN365_CDS_O365_P3',
'provisioningStatus': 'Success',
'appliesTo': 'User'},
{'servicePlanId': '199a5c09-e0ca-4e37-8f7c-b05d533e1ea2',
'servicePlanName': 'MICROSOFTBOOKINGS',
'provisioningStatus': 'Success',
'appliesTo': 'User'},
{'servicePlanId': '65cc641f-cccd-4643-97e0-a17e3045e541',
'servicePlanName': 'RECORDS_MANAGEMENT',
'provisioningStatus': 'Success',
'appliesTo': 'User'},
{'servicePlanId': 'd2d51368-76c9-4317-ada2-a12c004c432f',
'servicePlanName': 'ML_CLASSIFICATION',
'provisioningStatus': 'Success',
'appliesTo': 'User'},
{'servicePlanId': '9d0c4ee5-e4a1-4625-ab39-d82b619b1a34',
'servicePlanName': 'INSIDER_RISK_MANAGEMENT',
'provisioningStatus': 'Success',
'appliesTo': 'User'},
{'servicePlanId': 'e26c2fcc-ab91-4a61-b35c-03cdc8dddf66',
'servicePlanName': 'INFO_GOVERNANCE',
'provisioningStatus': 'Success',
'appliesTo': 'User'},
{'servicePlanId': '46129a58-a698-46f0-aa5b-17f6586297d9',
'servicePlanName': 'DATA_INVESTIGATIONS',
'provisioningStatus': 'Success',
'appliesTo': 'User'},
{'servicePlanId': '6db1f1db-2b46-403f-be40-e39395f08dbb',
'servicePlanName': 'CUSTOMER_KEY',
'provisioningStatus': 'Success',
'appliesTo': 'User'},
{'servicePlanId': '6dc145d6-95dd-4191-b9c3-185575ee6f6b',
'servicePlanName': 'COMMUNICATIONS_DLP',
'provisioningStatus': 'Success',
'appliesTo': 'User'},
{'servicePlanId': '41fcdd7d-4733-4863-9cf4-c65b83ce2df4',
'servicePlanName': 'COMMUNICATIONS_COMPLIANCE',
'provisioningStatus': 'Success',
'appliesTo': 'User'},
{'servicePlanId': 'bf6f5520-59e3-4f82-974b-7dbbc4fd27c7',
'servicePlanName': 'SAFEDOCS',
'provisioningStatus': 'Success',
'appliesTo': 'User'},
{'servicePlanId': '2f442157-a11c-46b9-ae5b-6e39ff4e5849',
'servicePlanName': 'M365_ADVANCED_AUDITING',
'provisioningStatus': 'Success',
'appliesTo': 'User'},
{'servicePlanId': '7547a3fe-08ee-4ccb-b430-5077c5041653',
'servicePlanName': 'YAMMER_ENTERPRISE',
'provisioningStatus': 'Success',
'appliesTo': 'User'},
{'servicePlanId': '4a51bca5-1eff-43f5-878c-177680f191af',
'servicePlanName': 'WHITEBOARD_PLAN3',
'provisioningStatus': 'Success',
'appliesTo': 'User'},
{'servicePlanId': '3fb82609-8c27-4f7b-bd51-30634711ee67',
'servicePlanName': 'BPOS_S_TODO_3',
'provisioningStatus': 'Success',
'appliesTo': 'User'},
{'servicePlanId': 'a23b959c-7ce8-4e57-9140-b90eb88a9e97',
'servicePlanName': 'SWAY',
'provisioningStatus': 'Success',
'appliesTo': 'User'},
{'servicePlanId': '0feaeb32-d00e-4d66-bd5a-43b5b83db82c',
'servicePlanName': 'MCOSTANDARD',
'provisioningStatus': 'Success',
'appliesTo': 'User'},
{'servicePlanId': '5dbe027f-2339-4123-9542-606e4d348a72',
'servicePlanName': 'SHAREPOINTENTERPRISE',
'provisioningStatus': 'Success',
'appliesTo': 'User'},
{'servicePlanId': '9c0dab89-a30c-4117-86e7-97bda240acd2',
'servicePlanName': 'POWERAPPS_O365_P3',
'provisioningStatus': 'Success',
'appliesTo': 'User'},
{'servicePlanId': '70d33638-9c74-4d01-bfd3-562de28bd4ba',
'servicePlanName': 'BI_AZURE_P2',
'provisioningStatus': 'Success',
'appliesTo': 'User'},
{'servicePlanId': '4828c8ec-dc2e-4779-b502-87ac9ce28ab7',
'servicePlanName': 'MCOEV',
'provisioningStatus': 'Success',
'appliesTo': 'User'},
{'servicePlanId': 'e95bec33-7c88-4a70-8e19-b10bd9d0c014',
'servicePlanName': 'SHAREPOINTWAC',
'provisioningStatus': 'Success',
'appliesTo': 'User'},
{'servicePlanId': '43de0ff5-c92c-492b-9116-175376d08c38',
'servicePlanName': 'OFFICESUBSCRIPTION',
'provisioningStatus': 'Success',
'appliesTo': 'User'},
{'servicePlanId': 'b1188c4c-1b36-4018-b48b-ee07604f6feb',
'servicePlanName': 'PAM_ENTERPRISE',
'provisioningStatus': 'Success',
'appliesTo': 'User'},
{'servicePlanId': '8e0c0a52-6a6c-4d40-8370-dd62790dcd70',
'servicePlanName': 'THREAT_INTELLIGENCE',
'provisioningStatus': 'Success',
'appliesTo': 'User'},
{'servicePlanId': 'f20fedf3-f3c3-43c3-8267-2bfdd51c0939',
'servicePlanName': 'ATP_ENTERPRISE',
'provisioningStatus': 'Success',
'appliesTo': 'Company'},
{'servicePlanId': '8c098270-9dd4-4350-9b30-ba4703f3b36b',
'servicePlanName': 'ADALLOM_S_O365',
'provisioningStatus': 'Success',
'appliesTo': 'User'},
{'servicePlanId': '4de31727-a228-4ec3-a5bf-8e45b5ca48cc',
'servicePlanName': 'EQUIVIO_ANALYTICS',
'provisioningStatus': 'Success',
'appliesTo': 'User'},
{'servicePlanId': '882e1d05-acd1-4ccb-8708-6ee03664b117',
'servicePlanName': 'INTUNE_O365',
'provisioningStatus': 'PendingActivation',
'appliesTo': 'Company'},
{'servicePlanId': '57ff2da0-773e-42df-b2af-ffb7a2317929',
'servicePlanName': 'TEAMS1',
'provisioningStatus': 'Success',
'appliesTo': 'User'},
{'servicePlanId': '6c6042f5-6f01-4d67-b8c1-eb99d36eed3e',
'servicePlanName': 'STREAM_O365_E5',
'provisioningStatus': 'Success',
'appliesTo': 'User'},
{'servicePlanId': '8c7d2df8-86f0-4902-b2ed-a0458298f3b3',
'servicePlanName': 'Deskless',
'provisioningStatus': 'Success',
'appliesTo': 'User'},
{'servicePlanId': 'b737dad2-2f6c-4c65-90e3-ca563267e8b9',
'servicePlanName': 'PROJECTWORKMANAGEMENT',
'provisioningStatus': 'Success',
'appliesTo': 'User'},
{'servicePlanId': '34c0d7a0-a70f-4668-9238-47f9fc208882',
'servicePlanName': 'EXCHANGE_ANALYTICS',
'provisioningStatus': 'Success',
'appliesTo': 'User'},
{'servicePlanId': 'c1ec4a95-1f05-45b3-a911-aa3fa01094f5',
'servicePlanName': 'INTUNE_A',
'provisioningStatus': 'PendingInput',
'appliesTo': 'User'},
{'servicePlanId': 'e212cbc7-0961-4c40-9825-01117710dcb1',
'servicePlanName': 'FORMS_PLAN_E5',
'provisioningStatus': 'Success',
'appliesTo': 'User'},
{'servicePlanId': '2e2ddb96-6af9-4b1d-a3f0-d6ecfd22edb2',
'servicePlanName': 'ADALLOM_S_STANDALONE',
'provisioningStatus': 'Success',
'appliesTo': 'User'},
{'servicePlanId': '8a256a2b-b617-496d-b51b-e76466e88db0',
'servicePlanName': 'MFA_PREMIUM',
'provisioningStatus': 'Success',
'appliesTo': 'User'},
{'servicePlanId': 'bea4c11e-220a-4e6d-8eb8-8ea15d019f90',
'servicePlanName': 'RMS_S_ENTERPRISE',
'provisioningStatus': 'Success',
'appliesTo': 'User'},
{'servicePlanId': '5136a095-5cf0-4aff-bec3-e84448b38ea5',
'servicePlanName': 'MIP_S_CLP1',
'provisioningStatus': 'Success',
'appliesTo': 'User'},
{'servicePlanId': 'efb0351d-3b08-4503-993d-383af8de41e3',
'servicePlanName': 'MIP_S_CLP2',
'provisioningStatus': 'Success',
'appliesTo': 'User'},
{'servicePlanId': '07699545-9485-468e-95b6-2fca3738be01',
'servicePlanName': 'FLOW_O365_P3',
'provisioningStatus': 'Success',
'appliesTo': 'User'},
{'servicePlanId': 'efb87545-963c-4e0d-99df-69c6916d9eb0',
'servicePlanName': 'EXCHANGE_S_ENTERPRISE',
'provisioningStatus': 'Success',
'appliesTo': 'User'},
{'servicePlanId': '9f431833-0334-42de-a7dc-70aa40db46db',
'servicePlanName': 'LOCKBOX_ENTERPRISE',
'provisioningStatus': 'Success',
'appliesTo': 'User'},
{'servicePlanId': '5689bec4-755d-4753-8b61-40975025187c',
'servicePlanName': 'RMS_S_PREMIUM2',
'provisioningStatus': 'Success',
'appliesTo': 'User'},
{'servicePlanId': '6c57d4b6-3b23-47a5-9bc9-69f17b4947b3',
'servicePlanName': 'RMS_S_PREMIUM',
'provisioningStatus': 'Success',
'appliesTo': 'User'},
{'servicePlanId': '14ab5db5-e6c4-4b20-b4bc-13e36fd2227f',
'servicePlanName': 'ATA',
'provisioningStatus': 'Success',
'appliesTo': 'User'},
{'servicePlanId': 'eec0eb4f-6444-4f95-aba0-50c24d67f998',
'servicePlanName': 'AAD_PREMIUM_P2',
'provisioningStatus': 'Success',
'appliesTo': 'User'},
{'servicePlanId': '41781fb2-bc02-4b7c-bd55-b576c07bb09d',
'servicePlanName': 'AAD_PREMIUM',
'provisioningStatus': 'Success',
'appliesTo': 'User'}]}
And I used pd.DataFrame(list_of_dic) to get to the first table mentioned above.
The code only returns me the last four columns mentioned in the second table. Is there a way to create the second table out of the first one?
Thanks a lot in advance.
CodePudding user response：
Given:
data = [{'id': 'd9f16477-89e5-4b8e-9246-b8433352d2c7',
'Name': 'Adele Vance',
'Principle_Name': '[email protected]',
'skuId': 'c42b9cae-ea4f-4ab7-9717-81576235ccac',
'servicePlans': [{'servicePlanId': 'b76fb638-6ba6-402a-b9f9-83d28acb3d86',
'servicePlanName': 'VIVA_LEARNING_SEEDED',
'provisioningStatus': 'Success',
'appliesTo': 'User'},
{'servicePlanId': 'db4d623d-b514-490b-b7ef-8885eee514de',
'servicePlanName': 'Nucleus',
'provisioningStatus': 'Success',
'appliesTo': 'Company'},
{'servicePlanId': 'cd31b152-6326-4d1b-ae1b-997b625182e6',
'servicePlanName': 'MIP_S_Exchange',
'provisioningStatus': 'Success',
'appliesTo': 'User'},
{'servicePlanId': 'a413a9ff-720c-4822-98ef-2f37c2a21f4c',
'servicePlanName': 'MICROSOFT_COMMUNICATION_COMPLIANCE',
'provisioningStatus': 'Success',
'appliesTo': 'User'},
{'servicePlanId': 'a6520331-d7d4-4276-95f5-15c0933bc757',
'servicePlanName': 'GRAPH_CONNECTORS_SEARCH_INDEX',
'provisioningStatus': 'Success',
'appliesTo': 'User'},
{'servicePlanId': 'd9fa6af4-e046-4c89-9226-729a0786685d',
'servicePlanName': 'Content_Explorer',
'provisioningStatus': 'Success',
'appliesTo': 'Company'},
{'servicePlanId': 'ded3d325-1bdc-453e-8432-5bac26d7a014',
'servicePlanName': 'POWER_VIRTUAL_AGENTS_O365_P3',
'provisioningStatus': 'Success',
'appliesTo': 'User'},
{'servicePlanId': 'afa73018-811e-46e9-988f-f75d2b1b8430',
'servicePlanName': 'CDS_O365_P3',
'provisioningStatus': 'Success',
'appliesTo': 'User'},
{'servicePlanId': 'b21a6b06-1988-436e-a07b-51ec6d9f52ad',
'servicePlanName': 'PROJECT_O365_P3',
'provisioningStatus': 'Success',
'appliesTo': 'User'},
{'servicePlanId': 'd587c7a3-bda9-4f99-8776-9bcf59c84f75',
'servicePlanName': 'INSIDER_RISK',
'provisioningStatus': 'Success',
'appliesTo': 'User'},
{'servicePlanId': '531ee2f8-b1cb-453b-9c21-d2180d014ca5',
'servicePlanName': 'EXCEL_PREMIUM',
'provisioningStatus': 'Success',
'appliesTo': 'User'},
{'servicePlanId': 'bf28f719-7844-4079-9c78-c1307898e192',
'servicePlanName': 'MTP',
'provisioningStatus': 'Success',
'appliesTo': 'User'},
{'servicePlanId': '94065c59-bc8e-4e8b-89e5-5138d471eaff',
'servicePlanName': 'MICROSOFT_SEARCH',
'provisioningStatus': 'Success',
'appliesTo': 'Company'},
{'servicePlanId': '28b0fa46-c39a-4188-89e2-58e979a6b014',
'servicePlanName': 'DYN365_CDS_O365_P3',
'provisioningStatus': 'Success',
'appliesTo': 'User'},
{'servicePlanId': '199a5c09-e0ca-4e37-8f7c-b05d533e1ea2',
'servicePlanName': 'MICROSOFTBOOKINGS',
'provisioningStatus': 'Success',
'appliesTo': 'User'},
{'servicePlanId': '65cc641f-cccd-4643-97e0-a17e3045e541',
'servicePlanName': 'RECORDS_MANAGEMENT',
'provisioningStatus': 'Success',
'appliesTo': 'User'},
{'servicePlanId': 'd2d51368-76c9-4317-ada2-a12c004c432f',
'servicePlanName': 'ML_CLASSIFICATION',
'provisioningStatus': 'Success',
'appliesTo': 'User'},
{'servicePlanId': '9d0c4ee5-e4a1-4625-ab39-d82b619b1a34',
'servicePlanName': 'INSIDER_RISK_MANAGEMENT',
'provisioningStatus': 'Success',
'appliesTo': 'User'},
{'servicePlanId': 'e26c2fcc-ab91-4a61-b35c-03cdc8dddf66',
'servicePlanName': 'INFO_GOVERNANCE',
'provisioningStatus': 'Success',
'appliesTo': 'User'},
{'servicePlanId': '46129a58-a698-46f0-aa5b-17f6586297d9',
'servicePlanName': 'DATA_INVESTIGATIONS',
'provisioningStatus': 'Success',
'appliesTo': 'User'},
{'servicePlanId': '6db1f1db-2b46-403f-be40-e39395f08dbb',
'servicePlanName': 'CUSTOMER_KEY',
'provisioningStatus': 'Success',
'appliesTo': 'User'},
{'servicePlanId': '6dc145d6-95dd-4191-b9c3-185575ee6f6b',
'servicePlanName': 'COMMUNICATIONS_DLP',
'provisioningStatus': 'Success',
'appliesTo': 'User'},
{'servicePlanId': '41fcdd7d-4733-4863-9cf4-c65b83ce2df4',
'servicePlanName': 'COMMUNICATIONS_COMPLIANCE',
'provisioningStatus': 'Success',
'appliesTo': 'User'},
{'servicePlanId': 'bf6f5520-59e3-4f82-974b-7dbbc4fd27c7',
'servicePlanName': 'SAFEDOCS',
'provisioningStatus': 'Success',
'appliesTo': 'User'},
{'servicePlanId': '2f442157-a11c-46b9-ae5b-6e39ff4e5849',
'servicePlanName': 'M365_ADVANCED_AUDITING',
'provisioningStatus': 'Success',
'appliesTo': 'User'},
{'servicePlanId': '7547a3fe-08ee-4ccb-b430-5077c5041653',
'servicePlanName': 'YAMMER_ENTERPRISE',
'provisioningStatus': 'Success',
'appliesTo': 'User'},
{'servicePlanId': '4a51bca5-1eff-43f5-878c-177680f191af',
'servicePlanName': 'WHITEBOARD_PLAN3',
'provisioningStatus': 'Success',
'appliesTo': 'User'},
{'servicePlanId': '3fb82609-8c27-4f7b-bd51-30634711ee67',
'servicePlanName': 'BPOS_S_TODO_3',
'provisioningStatus': 'Success',
'appliesTo': 'User'},
{'servicePlanId': 'a23b959c-7ce8-4e57-9140-b90eb88a9e97',
'servicePlanName': 'SWAY',
'provisioningStatus': 'Success',
'appliesTo': 'User'},
{'servicePlanId': '0feaeb32-d00e-4d66-bd5a-43b5b83db82c',
'servicePlanName': 'MCOSTANDARD',
'provisioningStatus': 'Success',
'appliesTo': 'User'},
{'servicePlanId': '5dbe027f-2339-4123-9542-606e4d348a72',
'servicePlanName': 'SHAREPOINTENTERPRISE',
'provisioningStatus': 'Success',
'appliesTo': 'User'},
{'servicePlanId': '9c0dab89-a30c-4117-86e7-97bda240acd2',
'servicePlanName': 'POWERAPPS_O365_P3',
'provisioningStatus': 'Success',
'appliesTo': 'User'},
{'servicePlanId': '70d33638-9c74-4d01-bfd3-562de28bd4ba',
'servicePlanName': 'BI_AZURE_P2',
'provisioningStatus': 'Success',
'appliesTo': 'User'},
{'servicePlanId': '4828c8ec-dc2e-4779-b502-87ac9ce28ab7',
'servicePlanName': 'MCOEV',
'provisioningStatus': 'Success',
'appliesTo': 'User'},
{'servicePlanId': 'e95bec33-7c88-4a70-8e19-b10bd9d0c014',
'servicePlanName': 'SHAREPOINTWAC',
'provisioningStatus': 'Success',
'appliesTo': 'User'},
{'servicePlanId': '43de0ff5-c92c-492b-9116-175376d08c38',
'servicePlanName': 'OFFICESUBSCRIPTION',
'provisioningStatus': 'Success',
'appliesTo': 'User'},
{'servicePlanId': 'b1188c4c-1b36-4018-b48b-ee07604f6feb',
'servicePlanName': 'PAM_ENTERPRISE',
'provisioningStatus': 'Success',
'appliesTo': 'User'},
{'servicePlanId': '8e0c0a52-6a6c-4d40-8370-dd62790dcd70',
'servicePlanName': 'THREAT_INTELLIGENCE',
'provisioningStatus': 'Success',
'appliesTo': 'User'},
{'servicePlanId': 'f20fedf3-f3c3-43c3-8267-2bfdd51c0939',
'servicePlanName': 'ATP_ENTERPRISE',
'provisioningStatus': 'Success',
'appliesTo': 'Company'},
{'servicePlanId': '8c098270-9dd4-4350-9b30-ba4703f3b36b',
'servicePlanName': 'ADALLOM_S_O365',
'provisioningStatus': 'Success',
'appliesTo': 'User'},
{'servicePlanId': '4de31727-a228-4ec3-a5bf-8e45b5ca48cc',
'servicePlanName': 'EQUIVIO_ANALYTICS',
'provisioningStatus': 'Success',
'appliesTo': 'User'},
{'servicePlanId': '882e1d05-acd1-4ccb-8708-6ee03664b117',
'servicePlanName': 'INTUNE_O365',
'provisioningStatus': 'PendingActivation',
'appliesTo': 'Company'},
{'servicePlanId': '57ff2da0-773e-42df-b2af-ffb7a2317929',
'servicePlanName': 'TEAMS1',
'provisioningStatus': 'Success',
'appliesTo': 'User'},
{'servicePlanId': '6c6042f5-6f01-4d67-b8c1-eb99d36eed3e',
'servicePlanName': 'STREAM_O365_E5',
'provisioningStatus': 'Success',
'appliesTo': 'User'},
{'servicePlanId': '8c7d2df8-86f0-4902-b2ed-a0458298f3b3',
'servicePlanName': 'Deskless',
'provisioningStatus': 'Success',
'appliesTo': 'User'},
{'servicePlanId': 'b737dad2-2f6c-4c65-90e3-ca563267e8b9',
'servicePlanName': 'PROJECTWORKMANAGEMENT',
'provisioningStatus': 'Success',
'appliesTo': 'User'},
{'servicePlanId': '34c0d7a0-a70f-4668-9238-47f9fc208882',
'servicePlanName': 'EXCHANGE_ANALYTICS',
'provisioningStatus': 'Success',
'appliesTo': 'User'},
{'servicePlanId': 'c1ec4a95-1f05-45b3-a911-aa3fa01094f5',
'servicePlanName': 'INTUNE_A',
'provisioningStatus': 'PendingInput',
'appliesTo': 'User'},
{'servicePlanId': 'e212cbc7-0961-4c40-9825-01117710dcb1',
'servicePlanName': 'FORMS_PLAN_E5',
'provisioningStatus': 'Success',
'appliesTo': 'User'},
{'servicePlanId': '2e2ddb96-6af9-4b1d-a3f0-d6ecfd22edb2',
'servicePlanName': 'ADALLOM_S_STANDALONE',
'provisioningStatus': 'Success',
'appliesTo': 'User'},
{'servicePlanId': '8a256a2b-b617-496d-b51b-e76466e88db0',
'servicePlanName': 'MFA_PREMIUM',
'provisioningStatus': 'Success',
'appliesTo': 'User'},
{'servicePlanId': 'bea4c11e-220a-4e6d-8eb8-8ea15d019f90',
'servicePlanName': 'RMS_S_ENTERPRISE',
'provisioningStatus': 'Success',
'appliesTo': 'User'},
{'servicePlanId': '5136a095-5cf0-4aff-bec3-e84448b38ea5',
'servicePlanName': 'MIP_S_CLP1',
'provisioningStatus': 'Success',
'appliesTo': 'User'},
{'servicePlanId': 'efb0351d-3b08-4503-993d-383af8de41e3',
'servicePlanName': 'MIP_S_CLP2',
'provisioningStatus': 'Success',
'appliesTo': 'User'},
{'servicePlanId': '07699545-9485-468e-95b6-2fca3738be01',
'servicePlanName': 'FLOW_O365_P3',
'provisioningStatus': 'Success',
'appliesTo': 'User'},
{'servicePlanId': 'efb87545-963c-4e0d-99df-69c6916d9eb0',
'servicePlanName': 'EXCHANGE_S_ENTERPRISE',
'provisioningStatus': 'Success',
'appliesTo': 'User'},
{'servicePlanId': '9f431833-0334-42de-a7dc-70aa40db46db',
'servicePlanName': 'LOCKBOX_ENTERPRISE',
'provisioningStatus': 'Success',
'appliesTo': 'User'},
{'servicePlanId': '5689bec4-755d-4753-8b61-40975025187c',
'servicePlanName': 'RMS_S_PREMIUM2',
'provisioningStatus': 'Success',
'appliesTo': 'User'},
{'servicePlanId': '6c57d4b6-3b23-47a5-9bc9-69f17b4947b3',
'servicePlanName': 'RMS_S_PREMIUM',
'provisioningStatus': 'Success',
'appliesTo': 'User'},
{'servicePlanId': '14ab5db5-e6c4-4b20-b4bc-13e36fd2227f',
'servicePlanName': 'ATA',
'provisioningStatus': 'Success',
'appliesTo': 'User'},
{'servicePlanId': 'eec0eb4f-6444-4f95-aba0-50c24d67f998',
'servicePlanName': 'AAD_PREMIUM_P2',
'provisioningStatus': 'Success',
'appliesTo': 'User'},
{'servicePlanId': '41781fb2-bc02-4b7c-bd55-b576c07bb09d',
'servicePlanName': 'AAD_PREMIUM',
'provisioningStatus': 'Success',
'appliesTo': 'User'}]}]
Doing:
df = pd.json_normalize(data, ['servicePlans'], ['id', 'Name', 'Principle_Name', 'skuId'])
print(df)
# Output:
servicePlanId servicePlanName provisioningStatus appliesTo id Name Principle_Name skuId
0 b76fb638-6ba6-402a-b9f9-83d28acb3d86 VIVA_LEARNING_SEEDED Success User d9f16477-89e5-4b8e-9246-b8433352d2c7 Adele Vance [email protected] c42b9cae-ea4f-4ab7-9717-81576235ccac
1 db4d623d-b514-490b-b7ef-8885eee514de Nucleus Success Company d9f16477-89e5-4b8e-9246-b8433352d2c7 Adele Vance [email protected] c42b9cae-ea4f-4ab7-9717-81576235ccac
2 cd31b152-6326-4d1b-ae1b-997b625182e6 MIP_S_Exchange Success User d9f16477-89e5-4b8e-9246-b8433352d2c7 Adele Vance [email protected] c42b9cae-ea4f-4ab7-9717-81576235ccac
3 a413a9ff-720c-4822-98ef-2f37c2a21f4c MICROSOFT_COMMUNICATION_COMPLIANCE Success User d9f16477-89e5-4b8e-9246-b8433352d2c7 Adele Vance [email protected] c42b9cae-ea4f-4ab7-9717-81576235ccac
4 a6520331-d7d4-4276-95f5-15c0933bc757 GRAPH_CONNECTORS_SEARCH_INDEX Success User d9f16477-89e5-4b8e-9246-b8433352d2c7 Adele Vance [email protected] c42b9cae-ea4f-4ab7-9717-81576235ccac
.. ... ... ... ... ... ... ... ...
57 5689bec4-755d-4753-8b61-40975025187c RMS_S_PREMIUM2 Success User d9f16477-89e5-4b8e-9246-b8433352d2c7 Adele Vance [email protected] c42b9cae-ea4f-4ab7-9717-81576235ccac
58 6c57d4b6-3b23-47a5-9bc9-69f17b4947b3 RMS_S_PREMIUM Success User d9f16477-89e5-4b8e-9246-b8433352d2c7 Adele Vance [email protected] c42b9cae-ea4f-4ab7-9717-81576235ccac
59 14ab5db5-e6c4-4b20-b4bc-13e36fd2227f ATA Success User d9f16477-89e5-4b8e-9246-b8433352d2c7 Adele Vance [email protected] c42b9cae-ea4f-4ab7-9717-81576235ccac
60 eec0eb4f-6444-4f95-aba0-50c24d67f998 AAD_PREMIUM_P2 Success User d9f16477-89e5-4b8e-9246-b8433352d2c7 Adele Vance [email protected] c42b9cae-ea4f-4ab7-9717-81576235ccac
61 41781fb2-bc02-4b7c-bd55-b576c07bb09d AAD_PREMIUM Success User d9f16477-89e5-4b8e-9246-b8433352d2c7 Adele Vance [email protected] c42b9cae-ea4f-4ab7-9717-81576235ccac
[62 rows x 8 columns]