Create a dropdown list for an existing column in html jinja

Question

I have a dataframe created in flask. The df has 3 columns: "Account Description", "Amount", and "Mapping". The column "Mapping" takes 3 values: "Cash", "Debt", and "Equity"

The dataframe is sent to html jinja using:

return render_template('file.html', data=df,column_names=data.columns.values, row_names=list(data.values.tolist(), link_column="Mapping"))

What I need to do now is to create a dropdown list for every cell in the 'Mapping' column, with the default option value for each dropdown box being the existing value of the cell. Please look at my image link to get an idea. The pre-populated in those blue boxes should be the value of the cell instead of "select an option"

https://drive.google.com/file/d/1sadDR0x8pJRzeJRS6pSvfDaNmKyoJ31G/view?usp=sharing

Here's the html code i'm trying but not working:

  <div>
     <table id="Mapped table">
       <h2>Mapped data</h2>
          <tr>
              {% for col in column_names %}
              <th>{{col}}</th>
              {% endfor %}
          </tr>
          {% for row in row_names %}
          <tr>
              {% for col_, row_ in zip(column_names, row) %}
              {% if col_ == link_column %}
              <td>

                <select class="dropbtn">
                  <option value="none" selected disabled hidden>
                      #Select existing option of the column cell
                  </option>
                  <option value="1">Cash</option>
                  <option value="2">DebtAccrual</option>
                  <option value="3">Equity</option>
                </select>
  </div>

Button onclick event:

  <script>
     var a = document.getElementById("dropbtn");
     var old = a.options[a.selectedIndex].text;
  </script>

  <div class="wrapper">
    <button class="button" id="myBtn">Confirm mapping</button>
    <script>
      $('#myBtn').on('click', function(a) {
          var drpdbJson = {};
          // for each row containing a select....
          $('#Mapped\\ table tr:gt(0):has(select)').each(function(idx, old_ele) {
              // get the selected value
              var old_drpdwnKey = $(old_ele).find('.dropbtn option:selected').text();
              // get the second column text
              var old_drpdwnValue = $(old_ele).find('td:eq(1)').text();
              // add to the json result
              if (drpdbJson[old_drpdwnKey] == undefined) {
                drpdbJson[old_drpdwnKey] = old_drpdwnValue;
              } else {
                drpdbJson[old_drpdwnKey] = (typeof drpdbJson[old_drpdwnKey] === 'string') ?
                    [drpdbJson[old_drpdwnKey], old_drpdwnValue] : [...drpdbJson[old_drpdwnKey], old_drpdwnValue];
              }
          })
          // print the json obj
          console.log(JSON.stringify(drpdbJson))
          $.post("http://localhost:5000/bs_mapped",JSON.stringify(drpdbJson))
      })
    </script>
  </div>

I apologize if my code is not entirely reproducible. I would like to just have some general idea of how to accomplish this. Thank you very much.

Answer 1

As the dropdown has fixed values, we can pass the choice list from Flask to HTML template. And then if values are matched, we can add selected attribute to the option tag.

I have used to_dict('records') to pass each row as a dictionary where key is the column name and value is the data in the cell.

app.py:

from flask import Flask, render_template, url_for, request
import pandas as pd


app = Flask(__name__)

@app.route('/', methods=['GET', 'POST'])
def index():
    account = pd.Series(["Petty cash", "Lucky Money", "RBC USD"])
    amount = pd.Series([-2.59, 1111111, 66170.97])
    mapping = pd.Series(["Debt", "Equity", "Cash"])
    choices = (("Cash", "Cash"), ("Debt", "DebtAccrual"), ("Equity", "Equity"))
    data = pd.DataFrame({
        "Account Description":account,
        "Amount":amount,
        "Mapping":mapping
    })
    return render_template('index.html',
                           column_names=data.columns.values,
                           rows=data.to_dict('records'),
                           link_column="Mapping",
                           choices = choices
                           )

index.html:

<html>
<head>
  <title>Dataframe in Flask template</title>
</head>
<body>
  <div>
    <h2>Mapped data</h2>
    <table id="Mapped table" border="1">
      <tr>
        {% for col in column_names %}
        <th>{{col}}</th>
        {% endfor %}
      </tr>
      {% for row in rows %}
      <tr>
        {% for key, val in row.items() %}
          {% if key == link_column %}
          <td>
            <select class="dropbtn">
              {% for choice in choices %}
                <option value={{ choice[0] }}
                {% if choice[0] == val %} selected {% endif %}>
                  {{ choice[1] }}
                </option>
              {% endfor %}
            </select>
          </td>
          {% else %}
          <td>
            {{ val }}
          </td>
          {% endif %}
        {% endfor %}
      </tr>
      {% endfor %}
    </table>
  </div>
</body>
</html>

Screenshot:

Default option value for each dropdown box being the existing value of the cell: