TechQA.

data sorting in html webpage with script.js file but data is not being sorted correctly?

66 views Asked by At

okay I have bunch of csv files in my folder I am running python script to convert all of them in html files. so the python script is working fine I have my csv files in html table files with styles.css correctly applied. but when I am clicking on percentage column for sorting the high percentages are coming on second third of fifth position instead of top position same is true for remaining two columns. now the sorting is not 100 % correct or incorrect.

structure of my data

structure of my csv data

python script:

import pandas as pd
import os

for file_name in os.listdir():
    if file_name.endswith(".csv"):
        df = pd.read_csv(file_name)
        html_table = df.to_html(table_id="sortTable", classes=["sortTable"],
                                index=False, justify='center', header=True, render_links=True)
        with open(file_name.replace('.csv', '.html'), 'w') as f:
            f.write("<!DOCTYPE html>\n<html>\n<head>\n")
            with open("styles.css", "r") as css:
                f.write("<style>\n")
                f.write(css.read())
                f.write("\n</style>\n")
            f.write("</head>\n<body>\n")
            f.write(html_table)
            f.write("<script src='https://code.jquery.com/jquery-3.6.0.min.js'></script>")
            with open("script.js", "r") as js:
                f.write("<script>\n")
                f.write(js.read())
                f.write("\n</script>\n")
            f.write("</body>\n</html>")

***************here, is the structure of **styles.css **file *********************************

   .sortTable {
  border-collapse: collapse;
  margin: 25px 0;
  font-size: 0.9em;
  min-width: 400px;
  border-radius: 5px 5px 0 0;
  overflow: hidden;
  box-shadow: 0 0 20px rgba(0, 0, 0, 0.15);
}

.sortTable thead tr {
  background-color: #009879;
  color: #ffffff;
  text-align: left;
  font-weight: bold;
  cursor: pointer;
}

.sortTable th,
.sortTable td {
  padding: 12px 15px;
}

.sortTable tbody tr {
  border-bottom: 1px solid #dddddd;
}

.sortTable tbody tr:nth-of-type(even) {
  background-color: #f3f3f3;
}

.sortTable tbody tr:last-of-type {
  border-bottom: 2px solid #009879;
}

th i{
    margin-left: 1px;
    visibility: hidden;
}

th .active{
    visibility: visible;    
}

******************************* here is the **script.js **file **********************************

const table = document.getElementById('sortTable');
const headers = table.querySelectorAll('th');
const rows = table.querySelectorAll('tr');

headers.forEach((header,headerIndex)=>{
    header.addEventListener('click',()=>{
        sortColumn(headerIndex);
    });
});

// Transform the content of given cell in given column
const transform = function (index, content) {
    // Get the data type of column
    const type = headers[index].getAttribute('type');
    switch (type) {
        case 'number':
            return parseFloat(content);
        case 'string':
        default:
            return content;
    }
};

// Track sort directions
let directions = Array(headers.length).fill("");
console.log(directions);

function sortColumn(headerIndex){
    
    //Check the direction asc or desc
    const direction = directions[headerIndex] || 'asc';
    const multiplier = (direction=='asc')? 1 :-1;

    //lets make new instance of rows
    let arrayRows = Array.from(rows);      
    arrayRows.shift();//Exclude header
    
    let newRows = Array.from(arrayRows);
    newRows.sort(function(rowA,rowB){
        //Get the content of cells
        const cellA = rowA.querySelectorAll('td')[headerIndex].innerHTML;
        const cellB = rowB.querySelectorAll('td')[headerIndex].innerHTML;
        let a = transform(headerIndex,cellA);        
        let b = transform(headerIndex,cellB);        

        if(a>b)
            return 1*multiplier;
        else if(a<b)
            return -1*multiplier;
        else
            return 0;
    });    

     //Remove old rows
     let tbody = document.getElementsByTagName("tbody");
     rows.forEach((row,index)=>{
        if(index!=0)
            tbody[0].removeChild(row);
     });
     //Append new row
     newRows.forEach((newRow)=>{
         tbody[0].appendChild(newRow);
     });

     // Reverse the direction
    directions[headerIndex] = direction === 'asc' ? 'desc' : 'asc';
    // console.log(directions);
}

// How sort function works:
// arr = ['3', '1', 4, 1, 5, 9];
// function compareFun(a,b){
//  if(a>b) 
//  {
//      return 1; //Place a after b
//  }
//  else if(a<b) 
//  {
//      return -1;//Place a before b
//  }
//  else 
//      return 0;//Don' change the position keep original order
// }
// arr.sort(compareFun);

**************************** output after running all scripts *******

notice how 24 is not ahead in the list

same as above big numbers are not in top

