How can I convert JSON to CSV

Question

I have a JSON file I want to convert to a CSV file  How can I do this with Python  I tried  import json import csv  f   open  data json   data   json load f  f close    f   open  data csv   csv file   csv writer f  for item in data      csv file writerow item   f close    However  it did not work  I am using Django and the error I received is   file  object has no attribute  writerow    I then tried the following  import json import csv  f   open  data json   data   json load f  f close    f   open  data csv   csv file   csv writer f  for item in data      f writerow item       changed  f close    I then get the error   sequence expected   Sample json file              quot pk quot   22           quot model quot    quot auth permission quot            quot fields quot                  quot codename quot    quot add logentry quot                quot name quot    quot Can add log entry quot                quot content type quot   8                             quot pk quot   23           quot model quot    quot auth permission quot            quot fields quot                  quot codename quot    quot change logentry quot                quot name quot    quot Can change log entry quot                quot content type quot   8                             quot pk quot   24           quot model quot    quot auth permission quot            quot fields quot                  quot codename quot    quot delete logentry quot                quot name quot    quot Can delete log entry quot                quot content type quot   8                             quot pk quot   4           quot model quot    quot auth permission quot            quot fields quot                  quot codename quot    quot add group quot                quot name quot    quot Can add group quot                quot content type quot   2                             quot pk quot   10           quot model quot    quot auth permission quot            quot fields quot                  quot codename quot    quot add message quot                quot name quot    quot Can add message quot                quot content type quot   4

User · Answer

As mentioned in the previous answers the difficulty in converting json to csv is because a json file can contain nested dictionaries and therefore be a multidimensional data structure verses a csv which is a 2D data structure. However, a good way to turn a multidimensional structure to a csv is to have multiple csvs that tie together with primary keys.

In your example, the first csv output has the columns "pk","model","fields" as your columns. Values for "pk", and "model" are easy to get but because the "fields" column contains a dictionary, it should be its own csv and because "codename" appears to the be the primary key, you can use as the input for "fields" to complete the first csv. The second csv contains the dictionary from the "fields" column with codename as the the primary key that can be used to tie the 2 csvs together.

Here is a solution for your json file which converts a nested dictionaries to 2 csvs.

import csv
import json

def readAndWrite(inputFileName, primaryKey=""):
    input = open(inputFileName+".json")
    data = json.load(input)
    input.close()

    header = set()

    if primaryKey != "":
        outputFileName = inputFileName+"-"+primaryKey
        if inputFileName == "data":
            for i in data:
                for j in i["fields"].keys():
                    if j not in header:
                        header.add(j)
    else:
        outputFileName = inputFileName
        for i in data:
            for j in i.keys():
                if j not in header:
                    header.add(j)

    with open(outputFileName+".csv", 'wb') as output_file:
        fieldnames = list(header)
        writer = csv.DictWriter(output_file, fieldnames, delimiter=',', quotechar='"')
        writer.writeheader()
        for x in data:
            row_value = {}
            if primaryKey == "":
                for y in x.keys():
                    yValue = x.get(y)
                    if type(yValue) == int or type(yValue) == bool or type(yValue) == float or type(yValue) == list:
                        row_value[y] = str(yValue).encode('utf8')
                    elif type(yValue) != dict:
                        row_value[y] = yValue.encode('utf8')
                    else:
                        if inputFileName == "data":
                            row_value[y] = yValue["codename"].encode('utf8')
                            readAndWrite(inputFileName, primaryKey="codename")
                writer.writerow(row_value)
            elif primaryKey == "codename":
                for y in x["fields"].keys():
                    yValue = x["fields"].get(y)
                    if type(yValue) == int or type(yValue) == bool or type(yValue) == float or type(yValue) == list:
                        row_value[y] = str(yValue).encode('utf8')
                    elif type(yValue) != dict:
                        row_value[y] = yValue.encode('utf8')
                writer.writerow(row_value)

readAndWrite("data")

User · Answer

If we consider the below example for converting the json format file to csv formatted file       item data                       item    10023456            class    100            subclass    123                      The below code will convert the json file   data3 json   to csv file   data3 csv     import json import csv with open   Users Desktop json data3 json   as file      data   json load file      file close       print data   fname     Users Desktop json data3 csv   with open fname   w   newline     as file      csv file   csv writer file      csv file writerow   dept                           class                           subclass        for item in data  item data             csv file writerow  item get  item data   get  dept                                item get  item data   get  class                                item get  item data   get  subclass       The above mentioned code has been executed in the locally installed pycharm and it has successfully converted the json file to the csv file  Hope this help to convert the files

