Using Pandas with Django to read and parse excel file [closed]

Question

In my Django(2.0.1) project, I want to read spreadsheets(excel, csv, ods) and convert it into python dictionary for processing.

I was looking into django-excel but it doesn't support Django 2. Hence, I am planning to use Pandas. I want to know what are the pros and cons(mainly complexity vs performance) in this scenario. Please suggest other solutions which can be used?

Project detail: It is a SPA using DRF for backend. Users will upload excel file and I want to process this in Django view.

Answer 1

No, it is not overkill. In this situation, pandas can add flexibility rather than restrictions.

For example, you can allow users to upload any of: excel, csv, csv.gz. Since pandas has a library of methods to handle various formats you can write a function to select the appropriate method and convert to a dataframe.

Once in a dataframe, conversion to dictionary is trivial.

Here's an example of a function I use for this purpose:

import os, pandas as pd

def read_file(filename, **kwargs):

    """Read file with **kwargs; files supported: xls, xlsx, csv, csv.gz, pkl"""

    read_map = {'xls': pd.read_excel, 'xlsx': pd.read_excel, 'csv': pd.read_csv,
                'gz': pd.read_csv, 'pkl': pd.read_pickle}

    ext = os.path.splitext(filename)[1].lower()[1:]
    assert ext in read_map, "Input file not in correct format, must be xls, xlsx, csv, csv.gz, pkl; current format '{0}'".format(ext)
    assert os.path.isfile(filename), "File Not Found Exception '{0}'.".format(filename)

    return read_map[ext](filename, **kwargs)

Then simply call it as follows:

df = read_file('file.xlsx')  # or 'file.csv', 'file.csv.gz', etc