pandas column to list for a json file

Question

from a Dataframe, I want to have a JSON output file with one key having a list:

Expected output:

[
  {
    "model": "xx",
    "id": 1,
    "name": "xyz",
    "categories": [1,2],
  },
  {
    ...
  },
]

What I have:

[
  {
    "model": "xx",
    "id": 1,
    "name": "xyz",
    "categories": "1,2",
  },
  {
    ...
  },
]

The actual code is :

df = pd.read_excel('data_threated.xlsx')
result = df.reset_index(drop=True).to_json("output_json.json", orient='records')
parsed = json.dumps(result)

jsonfile = open("output_json.json", 'r')
data = json.load(jsonfile)

How can I achive this easily?

EDIT:

print(df['categories'].unique().tolist())

['1,2,3', 1, nan, '1,2,3,6', 9, 8, 11, 4, 5, 2, '1,2,3,4,5,6,7,8,9']

Will you please show a sample of your dataframe? :)

user17242583
– user17242583

2022-02-04 15:11:22 +00:00
Commented Feb 4, 2022 at 15:11 — user17242583
– user17242583, Commented Feb 4, 2022 at 15:11

Corralien · Accepted Answer · 2022-02-05 08:59:21Z

1

You can use:

df = pd.read_excel('data_threated.xlsx').reset_index(drop=True)
df['categories'] = df['categories'].apply(lambda x: [int(i) for i in x.split(',')] if isinstance(x, str) else '')
df.to_json('output.json', orient='records', indent=4)

Content of output.json

[
    {
        "model":"xx",
        "id":1,
        "name":"xyz",
        "categories":[
            1,
            2
        ]
    }
]

Note you can also use:

df['categories'] = pd.eval(df['categories'])

edited Feb 5, 2022 at 8:59

answered Feb 4, 2022 at 15:17

Corralien

121k8 gold badges44 silver badges69 bronze badges

Sign up to request clarification or add additional context in comments.

11 Comments

Minseven Over a year ago

I have an error AttributeError: 'int' object has no attribute 'split' Maybe it's due to the inconsistent data in catagories: I may have - "1,2,3" - "1" (this one is treated like an integer) - "" (empty)

Corralien Over a year ago

Ok. I will fixed it

Corralien Over a year ago

Ok you have more 100 records in your dataframe so pd.eval is useless here

Corralien Over a year ago

Can you update your post with the output of df['categories'].unique().tolist() please?

Minseven Over a year ago

Still not working, really struggling here :/ df['categories'] = df['categories'].apply(lambda x: [int(i) for i in x.split(',')] if isinstance(str, x) else '') TypeError: isinstance() arg 2 must be a type, a tuple of types, or a union

|

Collectives™ on Stack Overflow

pandas column to list for a json file

1 Answer 1

11 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

11 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related