How to stop Pandas adding time to column title after transposing a datetime index?

2
votes

I have a Pandas dataframe as follows:

In [10]: libor_table
Out[10]:
           Euribor interest rate - 3 months Euribor interest rate - 6 months  \
2015-07-17                          -0.019%                           0.049%
2015-07-16                          -0.019%                           0.049%
2015-07-15                          -0.019%                           0.049%
2015-07-14                          -0.019%                           0.049%
2015-07-13                          -0.019%                           0.049%

           GBP LIBOR - 3 months GBP LIBOR - 6 months USD LIBOR - 3 months  \
2015-07-17             0.58375%             0.75406%             0.29175%
2015-07-16             0.58438%             0.75313%             0.28700%
2015-07-15             0.58406%             0.75063%             0.28850%
2015-07-14             0.58219%             0.74250%             0.28850%
2015-07-13             0.58188%             0.73750%             0.28880%

           USD LIBOR - 6 months
2015-07-17             0.46020%
2015-07-16             0.45570%
2015-07-15             0.46195%
2015-07-14             0.46345%
2015-07-13             0.46340%

The index is in datetime:

In [11]: libor_table.index
Out[11]:
DatetimeIndex(['2015-07-17', '2015-07-16', '2015-07-15', '2015-07-14',
               '2015-07-13'],
              dtype='datetime64[ns]', freq=None, tz=None)

My problem is when I then make the table into an HTML table using to_html(). The standard dataframe converts to an HTML table just fine:

<table border="1" class="dataframe">
  <thead>
    <tr style="text-align: right;">
      <th></th>
      <th>Euribor interest rate - 3 months</th>
      <th>Euribor interest rate - 6 months</th>
      <th>GBP LIBOR - 3 months</th>
      <th>GBP LIBOR - 6 months</th>
      <th>USD LIBOR - 3 months</th>
      <th>USD LIBOR - 6 months</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <th>2015-07-17</th>
      <td>-0.019%</td>
      <td>0.049%</td>
      <td>0.58375%</td>
      <td>0.75406%</td>
      <td>0.29175%</td>
      <td>0.46020%</td>
    </tr>
    <tr>
      <th>2015-07-16</th>
      <td>-0.019%</td>
      <td>0.049%</td>
      <td>0.58438%</td>
      <td>0.75313%</td>
      <td>0.28700%</td>
      <td>0.45570%</td>
    </tr>
    <tr>
      <th>2015-07-15</th>
      <td>-0.019%</td>
      <td>0.049%</td>
      <td>0.58406%</td>
      <td>0.75063%</td>
      <td>0.28850%</td>
      <td>0.46195%</td>
    </tr>
    <tr>
      <th>2015-07-14</th>
      <td>-0.019%</td>
      <td>0.049%</td>
      <td>0.58219%</td>
      <td>0.74250%</td>
      <td>0.28850%</td>
      <td>0.46345%</td>
    </tr>
    <tr>
      <th>2015-07-13</th>
      <td>-0.019%</td>
      <td>0.049%</td>
      <td>0.58188%</td>
      <td>0.73750%</td>
      <td>0.28880%</td>
      <td>0.46340%</td>
    </tr>
  </tbody>
</table>

However I would like to tranpose the dataframe for the HTML output - libor_table.transpose().to_html(), when I do so pandas adds the time to the column title like so:

<table border="1" class="dataframe">
  <thead>
    <tr style="text-align: right;">
      <th></th>
      <th>2015-07-17 00:00:00</th>
      <th>2015-07-16 00:00:00</th>
      <th>2015-07-15 00:00:00</th>
      <th>2015-07-14 00:00:00</th>
      <th>2015-07-13 00:00:00</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <th>Euribor interest rate - 3 months</th>
      <td>-0.019%</td>
      <td>-0.019%</td>
      <td>-0.019%</td>
      <td>-0.019%</td>
      <td>-0.019%</td>
    </tr>
    <tr>
      <th>Euribor interest rate - 6 months</th>
      <td>0.049%</td>
      <td>0.049%</td>
      <td>0.049%</td>
      <td>0.049%</td>
      <td>0.049%</td>
    </tr>
    <tr>
      <th>GBP LIBOR - 3 months</th>
      <td>0.58375%</td>
      <td>0.58438%</td>
      <td>0.58406%</td>
      <td>0.58219%</td>
      <td>0.58188%</td>
    </tr>
    <tr>
      <th>GBP LIBOR - 6 months</th>
      <td>0.75406%</td>
      <td>0.75313%</td>
      <td>0.75063%</td>
      <td>0.74250%</td>
      <td>0.73750%</td>
    </tr>
    <tr>
      <th>USD LIBOR - 3 months</th>
      <td>0.29175%</td>
      <td>0.28700%</td>
      <td>0.28850%</td>
      <td>0.28850%</td>
      <td>0.28880%</td>
    </tr>
    <tr>
      <th>USD LIBOR - 6 months</th>
      <td>0.46020%</td>
      <td>0.45570%</td>
      <td>0.46195%</td>
      <td>0.46345%</td>
      <td>0.46340%</td>
    </tr>
  </tbody>
</table>

Why does Pandas do this and is there a way of stopping it?

EDIT: This bug is submitted here.

1
pythonpandas
Don't know why it does this but the following worked for me as a workaround: libor_table.index = libor_table.index.date libor_table.T.to_html() worth posting as an issue: github.com/pydata/pandas/issues – EdChum
That did the trick, thanks. I've never posted an issue to github before - how exciting. – BML91
If you do post an issue, can you edit your question with a link to the issue, thanks – EdChum
I posted the issue here - github.com/pydata/pandas/issues/10640 - let me know if I need to change anything about it, I've never done this before. – BML91
It looks fine to me, you should get a response soon, the pandas devs are very quick to respond I find – EdChum

1 Answers

1
votes

This looks like a bug to me which I can reproduce using a small example:

In [120]:
# generate some dummy data
t="""time,value
2015-07-17,0
2015-07-18,1"""
df = pd.read_csv(io.StringIO(t), parse_dates=True, index_col=[0])
df

Out[120]:
            value
time             
2015-07-17      0
2015-07-18      1

Calling to_html on this works as expected:

In [121]:   
df.to_html()

Out[121]:
'<table border="1" class="dataframe">\n  <thead>\n    <tr style="text-align: right;">\n      <th></th>\n      <th>value</th>\n    </tr>\n    <tr>\n      <th>time</th>\n      <th></th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>2015-07-17</th>\n      <td>0</td>\n    </tr>\n    <tr>\n      <th>2015-07-18</th>\n      <td>1</td>\n    </tr>\n  </tbody>\n</table>'

To workaround the transposed formatting issue you can explicitly set the datetimeindex to just the date:

In [122]:
df.index = df.index.date
df.T.to_html()

Out[122]:
'<table border="1" class="dataframe">\n  <thead>\n    <tr style="text-align: right;">\n      <th></th>\n      <th>2015-07-17</th>\n      <th>2015-07-18</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>value</th>\n      <td>0</td>\n      <td>1</td>\n    </tr>\n  </tbody>\n</table>'