How to save nested json data as individual column in CVS file using python 3?

Question

I need to store the data in seperate column in csv file. particularly (Professional experiance)

Column shoud be like this

Date, Education, Email, Id, Job_position, Mobile_number, Name, Working_experiance1, Date1, Experiance1, Working_experiance2, Date2, Experiance2, Skills, Total_Experiance

Input

[{'Date': '12 12 2019',
  'Education': ['BSC'],
  'Email': None,
  'Id': None,
  'Job_position': [],
  'Mobile_number': None,
  'Name': 'Kenny Dosumu',
  'Professional experiance': [{'Date1': ['May 2016', 'Dec 2019'],
                               'Experiance1': 3,
                               'Working_Experiance1':['Project1: Aetna Insurance May 2016 – Present Scrum Master Responsibilities Indian and Philippines]},

                              {'Date2': ['Jan 2013', 'Apr 2016'],
                               'Experiance2': 3,
                               'Working_Experiance2': ['Project2: Children’s Hospital of Philadelphia Jan 2013 – Apr 2016 Responsibilities Keeping the team together all the time to ensure successful sprints. Migrating projects from Waterfall to Scrum is major responsibility.]}],

  'Skills': ['Vision',
             'Matrix',
             'Product owner',
             'Scrum',
             'Documents'],
  'Total_experience': 6}]

Database Output

score 0 · Answer 1 · answered Dec 13 '19 at 11:28

To complete your task, you must load your json file to python. To do so, you can use something like this:

import json

with open('raw_data.json') as json_file:
    raw_data = json.load(json_file)

I simply used a couple of lines of your input in my example by copy-pasting the raw data in the script, but you should import the file. So here we go!

import xlwt

raw_data = [{'Date': '12 12 2019',
              'Education': ['BSC'],
              'Email': None,
              'Id': None,}]

You then need to create a list to store all the info contained in the json file.The first line of this list will contain the title of every column:

excel_data = [['Date','Education','Email','Id']]

Then, for every dictionary stored in your json file, you get the information and store it in the previously created list.

for element in raw_data:
    date = element['Date']
    education = element['Education']
    email = element['Email']
    identification = element['Id']
    excel_data.append([date, education, email, identification])

Finally, you build the Excel file using xlwt and the previously created list.

excel_file = xlwt.Workbook()
sheet = excel_file.add_sheet('Data')

for i, l in enumerate(excel_data):
    for j, col in enumerate(l):
        sheet.write(i, j, col)

excel_file.save('ExcelFile.csv')

I hoped this solved your problem. As you can see, your problem contained a bunch of different questions namely:

1-How to import a json file to python (Import JSON data into Python)

2-How to recover elements from a dictionary (Accessing elements of Python dictionary by index)

3-How to write a Python list to Excel (Writing to an Excel spreadsheet)

Next time, try breaking up your problem into small steps and quickly Google how to accomplish every one of them if you are not sure. This will rapidly allow you to improve your programming skills and to solve the problem at hand.

Happy Holidays!

How to save nested json data as individual column in CVS file using python 3?

1 Answers1