User · Answer

You can use this code to convert a json file to csv file After reading the file  I am converting the object to pandas dataframe and then saving this to a CSV file  import os import pandas as pd import json import numpy as np  data      os chdir  D   Your directory  folder   with open  file name json   encoding  utf8   as data file           for line in data file          data append json loads line    dataframe   pd DataFrame data             Saving the dataframe to a csv file dataframe to csv  filename csv   encoding  utf-8  index  False

User · Answer

Alec s answer is great  but it doesn t work in the case where there are multiple levels of nesting  Here s a modified version that supports multiple levels of nesting  It also makes the header names a bit nicer if the nested object already specifies its own key  e g  Firebase Analytics   BigTable   BigQuery data    quot  quot  quot Converts JSON with nested fields into a flattened CSV file   quot  quot  quot   import sys import json import csv import os  import jsonlines  from orderedset import OrderedSet    from https   stackoverflow com a 28246154 473201 def flattenjson  b  prefix     delim      val None      if val is None      val         if isinstance  b  dict        for j in b keys          flattenjson b j   prefix   delim   j  delim  val    elif isinstance  b  list        get   b     for j in range len get          key   str j           If the nested data contains its own key  use that as the header instead        if isinstance  get j   dict            if  key  in get j             key   get j   key          flattenjson get j   prefix   delim   key  delim  val    else      val prefix    b    return val  def main argv     if len argv   lt  2      raise Error  Please specify a JSON file to parse      print  quot Loading and Flattening    quot    filename   argv 1    allRows        fieldnames   OrderedSet     with jsonlines open filename  as reader      for obj in reader          print  orig  n          print obj       flattened   flattenjson obj         print  keys   s    flattened keys           print  flattened  n          print flattened       fieldnames update flattened keys          allRows append flattened     print  quot Exporting to CSV    quot    outfilename   filename     csv    count   0   with open outfilename   w   as file      csvwriter   csv DictWriter file  fieldnames fieldnames      csvwriter writeheader       for obj in allRows          print  allRows  n          print obj       csvwriter writerow obj        count    1    print  quot Wrote  d rows quot    count    if   name         main       main sys argv

User · Answer

It is not a very smart way to do it  but I have had the same problem and this worked for me   import csv  f   open  data json   data   json load f  f close    new data       for i in data     flat         names   i keys      for n in names        try           if len i n  keys     gt  0              for ii in i n  keys                   flat n     ii    i n  ii        except           flat n    i n     new data append flat     f   open filename   r   writer   csv DictWriter f  new data 0  keys    writer writeheader   for row in new data     writer writerow row  f close

User · Answer

Use json normalize from pandas   Given the sample data from the OP  in a file named test json  encoding  utf-8  may not be necessary  The following code takes advantage of the pathlib library    open is a method of pathlib  Works with non-Windows paths too    Use pandas to csv      to save the data to a csv file   import pandas as pd   As of Pandas 1 01  json normalize as pandas io json json normalize is deprecated and is now exposed in the top-level namespace    from pandas io json import json normalize from pathlib import Path import json    set path to file p   Path r c  some path to file test json      read json with p open  r   encoding  utf-8   as f      data   json loads f read       create dataframe df   pd json normalize data     dataframe view  pk            model  fields codename           fields name  fields content type  22  auth permission     add logentry     Can add log entry                    8  23  auth permission  change logentry  Can change log entry                    8  24  auth permission  delete logentry  Can delete log entry                    8   4  auth permission        add group         Can add group                    2  10  auth permission      add message       Can add message                    4    save to csv df to csv  test csv   index False  encoding  utf-8    CSV Output  pk model fields codename fields name fields content type 22 auth permission add logentry Can add log entry 8 23 auth permission change logentry Can change log entry 8 24 auth permission delete logentry Can delete log entry 8 4 auth permission add group Can add group 2 10 auth permission add message Can add message 4  Other Resources for more heavily nested JSON objects   SO Answers   Flatten a JSON array with python How to flatten nested JSON recursively  with flatten json  How to json normalize a column with NaNs Splitting dictionary list inside a Pandas Column into Separate Columns

