How to concatenate multiple json as dict in pandas?

Question

I have two json files that I would like to concatenate into one. Is there any approach to combine these json?

json1 = {
  "105912": {
    "name": "Avatar - Tocasia, Dig Site Mentor",
    "cardset": "VAN",
    "rarity": "Rare",
    "foil": 0,
    "price": 0.05
  },
  "105911": {
    "name": "Avatar - Yotian Frontliner",
    "cardset": "VAN",
    "rarity": "Rare",
    "foil": 0,
    "price": 0.05
  }
}

json2 = {
  "105912": {
    "name": "Avatar - Tocasia, Dig Site Mentor",
    "cardset": "VAN",
    "rarity": "Rare",
    "foil": 0,
    "price": 0.0007
  },
  "105911": {
    "name": "Avatar - Yotian Frontliner",
    "cardset": "VAN",
    "rarity": "Rare",
    "foil": 0,
    "price": 0.0007
  }
}

import pandas as pd
from glob import glob


arquivos = sorted(glob('price-history\*.json'))


todos_dados = pd.concat((pd.read_json(cont, lines=True, orient='records') for cont in 
arquivos))


print(todos_dados)

the error that is returning is ValueError: Expected object or value

The expected output would be a dataframe to be able to filter data.

Does this answer your question? [How to merge two json string in Python?](https://stackoverflow.com/questions/22698244/how-to-merge-two-json-string-in-python) — FAB, Nov 27 '22 at 14:46
Can you please update your question and put there expected output? — Andrej Kesely, Nov 27 '22 at 14:49
@FAB Would it be possible to have a dataframe output with this solution? — Fabrício Gama, Nov 27 '22 at 15:00
@AndrejKesely The expected output would be a dataframe to be able to filter data. — Fabrício Gama, Nov 27 '22 at 15:01

score 2 · Accepted Answer · answered Nov 27 '22 at 15:12

Try:

import pandas as pd

json1 = {
    "105912": {
        "name": "Avatar - Tocasia, Dig Site Mentor",
        "cardset": "VAN",
        "rarity": "Rare",
        "foil": 0,
        "price": 0.05,
    },
    "105911": {
        "name": "Avatar - Yotian Frontliner",
        "cardset": "VAN",
        "rarity": "Rare",
        "foil": 0,
        "price": 0.05,
    },
}

json2 = {
    "105912": {
        "name": "Avatar - Tocasia, Dig Site Mentor",
        "cardset": "VAN",
        "rarity": "Rare",
        "foil": 0,
        "price": 0.0007,
    },
    "105911": {
        "name": "Avatar - Yotian Frontliner",
        "cardset": "VAN",
        "rarity": "Rare",
        "foil": 0,
        "price": 0.0007,
    },
}

jsons = json1, json2

df = pd.DataFrame(
    [v for j in jsons for v in j.values()], index=[k for j in jsons for k in j]
)
print(df)

Prints:

                                     name cardset rarity  foil   price
105912  Avatar - Tocasia, Dig Site Mentor     VAN   Rare     0  0.0500
105911         Avatar - Yotian Frontliner     VAN   Rare     0  0.0500
105912  Avatar - Tocasia, Dig Site Mentor     VAN   Rare     0  0.0007
105911         Avatar - Yotian Frontliner     VAN   Rare     0  0.0007

score 0 · Answer 2 · answered Nov 27 '22 at 15:25

import pandas as pd

json1 = {
  "105912": {
    "name": "Avatar - Tocasia, Dig Site Mentor",
    "cardset": "VAN",
    "rarity": "Rare",
    "foil": 0,
    "price": 0.05
  },
  "105911": {
    "name": "Avatar - Yotian Frontliner",
    "cardset": "VAN",
    "rarity": "Rare",
    "foil": 0,
    "price": 0.05
  }
}

json2 = {
  "105912": {
    "name": "Avatar - Tocasia, Dig Site Mentor",
    "cardset": "VAN",
    "rarity": "Rare",
    "foil": 0,
    "price": 0.0007
  },
  "105911": {
    "name": "Avatar - Yotian Frontliner",
    "cardset": "VAN",
    "rarity": "Rare",
    "foil": 0,
    "price": 0.0007
  }
}

list_jsons = [json1,json2]
todos_dados = pd.concat(([pd.DataFrame.from_dict(json_obj,orient='index') for json_obj in list_jsons]))

todos_dados

How to concatenate multiple json as dict in pandas?

2 Answers2