Introduction

Whenever I am doing analysis with pandas my first goal is to get data into a panda’s DataFrame using one of the many available options. For the vast majority of instances, I use read_excel , read_csv , or read_sql .

However, there are instances when I just have a few lines of data or some calculations that I want to include in my analysis. In these cases it is helpful to know how to create DataFrames from standard python lists or dictionaries. The basic process is not difficult but because there are several different options it is helpful to understand how each works. I can never remember whether I should use from_dict , from_records , from_items or the default DataFrame constructor. Normally, through some trial and error, I figure it out. Since it is still confusing to me, I thought I would walk through several examples below to clarify the different approaches. At the end of the article, I briefly show how this can be useful when generating Excel reports.

DataFrames from Python Structures

There are multiple methods you can use to take a standard python datastructure and create a panda’s DataFrame. For the purposes of these examples, I’m going to create a DataFrame with 3 months of sales information for 3 fictitious companies.

import pandas as pd
from collections import OrderedDict
from datetime import date

sales = [{'account': 'Jones LLC', 'Jan': 150, 'Feb': 200, 'Mar': 140},
         {'account': 'Alpha Co',  'Jan': 200, 'Feb': 210, 'Mar': 215},
         {'account': 'Blue Inc',  'Jan': 50,  'Feb': 90,  'Mar': 95 }]
df = pd.DataFrame(sales)

sales = {'account': ['Jones LLC', 'Alpha Co', 'Blue Inc'],
         'Jan': [150, 200, 50],
         'Feb': [200, 210, 90],
         'Mar': [140, 215, 95]}
df = pd.DataFrame.from_dict(sales)

df = df[['account', 'Jan', 'Feb', 'Mar']]

sales = OrderedDict([ ('account', ['Jones LLC', 'Alpha Co', 'Blue Inc']),
          ('Jan', [150, 200, 50]),
          ('Feb',  [200, 210, 90]),
          ('Mar', [140, 215, 95]) ] )
df = pd.DataFrame.from_dict(sales)

sales = [('Jones LLC', 150, 200, 50),
         ('Alpha Co', 200, 210, 90),
         ('Blue Inc', 140, 215, 95)]
labels = ['account', 'Jan', 'Feb', 'Mar']
df = pd.DataFrame.from_records(sales, columns=labels)

sales = [('account', ['Jones LLC', 'Alpha Co', 'Blue Inc']),
         ('Jan', [150, 200, 50]),
         ('Feb', [200, 210, 90]),
         ('Mar', [140, 215, 95]),
         ]
df = pd.DataFrame.from_items(sales)

Keeping the Options Straight

In order to keep the various options clear in my mind, I put together this simple graphic to show the dictionary vs. list options as well as row vs. column oriented approaches. It’s a 2X2 grid so I hope all the consultants are impressed!

For the sake of simplicity, I am not showing the OrderedDict approach because the from_items approach is probably a more likely real world solution.

If this is a little hard to read, you can also get the PDF version.

Simple Example

This may seem like a lot of explaining for a simple concept. However, I frequently use these approaches to build small DataFrames that I combine with my more complicated analysis.

For one example, let’s say we want to save our DataFrame and include a footer so we know when it was created and who it was created by. This is much easier to do if we populate a DataFrame and write it to Excel than if we try to write individual cells to Excel.

Take our existing DataFrame:

sales = [('account', ['Jones LLC', 'Alpha Co', 'Blue Inc']),
         ('Jan', [150, 200, 50]),
         ('Feb', [200, 210, 90]),
         ('Mar', [140, 215, 95]),
         ]
df = pd.DataFrame.from_items(sales)

Now build a footer (in a column oriented manner):

from datetime import date

create_date = "{:%m-%d-%Y}".format(date.today())
created_by = "CM"
footer = [('Created by', [created_by]), ('Created on', [create_date]), ('Version', [1.1])]
df_footer = pd.DataFrame.from_items(footer)

Combine into a single Excel sheet:

writer = pd.ExcelWriter('simple-report.xlsx', engine='xlsxwriter')
df.to_excel(writer, index=False)
df_footer.to_excel(writer, startrow=6, index=False)
writer.save()

The secret sauce here is to use startrow to write the footer DataFrame below the sales DataFrame. There is also a corresponding startcol so you can control the column layout as well. This allows for a lot of flexibility with the basic to_excel function.

Summary

Most pandas users quickly get familiar with ingesting spreadsheets, CSVs and SQL data. However, there are times when you will have data in a basic list or dictionary and want to populate a DataFrame. Pandas offers several options but it may not always be immediately clear on when to use which ones.

There is no one approach that is “best”, it really depends on your needs. I tend to like the list based methods because I normally care about the ordering and the lists make sure I preserve the order. The most important thing is to know the options are available so you can be smart about using the simplest one for your specific case.

On the surface, these samples may seem simplistic but I do find that it is pretty common that I use these methods to generate quick snippets of information that can augment or clarify the more complex analysis. The nice thing about data in a DataFrame is that it is very easy to convert into other formats such as Excel, CSV, HTML, LaTeX, etc. This flexibility is really handy for ad-hoc report generation.

Updates

• 19-Nov-2018: As of pandas 0.23, DataFrame.from_items() has been deprecated. You can use DataFrame.from_dict(dict(items)) instead. If you want to preserve order, you can use DataFrame.from_dict(OrderedDict(items))