User · Answer

A generic solution which translates any json list of flat objects to csv   Pass the input json file as first argument on command line   import csv  json  sys  input   open sys argv 1   data   json load input  input close    output   csv writer sys stdout   output writerow data 0  keys       header row  for row in data      output writerow row values

User · Answer

It ll be easy to use csv DictWriter   the detailed implementation can be like this   def read json filename       return json loads open filename  read    def write csv data filename       with open filename   w    as outf          writer   csv DictWriter outf  data 0  keys            writer writeheader           for row in data              writer writerow row    implement write csv read json  test json     output csv     Note that this assumes that all of your JSON objects have the same fields    Here is the reference which may help you

User · Answer

Surprisingly  I found that none of the answers posted here so far correctly deal with all possible scenarios  e g   nested dicts  nested lists  None values  etc    This solution should work across all scenarios   def flatten json json       def process value keys  value  flattened           if isinstance value  dict               for key in value keys                    process value keys    key   value key   flattened          elif isinstance value  list               for idx  v in enumerate value                   process value keys    str idx    v  flattened          else              flattened      join keys     value      flattened          for key in json keys            process value  key   json key   flattened      return flattened

User · Answer

Try this  import csv  json  sys  input   open sys argv 1   data   json load input  input close    output   csv writer sys stdout   output writerow data 0  keys       header row  for item in data      output writerow item values

User · Answer

This code should work for you  assuming that your JSON data is in a file called data json   import json import csv  with open  data json   as file      data   json load file   with open  data csv    w   as file      csv file   csv writer file      for item in data          fields   list item  fields   values            csv file writerow  item  pk    item  model      fields

User · Answer

This code works for any given json file    - - coding  utf-8 - -     Created on Mon Jun 17 20 35 35 2019 author  Ram      import json import csv  with open  file1 json   as file      data   json load file       create the csv writer object pt data1   open  pt data1 csv    w   csvwriter   csv writer pt data1   count   0  for pt in data         if count    0                header   pt keys                 csvwriter writerow header                count    1        csvwriter writerow pt values     pt data1 close

User · Answer

Since the data appears to be in a dictionary format  it would appear that you should actually use csv DictWriter   to actually output the lines with the appropriate header information  This should allow the conversion to be handled somewhat easier  The fieldnames parameter would then set up the order properly while the output of the first line as the headers would allow it to be read and processed later by csv DictReader     For example  Mike Repass used  output   csv writer sys stdout   output writerow data 0  keys       header row  for row in data    output writerow row values      However just change the initial setup to     output   csv DictWriter filesetting  fieldnames data 0  keys     Note that since the order of elements in a dictionary is not defined  you might have to create fieldnames entries explicitly  Once you do that  the writerow will work  The writes then work as originally shown

User · Answer

My simple way to solve this   Create a new Python file like  json to csv py  Add this code    import csv  json  sys  if you are not using utf-8 files  remove the next line sys setdefaultencoding  UTF-8    check if you pass the input file and output file if sys argv 1  is not None and sys argv 2  is not None       fileInput   sys argv 1      fileOutput   sys argv 2       inputFile   open fileInput      outputFile   open fileOutput   w       data   json load inputFile      inputFile close        output   csv writer outputFile       output writerow data 0  keys       header row      for row in data          output writerow row values      After add this code  save the file and run at the terminal      python json to csv py input txt output csv   I hope this help you   SEEYA

User · Answer

Modified Alec McGail s answer to support JSON with lists inside      def flattenjson self  mp  delim                   ret                  if isinstance mp  dict                       for k in mp keys                                csvs   self flattenjson mp k   delim                              for csv in csvs                                      ret append k   delim   csv              elif isinstance mp  list                       for k in mp                              csvs   self flattenjson k  delim                              for csv in csvs                                      ret append csv              else                      ret append mp               return ret   Thanks

User · Answer

import json csv t    t  type  a    json data      data   None write header   True item keys      try  with open  kk json   as json file      json data   json file read        data   json loads json data  except Exception as e      print  e   with open  bar csv    at   as csv file      writer   csv writer csv file    quoting csv QUOTE MINIMAL      for item in data          item values              for key in item              if write header                  item keys append key              value   item get key                  if  type value   t                   item values append value encode  utf-8                else                  item values append value          if write header              writer writerow item keys              write header   False         writer writerow item values

