Flask return pd dataframe groupby - how to delete column that is used to group?

Question

I'm new to Python - my first experience took place 2-3 days ago - and am stuck with mapping data using df.groupby method, therefore help would be highly appriciated.

With given df:

    id   VALUE1  VALUE2  VALUE3
    1         1      11     abc
    2         1      22     def
    3         2      33     ghi
    4         2      44     jkl

I want to format this data to format returnable by Flask. In order to achieve that I'm starting with using a groupby method like this:

df = df.groupby('VALUE1', group_keys=False, as_index=False)

Completing that, my 'df' is not returnable by Flask, so I took another step to format this data using

 df = df.groupby('VALUE1', group_keys=False, as_index=False).apply(lambda x: x.to_json(orient='records'))

This resulted in almost acceptable format:

id, value:
  1       [
            { "VALUE1": "1", "VALUE2": "11", "VALUE3": "abc" },
            { "VALUE1": "1", "VALUE2": "22", "VALUE3": "def" },
          ]
  2       [
            { "VALUE1": "2", "VALUE2": "33", "VALUE3": "ghi" },
            { "VALUE1": "2", "VALUE2": "44", "VALUE3": "jkl" },
          ]

Format that ultimately I would like to achieve is this:

id, value:
  1      1: {
              { "VALUE2": "11", "VALUE3": "abc" },
              { "VALUE2": "22", "VALUE3": "def" },
            }
  2      2: {
              { "VALUE2": "33", "VALUE3": "ghi" },
              { "VALUE2": "44", "VALUE3": "jkl" },
            }

Thanks!

Why make things complex? Have you tried to pass the dataframe directly to html? See here for some ideas https://stackoverflow.com/questions/52644035/how-to-show-a-pandas-dataframe-into-a-existing-flask-html-table — IoaTzimas, Dec 01 '20 at 14:05
Because that is the requirement - who am I do judge. I didn't try to pass it to HTML - this code is inside of an API — Kalik, Dec 01 '20 at 14:15

score 1 · Accepted Answer · answered Dec 01 '20 at 15:23

Try this:

import io
import pandas

from flask import Flask, jsonify

# Create a flask app
app = Flask(__name__)


@app.route("/")
def index():

    # Your data
    data = io.StringIO(
    """
    id   VALUE1  VALUE2  VALUE3
    1         1      11     abc
    2         1      22     def
    3         2      33     ghi
    4         2      44     jkl
    """
    )
    df = pandas.read_table(data, sep="\s+")

    # Create a placeholder for the output
    output = {}

    # Loop all the groups in your groupby
    for group_id, group in df.groupby('VALUE1'):

        # Add a dict representation of the group to the output, without the columns you don't want
        output[group_id] = group.drop(columns=["VALUE1", "id"]).to_dict(orient="records")


    # Return the output in JSON format
    return jsonify(output)


if __name__ == "__main__":
    app.run(debug=True)

Output:

{
  "1": [
    {
      "VALUE2": 11, 
      "VALUE3": "abc"
    }, 
    {
      "VALUE2": 22, 
      "VALUE3": "def"
    }
  ], 
  "2": [
    {
      "VALUE2": 33, 
      "VALUE3": "ghi"
    }, 
    {
      "VALUE2": 44, 
      "VALUE3": "jkl"
    }
  ]
}

Flask return pd dataframe groupby - how to delete column that is used to group?

1 Answers1