Парсинг json файла в excel таблицу python

import ijson
import xlsxwriter
from collections import defaultdict

# Создаю excel файл
workbook = xlsxwriter.Workbook('main.xlsx')
worksheet = workbook.add_worksheet()

# Даю имена двум столбцам
worksheet.write(0, 0, 'ID')
worksheet.write(0, 1, 'title')

# Функция для записи titles, используя рекурсию
def write_titles(id, titles, row):
    if not titles:
        worksheet.write(row, 0, id)
        worksheet.write(row, 1, '')
        return row
    title = titles[0]
    worksheet.write(row, 0, id)
    worksheet.write(row, 1, title)
    row += 1
    return write_titles(id, titles[1:], row)

# Читаю json файл
with open("sanya.json", 'r', encoding="utf8") as f:
    id_to_titles = defaultdict(list)
    for item in ijson.items(f, "item"):
        if not isinstance(item, dict):
            continue
        id = item.get('_id', {}).get('$oid')
        if not id:
            continue
        titles = []
        for method in item.get('methods', []):
            if not isinstance(method, dict):
                continue
            subcategories = method.get('subcategories', [])
            for subcategory in subcategories:
                if not isinstance(subcategory, dict):
                    continue
                title = subcategory.get('title')
                if title:
                    titles.append(title)
        id_to_titles[id].extend(titles)

# Произвожу запись id и titles из json файла в excel, используя функцию write_titles
row = 1
for id, titles in id_to_titles.items():
    row = write_titles(id, titles, row)

# Закрываем excel файл
workbook.close()

Задача решается буквально в несколько строк с pandas.
Получим первичную табличку через pd.json_normalize(), прекрасной функцией для работы c json данными и преобразованием их в табличный формат.

import json
import pandas as pd
with open('sanya.json', 'r') as f:
    data = json.load(f)
df = pd.json_normalize(data)
print(df)

Вывод:

                                             methods                                            name                              recordId                    status  slug                  _id.$oid
0  [{'category': ['Профилактические мероприятия']...  Муниципальный контроль в сфере благоустройства  0af4cd2e-78cb-109b-8178-e98136ad03fc  NOT_MODERATION_PUBLISHED  2311  61c49773d06bd238714e7809

В целом можно сказать, что результат чудесен. Ну правда неплох, обработалось вложение _id ⇛ $oid, всё остальное у нас находится на первом уровне, кроме methods. Там творится полный бардак, очень много списков, вложенных словарей и т.д. Раскрывать это даже в рекурсии мартышкин труд, но в целом можно сделать из этого реляционную БД, желательно не в Excel.

Ладно, если я правильно понял ваш скриншот в вопросе, вы хотите забрать первый уровень, а также title и record_id из subcategories, которому вы почему-то поставили префикс name_.

Будем придерживаться данной концепции, поехали:

1. df.set_index('_id.$oid'):
   Ставим ID на место индекса, по не нему потом будем делать join.

2. df[['methods']].iterrows() цикл:
   Идём по всем строкам по колонке methods, ведь их у вас в json будет больше, и:

   • берём row.methods, колонка из которой надо достать наш бардак, сразу идём в subcategories т.к. искомые колонки находятся там и делаем префикс name_ как на скриншоте. Т.к. колонки recordId повторяются, префикс крайне необходим.

   • ставим индекс из iterrows(), т.е. строчка с которой работаем в цикле

   • делаем .join() по индексу, тут не надо переживать, что мы добавим в искомый df новые строчки и несколько раз по ним пройдёмся, нет, iterrows() создаёт копию в памяти, так что на нём изменения не отразятся
     Тут же меняем titile с перфиксом на обычный.

   • дальше if и то что я изначально забыл, мы обсудили в комментариях. Для первого раза join отработает отлично, для последующих колонки уже будут в df, новые добавятся с суффиксом, чтобы не было конфликта.

     • если лишние есть (второй и последующие разы), собираем всё в одну колонку и убираем созданные лишние

     • else если проходимся первый раз, то уберём из df колонку methods, чтобы немного сэкономить память. Как мы помним она так и так останется в iterrows

3. Делаем фильтр по колонкам, тем самым убираем ненужные и сортируем

4. Пишем в excel.

df = df.set_index('_id.$oid')
for ix, row in df[['methods']].iterrows():
    df_x = pd.json_normalize(row.methods, record_path=['subcategories'], record_prefix='name_')
    df_x.index = [ix] * len(df_x)
    df = df.join(df_x[['name_title', 'name_recordId']].rename(columns={'name_title': 'title'}), rsuffix='_right')
    if 'title_right' in df.columns:
        for i in range(2):
            df.iloc[:,-4+i] = df.iloc[:,-4+i].fillna(df.iloc[:,-2+i])
        df = df.iloc[:,:-2]
    else:
        df = df.drop(columns=['methods'])

df = df[['title', 'recordId', 'name', 'name_recordId', 'status', 'slug']]
df.index.names = ['ID']
df.to_excel("main.xlsx")

Выввод: