Pandas DataFrame to Markdown

Over the last few months I have been including markdown representations of data frames quite a bit to put in documentation to show what the table outputs look like. Using Jupyter it is not overly difficult to use the df.to_markdown() method, and copy the output in the notebook. Recently I discovered how to use some of the built in capabilities of Pandas to streamline this even further. Pandas has the ability to output directly to the clipboard, but it is designed to output ready to paste into Excel. Fortunately, this can be suppressed fairly easily.

pd.io.clipboards.to_clipboard(df.to_markdown(), excel=False)

That is it. Now you can paste into a markdown file and you have what you need. If you want to run an entire example, try the following.

from arcgis.gis import GIS
from pandas.io.clipboards import to_clipboard

# create a dataframe from zip code centroids on ArcGIS Online
itm_id = 'dc123f738bf846779c49db6472f82a4b'
lyr = GIS().content.get(itm_id).layers[0]
df = lyr.query("ZIP_TYPE = 'Zip Code Area'", 
               out_fields=['ZIP_CODE', 'PO_NAME', 'STATE', 'SQMI'],
               return_geometry=False,
               out_sr=4326).sdf

# pull off a sample to display
smpl_df = df.sample(5)

# put the sample into the clipboard
to_clipboard(smpl_df.to_markdown(), excel=False)

Now, all you have to do is paste the markdown to display the table. True, you can select, copy and paste manually, but I really like this because it saves trying to scroll around while selecting.

This is what the result looks like when rendered here in the Ghost blog. Admittedly, it does not look great, but that is a styling issue.

Thankfully, depending on where you want to display the data, it is not hard to do the same thing with html either.

to_clipboard(smpl_df.to_html(), excel=False)

Just like before, here is what it looks like.

Although it does not look dramatically different, this really is the point. I was able to output the table in two different formats for rendering, and it took seconds to do.

Hopefully this simple hack makes your life a little easier as well.