PySpark展平嵌套结构：MongoDB表单提交数据转CSV

需求概述 #

我有MongoDB的forms和submissions两个集合：

• forms：定义动态UI组件，支持textfield、checkbox、radio、selectboxes、columns、tables、datagrids等类型
• submissions：存储用户提交的扁平化数据，已加载到DataFrame，其中data列是JSON格式的键值对（键为组件key，值为用户输入）

需要将提交数据转换为CSV格式，要求：

• 保留组件的key、用户输入value、组件label
• 对数组类型的提交数据（如datagrid），跟踪每条数据的index

核心处理逻辑 #

1. 解析表单组件，构建映射表：

   • 递归遍历所有表单组件，提取每个组件的key与label的对应关系
   • 对selectboxes类型，额外提取选项的value与label映射
   • 处理嵌套组件（columns、table、datagrid）中的子组件，统一收集所有key-label映射

2. 处理提交数据，生成结构化记录：

   • 遍历data中的每个键值对
   • 普通字段（如textfield、number）：直接生成一条记录，index为0
   • selectboxes类型：遍历其内部的键值对，用选项的label替代父组件label
   • datagrid类型：遍历数组中的每个元素，记录当前元素的index，再提取元素内的字段生成记录

代码实现 #

import pandas as pd
from typing import Dict, List

def parse_form_components(components: List[Dict]) -> Dict:
    """解析表单组件，生成key到label的映射，包括selectboxes的选项映射"""
    key_label_map = {}
    select_options_map = {}

    def recursive_parse(comp):
        comp_type = comp.get('type')
        # 处理普通输入组件
        if 'key' in comp and comp_type not in ['columns', 'table', 'datagrid']:
            key_label_map[comp['key']] = comp['label']
        
        # 处理selectboxes，记录选项的value-label映射
        if comp_type == 'selectboxes' and 'values' in comp:
            select_options_map[comp['key']] = {opt['value']: opt['label'] for opt in comp['values']}
        
        # 处理嵌套组件：columns
        if comp_type == 'columns' and 'columns' in comp:
            for col in comp['columns']:
                for sub_comp in col.get('components', []):
                    recursive_parse(sub_comp)
        
        # 处理table组件
        if comp_type == 'table' and 'rows' in comp:
            for row in comp['rows']:
                for cell in row:
                    for sub_comp in cell.get('components', []):
                        recursive_parse(sub_comp)
        
        # 处理datagrid组件
        if comp_type == 'datagrid' and 'components' in comp:
            for sub_comp in comp['components']:
                recursive_parse(sub_comp)
    
    for comp in components:
        recursive_parse(comp)
    
    return key_label_map, select_options_map

def process_submission_data(submission_data: Dict, key_label_map: Dict, select_options_map: Dict) -> List[Dict]:
    """处理单条提交数据，生成CSV所需的记录列表"""
    records = []

    def add_record(key, value, label, index=0):
        records.append({
            'key': key,
            'value': value,
            'label': label,
            'index': index
        })
    
    for key, value in submission_data.items():
        # 处理datagrid数组类型
        if isinstance(value, list):
            for idx, item in enumerate(value):
                for item_key, item_value in item.items():
                    if item_key in key_label_map:
                        add_record(item_key, item_value, key_label_map[item_key], idx)
            continue
        
        # 处理selectboxes的嵌套键值对
        if key in select_options_map and isinstance(value, dict):
            for opt_key, opt_value in value.items():
                add_record(opt_key, opt_value, select_options_map[key][opt_key])
            continue
        
        # 普通字段
        if key in key_label_map:
            add_record(key, value, key_label_map[key])
    
    return records

# 示例使用
if __name__ == '__main__':
    # 示例表单组件
    sample_form_components = [
        {"key": "name", "label": "Name", "type": "textfield"},
        {"label": "Age", "key": "age", "type": "number"},
        {"key": "adult", "label": "18 Plus", "type": "checkbox"},
        {"label": "Gender", "type": "radio", "key": "gender", "values": [{"label": "Male", "value": "male"}, {"label": "Female", "value": "female"}, {"label": "Other", "value": "other"}]},
        {"label": "Countries Visited", "type": "selectboxes", "key": "countriesVisited", "values": [{"label": "France", "value": "fr"}, {"label": "India", "value": "in"}]},
        {"label": "Columns", "type": "columns", "key": "columns", "columns": [{"components": [{"label": "Column Field 1", "key": "columnField1", "type": "textfield"}]}, {"components": [{"label": "Column Field 2", "type": "number", "key": "columnField2"}]}]},
        {"type": "table", "rows": [[{"components": [{"label": "Table Field 1", "key": "textField", "type": "textfield"}]}, {"components": [{"key": "number", "type": "number", "label": "Number"}]}], [{"components": [{"label": "Table Field 3", "key": "tableField3", "type": "number"}]}, {"components": [{"label": "Table Field 4", "key": "tableField4", "type": "number"}]}]]},
        {"label": "Data Grid", "key": "dataGrid", "type": "datagrid", "components": [{"key": "gridField1", "label": "Grid Field 1", "type": "textfield"}, {"label": "Gird Field 2", "key": "girdField2", "type": "number"}, {"label": "Grid Field 4", "key": "gridField4", "type": "checkbox"}]}
    ]

    # 示例提交数据
    sample_submission = {
        "_id": "69283e5d3703daffa931277c",
        "data": {
            "name": "John",
            "age": 22,
            "adult": True,
            "gender": "male",
            "countriesVisited": {"fr": True, "in": False},
            "columnField1": "ewvew",
            "columnField2": 1234,
            "textField": "sdv",
            "number": 12341,
            "tableField3": 123421,
            "tableField4": 2323,
            "dataGrid": [{"gridField1": "Data 1", "girdField2": 234, "gridField4": True}, {"gridField1": "Data 23", "gridField4": False}]
        }
    }

    # 解析组件映射
    key_label_map, select_options_map = parse_form_components(sample_form_components)
    # 处理提交数据
    records = process_submission_data(sample_submission['data'], key_label_map, select_options_map)
    # 转换为DataFrame并输出CSV
    df = pd.DataFrame(records)
    print(df.to_csv(index=False))

