How to concatenate multiple json as dict in pandas?-CodePudding

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.

CodePudding user response：

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

CodePudding user response：

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