Converting CSV to HTML Table in Python

Question

I'm trying to take data from a .csv file and importing into a HTML table within python.

This is the csv file https://www.mediafire.com/?mootyaa33bmijiq

Context:
The csv is populated with data from a football team [Age group, Round, Opposition, Team Score, Opposition Score, Location]. I need to be able to select a specific age group and only display those details in separate tables.

This is all I've got so far....

infile = open("Crushers.csv","r")

for line in infile:
    row = line.split(",")
    age = row[0]
    week = row [1]
    opp = row[2]
    ACscr = row[3]
    OPPscr = row[4]
    location = row[5]

if age == 'U12':
   print(week, opp, ACscr, OPPscr, location)

Enclosure

you can use pandas library to achieve this. pandas have a method named to_html. Here is link pandas.pydata.org/pandas-docs/stable/generated/… – Bookstall

Answer 1

Before you begin printing the desired rows, output some HTML to set up an appropriate table structure.

When you find a row you want to print, output it in HTML table row format.

# begin the table
print("<table>")

# column headers
print("<th>")
print("<td>Week</td>")
print("<td>Opp</td>")
print("<td>ACscr</td>")
print("<td>OPPscr</td>")
print("<td>Location</td>")
print("</th>")

infile = open("Crushers.csv","r")

for line in infile:
    row = line.split(",")
    age = row[0]
    week = row [1]
    opp = row[2]
    ACscr = row[3]
    OPPscr = row[4]
    location = row[5]

    if age == 'U12':
        print("<tr>")
        print("<td>%s</td>" % week)
        print("<td>%s</td>" % opp)
        print("<td>%s</td>" % ACscr)
        print("<td>%s</td>" % OPPscr)
        print("<td>%s</td>" % location)
        print("</tr>")

# end the table
print("</table>")

Abhorrent

Answer 2

First install pandas:

pip install pandas

Then run:

import pandas as pd

columns = ['age', 'week', 'opp', 'ACscr', 'OPPscr', 'location']
df = pd.read_csv('Crushers.csv', names=columns)

# This you can change it to whatever you want to get
age_15 = df[df['age'] == 'U15']
# Other examples:
bye = df[df['opp'] == 'Bye']
crushed_team = df[df['ACscr'] == '0']
crushed_visitor = df[df['OPPscr'] == '0']
# Play with this

# Use the .to_html() to get your table in html
print(crushed_visitor.to_html())

You'll get something like:

<table border="1" class="dataframe">
  <thead>
    <tr style="text-align: right;">
      <th></th>
      <th>age</th>
      <th>week</th>
      <th>opp</th>
      <th>ACscr</th>
      <th>OPPscr</th>
      <th>location</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <th>34</th>
      <td>U17</td>
      <td>1</td>
      <td>Banyo</td>
      <td>52</td>
      <td>0</td>
      <td>Home</td>
    </tr>
    <tr>
      <th>40</th>
      <td>U17</td>
      <td>7</td>
      <td>Aspley</td>
      <td>62</td>
      <td>0</td>
      <td>Home</td>
    </tr>
    <tr>
      <th>91</th>
      <td>U12</td>
      <td>7</td>
      <td>Rochedale</td>
      <td>8</td>
      <td>0</td>
      <td>Home</td>
    </tr>
  </tbody>
</table>

Semiliterate

Answer 3

Firstly, install pandas:

pip install pandas

Then,

import pandas as pd         
a = pd.read_csv("Crushers.csv") 
# to save as html file 
# named as "Table" 
a.to_html("Table.htm") 
# assign it to a  
# variable (string) 
html_file = a.to_html()

Definitive

Answer 4

Below function takes filename, headers(optional) and delimiter(optional) as input and converts csv to html table and returns as string. If headers are not provided, assumes header is already present in csv file.

Converts csv file contents to HTML formatted table

def csv_to_html_table(fname,headers=None,delimiter=","):
    with open(fname) as f:
        content = f.readlines()
    #reading file content into list
    rows = [x.strip() for x in content]
    table = "<table>"
    #creating HTML header row if header is provided 
    if headers is not None:
        table+= "".join(["<th>"+cell+"</th>" for cell in headers.split(delimiter)])
    else:
        table+= "".join(["<th>"+cell+"</th>" for cell in rows[0].split(delimiter)])
        rows=rows[1:]
    #Converting csv to html row by row
    for row in rows:
        table+= "<tr>" + "".join(["<td>"+cell+"</td>" for cell in row.split(delimiter)]) + "</tr>" + "\n"
    table+="</table><br>"
    return table

In your case, function call will look like this, but this will not filter out entries in csv but directly convert whole csv file to HTML table.

filename="Crushers.csv"
myheader='age,week,opp,ACscr,OPPscr,location'
html_table=csv_to_html_table(filename,myheader)

Note: To filter out entries with certain values add conditional statement in for loop.

