从JSON聊天机器人日志中提取字段并将其写入Python中的XLSX文件

Question

我有下面的JSON文件，我需要从中提取字段并写入XLSX文件

这是一个聊天机器人的对话。我需要自底向上提取，这意味着聊天是按照他们的时间戳的降序提取的

下面是两个例子

在示例1中

用户1说“嗨”

机器人通过两个按钮“A1任务和B1任务”回复“你好，下午好”

---

在示例2中

用户2说“T市场”

Bot回复“法国市场很好”

输入Json

[
    {
        "document": {
            "$type": "abc",
            "From": {
                "$type": "aaa",
                "id": "123",
                "name": "buddy",
                "aadObjectId": null,
                "role": null
            },
            "To": {
                "$type": "abc",
                "id": "345",
                "name": "User1",
                "aadObjectId": "yyu5",
                "role": "User1"
            },
            "Text": null,
            "attchment": {
                "text": "Hi User1, Good Afternoon!! \n\n I can help you with",
                "buttons": [
                    {
                        "type": "imBack",
                        "title": "A1 task",
                        "value": "B1 task"
                    },
                    {
                        "type": "imBack",
                        "title": "b1 task",
                        "value": "b1 task"
                    }
                ]
            },
            "timestamp": "2020-02-13T08:10:32.9160151+00:00"
        }
    },

    {
        "document": {
            "$type": "iop",
            "From": {
                "$type": "jkl",
                "id": "984",
                "name": "User1",
                "aadObjectId": "jh56",
                "role": "User1"
            },
            "To": {
                "$type": "iopa",
                "id": "865",
                "name": "buddy",
                "aadObjectId": null,
                "role": null
            },
            "Text": "hi ",
            "attchment": null,
            "timestamp": "2020-02-13T08:10:32.9117352+00:00"
        }
    },



    {
        "document": {
            "$type": "pqr",
            "From": {
                "$type": "ppq",
                "id": "567",
                "name": "buddy",
                "aadObjectId": null,
                "role": null
            },
            "To": {
                "$type": "ppw",
                "id": "908",
                "name": "User2",
                "aadObjectId": "dd4",
                "role": "User2"
            },
            "Text": "Market in france is awesome",
            "attchment": null,
            "timestamp": "2020-02-13T07:50:43.4723205+00:00"
        }
    },
    {
        "document": {
            "$type": "hjk",
            "From": {
                "$type": "iop",
                "id": "098",
                "name": "User2",
                "aadObjectId": "ffr",
                "role": "User2"
            },
            "To": {
                "$type": "uioo",
                "id": "980",
                "name": "buddy",
                "aadObjectId": null,
                "role": null
            },
            "Text": "t markets",
            "attchment": null,
            "timestamp": "2020-02-13T07:50:43.4717092+00:00"
        }
    },

我正在尝试的输出如下所示

            User          Utterances        Response

            User1         Hi                Hi Good afternoon 
                                            A1 B1      

            User2         T market          market is good in france