How to convert json to csv python with proper headers

Question

Trying to get Json data to csv i am getting the values but one block is showing as one line in result, new to python so any help appriciated. Have tried the below code to do the same.

import pandas as pd

with open(r'C:\Users\anath\hard.json', encoding='utf-8') as inputfile:
    df = pd.read_json(inputfile)

    df.to_csv(r'C:\Users\anath\csvfile.csv', encoding='utf-8', index=True)

Sample Json in the source file, short snippet

{
  "issues": [
    {
      "issueId": 110052,
      "revision": 84,
      "definitionId": "DNS1012",
      "subject": "urn:h:domain:fitestdea.com",
      "subjectDomain": "fitestdea.com",
      "title": "Nameserver name doesn\u0027t resolve to an IPv6 address",
      "category": "DNS",
      "severity": "low",
      "cause": "urn:h:domain:ns1.gname.net",
      "causeDomain": "ns1.gname.net",
      "open": true,
      "status": "active",
      "auto": true,
      "autoOpen": true,
      "createdOn": "2022-09-01T02:29:09.681451Z",
      "lastUpdated": "2022-11-23T02:26:28.785601Z",
      "lastChecked": "2022-11-23T02:26:28.785601Z",
      "lastConfirmed": "2022-11-23T02:26:28.785601Z",
      "details": "{}"
    },
    {
      "issueId": 77881,
      "revision": 106,
      "definitionId": "DNS2001",
      "subject": "urn:h:domain:origin-mx.stagetest.test.com.test.com",
      "subjectDomain": "origin-mx.stagetest.test.com.test.com",
      "title": "Dangling domain alias (CNAME)",
      "category": "DNS",
      "severity": "high",
      "cause": "urn:h:domain:origin-www.stagetest.test.com.test.com",
      "causeDomain": "origin-www.stagetest.test.com.test.com",
      "open": true,
      "status": "active",
      "auto": true,
      "autoOpen": true,
      "createdOn": "2022-08-10T09:34:36.929071Z",
      "lastUpdated": "2022-11-23T09:33:32.553663Z",
      "lastChecked": "2022-11-23T09:33:32.553663Z",
      "lastConfirmed": "2022-11-23T09:33:32.553663Z",
      "details": "{\"@type\": \"hardenize/com.hardenize.schemas.dns.DanglingProblem\", \"rrType\": \"CNAME\", \"rrDomain\": \"origin-mx.stagetest.test.com.test.com\", \"causeDomain\": \"origin-www.stagetest.test.com.test.com\", \"danglingType\": \"nxdomain\", \"rrEffectiveDomain\": \"origin-mx.stagetest.test.com.test.com\"}"
    }
    }
  ]
}

Output i am getting is as below was looking a way where could field name in header and values in a column or cell so far getting the entire record in 1 cell. Any way we can just get specific field only like title, severity or issueid not everything but only the feilds i need.

Andrej Kesely · Accepted Answer · 2022-11-23 17:48:17Z

2

Try:

import json
import pandas as pd

with open("your_file.json", "r") as f_in:
    data = json.load(f_in)

df = pd.DataFrame(data["issues"])
print(df[["title", "severity", "issueId"]])

Prints:

                                                title severity  issueId
0  Nameserver name doesn't resolve to an IPv6 address      low   110052
1                       Dangling domain alias (CNAME)     high    77881

To save as CSV you can do:

df[["title", "severity", "issueId"]].to_csv('data.csv', index=False)

edited Nov 23, 2022 at 17:48

answered Nov 23, 2022 at 16:17

Andrej Kesely

196k15 gold badges60 silver badges105 bronze badges

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

2 Comments

user3754136 Over a year ago

df = pd.DataFrame(data["issues"]) NameError: name 'pd' is not defined. Did you mean: 'id'?, getting this error, sorry i am bit new to python

Andrej Kesely Over a year ago

@user3754136 I mean import pandas as pd. I've updated my answer.

user3754136 · Accepted Answer · 2022-11-23 17:11:19Z

0

Finally this worked for me @Andrej Kesely thanks for the inputs. sharing as might help others.

import pandas as pd
import json

with open(r'C:\Users\anath\hard.json', encoding='utf-8') as inputfile:
    data = json.load(inputfile)


df = pd.DataFrame(data["issues"])
print(df[["title", "severity", "issueId"]])
df[["title", "severity", "issueId"]].to_csv('data.csv', index=False)

answered Nov 23, 2022 at 17:11

user3754136

53911 silver badges26 bronze badges

Collectives™ on Stack Overflow

How to convert json to csv python with proper headers

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related