Converting Django Querysets to Pandas Dataframes

django queryset pandas dataframe conversion

Django is an incredible Python-based web application with a powerful ORM model that makes rapid-prototyping and data-wrangling a breeze. By default, queries to Django-accessed databases are returned as Django Queryset objects.

Converting such a Queryset object to a Pandas DataFrame can offer several performance benefits as well as keep data in a familiar format when working with common data analysis pipelines. Fortunately, converting a Django Queryset into a Pandas DataFrame is a breeze!

The Scenario

Let’s say you’re doing financial analysis on stock market data—you’ve gathered historic stock prices over a long period of time. These data have been put into a database via Django’s super friendly ORM framework. Such an assortment of data might be represented by a Stock and Price model resembling the following:

class Stock(Model):
    """
    Object model for Stock 
    """
    symbol      = CharField(max_length=8, unique=True)
    
    
class Price(Model):
    """
    Object model for historic stock prices
    """
    # Define core reqs of a price
    date        = DateTimeField()
    opening     = TextField()
    closing     = TextField()
    high        = TextField()
    low         = TextField()
    adj_close   = TextField()
    volume      = TextField()

    # Relate to stock
    stock       = ForeignKey('Stock', on_delete=CASCADE)

The Django Queryset

With the above two models, one would be able to query all historic pricing for a given Stock symbol by doing the following:

# Get the Stock entry
stock       = Stock.objects.filter(symbol="ABCD").first()

# Get the associated historic pricing data
pricing     = Quote.objects.filter(stock=stock)

