Question 109278 in Json Question posted in
Our archive of expertly curated questions and answers provides insights and solutions to common problems related to this popular data interchange format. From parsing and manipulating JSON data to integrating it with various programming languages and web services, our archive has got you covered. Start exploring today and take your JSON skills to the next level

convert .json file to .csv

I have the following .json file:

data:   
    0:  
        area_code   "1A40"
        blacklist   0
        cost_center "Payments "
        country "Colombia"
        created_date    "Thu, 03 Jul 2014 12:57:02 GMT"
        .
        .
        .
    1:
        area_code   "1A42"
        blacklist   0
        cost_center "Payments "
        country "Colombia"
        created_date    "Thu, 03 Jul 2014 12:57:02 GMT"
        .
        .
        .
    2:{...}
    .
    .
    .
    1000:{...}

next url: "eyJzdGFydElu...="

I am using the following code to convert my .json file to csv :

import pandas as pd
df = pd.read_json('Data.json')['data']
df.to_csv('file.csv')

when I open the file from google sheet it brings me this way:

data
0 {‘area_code’: ‘1A40’, ‘blacklist’: 0, ‘cost_center’: ‘Payments’, ‘country’: ‘Colombia’, ‘created_date’: ‘Thu, 03 Jul 2014 12:57:02 GMT’, ‘deleted_date’: None, ‘department’: ‘Core Enablers’, ‘display_name’: ‘Oscar’, ‘division’: ‘IT’, …’}
1 {‘area_code’: ‘1A41’, ‘blacklist’: 0, ‘cost_center’: ‘Payments’, ‘country’: ‘Colombia’, ‘created_date’: ‘Thu, 03 Jul 2014 12:57:02 GMT’, ‘deleted_date’: None, ‘department’: ‘Core Enablers’, ‘display_name’: ‘Oscar’, ‘division’: ‘IT’, …’}
… {…}
999 {…}

What I’m needing is for the information to be listed in the sheet without repeating the headers for each cell

area_code blacklist cost_center country created_date …
0 1A40 0 Payments Colombia Thu, 03 Jul 2014 12:57:02 GMT …
1 1A42 0 Payments Colombia Thu, 03 Jul 2014 12:57:02 GMT …
… … …. … … …. …
999 … …. … … … …

Maybe you could tell me how I can list it like this?

Tags:

2

Answers


  1. 0

    You can try:

    import json
import pandas as pd

with open('data.json', 'r') as f_in:
    df = pd.DataFrame(json.load(f_in)['data'])

print(df)

#to save to CSV:
#df.to_csv('file.csv')

    Prints:

      area_code  blacklist cost_center   country                   created_date
0      1A40          0   Payments   Colombia  Thu, 03 Jul 2014 12:57:02 GMT
1      1A42          0   Payments   Colombia  Thu, 03 Jul 2014 12:57:02 GMT

    Contents of data.json file:

    {
    "data": [
        {
            "area_code": "1A40",
            "blacklist": 0,
            "cost_center": "Payments ",
            "country": "Colombia",
            "created_date": "Thu, 03 Jul 2014 12:57:02 GMT"
        },
        {
            "area_code": "1A42",
            "blacklist": 0,
            "cost_center": "Payments ",
            "country": "Colombia",
            "created_date": "Thu, 03 Jul 2014 12:57:02 GMT"
        }
    ]
}
    Login or Signup to reply.
  2. 0

    You have to expand the dicts in the list. you can do this:

    import pandas as pd
df = pd.read_json('Data.json')['data'].apply(pd.Series)
df.to_csv('file.csv')
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search