Combine pandas dataframe and additional value into a JSON file-CodePudding

I have created a function to output a JSON file into which I input a pandas data frame and a variable.

Dataframe (df) has 3 columns: ' a', 'b', and 'c'.

df = pd.DataFrame([[1,2,3], [0.1, 0.2, 0.3]], columns=('a','b','c'))

Error is a variable with a float value.

Error = 45

The output format of the JSON file should look like this:

{
"Error": 45,
"Data": [
    {
        "a": [1, 0.1]
    },
    {
        "b": [2, 0.2]

    },
    {
        "c": [3, 0.3]

    },

] }

I can convert the dataframe into a JSON using the below code. But how can I obtain the desired format of the JSON file?

def OutputFunction(df, Error):  

#json_output = df_ViolationSummary.to_json(orient = 'records')

df.to_json(r'C:\Users\Administrator\Downloads\export_dataframe.json', orient = 'records')


## Calling the Function 
OutputFunction(df, Error)

CodePudding user response：

calling to_dict(orient='list') will return a dictionary object with each key representing the column and value as the column values in a list. Then you can achieve your desired json object like this: output = {"Error":Error, "Data": df.to_dict(orient='list')}.

Running this line will return:

{'Error': 45, 'Data': {'a': [1.0, 0.1], 'b': [2.0, 0.2], 'c': [3.0, 0.3]}}

Note that the integer values will be in a float format since some of the values in the dataframe are floats, so the the columns' data types become float. If you really wish to have mixed types, you could use some form of mapping/dictionary comprehension as the following, although it should not be necessary for most cases:

output = {
    "Error":Error, 
    "Data": {
        col: [
            int(v) if v%1 == 0 else v 
            for v in vals 
        ] 
        for col,vals in df.to_dict(orient='list').items()
    }
}