JSON & Excel Handling¶

1. Working with JSON¶

JSON is the standard format for exchanging data on the web. Python has a built-in json module.

Parsing JSON (String to Dictionary)¶

import json

# some JSON:
x = '{ "name":"John", "age":30, "city":"New York"}'

# parse x:
y = json.loads(x)

# the result is a Python dictionary:
print(y["age"])

Converting to JSON (Dictionary to String)¶

import json

x = {
  "name": "John",
  "age": 30,
  "city": "New York"
}

# convert into JSON string:
y = json.dumps(x)

print(y)

2. Working with Excel¶

Python does not have a built-in library for Excel. We need to install an external library called openpyxl.

Installation¶

pip install openpyxl

Reading Excel¶

import openpyxl

# Load workbook
wb = openpyxl.load_workbook('data.xlsx')
sheet = wb.active

# Get data from cell A1
print(sheet['A1'].value)

# Loop through rows
for row in sheet.iter_rows(values_only=True):
    print(row)

Writing Excel¶

from openpyxl import Workbook

wb = Workbook()
sheet = wb.active

sheet['A1'] = "Name"
sheet['B1'] = "Score"
sheet.append(["Vishnu", 100])

wb.save("new_results.xlsx")

Industry Standard

For heavy Excel work (Data Science), developers almost always use Pandas: pd.read_excel('file.xlsx').

import pandas as pd
df = pd.read_excel('file.xlsx')