Micromaster Website Discounts Results Parser

This repository contains a Python script designed to efficiently extract discount data from JSON files generated by the Micromaster website. The script organizes these JSON results into a neatly formatted Excel spreadsheet.

Features

• JSON Parsing: This Python script is adept at interpreting and reading JSON files containing installment data.
• Data Manipulation: Capabilities include renaming columns, deleting specified columns, translating specific cell values, and more.
• Excel Exportation: The processed data is exported into an Excel file, with cell formatting applied for clearer representation.
• Automation: The entire process, from reading JSON to producing a formatted Excel file, is automated, ensuring accuracy and efficiency.

Code Walkthrough

Here is a section-by-section walkthrough of the script:

1. Importing Necessary Libraries: The script starts by importing essential libraries such as pandas for data handling, json for reading JSON files, and openpyxl for managing Excel-specific features.

import pandas as pd
import json
import openpyxl
from openpyxl.styles import Alignment, Font, PatternFill
from openpyxl.utils import get_column_letter

2. Loading and parsing JSON file: The script reads the JSON file into a Python dictionary and then normalizes it into a DataFrame.

with open('Installments.json', 'r', encoding='utf-8') as file:
    data = json.load(file)

df_main = pd.json_normalize(data)

3. Data Processing: Based on the requirements, various operations like renaming columns, deleting unwanted columns, and transforming specific cell values are performed.

column_renaming = {...}
df_main.rename(columns=column_renaming, inplace=True)

columns_to_drop = [...]
df_main = df_main.drop(columns=[col for col in columns_to_drop if col in df_main.columns])

messenger_mapping = {...}
df_main['Internal Messenger Type'] = df_main['Internal Messenger Type'].replace(messenger_mapping)

4. Data Export and Excel Formatting: The processed DataFrame is then exported to an Excel file. Post-export, cell formatting, including font style, cell color, and cell alignment, is applied to the resulting Excel sheet.

output_file = 'output.xlsx'
with pd.ExcelWriter(output_file, engine='openpyxl') as writer:
    df_main.to_excel(writer, sheet_name='Main', index=False)

wb = openpyxl.load_workbook(output_file)
...
wb.save(output_file)

Usage

To run the script, make sure the JSON file is located in the same directory as the script, then simply execute the script with a Python interpreter. You'll need to have json, pandas, and openpyxl installed in your Python environment.

The script is designed to parse JSON files with a specific structure, so ensure your files follow the expected format. Here's an example of how the JSON file should be structured:

[
  {
    "id": "integer",
    "user_id": "integer",
    "amount": "integer",
    "sharif_order_id": "string",
    "reference_id": "string",
    "status": "string",
    "created_at": "string",
    "updated_at": "string",
    "discount_coefficient": "float",
    "sources": [
      {
        "id": "integer",
        "payment_id": "integer",
        "type": "string",
        "course_code": "string",
        "created_at": "string",
        "updated_at": "string",
        "course": {
          "name": "string",
          "code": "string",
          "micromaster_id": "integer",
          "description": "string",
          "fee": "integer",
          "created_at": "string",
          "updated_at": "string"
        }
      }
    ],
    "user": {
      "id": "integer",
      "gender": "string",
      "name": "string",
      "surname": "string",
      "photo": "null/string",
      "bio": "null/string",
      "email": "string",
      "mobile_number": "string",
      "national_code": "string",
      "phone_number": "string",
      "created_at": "string",
      "updated_at": "string",
      "extra": "null/object"
    }
  }
]

If your JSON file is structured differently, you must modify the script accordingly.

Maintainer

• Iman Mohammadi