{"id":510,"date":"2020-12-25T14:47:41","date_gmt":"2020-12-25T19:47:41","guid":{"rendered":"https:\/\/www.alpharithms.com\/?p=510"},"modified":"2022-06-16T15:24:10","modified_gmt":"2022-06-16T19:24:10","slug":"converting-django-querysets-to-pandas-dataframes","status":"publish","type":"post","link":"https:\/\/www.alpharithms.com\/converting-django-querysets-to-pandas-dataframes-414714\/","title":{"rendered":"Converting Django Querysets to Pandas Dataframes"},"content":{"rendered":"

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.<\/p>\n

Converting such a Queryset<\/code> object to a Pandas DataFrame<\/code> 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<\/code> into a Pandas DataFrame<\/code> is a breeze!<\/p>\n

The Scenario<\/h2>\n

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

class Stock(Model):\r\n    \"\"\"\r\n    Object model for Stock \r\n    \"\"\"\r\n    symbol      = CharField(max_length=8, unique=True)\r\n    \r\n    \r\nclass Price(Model):\r\n    \"\"\"\r\n    Object model for historic stock prices\r\n    \"\"\"\r\n    # Define core reqs of a price\r\n    date        = DateTimeField()\r\n    opening     = TextField()\r\n    closing     = TextField()\r\n    high        = TextField()\r\n    low         = TextField()\r\n    adj_close   = TextField()\r\n    volume      = TextField()\r\n\r\n    # Relate to stock\r\n    stock       = ForeignKey('Stock', on_delete=CASCADE)<\/pre>\n
The Django Queryset<\/h2>\n
With the above two models, one would be able to query all historic pricing for a given Stock<\/code> symbol by doing the following:<\/p>\n
# Get the Stock entry\r\nstock       = Stock.objects.filter(symbol=\"ABCD\").first()\r\n\r\n# Get the associated historic pricing data\r\npricing     = Quote.objects.filter(stock=stock)\r\n\r\n>>> <QuerySet [<Price: Price object (1)>, <Price: Price object (2)>, ... , <Price: Price object (2500)><\/pre>\n
This is an example of Django database querying 101<\/a>. The Queryset<\/code> 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.<\/p>\n
The Pandas DataFrame<\/h2>\n
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<\/em> within Django’s wheelhouse but what if there were 500,000+ entries? Millions? More!?<\/p>\n
Pandas is a Python library developed by a financial management group with the expressed intent of providing fast and efficient data analysis<\/a>. 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<\/a>.<\/p>\n
One can think of the DataFrame<\/code> 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:<\/p>\n
Explicit Queryset Conversion<\/h3>\n
Let’s say you’ve queried the database to get all the historic Price<\/code> data for a given stock as illustrated in the example above\u2014leaving you with a Django Queryset<\/code> object. The following line of code will convert that into a Pandas DataFrame<\/code>:<\/p>\n
# Convert Django's Queryset into a Pandas DataFrame\r\npricing_dataframe = pd.DataFrame.from_records(prices.values())\r\n\r\n>>>             date   opening   closing      high       low adj_close    volume\r\n0     1340337600  0.002200  0.002200  0.002200  0.002200  0.002200         0\r\n1     1340596800  0.002200  0.002200  0.002200  0.002200  0.002200         0\r\n2     1341460800  0.002200  0.002200  0.002200  0.002200  0.002200         0\r\n3     1341547200  0.002200  0.002200  0.002200  0.002200  0.002200     11000\r\n4     1341806400  0.002200  0.002200  0.002200  0.002200  0.002200         0\r\n...          ...       ...       ...       ...       ...       ...       ...\r\n2140  1608181200  0.005500  0.005000  0.005500  0.004800  0.005000  14304663\r\n2141  1608267600  0.004950  0.004500  0.005300  0.004100  0.004500  15350831\r\n2142  1608526800  0.004950  0.005600  0.005800  0.004200  0.005600  19676276\r\n2143  1608613200  0.007000  0.006000  0.007000  0.005300  0.006000  33831552\r\n2144  1608699600  0.006750  0.006450  0.007000  0.005500  0.006450  21728887\r\n\r\n[2145 rows x 7 columns]<\/pre>\n
Immediately, it’s evident that the DataFrame<\/code> object provides a better visual representation. This is 100% achievable with Django models, however, provided one takes the time to write some custom __str__<\/code> or __repr__<\/code> methods.<\/p>\n
Custom Model Method Conversion<\/h3>\n
In my uses of DataFrame<\/code> objects within a Django setting, I find that when I need to convert\u00a0one\u00a0<\/em>Queryset<\/code> into a DataFrame<\/code>\u2014I end up needing to convert\u00a0many<\/em>. As such, it would be very repetitive to continually convert Querysets<\/code> to DataFrames<\/code> explicitly. Adding a custom method on an object class\u2014even better on a base class<\/em>\u2014provides an easier approach. Consider a revised version of the Stock<\/code> class from above:<\/p>\n
class Stock(Model):\r\n    \"\"\"\r\n    Object model for Stock\r\n    \"\"\"\r\n    symbol      = CharField(max_length=8, unique=True)\r\n\r\n    def pricing_dataframe(self) -> DataFrame:\r\n        \"\"\"\r\n        Get all pricing data, as a Pandas DataFrame object, for a given Stock.\r\n        Returns:\r\n            Pandas DataFrame\r\n        \"\"\"\r\n        return DataFrame.from_records(Quote.objects.filter(stock=self).values())<\/pre>\n
This method provides two notable extensions from earlier:<\/p>\n
    \n
  1. Returns all pricing data for a given stock (could be done with a ManyToMany relationship);<\/li>\n
  2. Converts that pricing data into a Pandas DataFrame object.<\/li>\n<\/ol>\n
    This code would be put into action with the following approach:<\/p>\n
    # Get the Stock model\r\nstock = Stock.objects.filter(symbol=\"ABCD\").first()\r\n\r\n# Get pricing data\r\nprices = stock.pricing_dataframe()\r\n\r\n>>>             date   opening   closing      high       low adj_close    volume\r\n0     1340337600  0.002200  0.002200  0.002200  0.002200  0.002200         0\r\n1     1340596800  0.002200  0.002200  0.002200  0.002200  0.002200         0\r\n2     1341460800  0.002200  0.002200  0.002200  0.002200  0.002200         0\r\n3     1341547200  0.002200  0.002200  0.002200  0.002200  0.002200     11000\r\n4     1341806400  0.002200  0.002200  0.002200  0.002200  0.002200         0\r\n...          ...       ...       ...       ...       ...       ...       ...\r\n2140  1608181200  0.005500  0.005000  0.005500  0.004800  0.005000  14304663\r\n2141  1608267600  0.004950  0.004500  0.005300  0.004100  0.004500  15350831\r\n2142  1608526800  0.004950  0.005600  0.005800  0.004200  0.005600  19676276\r\n2143  1608613200  0.007000  0.006000  0.007000  0.005300  0.006000  33831552\r\n2144  1608699600  0.006750  0.006450  0.007000  0.005500  0.006450  21728887\r\n\r\n[2145 rows x 7 columns]<\/pre>\n
    There’s no real performance gain here, but the convenience is nice. Also, by specifying model values in the value()<\/code> method, one can provide filtered results. For example, let’s say only the date<\/code>, opening<\/code>, and adj_close<\/code> values were needed for most cases. That could be achieved by updating the method on the Stock<\/code> model as such:<\/p>\n
    class Stock(Model):\r\n    \"\"\"\r\n    Object model for Stock\r\n    \"\"\"\r\n    symbol      = CharField(max_length=8, unique=True)\r\n\r\n    def pricing_dataframe(self) -> DataFrame:\r\n        \"\"\"\r\n        Get all pricing data, as a Pandas DataFrame object, for a given Stock.\r\n        Returns:\r\n            Pandas DataFrame\r\n        \"\"\"\r\n        return DataFrame.from_records(Quote.objects.filter(stock=self).values(\r\n            \"date\", \"opening\", \"adj_close\"\r\n        ))<\/pre>\n
    Now, if we run the same query as before, a filtered set of data will be returned:<\/p>\n
    # Get the Stock model\r\nstock = Stock.objects.filter(symbol=\"ABCD\").first()\r\n\r\n# Get pricing data\r\nprices = stock.pricing_dataframe()\r\n\r\n>>>            date   opening adj_close\r\n0     1340337600  0.002200  0.002200\r\n1     1340596800  0.002200  0.002200\r\n2     1341460800  0.002200  0.002200\r\n3     1341547200  0.002200  0.002200\r\n4     1341806400  0.002200  0.002200\r\n...          ...       ...       ...\r\n2140  1608181200  0.005500  0.005000\r\n2141  1608267600  0.004950  0.004500\r\n2142  1608526800  0.004950  0.005600\r\n2143  1608613200  0.007000  0.006000\r\n2144  1608699600  0.006750  0.006450\r\n\r\n[2145 rows x 3 columns]<\/pre>\n
    Final Thoughts<\/h2>\n
    Django is an incredibly powerful web application framework that drives some\u00a0seriously<\/em> enterprise-grade projects.<\/a> 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<\/code> rely on Pandas DataFrame objects.<\/p>\n
    Being able to easily convert Django querysets into DataFrames<\/code> can help facilitate such workflows. Personally, I love Django’s ORM so much that I use it for web scraping and data modeling all<\/em> the time with\u00a0zero<\/em> intention of it ever being an application\u2014let alone a web\u00a0<\/em>application.<\/p>\n","protected":false},"excerpt":{"rendered":"
    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 […]<\/p>\n","protected":false},"author":2,"featured_media":512,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"inline_featured_image":false,"footnotes":""},"categories":[655,10],"tags":[211,155,214,213,216,210,143,215,212],"acf":[],"_links":{"self":[{"href":"https:\/\/www.alpharithms.com\/wp-json\/wp\/v2\/posts\/510"}],"collection":[{"href":"https:\/\/www.alpharithms.com\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.alpharithms.com\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.alpharithms.com\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.alpharithms.com\/wp-json\/wp\/v2\/comments?post=510"}],"version-history":[{"count":3,"href":"https:\/\/www.alpharithms.com\/wp-json\/wp\/v2\/posts\/510\/revisions"}],"predecessor-version":[{"id":514,"href":"https:\/\/www.alpharithms.com\/wp-json\/wp\/v2\/posts\/510\/revisions\/514"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.alpharithms.com\/wp-json\/wp\/v2\/media\/512"}],"wp:attachment":[{"href":"https:\/\/www.alpharithms.com\/wp-json\/wp\/v2\/media?parent=510"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.alpharithms.com\/wp-json\/wp\/v2\/categories?post=510"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.alpharithms.com\/wp-json\/wp\/v2\/tags?post=510"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}