I have a large json file that needs to be parsed into a dataframe. Here is an example of the data structure...
{
"genericTags": [
{
"appliesTo": [
"program",
"email_batch",
"nurture",
"event",
"webinar"
],
"name": "Language",
"type": null,
"values": [
{
"value": "Chinese",
"hidden": false,
"deleted": false
},
{
"value": "English",
"hidden": false,
"deleted": false
},
{
"value": "French",
"hidden": false,
"deleted": false
},
{
"value": "Japanese",
"hidden": false,
"deleted": false
},
{
"value": "Portuguese",
"hidden": false,
"deleted": false
},
{
"value": "Spanish",
"hidden": false,
"deleted": false
}
],
"description": null
},
{
"appliesTo": [
"program",
"email_batch",
"nurture",
"webinar",
"event"
],
"name": "Product",
"type": null,
"values": [
{
"value": "P1",
"hidden": false,
"deleted": false
},
{
"value": "P2",
"hidden": false,
"deleted": false
},
{
"value": "P3",
"hidden": false,
"deleted": false
},
{
"value": "P4",
"hidden": false,
"deleted": false
},
{
"value": "P5",
"hidden": false,
"deleted": false
},
{
"value": "P6",
"hidden": false,
"deleted": false
},
{
"value": "P7",
"hidden": false,
"deleted": false
},
{
"value": "P8",
"hidden": false,
"deleted": false
},
{
"value": "P9",
"hidden": false,
"deleted": false
},
{
"value": "P10",
"hidden": false,
"deleted": false
},
{
"value": "P11",
"hidden": false,
"deleted": false
},
{
"value": "P12",
"hidden": false,
"deleted": false
},
{
"value": "Multiple",
"hidden": false,
"deleted": false
},
{
"value": "None",
"hidden": false,
"deleted": false
}
],
"description": null
},
{
"appliesTo": [
"program",
"email_batch",
"nurture",
"event",
"webinar"
],
"name": "Region",
"type": null,
"values": [
{
"value": "Americas",
"hidden": false,
"deleted": false
},
{
"value": "APJ",
"hidden": false,
"deleted": false
},
{
"value": "EMEA",
"hidden": false,
"deleted": false
},
{
"value": "WW",
"hidden": false,
"deleted": false
}
],
"description": null
}
],
"channelTags": [
{
"value": "Advertising",
"progression": {
"name": "Advertising",
"type": "program",
"description": null,
"steps": [
{
"name": "Not in Program",
"type": "Not in Program",
"typeName": "not_in_program",
"description": null,
"typeId": 1,
"order": 0,
"success": false,
"hidden": false,
"system": true,
"isEventCapSet": null,
"isUsed": false
},
{
"name": "Member",
"description": null,
"typeId": null,
"order": 10,
"success": false,
"hidden": false,
"system": false,
"isEventCapSet": null,
"isUsed": true
},
{
"name": "Engaged",
"description": null,
"typeId": null,
"order": 20,
"success": true,
"hidden": false,
"system": false,
"isEventCapSet": null,
"isUsed": true
}
]
},
"hidden": false,
"deleted": false
},
{
"value": "Association",
"progression": {
"name": "Association",
"type": "program",
"description": null,
"steps": [
{
"name": "Not in Program",
"type": "Not in Program",
"typeName": "not_in_program",
"description": null,
"typeId": 1,
"order": 0,
"success": false,
"hidden": false,
"system": true,
"isEventCapSet": null,
"isUsed": false
},
{
"name": "Member",
"description": null,
"typeId": null,
"order": 10,
"success": false,
"hidden": false,
"system": false,
"isEventCapSet": null,
"isUsed": true
},
{
"name": "Engaged",
"description": null,
"typeId": null,
"order": 20,
"success": true,
"hidden": false,
"system": false,
"isEventCapSet": null,
"isUsed": true
}
]
},
"hidden": false,
"deleted": false
},
{
"value": "Contact Request",
"progression": {
"name": "Contact Request",
"type": "program",
"description": null,
"steps": [
{
"name": "Not in Program",
"type": "Not in Program",
"typeName": "not_in_program",
"description": null,
"typeId": 1,
"order": 0,
"success": false,
"hidden": false,
"system": true,
"isEventCapSet": null,
"isUsed": false
},
{
"name": "Member",
"description": null,
"typeId": null,
"order": 10,
"success": false,
"hidden": false,
"system": false,
"isEventCapSet": null,
"isUsed": true
},
{
"name": "Sent Email",
"description": null,
"typeId": null,
"order": 20,
"success": false,
"hidden": false,
"system": false,
"isEventCapSet": null,
"isUsed": true
},
{
"name": "Engaged",
"description": null,
"typeId": null,
"order": 30,
"success": true,
"hidden": false,
"system": false,
"isEventCapSet": null,
"isUsed": true
}
]
},
"hidden": false,
"deleted": false
},
{
"value": "Content",
"progression": {
"name": "Content",
"type": "program",
"description": null,
"steps": [
{
"name": "Not in Program",
"type": "Not in Program",
"typeName": "not_in_program",
"description": null,
"typeId": 1,
"order": 0,
"success": false,
"hidden": false,
"system": true,
"isEventCapSet": null,
"isUsed": false
},
{
"name": "Member",
"description": null,
"typeId": null,
"order": 10,
"success": false,
"hidden": false,
"system": false,
"isEventCapSet": null,
"isUsed": true
},
{
"name": "Engaged",
"description": null,
"typeId": null,
"order": 20,
"success": true,
"hidden": false,
"system": false,
"isEventCapSet": null,
"isUsed": true
}
]
},
"hidden": false,
"deleted": false
},
{
"value": "Content Syndication",
"progression": {
"name": "Content Syndication",
"type": "program",
"description": null,
"steps": [
{
"name": "Not in Program",
"type": "Not in Program",
"typeName": "not_in_program",
"description": null,
"typeId": 1,
"order": 0,
"success": false,
"hidden": false,
"system": true,
"isEventCapSet": null,
"isUsed": false
},
{
"name": "Member",
"description": null,
"typeId": null,
"order": 10,
"success": false,
"hidden": false,
"system": false,
"isEventCapSet": null,
"isUsed": true
},
{
"name": "Engaged",
"description": null,
"typeId": null,
"order": 20,
"success": true,
"hidden": false,
"system": false,
"isEventCapSet": null,
"isUsed": true
}
]
},
"hidden": false,
"deleted": false
},
{
"value": "Direct Mail",
"progression": {
"name": "Direct Mail",
"type": "program",
"description": null,
"steps": [
{
"name": "Not in Program",
"type": "Not in Program",
"typeName": "not_in_program",
"description": null,
"typeId": 1,
"order": 0,
"success": false,
"hidden": false,
"system": true,
"isEventCapSet": null,
"isUsed": false
},
{
"name": "Member",
"description": null,
"typeId": null,
"order": 10,
"success": false,
"hidden": false,
"system": false,
"isEventCapSet": null,
"isUsed": true
},
{
"name": "Engaged",
"description": null,
"typeId": null,
"order": 20,
"success": true,
"hidden": false,
"system": false,
"isEventCapSet": null,
"isUsed": true
}
]
},
"hidden": false,
"deleted": false
},
{
"value": "Display",
"progression": {
"name": "Display",
"type": "program",
"description": null,
"steps": [
{
"name": "Not in Program",
"type": "Not in Program",
"typeName": "not_in_program",
"description": null,
"typeId": 1,
"order": 0,
"success": false,
"hidden": false,
"system": true,
"isEventCapSet": null,
"isUsed": false
},
{
"name": "Member",
"description": null,
"typeId": null,
"order": 10,
"success": false,
"hidden": false,
"system": false,
"isEventCapSet": null,
"isUsed": true
},
{
"name": "Engaged",
"description": null,
"typeId": null,
"order": 20,
"success": true,
"hidden": false,
"system": false,
"isEventCapSet": null,
"isUsed": true
}
]
},
"hidden": false,
"deleted": false
},
{
"value": "Email",
"progression": {
"name": "Email",
"type": "program",
"description": null,
"steps": [
{
"name": "Not in Program",
"type": "Not in Program",
"typeName": "not_in_program",
"description": null,
"typeId": 1,
"order": 0,
"success": false,
"hidden": false,
"system": true,
"isEventCapSet": null,
"isUsed": true
},
{
"name": "Member",
"description": null,
"typeId": null,
"order": 10,
"success": false,
"hidden": false,
"system": false,
"isEventCapSet": null,
"isUsed": true
},
{
"name": "Engaged",
"description": null,
"typeId": null,
"order": 20,
"success": true,
"hidden": false,
"system": false,
"isEventCapSet": null,
"isUsed": true
}
]
},
"hidden": false,
"deleted": false
},
{
"value": "Email Send",
"progression": {
"name": "Email Send",
"type": "email_batch",
"description": null,
"steps": [
{
"name": "Not in Program",
"type": "Not in Program",
"typeName": "not_in_program",
"description": null,
"typeId": 1,
"order": 0,
"success": false,
"hidden": false,
"system": true,
"isEventCapSet": null,
"isUsed": true
},
{
"name": "Member",
"description": null,
"typeId": null,
"order": 10,
"success": false,
"hidden": false,
"system": false,
"isEventCapSet": null,
"isUsed": true
},
{
"name": "Engaged",
"description": null,
"typeId": null,
"order": 20,
"success": true,
"hidden": false,
"system": false,
"isEventCapSet": null,
"isUsed": true
}
]
},
"hidden": false,
"deleted": false
},
{
"value": "Event",
"progression": {
"name": "Event",
"type": "event",
"description": null,
"steps": [
{
"name": "Not in Program",
"type": "Not in Program",
"typeName": "not_in_program",
"description": null,
"typeId": 1,
"order": 0,
"success": false,
"hidden": false,
"system": true,
"isEventCapSet": false,
"isUsed": false
},
{
"name": "Invited",
"description": null,
"typeId": null,
"order": 10,
"success": false,
"hidden": false,
"system": false,
"isEventCapSet": false,
"isUsed": true
},
{
"name": "Registered",
"type": "Registered",
"typeName": "registered",
"description": null,
"typeId": 5,
"order": 20,
"success": false,
"hidden": false,
"system": false,
"isEventCapSet": false,
"isUsed": true
},
{
"name": "Waitlisted",
"type": "Waitlisted",
"typeName": "wait_listed",
"description": null,
"typeId": 4,
"order": 20,
"success": false,
"hidden": false,
"system": false,
"isEventCapSet": false,
"isUsed": false
},
{
"name": "No Show",
"description": null,
"typeId": null,
"order": 30,
"success": false,
"hidden": false,
"system": false,
"isEventCapSet": false,
"isUsed": true
},
{
"name": "Attended",
"type": "Attended",
"typeName": "attended",
"description": null,
"typeId": 7,
"order": 40,
"success": true,
"hidden": false,
"system": false,
"isEventCapSet": false,
"isUsed": true
},
{
"name": "Attended Session",
"description": null,
"typeId": null,
"order": 50,
"success": true,
"hidden": false,
"system": false,
"isEventCapSet": false,
"isUsed": true
}
]
},
"hidden": false,
"deleted": false
},
{
"value": "Intent Signal",
"progression": {
"name": "Intent Signal",
"type": "program",
"description": null,
"steps": [
{
"name": "Not in Program",
"type": "Not in Program",
"typeName": "not_in_program",
"description": null,
"typeId": 1,
"order": 0,
"success": false,
"hidden": false,
"system": true,
"isEventCapSet": null,
"isUsed": false
},
{
"name": "Member",
"description": null,
"typeId": null,
"order": 10,
"success": false,
"hidden": false,
"system": false,
"isEventCapSet": null,
"isUsed": true
},
{
"name": "Engaged",
"description": null,
"typeId": null,
"order": 20,
"success": true,
"hidden": false,
"system": false,
"isEventCapSet": null,
"isUsed": true
}
]
},
"hidden": false,
"deleted": false
},
{
"value": "Interactive Chat",
"progression": {
"name": "Interactive Chat",
"type": "program",
"description": null,
"steps": [
{
"name": "Not in Program",
"type": "Not in Program",
"typeName": "not_in_program",
"description": null,
"typeId": 1,
"order": 0,
"success": false,
"hidden": false,
"system": true,
"isEventCapSet": null,
"isUsed": false
},
{
"name": "Member",
"description": null,
"typeId": null,
"order": 10,
"success": false,
"hidden": false,
"system": false,
"isEventCapSet": null,
"isUsed": true
},
{
"name": "Engaged",
"description": null,
"typeId": null,
"order": 20,
"success": true,
"hidden": false,
"system": false,
"isEventCapSet": null,
"isUsed": true
}
]
},
"hidden": false,
"deleted": false
},
{
"value": "List Import",
"progression": {
"name": "List Import",
"type": "program",
"description": null,
"steps": [
{
"name": "Not in Program",
"type": "Not in Program",
"typeName": "not_in_program",
"description": null,
"typeId": 1,
"order": 0,
"success": false,
"hidden": false,
"system": true,
"isEventCapSet": null,
"isUsed": false
},
{
"name": "Member",
"description": null,
"typeId": null,
"order": 10,
"success": false,
"hidden": false,
"system": false,
"isEventCapSet": null,
"isUsed": true
},
{
"name": "Engaged",
"description": null,
"typeId": null,
"order": 20,
"success": true,
"hidden": false,
"system": false,
"isEventCapSet": null,
"isUsed": true
}
]
},
"hidden": false,
"deleted": false
},
{
"value": "Nurture",
"progression": {
"name": "Nurture",
"type": "nurture",
"description": null,
"steps": [
{
"name": "Not in Program",
"type": "Not in Program",
"typeName": "not_in_program",
"description": null,
"typeId": 1,
"order": 0,
"success": false,
"hidden": false,
"system": true,
"isEventCapSet": null,
"isUsed": true
},
{
"name": "Member",
"description": null,
"typeId": null,
"order": 10,
"success": false,
"hidden": false,
"system": false,
"isEventCapSet": null,
"isUsed": true
},
{
"name": "Engaged",
"description": null,
"typeId": null,
"order": 20,
"success": true,
"hidden": false,
"system": false,
"isEventCapSet": null,
"isUsed": true
},
{
"name": "Converted",
"description": null,
"typeId": null,
"order": 30,
"success": true,
"hidden": false,
"system": false,
"isEventCapSet": null,
"isUsed": false
}
]
},
"hidden": false,
"deleted": false
},
{
"value": "Nurture Cast",
"progression": {
"name": "Nurture Cast",
"type": "program",
"description": null,
"steps": [
{
"name": "Not in Program",
"type": "Not in Program",
"typeName": "not_in_program",
"description": null,
"typeId": 1,
"order": 0,
"success": false,
"hidden": false,
"system": true,
"isEventCapSet": null,
"isUsed": true
},
{
"name": "Member",
"description": null,
"typeId": null,
"order": 10,
"success": false,
"hidden": false,
"system": false,
"isEventCapSet": null,
"isUsed": true
},
{
"name": "Excluded",
"description": null,
"typeId": null,
"order": 20,
"success": false,
"hidden": false,
"system": false,
"isEventCapSet": null,
"isUsed": true
},
{
"name": "Converted",
"description": null,
"typeId": null,
"order": 30,
"success": true,
"hidden": false,
"system": false,
"isEventCapSet": null,
"isUsed": true
},
{
"name": "Engaged",
"description": null,
"typeId": null,
"order": 30,
"success": true,
"hidden": false,
"system": false,
"isEventCapSet": null,
"isUsed": true
}
]
},
"hidden": false,
"deleted": false
},
{
"value": "Operational",
"progression": {
"name": "Operational",
"type": "program",
"description": null,
"steps": [
{
"name": "Not in Program",
"type": "Not in Program",
"typeName": "not_in_program",
"description": null,
"typeId": 1,
"order": 0,
"success": false,
"hidden": false,
"system": true,
"isEventCapSet": null,
"isUsed": false
},
{
"name": "Member",
"description": null,
"typeId": null,
"order": 10,
"success": false,
"hidden": false,
"system": false,
"isEventCapSet": null,
"isUsed": true
},
{
"name": "Engaged",
"description": null,
"typeId": null,
"order": 20,
"success": true,
"hidden": true,
"system": false,
"isEventCapSet": null,
"isUsed": true
}
]
},
"hidden": false,
"deleted": false
}
]
}
What i am needing is specifically the channelTags array which i have currently broken out like this...
import pandas as pd
import json
# open json file
with open('file_name.json') as json_file:
data = json.load(json_file)
# turn json into df and get channelTags array
df_channel = pd.DataFrame.from_dict(data['channelTags'])
This is resulting in the following data structure...
| value | hidden | deleted | progression.name | progression.type | progression.description | progression.steps |
|---|---|---|---|---|---|---|
| Advertising | False | False | Advertising | program | null | [{'name': 'Not in Program', 'type': 'Not in Program', 'typeName': 'not_in_program', 'description': None, 'typeId': 1, 'order': 0, 'success': False, 'hidden': False, 'system': True, 'isEventCapSet': None, 'isUsed': False}, {'name': 'Member', 'description': None, 'typeId': None, 'order': 10, 'success': False, 'hidden': False, 'system': False, 'isEventCapSet': None, 'isUsed': True}, {'name': 'Engaged', 'description': None, 'typeId': None, 'order': 20, 'success': True, 'hidden': False, 'system': False, 'isEventCapSet': None, 'isUsed': True}] |
| Trade Show | False | False | Trade Show | event | null | [{'name': 'Not in Program', 'type': 'Not in Program', 'typeName': 'not_in_program', 'description': None, 'typeId': 1, 'order': 0, 'success': False, 'hidden': False, 'system': True, 'isEventCapSet': False, 'isUsed': False}, {'name': 'Invited', 'description': None, 'typeId': None, 'order': 10, 'success': False, 'hidden': False, 'system': False, 'isEventCapSet': False, 'isUsed': True}, {'name': 'Registered', 'type': 'Registered', 'typeName': 'registered', 'description': None, 'typeId': 5, 'order': 20, 'success': False, 'hidden': False, 'system': False, 'isEventCapSet': False, 'isUsed': True}, {'name': 'Waitlisted', 'type': 'Waitlisted', 'typeName': 'wait_listed', 'description': None, 'typeId': 4, 'order': 20, 'success': False, 'hidden': False, 'system': False, 'isEventCapSet': False, 'isUsed': False}, {'name': 'No Show', 'description': None, 'typeId': None, 'order': 30, 'success': False, 'hidden': False, 'system': False, 'isEventCapSet': False, 'isUsed': False}, {'name': 'Attended Show', 'type': 'Attended', 'typeName': 'attended', 'description': None, 'typeId': 7, 'order': 40, 'success': False, 'hidden': False, 'system': False, 'isEventCapSet': False, 'isUsed': True}, {'name': 'Visited Booth', 'description': None, 'typeId': None, 'order': 50, 'success': True, 'hidden': False, 'system': False, 'isEventCapSet': False, 'isUsed': True}, {'name': 'Attended Hosted Event', 'description': None, 'typeId': None, 'order': 60, 'success': True, 'hidden': False, 'system': False, 'isEventCapSet': False, 'isUsed': True}] |
I still need to be able to go one step further down to parse out the progression.steps. I have accomplished this somewhat by using the following...
df_normal = pd.json_normalize(df_channel['progression'], 'steps', errors='ignore')
This code results in the following...
| name | type | typeName | description | typeId | order | success | hidden | system | isEventCapSet | IsUsed |
|---|---|---|---|---|---|---|---|---|---|---|
| Not in Program | Not in Program | Not in Program | null | 1 | 0 | FALSE | FALSE | TRUE | null | FALSE |
| Member | null | null | null | null | 10 | FALSE | FALSE | TRUE | null | FALSE |
| Engaged | null | null | null | null | 20 | FALSE | FALSE | TRUE | null | FALSE |
What i am missing is the ability to combine these two dataframes as i do not have a key field, the end result should be the df_channel dataframe and the df_normal dataframe combined with a left join on a df_channel['value'] entry.
What am i missing here?
CodePudding user response：
starting from a sample dictionary data as below:
data = {
"genericTags": [
{
"appliesTo": [
"program",
"email_batch",
"nurture",
"event",
"webinar"
],
"name": "Language",
"type": None,
"values": [
{
"value": "Chinese",
"hidden": False,
"deleted": False
},
{
"value": "English",
"hidden": False,
"deleted": False
},
{
"value": "French",
"hidden": False,
"deleted": False
},
{
"value": "Japanese",
"hidden": False,
"deleted": False
},
{
"value": "Portuguese",
"hidden": False,
"deleted": False
},
{
"value": "Spanish",
"hidden": False,
"deleted": False
}
],
"description": None
},
{
"appliesTo": [
"program",
"email_batch",
"nurture",
"webinar",
"event"
],
"name": "Product",
"type": None,
"values": [
{
"value": "P1",
"hidden": False,
"deleted": False
},
{
"value": "P2",
"hidden": False,
"deleted": False
},
{
"value": "P3",
"hidden": False,
"deleted": False
},
{
"value": "P4",
"hidden": False,
"deleted": False
},
{
"value": "P5",
"hidden": False,
"deleted": False
},
{
"value": "P6",
"hidden": False,
"deleted": False
},
{
"value": "P7",
"hidden": False,
"deleted": False
},
{
"value": "P8",
"hidden": False,
"deleted": False
},
{
"value": "P9",
"hidden": False,
"deleted": False
},
{
"value": "P10",
"hidden": False,
"deleted": False
},
{
"value": "P11",
"hidden": False,
"deleted": False
},
{
"value": "P12",
"hidden": False,
"deleted": False
},
{
"value": "Multiple",
"hidden": False,
"deleted": False
},
{
"value": "None",
"hidden": False,
"deleted": False
}
],
"description": None
},
{
"appliesTo": [
"program",
"email_batch",
"nurture",
"event",
"webinar"
],
"name": "Region",
"type": None,
"values": [
{
"value": "Americas",
"hidden": False,
"deleted": False
},
{
"value": "APJ",
"hidden": False,
"deleted": False
},
{
"value": "EMEA",
"hidden": False,
"deleted": False
},
{
"value": "WW",
"hidden": False,
"deleted": False
}
],
"description": None
}
],
"channelTags": [
{
"value": "Advertising",
"progression": {
"name": "Advertising",
"type": "program",
"description": None,
"steps": [
{
"name": "Not in Program",
"type": "Not in Program",
"typeName": "not_in_program",
"description": None,
"typeId": 1,
"order": 0,
"success": False,
"hidden": False,
"system": True,
"isEventCapSet": None,
"isUsed": False
},
{
"name": "Member",
"description": None,
"typeId": None,
"order": 10,
"success": False,
"hidden": False,
"system": False,
"isEventCapSet": None,
"isUsed": True
},
{
"name": "Engaged",
"description": None,
"typeId": None,
"order": 20,
"success": True,
"hidden": False,
"system": False,
"isEventCapSet": None,
"isUsed": True
}
]
},
"hidden": False,
"deleted": False
},
{
"value": "Association",
"progression": {
"name": "Association",
"type": "program",
"description": None,
"steps": [
{
"name": "Not in Program",
"type": "Not in Program",
"typeName": "not_in_program",
"description": None,
"typeId": 1,
"order": 0,
"success": False,
"hidden": False,
"system": True,
"isEventCapSet": None,
"isUsed": False
},
{
"name": "Member",
"description": None,
"typeId": None,
"order": 10,
"success": False,
"hidden": False,
"system": False,
"isEventCapSet": None,
"isUsed": True
},
{
"name": "Engaged",
"description": None,
"typeId": None,
"order": 20,
"success": True,
"hidden": False,
"system": False,
"isEventCapSet": None,
"isUsed": True
}
]
},
"hidden": False,
"deleted": False
},
{
"value": "Contact Request",
"progression": {
"name": "Contact Request",
"type": "program",
"description": None,
"steps": [
{
"name": "Not in Program",
"type": "Not in Program",
"typeName": "not_in_program",
"description": None,
"typeId": 1,
"order": 0,
"success": False,
"hidden": False,
"system": True,
"isEventCapSet": None,
"isUsed": False
},
{
"name": "Member",
"description": None,
"typeId": None,
"order": 10,
"success": False,
"hidden": False,
"system": False,
"isEventCapSet": None,
"isUsed": True
},
{
"name": "Sent Email",
"description": None,
"typeId": None,
"order": 20,
"success": False,
"hidden": False,
"system": False,
"isEventCapSet": None,
"isUsed": True
},
{
"name": "Engaged",
"description": None,
"typeId": None,
"order": 30,
"success": True,
"hidden": False,
"system": False,
"isEventCapSet": None,
"isUsed": True
}
]
},
"hidden": False,
"deleted": False
},
{
"value": "Content",
"progression": {
"name": "Content",
"type": "program",
"description": None,
"steps": [
{
"name": "Not in Program",
"type": "Not in Program",
"typeName": "not_in_program",
"description": None,
"typeId": 1,
"order": 0,
"success": False,
"hidden": False,
"system": True,
"isEventCapSet": None,
"isUsed": False
},
{
"name": "Member",
"description": None,
"typeId": None,
"order": 10,
"success": False,
"hidden": False,
"system": False,
"isEventCapSet": None,
"isUsed": True
},
{
"name": "Engaged",
"description": None,
"typeId": None,
"order": 20,
"success": True,
"hidden": False,
"system": False,
"isEventCapSet": None,
"isUsed": True
}
]
},
"hidden": False,
"deleted": False
},
{
"value": "Content Syndication",
"progression": {
"name": "Content Syndication",
"type": "program",
"description": None,
"steps": [
{
"name": "Not in Program",
"type": "Not in Program",
"typeName": "not_in_program",
"description": None,
"typeId": 1,
"order": 0,
"success": False,
"hidden": False,
"system": True,
"isEventCapSet": None,
"isUsed": False
},
{
"name": "Member",
"description": None,
"typeId": None,
"order": 10,
"success": False,
"hidden": False,
"system": False,
"isEventCapSet": None,
"isUsed": True
},
{
"name": "Engaged",
"description": None,
"typeId": None,
"order": 20,
"success": True,
"hidden": False,
"system": False,
"isEventCapSet": None,
"isUsed": True
}
]
},
"hidden": False,
"deleted": False
},
{
"value": "Direct Mail",
"progression": {
"name": "Direct Mail",
"type": "program",
"description": None,
"steps": [
{
"name": "Not in Program",
"type": "Not in Program",
"typeName": "not_in_program",
"description": None,
"typeId": 1,
"order": 0,
"success": False,
"hidden": False,
"system": True,
"isEventCapSet": None,
"isUsed": False
},
{
"name": "Member",
"description": None,
"typeId": None,
"order": 10,
"success": False,
"hidden": False,
"system": False,
"isEventCapSet": None,
"isUsed": True
},
{
"name": "Engaged",
"description": None,
"typeId": None,
"order": 20,
"success": True,
"hidden": False,
"system": False,
"isEventCapSet": None,
"isUsed": True
}
]
},
"hidden": False,
"deleted": False
},
{
"value": "Display",
"progression": {
"name": "Display",
"type": "program",
"description": None,
"steps": [
{
"name": "Not in Program",
"type": "Not in Program",
"typeName": "not_in_program",
"description": None,
"typeId": 1,
"order": 0,
"success": False,
"hidden": False,
"system": True,
"isEventCapSet": None,
"isUsed": False
},
{
"name": "Member",
"description": None,
"typeId": None,
"order": 10,
"success": False,
"hidden": False,
"system": False,
"isEventCapSet": None,
"isUsed": True
},
{
"name": "Engaged",
"description": None,
"typeId": None,
"order": 20,
"success": True,
"hidden": False,
"system": False,
"isEventCapSet": None,
"isUsed": True
}
]
},
"hidden": False,
"deleted": False
},
{
"value": "Email",
"progression": {
"name": "Email",
"type": "program",
"description": None,
"steps": [
{
"name": "Not in Program",
"type": "Not in Program",
"typeName": "not_in_program",
"description": None,
"typeId": 1,
"order": 0,
"success": False,
"hidden": False,
"system": True,
"isEventCapSet": None,
"isUsed": True
},
{
"name": "Member",
"description": None,
"typeId": None,
"order": 10,
"success": False,
"hidden": False,
"system": False,
"isEventCapSet": None,
"isUsed": True
},
{
"name": "Engaged",
"description": None,
"typeId": None,
"order": 20,
"success": True,
"hidden": False,
"system": False,
"isEventCapSet": None,
"isUsed": True
}
]
},
"hidden": False,
"deleted": False
},
{
"value": "Email Send",
"progression": {
"name": "Email Send",
"type": "email_batch",
"description": None,
"steps": [
{
"name": "Not in Program",
"type": "Not in Program",
"typeName": "not_in_program",
"description": None,
"typeId": 1,
"order": 0,
"success": False,
"hidden": False,
"system": True,
"isEventCapSet": None,
"isUsed": True
},
{
"name": "Member",
"description": None,
"typeId": None,
"order": 10,
"success": False,
"hidden": False,
"system": False,
"isEventCapSet": None,
"isUsed": True
},
{
"name": "Engaged",
"description": None,
"typeId": None,
"order": 20,
"success": True,
"hidden": False,
"system": False,
"isEventCapSet": None,
"isUsed": True
}
]
},
"hidden": False,
"deleted": False
},
{
"value": "Event",
"progression": {
"name": "Event",
"type": "event",
"description": None,
"steps": [
{
"name": "Not in Program",
"type": "Not in Program",
"typeName": "not_in_program",
"description": None,
"typeId": 1,
"order": 0,
"success": False,
"hidden": False,
"system": True,
"isEventCapSet": False,
"isUsed": False
},
{
"name": "Invited",
"description": None,
"typeId": None,
"order": 10,
"success": False,
"hidden": False,
"system": False,
"isEventCapSet": False,
"isUsed": True
},
{
"name": "Registered",
"type": "Registered",
"typeName": "registered",
"description": None,
"typeId": 5,
"order": 20,
"success": False,
"hidden": False,
"system": False,
"isEventCapSet": False,
"isUsed": True
},
{
"name": "Waitlisted",
"type": "Waitlisted",
"typeName": "wait_listed",
"description": None,
"typeId": 4,
"order": 20,
"success": False,
"hidden": False,
"system": False,
"isEventCapSet": False,
"isUsed": False
},
{
"name": "No Show",
"description": None,
"typeId": None,
"order": 30,
"success": False,
"hidden": False,
"system": False,
"isEventCapSet": False,
"isUsed": True
},
{
"name": "Attended",
"type": "Attended",
"typeName": "attended",
"description": None,
"typeId": 7,
"order": 40,
"success": True,
"hidden": False,
"system": False,
"isEventCapSet": False,
"isUsed": True
},
{
"name": "Attended Session",
"description": None,
"typeId": None,
"order": 50,
"success": True,
"hidden": False,
"system": False,
"isEventCapSet": False,
"isUsed": True
}
]
},
"hidden": False,
"deleted": False
},
{
"value": "Intent Signal",
"progression": {
"name": "Intent Signal",
"type": "program",
"description": None,
"steps": [
{
"name": "Not in Program",
"type": "Not in Program",
"typeName": "not_in_program",
"description": None,
"typeId": 1,
"order": 0,
"success": False,
"hidden": False,
"system": True,
"isEventCapSet": None,
"isUsed": False
},
{
"name": "Member",
"description": None,
"typeId": None,
"order": 10,
"success": False,
"hidden": False,
"system": False,
"isEventCapSet": None,
"isUsed": True
},
{
"name": "Engaged",
"description": None,
"typeId": None,
"order": 20,
"success": True,
"hidden": False,
"system": False,
"isEventCapSet": None,
"isUsed": True
}
]
},
"hidden": False,
"deleted": False
},
{
"value": "Interactive Chat",
"progression": {
"name": "Interactive Chat",
"type": "program",
"description": None,
"steps": [
{
"name": "Not in Program",
"type": "Not in Program",
"typeName": "not_in_program",
"description": None,
"typeId": 1,
"order": 0,
"success": False,
"hidden": False,
"system": True,
"isEventCapSet": None,
"isUsed": False
},
{
"name": "Member",
"description": None,
"typeId": None,
"order": 10,
"success": False,
"hidden": False,
"system": False,
"isEventCapSet": None,
"isUsed": True
},
{
"name": "Engaged",
"description": None,
"typeId": None,
"order": 20,
"success": True,
"hidden": False,
"system": False,
"isEventCapSet": None,
"isUsed": True
}
]
},
"hidden": False,
"deleted": False
},
{
"value": "List Import",
"progression": {
"name": "List Import",
"type": "program",
"description": None,
"steps": [
{
"name": "Not in Program",
"type": "Not in Program",
"typeName": "not_in_program",
"description": None,
"typeId": 1,
"order": 0,
"success": False,
"hidden": False,
"system": True,
"isEventCapSet": None,
"isUsed": False
},
{
"name": "Member",
"description": None,
"typeId": None,
"order": 10,
"success": False,
"hidden": False,
"system": False,
"isEventCapSet": None,
"isUsed": True
},
{
"name": "Engaged",
"description": None,
"typeId": None,
"order": 20,
"success": True,
"hidden": False,
"system": False,
"isEventCapSet": None,
"isUsed": True
}
]
},
"hidden": False,
"deleted": False
},
{
"value": "Nurture",
"progression": {
"name": "Nurture",
"type": "nurture",
"description": None,
"steps": [
{
"name": "Not in Program",
"type": "Not in Program",
"typeName": "not_in_program",
"description": None,
"typeId": 1,
"order": 0,
"success": False,
"hidden": False,
"system": True,
"isEventCapSet": None,
"isUsed": True
},
{
"name": "Member",
"description": None,
"typeId": None,
"order": 10,
"success": False,
"hidden": False,
"system": False,
"isEventCapSet": None,
"isUsed": True
},
{
"name": "Engaged",
"description": None,
"typeId": None,
"order": 20,
"success": True,
"hidden": False,
"system": False,
"isEventCapSet": None,
"isUsed": True
},
{
"name": "Converted",
"description": None,
"typeId": None,
"order": 30,
"success": True,
"hidden": False,
"system": False,
"isEventCapSet": None,
"isUsed": False
}
]
},
"hidden": False,
"deleted": False
},
{
"value": "Nurture Cast",
"progression": {
"name": "Nurture Cast",
"type": "program",
"description": None,
"steps": [
{
"name": "Not in Program",
"type": "Not in Program",
"typeName": "not_in_program",
"description": None,
"typeId": 1,
"order": 0,
"success": False,
"hidden": False,
"system": True,
"isEventCapSet": None,
"isUsed": True
},
{
"name": "Member",
"description": None,
"typeId": None,
"order": 10,
"success": False,
"hidden": False,
"system": False,
"isEventCapSet": None,
"isUsed": True
},
{
"name": "Excluded",
"description": None,
"typeId": None,
"order": 20,
"success": False,
"hidden": False,
"system": False,
"isEventCapSet": None,
"isUsed": True
},
{
"name": "Converted",
"description": None,
"typeId": None,
"order": 30,
"success": True,
"hidden": False,
"system": False,
"isEventCapSet": None,
"isUsed": True
},
{
"name": "Engaged",
"description": None,
"typeId": None,
"order": 30,
"success": True,
"hidden": False,
"system": False,
"isEventCapSet": None,
"isUsed": True
}
]
},
"hidden": False,
"deleted": False
},
{
"value": "Operational",
"progression": {
"name": "Operational",
"type": "program",
"description": None,
"steps": [
{
"name": "Not in Program",
"type": "Not in Program",
"typeName": "not_in_program",
"description": None,
"typeId": 1,
"order": 0,
"success": False,
"hidden": False,
"system": True,
"isEventCapSet": None,
"isUsed": False
},
{
"name": "Member",
"description": None,
"typeId": None,
"order": 10,
"success": False,
"hidden": False,
"system": False,
"isEventCapSet": None,
"isUsed": True
},
{
"name": "Engaged",
"description": None,
"typeId": None,
"order": 20,
"success": True,
"hidden": True,
"system": False,
"isEventCapSet": None,
"isUsed": True
}
]
},
"hidden": False,
"deleted": False
}
]
}
try:
df = pd.json_normalize(data['channelTags'])
df = df.merge(pd.concat(df['progression.steps'].apply(pd.DataFrame).tolist(), keys=df.index), how='left')
CodePudding user response：
df=pd.DataFrame(data) #all data. there are two columns: genericTags and channelTags
import json
to_json = json.loads(df.to_json(orient="records"))
df_v1 = pd.json_normalize(to_json)
mask = df_v1.explode('genericTags.values', ignore_index=True)
final=mask.join(pd.DataFrame([*mask.pop('genericTags.values')], index=mask.index))
final.columns=final.columns '_' #for second step. because there are columns with the same name.
final_mask = final.explode('channelTags.progression.steps_', ignore_index=True)
final=final_mask.join(pd.DataFrame([*final_mask.pop('channelTags.progression.steps_')], index=final_mask.index))