>>> <QuerySet [<Price: Price object (1)>, <Price: Price object (2)>, ... , <Price: Price object (2500)>

This is an example of Django database querying 101. The Queryset object API provides several easy-to-access methods for doing things like getting the total number of elements, getting the first element, or even filtering based on given criteria.

The Pandas DataFrame

Django is designed to be a web application framework. As such, it’s APIs are designed, largely, with smaller queryset displays, manipulations, and storages in mind. 2,500 stock quotes are still well within Django’s wheelhouse but what if there were 500,000+ entries? Millions? More!?

Pandas is a Python library developed by a financial management group with the expressed intent of providing fast and efficient data analysis. It’s used by data scientists around the world and is one of the most popular Python libraries out there. Among its many data-oriented tools: the DataFrame.

One can think of the DataFrame as a highly-optimized, in-memory spreadsheet with intuitive APIs. That’s a broad simplification but good-enough for our current context. To get a better idea of the Pandas DataFrame, let’s look at how it can be integrated into Django’s Object models:

Explicit Queryset Conversion

Let’s say you’ve queried the database to get all the historic Price data for a given stock as illustrated in the example above—leaving you with a Django Queryset object. The following line of code will convert that into a Pandas DataFrame:

# Convert Django's Queryset into a Pandas DataFrame
pricing_dataframe = pd.DataFrame.from_records(prices.values())

>>>             date   opening   closing      high       low adj_close    volume
0     1340337600  0.002200  0.002200  0.002200  0.002200  0.002200         0
1     1340596800  0.002200  0.002200  0.002200  0.002200  0.002200         0
2     1341460800  0.002200  0.002200  0.002200  0.002200  0.002200         0
3     1341547200  0.002200  0.002200  0.002200  0.002200  0.002200     11000
4     1341806400  0.002200  0.002200  0.002200  0.002200  0.002200         0
...          ...       ...       ...       ...       ...       ...       ...
2140  1608181200  0.005500  0.005000  0.005500  0.004800  0.005000  14304663
2141  1608267600  0.004950  0.004500  0.005300  0.004100  0.004500  15350831
2142  1608526800  0.004950  0.005600  0.005800  0.004200  0.005600  19676276
2143  1608613200  0.007000  0.006000  0.007000  0.005300  0.006000  33831552
2144  1608699600  0.006750  0.006450  0.007000  0.005500  0.006450  21728887

[2145 rows x 7 columns]

Immediately, it’s evident that the DataFrame object provides a better visual representation. This is 100% achievable with Django models, however, provided one takes the time to write some custom __str__ or __repr__ methods.

Custom Model Method Conversion

In my uses of DataFrame objects within a Django setting, I find that when I need to convert one Queryset into a DataFrame—I end up needing to convert many. As such, it would be very repetitive to continually convert Querysets to DataFrames explicitly. Adding a custom method on an object class—even better on a base class—provides an easier approach. Consider a revised version of the Stock class from above:

class Stock(Model):
    """
    Object model for Stock
    """
    symbol      = CharField(max_length=8, unique=True)

    def pricing_dataframe(self) -> DataFrame:
        """
        Get all pricing data, as a Pandas DataFrame object, for a given Stock.
        Returns:
            Pandas DataFrame
        """
        return DataFrame.from_records(Quote.objects.filter(stock=self).values())

This method provides two notable extensions from earlier:

  1. Returns all pricing data for a given stock (could be done with a ManyToMany relationship);
  2. Converts that pricing data into a Pandas DataFrame object.

This code would be put into action with the following approach:

# Get the Stock model
stock = Stock.objects.filter(symbol="ABCD").first()

# Get pricing data
prices = stock.pricing_dataframe()

>>>             date   opening   closing      high       low adj_close    volume
0     1340337600  0.002200  0.002200  0.002200  0.002200  0.002200         0
1     1340596800  0.002200  0.002200  0.002200  0.002200  0.002200         0
2     1341460800  0.002200  0.002200  0.002200  0.002200  0.002200         0
3     1341547200  0.002200  0.002200  0.002200  0.002200  0.002200     11000
4     1341806400  0.002200  0.002200  0.002200  0.002200  0.002200         0
...          ...       ...       ...       ...       ...       ...       ...
2140  1608181200  0.005500  0.005000  0.005500  0.004800  0.005000  14304663
2141  1608267600  0.004950  0.004500  0.005300  0.004100  0.004500  15350831
2142  1608526800  0.004950  0.005600  0.005800  0.004200  0.005600  19676276
2143  1608613200  0.007000  0.006000  0.007000  0.005300  0.006000  33831552
2144  1608699600  0.006750  0.006450  0.007000  0.005500  0.006450  21728887

[2145 rows x 7 columns]

There’s no real performance gain here, but the convenience is nice. Also, by specifying model values in the value() method, one can provide filtered results. For example, let’s say only the date, opening, and adj_close values were needed for most cases. That could be achieved by updating the method on the Stock model as such:

class Stock(Model):
    """
    Object model for Stock
    """
    symbol      = CharField(max_length=8, unique=True)

    def pricing_dataframe(self) -> DataFrame:
        """
        Get all pricing data, as a Pandas DataFrame object, for a given Stock.
        Returns:
            Pandas DataFrame
        """
        return DataFrame.from_records(Quote.objects.filter(stock=self).values(
            "date", "opening", "adj_close"
        ))

Now, if we run the same query as before, a filtered set of data will be returned:

# Get the Stock model
stock = Stock.objects.filter(symbol="ABCD").first()

# Get pricing data
prices = stock.pricing_dataframe()

>>>            date   opening adj_close
0     1340337600  0.002200  0.002200
1     1340596800  0.002200  0.002200
2     1341460800  0.002200  0.002200
3     1341547200  0.002200  0.002200
4     1341806400  0.002200  0.002200
...          ...       ...       ...
2140  1608181200  0.005500  0.005000
2141  1608267600  0.004950  0.004500
2142  1608526800  0.004950  0.005600
2143  1608613200  0.007000  0.006000
2144  1608699600  0.006750  0.006450

[2145 rows x 3 columns]

Final Thoughts

Django is an incredibly powerful web application framework that drives some seriously enterprise-grade projects. For data-centric projects, some of the default approaches may not be optimal for certain workflows. For example, many popular machine learning frameworks like scikit-learn rely on Pandas DataFrame objects.

Being able to easily convert Django querysets into DataFrames can help facilitate such workflows. Personally, I love Django’s ORM so much that I use it for web scraping and data modeling all the time with zero intention of it ever being an application—let alone a web application.

Zack West
Entrepreneur, programmer, designer, and lifelong learner. Can be found taking notes from Mother Nature when not hammering away at the keyboard.