User · Answer

JSON can represent a wide variety of data structures -- a JS  object  is roughly like a Python dict  with string keys   a JS  array  roughly like a Python list  and you can nest them as long as the final  leaf  elements are numbers or strings   CSV can essentially represent only a 2-D table -- optionally with a first row of  headers   i e    column names   which can make the table interpretable as a list of dicts  instead of the normal interpretation  a list of lists  again   leaf  elements can be numbers or strings    So  in the general case  you can t translate an arbitrary JSON structure to a CSV   In a few special cases you can  array of arrays with no further nesting  arrays of objects which all have exactly the same keys    Which special case  if any  applies to your problem   The details of the solution depend on which special case you do have   Given the astonishing fact that you don t even mention which one applies  I suspect you may not have considered the constraint  neither usable case in fact applies  and your problem is impossible to solve  But please do clarify

User · Answer

I know it has been a long time since this question has been asked but I thought I might add to everyone else s answer and share a blog post that I think explain the solution in a very concise way   Here is the link  Open a file for writing  employ data   open   tmp EmployData csv    w     Create the csv writer object  csvwriter   csv writer employ data  count   0 for emp in emp data        if count    0               header   emp keys                csvwriter writerow header               count    1       csvwriter writerow emp values      Make sure to close the file in order to save the contents  employ data close

User · Answer

I am assuming that your JSON file will decode into a list of dictionaries  First we need a function which will flatten the JSON objects  def flattenjson b  delim       val          for i in b keys            if isinstance b i   dict               get   flattenjson b i   delim              for j in get keys                    val i   delim   j    get j          else              val i    b i                   return val  The result of running this snippet on your JSON object  flattenjson        quot pk quot   22        quot model quot    quot auth permission quot         quot fields quot            quot codename quot    quot add message quot           quot name quot    quot Can add message quot           quot content type quot   8             quot    quot    is        quot pk quot   22        quot model quot    quot auth permission quot         quot fields  codename quot    quot add message quot         quot fields  name quot    quot Can add message quot         quot fields  content type quot   8    After applying this function to each dict in the input array of JSON objects  input   map lambda x  flattenjson  x   quot    quot     input   and finding the relevant column names  columns    x for row in input for x in row keys    columns   list set columns    it s not hard to run this through the csv module  with open fname   wb   as out file      csv w   csv writer out file      csv w writerow columns       for i r in input          csv w writerow map lambda x  i r get x   quot  quot    columns    I hope this helps

User · Answer

First  your JSON has nested objects  so it normally cannot be directly converted to CSV  You need to change that to something like this          pk   22       model    auth permission        codename    add logentry        content type   8       name    Can add log entry               Here is my code to generate CSV from that   import csv import json  x                       pk   22           model    auth permission            fields                  codename    add logentry                name    Can add log entry                content type   8                                 pk   23           model    auth permission            fields                  codename    change logentry                name    Can change log entry                content type   8                                 pk   24           model    auth permission            fields                  codename    delete logentry                name    Can delete log entry                content type   8                       x   json loads x   f   csv writer open  test csv    wb        Write CSV Header  If you dont need that  remove this line f writerow   pk    model    codename    name    content type     for x in x      f writerow  x  pk                    x  model                    x  fields    codename                    x  fields    name                    x  fields    content type       You will get output as   pk model codename name content type 22 auth permission add logentry Can add log entry 8 23 auth permission change logentry Can change log entry 8 24 auth permission delete logentry Can delete log entry 8

User · Answer

I was having trouble with Dan s proposed solution  but this worked for me   import json import csv   f   open  test json   data   json load f  f close    f csv writer open  test csv   wb      for item in data    f writerow  item  pk    item  model      item  fields   values      Where  test json  contained the following        pk   22   model    auth permission    fields        codename    add logentry    name    Can add log entry    content type   8         pk   23   model    auth permission    fields        codename    change logentry    name    Can change log entry    content type   8        pk   24   model    auth permission    fields        codename    delete logentry    name    Can delete log entry    content type   8

User · Answer

