How to recalculate data from table in flask/python?

I want to show df in my app, im using flask to do it. But how can i make the table editable and calculated sum value by using button?

I couldn’t found good and easy way to edit that html table, and somehow send it to backend to recalculate.

Most tips i found abuot editables tables involve data from sql and model. I’m new to web apps, so js/html/css are a nightmere for me right now.

from flask import Flask, request, render_template, session, redirect
import pandas as pd
import numpy as np

app = Flask(__name__)

df = pd.DataFrame({'A': [0, 1, 2, 3, 4],
                   'B': [5, 6, 7, 8, 9],
                   'C': [6, 9, 1, 2, 5]})
sum_df = df.to_numpy().sum()
print(int(sum_df))

@app.route('/')
def index():
    return render_template('index.html', tables=[df.to_html(classes='data', header="true")], sum_df=sum_df)


@app.route('/my-link/')
def my_link():
    print('I got clicked!')
    return render_template('index.html', tables=[df.to_html(classes='data', header="true")], sum_df=sum_df)


if __name__ == '__main__':
    app.run(debug=True, host='127.0.0.9', port="8080")

JavaScript
​x
 
from flask import Flask, request, render_template, session, redirect
import pandas as pd
import numpy as np
​
app = Flask(__name__)
​
df = pd.DataFrame({'A': [0, 1, 2, 3, 4],
                   'B': [5, 6, 7, 8, 9],
                   'C': [6, 9, 1, 2, 5]})
sum_df = df.to_numpy().sum()
print(int(sum_df))
​
@app.route('/')
def index():
    return render_template('index.html', tables=[df.to_html(classes='data', header="true")], sum_df=sum_df)
​
​
@app.route('/my-link/')
def my_link():
    print('I got clicked!')
    return render_template('index.html', tables=[df.to_html(classes='data', header="true")], sum_df=sum_df)
​
​
if __name__ == '__main__':
    app.run(debug=True, host='127.0.0.9', port="8080")
​

<!doctype html>

<html lang="eng">
<head><title>Test</title>
    <meta charset=utf-8> </head>
    <body>
        <form action="/my-link/">
            <input type="submit" value="Calculate" />
        </form>
    <h1>Table</h1>
    {% for table in tables %}
        {{ table|safe }}
    {% endfor %}
    {{sum_df}}
    </body>
</html>

JavaScript
 
<!doctype html>
​
<html lang="eng">
<head><title>Test</title>
    <meta charset=utf-8> </head>
    <body>
        <form action="/my-link/">
            <input type="submit" value="Calculate" />
        </form>
    <h1>Table</h1>
    {% for table in tables %}
        {{ table|safe }}
    {% endfor %}
    {{sum_df}}
    </body>
</html>
​

Answer

You can convert your data to a matrix to present it on table with inputs. And when you press the button you grab the data from the table and make an AJAX request to send the data in JSON format to another route and recalculate the sum.

After updating the data you just need to reload the page to see the changes.

You can use a transpose function to switch rows and columns.

main.py

from flask import Flask, request, render_template, session, redirect, jsonify
import pandas as pd
import numpy as np

app = Flask(__name__)

df = pd.DataFrame({'A': [0, 1, 2, 3, 4],
                   'B': [5, 6, 7, 8, 9],
                   'C': [6, 9, 1, 2, 5]})

sum_df = df.to_numpy().sum()

@app.route('/')
def index():
    data = [df.columns.values.tolist()] + df.values.tolist()
    return render_template('index.html', data=data, sum_df=sum_df)


@app.route('/submit-data', methods=["POST"])
def submitData():
    data = request.get_json()
    global df
    global sum_df
    df = pd.DataFrame(data)
    df = df.apply(pd.to_numeric)
    sum_df = df.to_numpy().sum()
    return jsonify({"status": "OK"})


if __name__ == '__main__':
    app.run(debug=True, host='127.0.0.1', port="5000")

JavaScript
 
from flask import Flask, request, render_template, session, redirect, jsonify
import pandas as pd
import numpy as np
​
app = Flask(__name__)
​
df = pd.DataFrame({'A': [0, 1, 2, 3, 4],
                   'B': [5, 6, 7, 8, 9],
                   'C': [6, 9, 1, 2, 5]})