示例输入输出 #

示例表单组件 #

[
    {
        "key": "name",
        "label": "Name",
        "type": "textfield"
    },
    {
        "label": "Age",
        "key": "age",
        "type": "number"
    },
    {
        "key": "adult",
        "label": "18 Plus",
        "type": "checkbox"
    },
    {
        "label": "Gender",
        "type": "radio",
        "key": "gender",
        "values": [
            {
                "label": "Male",
                "value": "male"
            },
            {
                "label": "Female",
                "value": "female"
            },
            {
                "label": "Other",
                "value": "other"
            }
        ]
    },
    {
        "label": "Countries Visited",
        "type": "selectboxes",
        "key": "countriesVisited",
        "values": [
            {
                "label": "France",
                "value": "fr"
            },
            {
                "label": "India",
                "value": "in"
            }
        ]
    },
    {
        "label": "Columns",
        "type": "columns",
        "key": "columns",
        "columns": [
            {
                "components": [
                    {
                        "label": "Column Field 1",
                        "key": "columnField1",
                        "type": "textfield"
                    }
                ]
            },
            {
                "components": [
                    {
                        "label": "Column Field 2",
                        "type": "number",
                        "key": "columnField2"
                    }
                ]
            }
        ]
    },
    {
        "type": "table",
        "rows": [
            [
                {
                    "components": [
                        {
                            "label": "Table Field 1",
                            "key": "textField",
                            "type": "textfield"
                        }
                    ]
                },
                {
                    "components": [
                        {
                            "key": "number",
                            "type": "number",
                            "label": "Number"
                        }
                    ]
                }
            ],
            [
                {
                    "components": [
                       {
                        "label": "Table Field 3",
                        "key": "tableField3",
                        "type": "number"
                       }
                    ]
                },
                {
                    "components": [
                        {
                            "label": "Table Field 4",
                            "key": "tableField4",
                            "type": "number"
                        }
                    ]
                }
            ]
        ]
    },
    {
        "label": "Data Grid",
        "key": "dataGrid",
        "type": "datagrid",
        "components": [
            {
                "key": "gridField1",
                "label": "Grid Field 1",
                "type": "textfield"
            },
            {
                "label": "Gird Field 2",
                "key": "girdField2",
                "type": "number"
            },
            {
                "label": "Grid Field 4",
                "key": "gridField4",
                "type": "checkbox"
            }
        ]
    }
]

示例提交数据 #

{
    "_id": "69283e5d3703daffa931277c",
    "data": {
        "name": "John",
        "age": 22,
        "adult": true,
        "gender": "male",
        "countriesVisited": {
            "fr": true,
            "in": false
        },
        "columnField1": "ewvew",
        "columnField2": 1234,
        "textField": "sdv",
        "number": 12341,
        "tableField3": 123421,
        "tableField4": 2323,
        "dataGrid": [
            {
                "gridField1": "Data 1",
                "girdField2": 234,
                "gridField4": true
            },
            {
                "gridField1": "Data 23",
                "gridField4": false
            }
        ]
    }
}

期望输出CSV #

key,value,label,index
name,John,Name,0
age,22,Age,0
adult,True,18 Plus,0
gender,male,Gender,0
fr,True,France,0
in,False,India,0
columnField1,ewvew,Column Field 1,0
columnField2,1234,Column Field 2,0
textField,sdv,Table Field 1,0
number,12341,Number,0
tableField3,123421,Table Field 3,0
tableField4,2323,Table Field 4,0
gridField1,Data 1,Grid Field 1,0
girdField2,234,Gird Field 2,0
gridField4,True,Grid Field 4,0
gridField1,Data 23,Grid Field 1,1
gridField4,False,Grid Field 4,1

内容的提问来源于stack exchange，提问作者Aniruth N