Converting Pandas DataFrame to JSON to Array

General
1

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
image841Ă—166 9.18 KB

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

[{"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!

2

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.

3

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

4

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.

5

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

6

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.

7

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

image
image1142Ă—214 18.2 KB

8

Show me how you have configured the json node.

Also show me your arrangement of nodes.

9

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

10

Here is the flow

[
    {
        "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

11

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.

1 Like
12

Thanks so much!

13

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