Akshay Jain‍

← Back to all posts

Mapping external data to Django model attributes

Mar ‘18

This post shows how to map custom pandas data frame or CSV/Excel columns to Django model attributes.

This is a very specific problem that I faced as part of a group project for a student analytics platform. I couldn’t find any direct resources to handle this and so this is my attempt to help out anyone else with this same problem. It might be easier to go through the entire code at the bottom of the page to get a better sense of the problem.

I needed to map columns from a CSV or Excel file to attributes in our Django model. The specific problem was to allow a school admin to easily enter student data via an Excel/CSV file upload. However, different schools may have different names for such attributes and there is a need for mapping.

For the complete Django view check the bottom of the page.

Firstly let’s obtain a list of the actual model attributes in our view:

NOTE: For simplicity convert your Excel files to CSV like so:

• A GET request is used to get the path for our file. It can be used like so from another view return redirect(‘/api/fieldmatching?df=’+ path_name)

• “names” is the list of columns from the CSV file. These are what are to be mapped to the “fields”/attributes of our Django model.

• “fieldmatching” is the name of our view and HTML file.

• fields are the concerned model’s attributes.

Next in our fieldmatching.html we can list out our actual field names like so:

We need to allow the user to select one of these options that matches the of of the “names” to one of the actual “fields”.

This should display the ‘name’ of each column from the CSV file and a set of the model attributes as options. The idea is for the user to select the attribute which most closely matches the user’s uploaded file’s column.

The hidden input allows the file’s path to be transferred to the fieldmatching view when the POST request is made.

Going back to our view, we need to change our CSV file to have the same column names as our actual field attribute names. We can do this with a post request.

Setup the path to the CSV file, create a pandas data frame and load the column list as “names” as above.

Now we create a dictionary called “matched”. request.POST.get() gets the attribute name corresponding to each column name. Using pandas rename() function, we rename the old columns (‘names’) with the new columns (‘fields’).

Now time to fill in the model data by converting the data frame to a dictionary and iterating:

This should allow you to create objects with custom CSV/Excel files.

Your view should look like:

The fieldmatching.html should look like:

urls.py should have the following path added:

And that’s it! Feel free to let me know how I can improve my post and let me know if you run into any problem!