I have a dictionary that looks like this:

{
    "Title": "Import/Export",
    "Values": [{
            "Region": "East",
            "Currency": "USD",
            "Amount": "32"
        },
        {
            "Region": "West",
            "Currency": "USD",
            "Amount": "325"
        }
    ],
    "Code": "21"
}

I want the resulting dataframe to look like this:

Title             Region     Currency   Amount   Code
Import/Export     East       USD        32       21
Import/Export     West       USD        325      21

How do I go about doing this? I've tried a lot of things like pandas convert to a dataframe and jons_normalize, but can't seem to figure it out.

CodePudding user response：

It seems your input has a problem. But I thought you might have wanted to define it such as what follows:

'Title': 'Import/Export',
'Values': [{"Region":"East","Currency":"USD","Amount":"32"}, {"Region":"West", "Currency":"USD", "Amount":"325"}],
"Code":"21"

If so, you can use code below:

dictionary = {'Title': 'Import/Export','Values': [{"Region":"East","Currency":"USD","Amount":"32"}, {"Region":"West", "Currency":"USD", "Amount":"325"}],"Code":"21"}
df = pd.DataFrame(dicti)
newColumns = {}
for index, row in df.iterrows():
  values = row["Values"]
  for key, value in values.items():
    if key not in newColumns:
      newColumns[key] = []
    newColumns[key].append(value)
for columnName, columnValue in newColumns.items():
  df[columnName] = columnValue
df = df.drop(columns=["Values"])
df

Output

CodePudding user response：

You can use json_normalize like this way.

# data is the param for the dictionary.
df = pd.json_normalize(data, record_path=['Values'], meta=['Title', 'Code'])

CodePudding user response：

Something like this:

df = pd.DataFrame(data["Values"]).assign(
    **{k: v for k, v in data.items() if k != "Values"}
)

Would suffice, or even just:

df = pd.DataFrame(data["Values"]).assign(Title=data["Title"], Code=data["Code"])