How to decode multilevel json into an array with python?

Question

Python

Hello!
I'm trying to put the received data from Bitrix24 into exel, with b24 I get json of the following format:

[('18835', [{'ID': '2903', 'OWNER_ID': '18835', 'OWNER_TYPE': 'D', 'PRODUCT_ID': 3839, 'PRODUCT_NAME': 'Установка ПП 1С', 'ORIGINAL_PRODUCT_NAME': 'Установка ПП 1С', 'PRODUCT_DESCRIPTION': 'Установка ПП 1С', 'PRICE': 1300, 'PRICE_EXCLUSIVE': 1300, 'PRICE_NETTO': 1300, 'PRICE_BRUTTO': 1300, 'PRICE_ACCOUNT': '1300.00', 'QUANTITY': 1, 'DISCOUNT_TYPE_ID': 2, 'DISCOUNT_RATE': 0, 'DISCOUNT_SUM': 0, 'TAX_RATE': 0, 'TAX_INCLUDED': 'N', 'CUSTOMIZED': 'Y', 'MEASURE_CODE': 356, 'MEASURE_NAME': 'ч', 'SORT': 10}]), ('5199', []), ('23291', [{'ID': '7407', 'OWNER_ID': '23291', 'OWNER_TYPE': 'D', 'PRODUCT_ID': 6483, 'PRODUCT_NAME': 'Установка и обновление ПП', 'ORIGINAL_PRODUCT_NAME': 'Установка и обновление ПП', 'PRODUCT_DESCRIPTION': 'Установка и обновление ПП', 'PRICE': 1300, 'PRICE_EXCLUSIVE': 1300, 'PRICE_NETTO': 1300, 'PRICE_BRUTTO': 1300, 'PRICE_ACCOUNT': '1300.00', 'QUANTITY': 0.5, 'DISCOUNT_TYPE_ID': 2, 'DISCOUNT_RATE': 0, 'DISCOUNT_SUM': 0, 'TAX_RATE': 0, 'TAX_INCLUDED': 'N', 'CUSTOMIZED': 'Y', 'MEASURE_CODE': 356, 'MEASURE_NAME': 'ч', 'SORT': 10}]), ('609', [])]

I import into exel using pandas:

json_prod = json.dumps(prod)
prod = pd.read_json(StringIO(json_cprod))
with pd.ExcelWriter('B24.xlsx',
                    mode='a') as writer:  
    prod.to_excel(writer, sheet_name='Товары')

as a result I get:



The zero column is not even needed, according to the idea. id is inside, I tried everything I thought of, but nothing worked because I have been doing python for a couple of months, I will be very grateful for any help!

Answer 1

It looks like a list of strings grouped by some extras. id. You can do this (we assume that the internal list of strings is always one element for each id, as in the example, if not, change it for yourself):

import pandas as pd

data = [('18835', [{'ID': '2903', 'OWNER_ID': '18835', 'OWNER_TYPE': 'D', 'PRODUCT_ID': 3839, 'PRODUCT_NAME': 'Установка ПП 1С', 'ORIGINAL_PRODUCT_NAME': 'Установка ПП 1С', 'PRODUCT_DESCRIPTION': 'Установка ПП 1С', 'PRICE': 1300, 'PRICE_EXCLUSIVE': 1300, 'PRICE_NETTO': 1300, 'PRICE_BRUTTO': 1300, 'PRICE_ACCOUNT': '1300.00', 'QUANTITY': 1, 'DISCOUNT_TYPE_ID': 2, 'DISCOUNT_RATE': 0, 'DISCOUNT_SUM': 0, 'TAX_RATE': 0, 'TAX_INCLUDED': 'N', 'CUSTOMIZED': 'Y', 'MEASURE_CODE': 356, 'MEASURE_NAME': 'ч', 'SORT': 10}]), ('5199', []), ('23291', [{'ID': '7407', 'OWNER_ID': '23291', 'OWNER_TYPE': 'D', 'PRODUCT_ID': 6483, 'PRODUCT_NAME': 'Установка и обновление ПП', 'ORIGINAL_PRODUCT_NAME': 'Установка и обновление ПП', 'PRODUCT_DESCRIPTION': 'Установка и обновление ПП', 'PRICE': 1300, 'PRICE_EXCLUSIVE': 1300, 'PRICE_NETTO': 1300, 'PRICE_BRUTTO': 1300, 'PRICE_ACCOUNT': '1300.00', 'QUANTITY': 0.5, 'DISCOUNT_TYPE_ID': 2, 'DISCOUNT_RATE': 0, 'DISCOUNT_SUM': 0, 'TAX_RATE': 0, 'TAX_INCLUDED': 'N', 'CUSTOMIZED': 'Y', 'MEASURE_CODE': 356, 'MEASURE_NAME': 'ч', 'SORT': 10}]), ('609', [])]

# формируем список словарей со значениями по колонкам
df_data = [row[1][0] for row in data if row[1]]

prod = pd.DataFrame(df_data)

with pd.ExcelWriter('B24.xlsx',
                    mode='w') as writer:
    prod.to_excel(writer, sheet_name='Товары')