I might be late to the party  but I think  I have dealt with the similar problem  I had a json file which looked like this    I only wanted to extract few keys values from these json file  So  I wrote the following code to extract the same          json to csv py     This script reads n numbers of json files present in a folder and then extract certain data from each file and write in a csv file      The folder contains the python script i e  json to csv py  output csv and another folder descriptions containing all the json files       import os import json import csv   def get list of json files           Returns the list of filenames of all the Json files present in the folder     Parameter     ---------     directory   str          descriptions  in this case     Returns     -------     list of files  list         List of the filenames of all the json files              list of files   os listdir  descriptions      creates list of all the files in the folder      return list of files   def create list from json jsonfile          Returns a list of the extracted items from json file in the same order we need it      Parameter                   jsonfile   json         The json file containing the data     Returns     -------     one sample list   list         The list of the extracted items needed for the final csv              with open jsonfile  as f          data   json load f       data list         create an empty list        append the items to the list in the same order      data list append data   id        data list append data   modelType        data list append data  creator     id        data list append data  creator    name        data list append data  dataset     accessLevel        data list append data  dataset     id        data list append data  dataset    description        data list append data  dataset    name        data list append data  meta    acquisition    image type        data list append data  meta    acquisition    pixelsX        data list append data  meta    acquisition    pixelsY        data list append data  meta    clinical    age approx        data list append data  meta    clinical    benign malignant        data list append data  meta    clinical    diagnosis        data list append data  meta    clinical    diagnosis confirm type        data list append data  meta    clinical    melanocytic        data list append data  meta    clinical    sex        data list append data  meta    unstructured    diagnosis          In few json files  the race was not there so using KeyError exception to add    at the place     try          data list append data  meta    unstructured    race        except KeyError          data list append        will add an empty string in case race is not there      data list append data  name         return data list   def write csv           Creates the desired csv file     Parameters                    list of files   file         The list created by get list of json files   method     result csv   csv         The csv file containing the header only     Returns                 result csv   csv         The desired csv file              list of files   get list of json files       for file in list of files          row   create list from json f descriptions  file       create the row to be added to csv for each file  json-file          with open  output csv    a   as c              writer   csv writer c              writer writerow row          c close     if   name         main         write csv     I hope this will help  For details on how this code work you can check here

User · Answer

With the pandas library  this is as easy as using two commands   pandas read json     To convert a JSON string to a  pandas object  either a series or dataframe   Then  assuming the results were stored as df   df to csv     Which can either return a string or write directly to a csv-file   Based on the verbosity of previous answers  we should all thank pandas for the shortcut

User · Answer

Unfortunately I have not enouthg reputation to make a small contribution to the amazing  Alec McGail answer  I was using Python3 and I have needed to convert the map to a list following the   Alexis R comment    Additionaly I have found the csv writer was adding a extra CR to the file  I have a empty line for each line with data inside the csv file   The solution was very easy following the  Jason R  Coombs answer to this thread  CSV in Python adding an extra carriage return  You need to simply add the lineterminator   n  parameter to the csv writer  It will be  csv w   csv writer  out file  lineterminator   n

User · Answer

This is a modification of  MikeRepass s answer   This version writes the CSV to a file  and works for both Python 2 and Python 3   import csv json input file  data json  output file  data csv  with open input file  as f      content json load f  try      context open output file  w  newline       Python 3 except TypeError      context open output file  wb     Python 2 with context as file      writer csv writer file      writer writerow content 0  keys      header row     for row in content          writer writerow row values

User · Answer

This works relatively well  It flattens the json to write it to a csv file  Nested elements are managed     That s for python 3  import json  o   json loads  your json string     Be careful  o must be a list  each of its objects will make a line of the csv   def flatten o  k           global l  c line     if isinstance o  dict           for key  value in o items                flatten value  k         key      elif isinstance o  list           for ov in o              flatten ov          elif isinstance o  str           o   o replace   r       replace   n       replace                   if not k in l              l k             l k  c line  o  def render csv l       ftime   True      for i in range 100    len l list l keys    0            for k in l              if ftime                   print   s     k  end                     continue             v   l k              try                  print   s     v i   end                 except                  print      end             print           ftime   False         i   0  def json to csv object list       global l  c line     l          c line   0     for ov in object list     Assumes json is a list of objects         flatten ov          c line    1     render csv l   json to csv o    enjoy

[python] How can I convert JSON to CSV?

Examples related to python

Examples related to json

Examples related to csv