Fining

if you want border line also at place of table = "<table>" replace with table = "<table border = 1>" – Neopythagoreanism

Thank you very much for this script which helps me a lot. I was wondering, do you know if once the csv file is converted to html table, is it possible to transform cells with text into HTML <a href link>? Do you have any guidelines or links to give me? – Endarch

you will have to change the else block, expand the for loop comprehension. Add conditional statement in for loop to check if cell content matches URL format using regex. If it matches add <a href> tag with cell data. – Fining

Hi, in the following link I tried to follow your advise, could you please have a loook and help me ; #71280910 – Endarch

Answer 5

First some imports:

import csv
from html import escape
import io

Now the building blocks - let's make one function for reading the CSV and another function for making the HTML table:

def read_csv(path, column_names):
    with open(path, newline='') as f:
        # why newline='': see footnote at the end of https://docs.python.org/3/library/csv.html
        reader = csv.reader(f)
        for row in reader:
            record = {name: value for name, value in zip(column_names, row)}
            yield record

def html_table(records):
    # records is expected to be a list of dicts
    column_names = []
    # first detect all posible keys (field names) that are present in records
    for record in records:
        for name in record.keys():
            if name not in column_names:
                column_names.append(name)
    # create the HTML line by line
    lines = []
    lines.append('<table>\n')
    lines.append('  <tr>\n')
    for name in column_names:
        lines.append('    <th>{}</th>\n'.format(escape(name)))
    lines.append('  </tr>\n')
    for record in records:
        lines.append('  <tr>\n')
        for name in column_names:
            value = record.get(name, '')
            lines.append('    <td>{}</td>\n'.format(escape(value)))
        lines.append('  </tr>\n')
    lines.append('</table>')
    # join the lines to a single string and return it
    return ''.join(lines)

Now just put it together :)

records = list(read_csv('Crushers.csv', 'age week opp ACscr OPPscr location'.split()))

# Print first record to see whether we are loading correctly
print(records[0])
# Output:
# {'age': 'U13', 'week': '1', 'opp': 'Waterford', 'ACscr': '22', 'OPPscr': '36', 'location': 'Home'}

records = [r for r in records if r['age'] == 'U12']

print(html_table(records))
# Output:
# <table>
#   <tr>
#     <th>age</th>
#     <th>week</th>
#     <th>opp</th>
#     <th>ACscr</th>
#     <th>OPPscr</th>
#     <th>location</th>
#   </tr>
#   <tr>
#     <td>U12</td>
#     <td>1</td>
#     <td>Waterford</td>
#     <td>0</td>
#     <td>4</td>
#     <td>Home</td>
#   </tr>
#   <tr>
#     <td>U12</td>
#     <td>2</td>
#     <td>North Lakes</td>
#     <td>12</td>
#     <td>18</td>
#     <td>Away</td>
#   </tr>
#   ...
# </table>

A few notes:

• csv.reader works better than line splitting because it also handles quoted values and even quoted values with newlines

• html.escape is used to escape strings that could potentially contain character < or >

• it is often times easier to worh with dicts than tuples

• usually the CSV files contain header (first line with column names) and could be easily loaded using csv.DictReader; but the Crushers.csv has no header (the data start from very first line) so we build the dicts ourselves in the function read_csv

• both functions read_csv and html_table are generalised so they can work with any data, the column names are not "hardcoded" into them

• yes, you could use pandas read_csv and to_html instead :) But it is good to know how to do it without pandas in case you need some customization. Or just as a programming exercise.

Canaday

Answer 6

This should be working as well:

from html import HTML
import csv

def to_html(csvfile):
    H = HTML()
    t=H.table(border='2')
    r = t.tr
    with open(csvfile) as csvfile:
        reader = csv.DictReader(csvfile)
        for column in reader.fieldnames:
            r.td(column)
        for row in reader:
            t.tr
            for col in row.iteritems():
                t.td(col[1])
    return t

and call the function by passing the csv file to it.

Junia

This is most straight forward solution, but you have to modify a couple of things to work - you need to initialize H and you need to preserve the order of columns in the rows. from html import HTML import csv def csv_to_html(csvfile): H = HTML() t=H.table(border='2') r = t.tr with open(csvfile) as csvfile: reader = csv.DictReader(csvfile) for column in reader.fieldnames: r.td(column) for row in reader: t.tr for column in reader.fieldnames: t.td(row.get(column, "empty")) return H – Agnomen

Answer 7

Other answers are suggesting pandas, but that's probably overkill if formatting CSV to an HTML table is all you need. If you want to use an existing package just for this purpose, there's tabulate:

import csv

from tabulate import tabulate

with open("Crushers.csv") as file:
    reader = csv.reader(file)
    u12_rows = [row for row in reader if row[0] == "U12"]
print(tabulate(u12_rows, tablefmt="html"))

Pallor