Flask Pandas Dataframe - How to code it
Tags: webdev, flask, pandas
Published: 2021-04-10 16:32:08

---
title: Flask Pandas Dataframe - How to code it
published: true
description: Simple Flask project that loads pandas dataframe into the database and shows the information on a page 
tags: webdev, flask, pandas
cover_image: https://raw.githubusercontent.com/app-generator/flask-pandas-dataframe/main/media/screen.png
canonical_url: https://blog.appseed.us/flask-pandas-dataframe-how-to-code/
---
Hello Coders, 

This article presents a simple Flask project that loads pandas dataframe 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

---

> **Thanks for reading!** TL;DR; 

- [Flask Pandas Dataframe](https://github.com/app-generator/flask-pandas-dataframe) - source code
- More [free samples](https://appseed.us/admin-dashboards/open-source) provided by AppSeed

---

### 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**

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

---

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

```bash
$ # 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** 

```bash
$ # 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`

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

---

**Start the app** and visualize the data

```bash
$ # 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 
```

---

### Dependencies

- [Flask](https://flask.palletsprojects.com/en/1.1.x/) - the framework used  
- [Pandas](https://pandas.pydata.org/) - an amazing `data analysis` library
- [SQLAlchemy](https://www.sqlalchemy.org/) - Python SQL Toolkit and ORM
- [Flask-SqlAlchemy](https://flask-sqlalchemy.palletsprojects.com/en/2.x/) - Flask extension for SQLAlchemy
- [Requests](https://pypi.org/project/requests/) - simple HTTP library. 

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

---

### Download the data

The dataset is downloaded from a remote [location](https://static.appseed.us/data/titanic.txt) and saved **titanic.csv**.


```python
>>> 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 

```python
>>> import pandas as pd
>>> 
>>> df = pd.read_csv( 'titanic.csv' )
>>> df
     PassengerId  Survived  Pclass                                               Name     Sex   Age  SibSp  Parch            Ticket     Fare Cabin Embarked
0              1         0       3                            Braund, Mr. Owen Harris    male  22.0      1      0         A/5 21171   7.2500   NaN        S
1              2         1       1  Cumings, Mrs. John Bradley (Florence Briggs Th...  female  38.0      1      0          PC 17599  71.2833   C85        C
2              3         1       3                             Heikkinen, Miss. Laina  female  26.0      0      0  STON/O2. 3101282   7.9250   NaN        S
3              4         1       1       Futrelle, Mrs. Jacques Heath (Lily May Peel)  female  35.0      1      0            113803  53.1000  C123        S
4              5         0       3                           Allen, Mr. William Henry    male  35.0      0      0            373450   8.0500   NaN        S
..           ...       ...     ...                                                ...     ...   ...    ...    ...               ...      ...   ...      ...
886          887         0       2                              Montvila, Rev. Juozas    male  27.0      0      0            211536  13.0000   NaN        S
887          888         1       1                       Graham, Miss. Margaret Edith  female  19.0      0      0            112053  30.0000   B42        S
888          889         0       3           Johnston, Miss. Catherine Helen "Carrie"  female   NaN      1      2        W./C. 6607  23.4500   NaN        S
889          890         1       1                              Behr, Mr. Karl Howell    male  26.0      0      0            111369  30.0000  C148        C
890          891         0       3                                Dooley, Mr. Patrick    male  32.0      0      0            370376   7.7500   NaN        Q
```

---

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

```python
>>> 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: 

```python
# 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`: 

```bash
$ 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](https://sqlitebrowser.org/), 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). 

```python
# 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:

```bash
$ 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.
```

---

```bash
$ # 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: 

**Default route**

![image](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/yqre9xfk25nl7j6k6rev.png)

---

**Data Route**

![image](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/soqv5572z9ax7bbya2ic.png)

---

> **Thanks for reading!** For more resources, please access:  

---

- [Flask](https://flask.palletsprojects.com/en/1.1.x/) - the framework used  
- [Pandas](https://pandas.pydata.org/) - an amazing `data analysis` library
- [AppSeed](https://appseed.us/) - for support annd more samples