Midrule in LaTeX output of Python Pandas

Question

I'm using Python Pandas.

I'm trying to automate the creation of LaTeX tables from excel workbooks. I so far have the script complete to create the following dataframe:

            Date        Factor A    Factor B    Total
Person A    01/01/2015  A           C           $220m
Person B    01/02/2015  B           D           $439m
                                    Total       $659m

I can use Pandas .to_latex() command to create a booktabs table from this, which is all fine.

My question is, is it possible to add a midrule just before the last row of the dataframe above to the LaTeX output?

Answer 1

Since pandas' .to_latex() does not seem to deliver such an option I would to this manually by some string handling:

import pandas as pd
import numpy as np

# use a DataFrame df with some sample data
df = pd.DataFrame(np.random.random((5, 5)))

# get latex string via `.to_latex()`
latex = df.to_latex()

# split lines into a list
latex_list = latex.splitlines()

# insert a `\midrule` at third last position in list (which will be the fourth last line in latex output)
latex_list.insert(len(latex_list)-3, '\midrule')

# join split lines to get the modified latex output string
latex_new = '\n'.join(latex_list)

Latex output without additional \midrule:

\begin{tabular}{lrrrrr}
\toprule
{} &         0 &         1 &         2 &         3 &         4 \\
\midrule
0 &  0.563803 &  0.962439 &  0.572583 &  0.567999 &  0.390899 \\
1 &  0.728756 &  0.452122 &  0.358927 &  0.426866 &  0.234689 \\
2 &  0.907841 &  0.622264 &  0.128458 &  0.098953 &  0.711350 \\
3 &  0.338298 &  0.576341 &  0.625921 &  0.139799 &  0.146484 \\
4 &  0.303568 &  0.495921 &  0.835966 &  0.583697 &  0.675465 \\
\bottomrule
\end{tabular}

Output with manually added \midrule:

\begin{tabular}{lrrrrr}
\toprule
{} &         0 &         1 &         2 &         3 &         4 \\
\midrule
0 &  0.563803 &  0.962439 &  0.572583 &  0.567999 &  0.390899 \\
1 &  0.728756 &  0.452122 &  0.358927 &  0.426866 &  0.234689 \\
2 &  0.907841 &  0.622264 &  0.128458 &  0.098953 &  0.711350 \\
3 &  0.338298 &  0.576341 &  0.625921 &  0.139799 &  0.146484 \\
\midrule
4 &  0.303568 &  0.495921 &  0.835966 &  0.583697 &  0.675465 \\
\bottomrule
\end{tabular}