# Converting Pandas DataFrame to JSON to Array

**URL:** <https://discourse.nodered.org/t/converting-pandas-dataframe-to-json-to-array/92226>\
**Category:** General\
**Created:** [7 October 2024 21:26 UTC](https://discourse.nodered.org/t/converting-pandas-dataframe-to-json-to-array/92226 "2024-10-07T21:26:07Z")\
**Posts on this page:** 13\
**Page:** 1

<div class="post-metadata">

**Author:** ![sonificator](https://avatars.discourse-cdn.com/v4/letter/s/53a042/32.png) [@sonificator](https://discourse.nodered.org/u/sonificator)\
**Post date:** [7 October 2024 21:26 UTC](https://discourse.nodered.org/t/converting-pandas-dataframe-to-json-to-array/92226/1 "2024-10-07T21:26:07Z")

</div>

Hi there, I am sourcing external (stock market) data via Python, which initially comes in Pandas DataFrame format, which I managed to convert into what seems like the correct JSON format, yet the debug panes continues to treat it as a string.

 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/0/4/04cb083d31cfdfcd2d3bbf9afdd57e6180b97895.png)

If I copy the data into the inject node (it validates that it's in the right json format) and outputs it as an array that I can then use in a table

```auto
[{"ticker":"NYSE:NAPA","name":"NAPA","close":10.95,"change":102.7777777778},{"ticker":"NASDAQ:SOBR","name":"SOBR","close":10.1,"change":165.7894736842},{"ticker":"AMEX:AMBO","name":"AMBO","close":4.02,"change":226.8292682927},{"ticker":"OTC:ACBD","name":"ACBD","close":5.07,"change":153.5},{"ticker":"OTC:EVTK","name":"EVTK","close":3.564,"change":117.3170731707},{"ticker":"OTC:GZUHY","name":"GZUHY","close":7.13,"change":112.8358208955},{"ticker":"OTC:NCCGF","name":"NCCGF","close":2.35,"change":113.6363636364}]

```

I tried the JSON Convert node but it didn't work; could you advise what needs to be done please. Thanks!

---

<div class="post-metadata">

**Author:** ![TotallyInformation](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/totallyinformation/32/31_2.png) [@TotallyInformation](https://discourse.nodered.org/u/TotallyInformation)\
**Post date:** [7 October 2024 21:52 UTC](https://discourse.nodered.org/t/converting-pandas-dataframe-to-json-to-array/92226/2 "2024-10-07T21:52:44Z")

</div>

Almost certainly, the best way to do the conversion is in Python if you can.

If you can't, there is always the danfos.js library which I think can do it for you.

---

<div class="post-metadata">

**Author:** ![sonificator](https://avatars.discourse-cdn.com/v4/letter/s/53a042/32.png) [@sonificator](https://discourse.nodered.org/u/sonificator)\
**Post date:** [7 October 2024 21:56 UTC](https://discourse.nodered.org/t/converting-pandas-dataframe-to-json-to-array/92226/3 "2024-10-07T21:56:15Z")

</div>

As I mentioned, I already did the conversion in Python into proper JSON format.

---

<div class="post-metadata">

**Author:** ![Steve-Mcl](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/steve-mcl/32/4826_2.png) [@Steve-Mcl](https://discourse.nodered.org/u/Steve-Mcl)\
**Post date:** [7 October 2024 21:56 UTC](https://discourse.nodered.org/t/converting-pandas-dataframe-to-json-to-array/92226/4 "2024-10-07T21:56:16Z")

</div>

Is the string in msg.payload? Just pass that through a JSON node and it will be come a usable object that you can access properties from.

---

<div class="post-metadata">

**Author:** ![jbudd](https://avatars.discourse-cdn.com/v4/letter/j/5f8ce5/32.png) [@jbudd](https://discourse.nodered.org/u/jbudd)\
**Post date:** [7 October 2024 21:58 UTC](https://discourse.nodered.org/t/converting-pandas-dataframe-to-json-to-array/92226/5 "2024-10-07T21:58:12Z")

</div>

Welcome to the forum @sonificator

How does the data get into Node-red - are you using an exec node to run the python script, MQTT, etc?

By "JSON Convert node" do you mean this, the standard JSON parser?  
 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/b/2/b282490a15dcb6aad05222607c522211ad1b0e1b.png)

---

<div class="post-metadata">

**Author:** ![Steve-Mcl](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/steve-mcl/32/4826_2.png) [@Steve-Mcl](https://discourse.nodered.org/u/Steve-Mcl)\
**Post date:** [7 October 2024 21:58 UTC](https://discourse.nodered.org/t/converting-pandas-dataframe-to-json-to-array/92226/6 "2024-10-07T21:58:14Z")

</div>

Yeah. And since it seems to be valid JSON, so long as it is in msg.payload, you should be able to pass it straight through the JSON node to parse it into an object.

---

<div class="post-metadata">

**Author:** ![sonificator](https://avatars.discourse-cdn.com/v4/letter/s/53a042/32.png) [@sonificator](https://discourse.nodered.org/u/sonificator)\
**Post date:** [7 October 2024 22:00 UTC](https://discourse.nodered.org/t/converting-pandas-dataframe-to-json-to-array/92226/7 "2024-10-07T22:00:22Z")

</div>

I tried passing it through JSON Node but it just replicated it. Here is another screenshot from the debug:

 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/f/3/f39021466f6c018e7f3f73e0d67ee4be3bfc8911.png)

---

<div class="post-metadata">

**Author:** ![Steve-Mcl](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/steve-mcl/32/4826_2.png) [@Steve-Mcl](https://discourse.nodered.org/u/Steve-Mcl)\
**Post date:** [7 October 2024 22:01 UTC](https://discourse.nodered.org/t/converting-pandas-dataframe-to-json-to-array/92226/8 "2024-10-07T22:01:17Z")

</div>

Show me how you have configured the json node.

Also show me your arrangement of nodes.

---

<div class="post-metadata">

**Author:** ![sonificator](https://avatars.discourse-cdn.com/v4/letter/s/53a042/32.png) [@sonificator](https://discourse.nodered.org/u/sonificator)\
**Post date:** [7 October 2024 22:02 UTC](https://discourse.nodered.org/t/converting-pandas-dataframe-to-json-to-array/92226/9 "2024-10-07T22:02:04Z")

</div>

Yes, indeed, coming from the exec node (yes, I meant the JSON parser). Thanks.

---

<div class="post-metadata">

**Author:** ![sonificator](https://avatars.discourse-cdn.com/v4/letter/s/53a042/32.png) [@sonificator](https://discourse.nodered.org/u/sonificator)\
**Post date:** [7 October 2024 22:04 UTC](https://discourse.nodered.org/t/converting-pandas-dataframe-to-json-to-array/92226/10 "2024-10-07T22:04:52Z")

</div>

Here is the flow

```auto
[
    {
        "id": "bc0911de95c8c5bb",
        "type": "tab",
        "label": "Flow 2",
        "disabled": false,
        "info": "",
        "env": []
    },
    {
        "id": "ecf691ca347c5c68",
        "type": "file",
        "z": "bc0911de95c8c5bb",
        "name": "TdV Scanner",
        "filename": "/home/ubuntu/scan.py",
        "filenameType": "str",
        "appendNewline": false,
        "createDir": false,
        "overwriteFile": "true",
        "encoding": "none",
        "x": 510,
        "y": 120,
        "wires": [
            [
                "382912e733161884"
            ]
        ]
    },
    {
        "id": "382912e733161884",
        "type": "exec",
        "z": "bc0911de95c8c5bb",
        "command": "python3 /home/ubuntu/scan.py",
        "addpay": "topic",
        "append": "",
        "useSpawn": "false",
        "timer": "",
        "winHide": true,
        "oldrc": false,
        "name": "TdV Scanner",
        "x": 510,
        "y": 160,
        "wires": [
            [
                "2aaa1d8217c72a92"
            ],
            [],
            []
        ]
    },
    {
        "id": "772421ee4d545f11",
        "type": "debug",
        "z": "bc0911de95c8c5bb",
        "name": "debug 30",
        "active": true,
        "tosidebar": true,
        "console": false,
        "tostatus": false,
        "complete": "payload",
        "targetType": "msg",
        "statusVal": "",
        "statusType": "auto",
        "x": 680,
        "y": 240,
        "wires": []
    },
    {
        "id": "9dc8863bae5cd720",
        "type": "template",
        "z": "bc0911de95c8c5bb",
        "name": "TdV Scanner",
        "field": "payload",
        "fieldType": "msg",
        "format": "python",
        "syntax": "plain",
        "template": "\nfrom tradingview_screener import Query, Column\nfrom json import loads, dumps\n\nq = Query().select('name', 'close', 'change')\\\n .where(\n Column('close').between(1, 20),\n Column('change') > 100,\n\n )\\\n\nnum_rows, dataFrame = q.get_scanner_data()\n\n\njson_columns = dataFrame.to_json(orient ='records') \nprint(json_columns, \"\\n\")\n \n",
        "output": "str",
        "x": 270,
        "y": 120,
        "wires": [
            [
                "ecf691ca347c5c68"
            ]
        ]
    },
    {
        "id": "ff081bc8414999a6",
        "type": "inject",
        "z": "bc0911de95c8c5bb",
        "name": "",
        "props": [
            {
                "p": "payload"
            },
            {
                "p": "topic",
                "vt": "str"
            }
        ],
        "repeat": "",
        "crontab": "",
        "once": false,
        "onceDelay": 0.1,
        "topic": "",
        "payload": "",
        "payloadType": "date",
        "x": 100,
        "y": 120,
        "wires": [
            [
                "9dc8863bae5cd720"
            ]
        ]
    },
    {
        "id": "2aaa1d8217c72a92",
        "type": "json",
        "z": "bc0911de95c8c5bb",
        "name": "",
        "property": "payload",
        "action": "str",
        "pretty": true,
        "x": 510,
        "y": 240,
        "wires": [
            [
                "772421ee4d545f11"
            ]
        ]
    }
]

```

And JSON node config:

![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/3/5/3549be229aa3827552010197f05a3478fcfe339f.png)

---

<div class="post-metadata">

**Author:** ![Steve-Mcl](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/steve-mcl/32/4826_2.png) [@Steve-Mcl](https://discourse.nodered.org/u/Steve-Mcl)\
**Post date:** [7 October 2024 22:07 UTC](https://discourse.nodered.org/t/converting-pandas-dataframe-to-json-to-array/92226/11 "2024-10-07T22:07:26Z")

</div>

> [@sonificator](#):
>
> JSON node config:
> 
> ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/3/5/3549be229aa3827552010197f05a3478fcfe339f.png)

There is your problem.

Your data is already JSON. Just to be clear, JSON stands for JavaScript object notation. I.e it is a string representation of a JavaScript object. since your data is already JSON, there is no point in converting it to JSON. Change the action option back to automatic.

---

<div class="post-metadata">

**Author:** ![sonificator](https://avatars.discourse-cdn.com/v4/letter/s/53a042/32.png) [@sonificator](https://discourse.nodered.org/u/sonificator)\
**Post date:** [7 October 2024 22:11 UTC](https://discourse.nodered.org/t/converting-pandas-dataframe-to-json-to-array/92226/12 "2024-10-07T22:11:24Z")

</div>

Thanks so much!

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/1X/d073cd938eafa2e558d7c2cd59003b3ef4963033.png) [@system](https://discourse.nodered.org/u/system)\
**Post date:** [21 October 2024 22:11 UTC](https://discourse.nodered.org/t/converting-pandas-dataframe-to-json-to-array/92226/13 "2024-10-21T22:11:46Z")

</div>

This topic was automatically closed 14 days after the last reply. New replies are no longer allowed.