Data Transformation | Python

Question

Need to perform a python data transformation from the left format to the right format for a excel file. This is a very common business problem in the finance world, matching debit vs credits to get even. I guess we might need a for loop, but after googling without success. any suggestions will be highly appreciated. the original data set is in json format below. thanks

Transformation requirement

{
    "from": [
        {
            "scenario": "case 1",
            "amount": "55.65",
            "debit/credit": "debit",
            "uid": "S001"
        },
        {
            "scenario": "case 1",
            "amount": "43.98",
            "debit/credit": "debit",
            "uid": "S002"
        },
        {
            "scenario": "case 1",
            "amount": "21.52",
            "debit/credit": "credit",
            "uid": "S003"
        },
        {
            "scenario": "case 1",
            "amount": "4.5",
            "debit/credit": "credit",
            "uid": "S004"
        },
        {
            "scenario": "case 1",
            "amount": "23.78",
            "debit/credit": "credit",
            "uid": "S005"
        },
        {
            "scenario": "case 1",
            "amount": "0.99",
            "debit/credit": "credit",
            "uid": "S006"
        },
        {
            "scenario": "case 1",
            "amount": "48.84",
            "debit/credit": "credit",
            "uid": "S007"
        },
        {
            "scenario": "case 2",
            "amount": "88.38",
            "debit/credit": "debit",
            "uid": "S008"
        },
        {
            "scenario": "case 2",
            "amount": "9.95",
            "debit/credit": "debit",
            "uid": "S009"
        },
        {
            "scenario": "case 2",
            "amount": "4.23",
            "debit/credit": "credit",
            "uid": "S010"
        },
        {
            "scenario": "case 2",
            "amount": "94.1",
            "debit/credit": "credit",
            "uid": "S011"
        }
    ]
}

Answer 1

You can read in your data as a json file. Then use pandas.read_json method to convert to a pandas data frame. The following will do what you want.

import pandas as pd

data = pd.read_json("./debit_credit.json")

# boolean mask: whether debit or credit
debits = data['debit/credit'] == 'debit'
credits = data['debit/credit'] == 'credit'

# desired output dataframes
debits_df = data.loc[debits]
credits_df = data.loc[credits]

print(debits_df)
print(credits_df)

# whether debits and credits match
is_match = debits_df.amount.sum() == credits_df.amount.sum()

print(f'credit and debit match: {is_match}')