Flask Pandas Dataframe - How to code it

This article presents a simple Flask project that loads pandas data frame into the database and shows the information on a page. Flask Pandas Dataframe is a one-file project that might help beginners to understand some basic Flask concepts:

• ✅ Create a simple Flask app
• ✅ Download a pandas dataframe from a remote URL
• ✅ Create an SQLite DB and a table to save the information
• ✅ Load pandas in DB using a new custom command
• ✅ Visualize the data in the browser
• ✅ Flask Pandas Dataframe - source code

Build & Set up

To build the project and see some action, please follow this simple setup presented below. Please note that Python3 is required to have a successful build.

Clone the sources

$ git clone https://github.com/app-generator/flask-pandas-dataframe.git
$ cd flask-pandas-dataframe

Install dependencies: Flask, Pandas, SqlAlchemy, and requests.

$ # Virtualenv modules installation (Unix based systems)
$ virtualenv env
$ source env/bin/activate
$
$ # Virtualenv modules installation (Windows based systems)
$ # virtualenv env
$ # .\env\Scripts\activate
$
$ # Install dependencies
$ pip3 install -r requirements.txt

Create the SQLite database

$ # Create database via Flask CLI
$ flask shell
>>> from app import db  # import SqlAlchemy interface 
>>> db.create_all()     # create SQLite database and Data table 
>>> quit()              # leave the Flask CLI  

Load the information via a custom command

$ # Load the data into the database
$ flask load-data titanic-min.csv

Start the app and visualize the data

$ # Set the FLASK_APP environment variable
$ (Unix/Mac) export FLASK_APP=run.py
$ (Windows) set FLASK_APP=run.py
$ (Powershell) $env:FLASK_APP = ".\run.py"
$
$ # Set up the DEBUG environment
$ # (Unix/Mac) export FLASK_ENV=development
$ # (Windows) set FLASK_ENV=development
$ # (Powershell) $env:FLASK_ENV = "development"
$ 
$ flask run 
$ # access the app in the browser: http://localhost:5005 

Download the data

The dataset is downloaded from a remote location and saved titanic.csv.

>>> import requests
>>> 
>>> # Define the remote CSV file
>>> csv_file = 'https://static.appseed.us/data/titanic.txt'
>>>
>>> # Download the file (via request library)
>>> r = requests.get( csv_file )
>>> 
>>> # Save the content to a new LOCAL file
>>> f = open('titanic.csv', 'w')  
>>> f.write( r.content.decode("utf-8") )
>>> f.close 

Process data

The RAW dataset has ~900 rows and we can inspect it with ease using pandas library

>>> import pandas as pd
>>> 
>>> df = pd.read_csv( 'titanic.csv' )
>>> df

Let's inspect columns data types in the DataFrame: df.dtypes. This information is used to design the table where is information is loaded.

>>> df.dtypes 
PassengerId      int64
Survived         int64
Pclass           int64
Name            object
Sex             object
Age            float64
SibSp            int64
Parch            int64
Ticket          object
Fare           float64
Cabin           object
Embarked        object

Prepare the SQLite storage

To manage the information with ease, SqlAchemy ORM will be used. The Data table will store the loaded information - here is the definition:

# Store the Titanic sad stats
class Data(db.Model):

    passengerId  = db.Column(db.Integer,     primary_key=True )
    name         = db.Column(db.String(250), nullable=False   )
    survived     = db.Column(db.Integer,     nullable=False   )
    sex          = db.Column(db.String(10 ), default=None     ) 
    age          = db.Column(db.Integer,     default=-1       ) 
    fare         = db.Column(db.Float,       default=-1       )

    # The string representation
    def __repr__(self):
        return str(self.passengerId) + ' - ' + str(self.name) 

Create the SQLite database and the new table via Flask CLI:

$ flask shell
App: app [development]
Instance: D:\work\repo-learn\python\how-to\instance
>>> from app import db
>>> db.create_all()

At this point, we can inspect the database using SQLiteBrowser, an open-source and free editor for SQLite (the table is empty).

Load Data

The information will be loaded into the database via a custom command = load-data. The command expects a file name as input argument (CSV format).

# New import
import click
...
# Custom command
@app.cli.command("load-data")
@click.argument("fname")
def load_data(fname):
    ''' Load data from a CSV file '''
    print ('*** Load from file: ' + fname)

    # The functional part goes here
    ... 

To check the command is properly coded we can type flask --help in the terminal:

$ flask --help

Options:
  --version  Show the flask version
  --help     Show this message and exit.

Commands:
  load-data  Load data from a CSV file          <-- NEW Command
  routes     Show the routes for the app.
  run        Run a development server.
  shell      Run a shell in the app context.

The information is loaded using the following command:

$ # Load the information
$ flask load-data titanic-min.csv

Once the information is loaded we can start the app and check the results in the browser:

The default route (app index):

Data Route (where loaded pandas are visible):

Thanks for reading!

• Flask - the framework used to code this simple project
• Pandas - an amazing data analysis library
• AppSeed - for more samples and starters