​
sum_df = df.to_numpy().sum()
​
@app.route('/')
def index():
    data = [df.columns.values.tolist()] + df.values.tolist()
    return render_template('index.html', data=data, sum_df=sum_df)
​
​
@app.route('/submit-data', methods=["POST"])
def submitData():
    data = request.get_json()
    global df
    global sum_df
    df = pd.DataFrame(data)
    df = df.apply(pd.to_numeric)
    sum_df = df.to_numpy().sum()
    return jsonify({"status": "OK"})
​
​
if __name__ == '__main__':
    app.run(debug=True, host='127.0.0.1', port="5000")
​

index.html

<!doctype html>

<html lang="eng">
<head><title>Test</title>
    <meta charset=utf-8> </head>
    <body>
    <button id="calculate-btn">Calculate</button>
    <h1>Table</h1>
    <table>
        <thead>
            <tr>
                <th></th>
                {% for col in data[0] %}
                    <th>{{col}}</th>
                {% endfor %}
            </tr>
        </thead>
    {% for row in range(1, data|length) %}
        <tbody>
            <tr>
                <th>{{row}}</th>
                {% for i in data[row] %}
                    <td><input type="text" value="{{i}}"></td>
                {% endfor %}
            </tr>
        </tbody>
    {% endfor %}
    </table>
    {{sum_df}}
    </body>
    <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.6.1/jquery.min.js"></script>
    <script>

        function transpose(matrix) {
            let [row] = matrix
            return row.map((value, column) => matrix.map(row => row[column]))
        }

        $("#calculate-btn").on("click", function() {
            var data = {};
            var table_h = [];
            var rows = $("tbody").children();
            var columns = $("thead tr").children().slice(1);

            rows.each(function(i, val) {
                table_h.push([]);
                var values = $(val).children().slice(1);
                values.each(function(i2, val2) {
                    console.log($(val2).find("input").val());
                    table_h[i].push($(val2).find("input").val());
                });
            });
            var table_v = transpose(table_h);

            columns.each(function(i, val) {
                var col = val.innerText;
                data[col] = table_v[i];
            });

            $.ajax({
                type: 'POST',
                url: '/submit-data',
                data: JSON.stringify(data),
                contentType: "application/json",
                dataType: 'json'
            })
            .then(function() {
                window.location.reload();
            });

        });
    </script>
</html>

JavaScript
 
<!doctype html>
​
<html lang="eng">
<head><title>Test</title>
    <meta charset=utf-8> </head>
    <body>
    <button id="calculate-btn">Calculate</button>
    <h1>Table</h1>
    <table>
        <thead>
            <tr>
                <th></th>
                {% for col in data[0] %}
                    <th>{{col}}</th>
                {% endfor %}
            </tr>
        </thead>
    {% for row in range(1, data|length) %}
        <tbody>
            <tr>
                <th>{{row}}</th>
                {% for i in data[row] %}
                    <td><input type="text" value="{{i}}"></td>
                {% endfor %}
            </tr>
        </tbody>
    {% endfor %}
    </table>
    {{sum_df}}
    </body>
    <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.6.1/jquery.min.js"></script>
    <script>
​
        function transpose(matrix) {
            let [row] = matrix
            return row.map((value, column) => matrix.map(row => row[column]))
        }
​
        $("#calculate-btn").on("click", function() {
            var data = {};
            var table_h = [];
            var rows = $("tbody").children();
            var columns = $("thead tr").children().slice(1);
​
            rows.each(function(i, val) {
                table_h.push([]);
                var values = $(val).children().slice(1);
                values.each(function(i2, val2) {
                    console.log($(val2).find("input").val());
                    table_h[i].push($(val2).find("input").val());
                });
            });
            var table_v = transpose(table_h);
​
            columns.each(function(i, val) {
                var col = val.innerText;
                data[col] = table_v[i];
            });
​
            $.ajax({
                type: 'POST',
                url: '/submit-data',
                data: JSON.stringify(data),
                contentType: "application/json",
                dataType: 'json'
            })
            .then(function() {
                window.location.reload();
            });
​
        });
    </script>
</html>
​

Advertisement

Answer