How can I output a Dataframe to a specific json?

August 30, 2023

i am doing a practice to proccess some financial transactions.
Here is what data is Excel. There is 2 transactions.

As you can see one transaction is include many record(one row in one record).
And they have different Trans_num (101,102)

          Date  Tracs num    Disscription      Debit     Credit  Account id
0   2017-01-31        101         Open Up  371275.83        NaN        1213
1   2017-01-31        101         Open Up   38052.22        NaN        1214
2   2017-01-31        101         Open Up   11000.00        NaN        2112
3   2017-01-31        101         Open Up    2000.00        NaN        2113
4   2017-01-31        101         Open Up   28017.00        NaN        2114
5   2017-01-31        101         Open Up  226940.00        NaN        2116
6   2017-01-31        101         Open Up        NaN 4490786.58        2211
7   2017-01-31        101         Open Up        NaN  421161.86        2212
8   2017-01-31        101         Open Up        NaN    3930.00        2213
9   2017-01-31        101         Open Up  249991.15        NaN        2712
10  2017-01-31        101         Open Up 3219067.10        NaN        3611
11  2017-01-31        101         Open Up  134415.00        NaN        4121
12  2017-01-31        101         Open Up    1235.00        NaN        4211
13  2017-01-31        101         Open Up    2776.02        NaN        4221
14  2017-01-31        101         Open Up    3043.47        NaN        4222
15  2017-01-31        102  January Income    3500.00        NaN        1213
16  2017-01-31        102  January Income        NaN   11330.00        2211
17  2017-01-31        102  January Income        NaN    5944.00        2212
18  2017-01-31        102  January Income   11330.00        NaN        6313
19  2017-01-31        102  January Income    1580.00        NaN        6319
20  2017-01-31        102  January Income     840.00        NaN        6322
21  2017-01-31        102  January Income      24.00        NaN        6521

I use Pandas to read it all in Excel.

Here is a question, I need to merge all record in a list, so how can i split them to this fomat? Thanks a lot.

[
Date : "2017-01-31"
Tracs_num : "101"
Disscription : "Open Up"
Data : [371275.83, 0, 1213],[38052.22,0,1214]...
],
[
Date : "2017-01-31"
Tracs_num : "102"
Disscription : "January Income"
Data : [3500, 0, 1213],[0,11300,1214]...
]

>Solution :

Assuming you need a list of dicts, you can use :

meta = ["Date", "Tracs num", "Disscription"]
data = ["Debit", "Credit", "Account id"]

out = [
    {
        "Date": da,
        "Tracs_num": tr,
        "Description": de,
        "Data": [*g[data].to_numpy().tolist()]
    } for (da, tr, de), g in (
        pd.read_excel("file.xlsx").fillna({"Credit": 0, "Debit": 0})
        .astype({"Date": "str", "Tracs num": "str"}).groupby(meta, sort=False)
    )
]

Output :

import json

print(json.dumps(out, indent=4))

[
    {
        "Date": "2017-01-31",
        "Tracs_num": "101",
        "Description": "Open Up",
        "Data": [
            [371275.83, 0.0, 1213.0],
            [38052.22, 0.0, 1214.0]
            ...
        ]
    },
    {
        "Date": "2017-01-31",
        "Tracs_num": "102",
        "Description": "January Income",
        "Data": [
            [3500.0, 0.0, 1213.0],
            [0.0, 11330.0, 2211.0]
            ...
        ]
    }
]