Question 180850 in Json Question posted in
Our archive of expertly curated questions and answers provides insights and solutions to common problems related to this popular data interchange format. From parsing and manipulating JSON data to integrating it with various programming languages and web services, our archive has got you covered. Start exploring today and take your JSON skills to the next level

How can I separate LED lights and Switches in a Pandas DataFrame using JSON and numpy?

enter image description hereI had this problem a year ago and once again need help with Pandas. Embarrassingly having the same problem as last year as an api has been updated.

Original Question Here – I have a nested json that I want to run a for loop to only match the key that is different

I have a json with two types of devices, a Switch and an LED light.
Now i need to differentiate between the two and had it before the imported json had its structure changed.

My json is received like this with 11 devices, so [19 rows x 11 columns]:

`{
 "192.168.1.102": {
        "ip": "192.168.1.102",
        "gwId": "eb7ac0fbe689ca95f7dxxk",
        "active": 2,
        "encrypt": true,
        "productKey": "keya4gvchmtapm8n",
        "version": "3.3",
        "name": "Switch Garage Overhead",
        "key": "449ebc60812eb8bb",
        "mac": "38:1f:8d:18:5e:a2",
        "id": "eb7ac0fbe689ca95f7dxxk",
        "ability": 0,
        "token": "",
        "wf_cfg": "",
        "dev_type": "default",
        "err": "",
        "type": "default",
        "dps": {
            "dps": {
                "1": true,
                "9": 0,
                "38": "off",
                "40": "relay",
                "41": false,
                "42": "",
                "43": "",
                "44": ""
            }
        },
        "origin": "broadcast"
    },
    "192.168.1.147": {
        "ip": "192.168.1.147",
        "gwId": "ebfc16d57ed374932cjqfk",
        "active": 2,
        "encrypt": true,
        "productKey": "keypcvqhw97kqy4h",
        "version": "3.3",
        "name": "LED Office",
        "key": "805217605357161b",
        "mac": "84:e3:42:1a:96:35",
        "id": "ebfc16d57ed374932cjqfk",
        "ability": 0,
        "token": "",
        "wf_cfg": "",
        "dev_type": "default",
        "err": "",
        "type": "default",
        "dps": {
            "dps": {
                "20": false,
                "21": "white",
                "22": 10,
                "23": 1000,
                "24": "00180120038d",
                "25": "020d0d00000000000000001903e8",
                "26": 0
            }
        },
        "origin": "broadcast"
    }
}
`

Now i again want to be able to separate each based on "dps" 20 = LED and 1 = Switch.

My test Code:

import tinytuya
import json
import pandas as pd
import numpy as np
import time

f = open('snapshot.json',)
devices = json.load(f)
list = json.dumps(devices, indent=4)
print(list)

df = pd.DataFrame(devices)
# df = pd.json_normalize(devices)
df.to_dict()
df = df.fillna(-1)
print('n')
print('devices')
print(df)

# Separation of led's and switches here
df['type'] = None
try:
    df['type'] = np.where(
        df['dps.20'] != -1, 'light', df['type'])
except:
    pass
try:
    df['type'] = np.where(
        df['dps.dps.1'] != -1, 'switch', df['type'])
except:
    pass


lights = df[df['type'] == 'light'].reset_index(drop=True)
print("Lights {}".format(str(lights)))
switches = df[df['type'] == 'switch'].reset_index(drop=True)
print("switches {}".format(str(switches)))

# Lights
device_list = [lights]
for device in device_list:
    for idx, row in device.iterrows():
        name = row['name']
        id = row['gwId']
        id_new = id
        ip = row['ip']
        key = row['key']
        ver = row['ver']
        # id_new = id
        address = row['type'] + '_%s' % (idx+1)
        print('{name}n{id_new}n{ip}n{key}n{ver}n{address}n'.format(
            name=name, id_new=id_new, ip=ip, key=key, ver=ver, address=address,))

# Switches
device_list = [switches]
for device in device_list:
    for idx, row in device.iterrows():
        name = row['name']
        id = row['id']
        id_new = id
        ip = row['ip']
        key = row['key']
        ver = row['ver']
        # id_new = id
        address = row['type'] + '_%s' % (idx+1)
        print('{name}n{id_new}n{ip}n{key}n{ver}n{address}n'.format(
            name=name, id_new=id_new, ip=ip, key=key, ver=ver, address=address,))
        # node = tuya_switch_node.SwitchNode(
        #    self.poly, self.address, address, name, id_new, ip, key)
        # self.poly.addNode(node)
        # self.wait_for_node_event()

print("Done Appending and sorting")

CODE Run Result:
Please see image attached.

So i want to be able to have switches and LED’s separated by "dps" row value.

LED Garage
ebfd4f4263bb769d99zjkq
192.168.1.148
ec0b2b581a246eab
3.3
light_3

Switch Garden Outside
017743508caab5f385a7
192.168.1.146
2bc2d5aef80f3aee
3.3
switch_1

Thank You for any help I definitely appreciate it!

Tried re-writing code to no avail also several help and tutorials on Pandas. Must be something simple because I am stumped.

Wrote test code to sort json to separate switches from Led’s within the json file with Pandas. Seperation based on row "dps".

Result attached and code above.

Tags:

2

Answers


  1. 0

    It seems like it would be simpler to just loop through the data in plain python?

    You can check for the existence of 1 or 20 as a key and append to either the LED or SWITCH list if desired.

    LEDS = []
SWITCHES = []

for device in data.values():
   name = device["name"]
   ip = device["ip"]
   print(f"{name=}")
   print(f"{ip=}")
   if "1" in device["dps"]["dps"]:
      print("SWITCH")
   elif "20" in device["dps"]["dps"]:
      print("LED")
   else:
      print("OTHER")

    name='Switch Garage Overhead'
ip='192.168.1.102'
SWITCH
name='LED Office'
ip='192.168.1.147'
LED
    Login or Signup to reply.
  2. 0 
    import json
import pandas as pd

f = open('snapshot.json',)
devices = json.load(f)

# Normalize the dictionary to a pandas DataFrame
df = pd.DataFrame(devices).T

# Add a new 'device_type' column based on the 'dps' column
df['type'] = df['dps'].apply(lambda x: 'switch' if '1' in x['dps'] else ('light' if '20' in x['dps'] else 'Unknown'))

switches = df[df['type'] == 'switch'].reset_index(drop=True) 
lights = df[df['type'] == 'light'].reset_index(drop=True)  


for table in [switches, lights]:
    for idx, row in table.iterrows():
        name = row['name']
        id = row['id']
        id_new = id
        ip = row['ip']
        key = row['key']
        ver = row['version']
        # id_new = id
        address = f"{row['type']}_{idx+1}"
        print('{name}n{id_new}n{ip}n{key}n{ver}n{address}n'.format(
            name=name, id_new=id_new, ip=ip, key=key, ver=ver, address=address,))

    Output:

    Switch Garage Overhead
eb7ac0fbe689ca95f7dxxk
192.168.1.102
449ebc60812eb8bb
3.3
switch_1

LED Office
ebfc16d57ed374932cjqfk
192.168.1.147
805217605357161b
3.3
light_1
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search