**************the scripts are obtained from youtubers! thanking them for their help.

  1. chatgpt provided the csv to html script
  2. youtube channel "Computer Nerds" provided the css and java script. (https://youtu.be/sqS5Jcttupo)

i tried to understand but i am microbiologist not a software engineer so not much.

type here
javascript html columnsorting
Original Q&A
2

There are 2 answers

3
Pawel Kam On

Your table headers <th> elements are missing type attribute and an icon <i> to trigger sorting. In your html, instead of

<tr style="text-align: center">
  <th>Taxa</th>
  <th>sample</th>
  <th>Percentage</th>
</tr>

the table headers should look more like this

<tr style="text-align: center">
  <th type="string">Taxa<i class="fa-solid"></i></th>
  <th type="number">sample<i class="fa-solid"></i></th>
  <th type="number">Percentage<i class="fa-solid"></i></th>
</tr>

To generate this with your script, you can do something like this:

# new function to map pandas dtypes to html types
# should work for your current data, but this does not exhaust dtype options
def choose_type(dtype):
    return {
        'object': 'string',
        'int64': 'number',
        'float64': 'number',
    }[dtype]

for file_name in os.listdir():
    if file_name.endswith(".csv"):
        df = pd.read_csv(file_name)
        html_table = df.to_html(table_id="sortTable", classes=["sortTable"],
                                index=False, justify='center', header=True, render_links=True)
        ### new lines to add ##
        # add types attribute
        columns_dtypes = [str(dtype) for dtype in df.dtypes]
        columns = dict(zip(df.columns, columns_dtypes))
        for col, dtype in columns.items():
            html_table = html_table.replace(f'<th>{col}', f'<th type="{choose_type(dtype)}">{col}')
        # add icon elenement
        html_table = html_table.replace('</th>', '<i class="fa-solid"></i></th>')
        ###
        with open(file_name.replace('.csv', '.html'), 'w') as f:
            f.write("<!DOCTYPE html>\n<html>\n<head>\n")
            # new line to add font-awsome for the sort icons
            f.write('<link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/6.2.0/css/all.min.css" integrity="sha512-xh6O/CkQoPOWDdYTDqeRdPCVd1SpvCA9XXcUnZS2FmJNp1coAFzvtCN9BmamE+4aHK8yyUHUSCcJHgXloTyT2A==" crossorigin="anonymous" referrerpolicy="no-referrer"/>')
            # ... and so on
0
ROHIT PATEL On

After analyzing the script.js file, I realized that Java was reading my numerical columns as text. Hence, I updated the script and forced java to read my column as numbers, and then compared which produced the right order of sorting. Here is the corrected.js file. 1.) python script (in question) 2.) styles.css (in question) 3.) and script.js (here), put all this files in cwd and all your cwd csv files will be converted in a HTML tables.

const table = document.getElementById('sortTable');
const headers = table.querySelectorAll('th');
const rows = table.querySelectorAll('tr');

headers.forEach((header,headerIndex)=>{
    header.addEventListener('click',()=>{
        sortColumn(headerIndex);
    });
});

// Transform the content of given cell in given column
const transform = function (index, content) {
    // Get the data type of column
    const type = headers[index].getAttribute('type');
    switch (type) {
        case 'number':
            return parseFloat(content);
        case 'string':
        default:
            return content;
    }
};

// Track sort directions
let directions = Array(headers.length).fill("");
console.log(directions);

function sortColumn(headerIndex) {
    // Check if the header text is "percentage"
    if (headers[headerIndex].textContent !== "Percentage") {
        return;
    }

    const transform = function (index, content) {
    // Get the data type of column
    if (headers[index].textContent === "Percentage") {
        return parseFloat(content);
    } else {
        const type = headers[index].getAttribute('type');
        switch (type) {
            case 'number':
                return parseFloat(content);
            case 'string':
            default:
                return content;
        }
    }
};


    //Check the direction asc or desc
    const direction = directions[headerIndex] || 'asc';
    const multiplier = (direction=='asc')? 1 :-1;

    //lets make new instance of rows
    let arrayRows = Array.from(rows);      
    arrayRows.shift();//Exclude header

    let newRows = Array.from(arrayRows);
    newRows.sort(function(rowA,rowB){
        //Get the content of cells
        const cellA = rowA.querySelectorAll('td')[headerIndex].innerHTML;
        const cellB = rowB.querySelectorAll('td')[headerIndex].innerHTML;
        let a = transform(headerIndex,cellA);        
        let b = transform(headerIndex,cellB);        

        if(a>b)
            return 1*multiplier;
        else if(a<b)
            return -1*multiplier;
        else
            return 0;
    });    

    let tbody = document.getElementsByTagName("tbody");
    rows.forEach((row,index)=>{
        if(index!=0)
            tbody[0].removeChild(row);
    });
    newRows.forEach((newRow)=>{
        tbody[0].appendChild(newRow);
    });

    // Reverse the direction
    directions[headerIndex] = direction === 'asc' ? 'desc' : 'asc';
}


// How sort function works:
// arr = ['3', '1', 4, 1, 5, 9];
// function compareFun(a,b){
//  if(a>b) 
//  {
//      return 1; //Place a after b
//  }
//  else if(a<b) 
//  {
//      return -1;//Place a before b
//  }
//  else 
//      return 0;//Don' change the position keep original order
// }
// arr.sort(compareFun);

Related Questions in JAVASCRIPT

Related Questions in HTML

Related Questions in COLUMNSORTING

Popular Questions

Popular Tags

javascript python java c# php android html jquery c++ css ios sql mysql r reactjs node.js arrays c asp.net json

Trending Questions