Python Pandas Dataframe Set specific datetime row label to string in index?

Question

My Pandas dataframe code

import pandas as pd
df = pd.DataFrame({'Impressions': [92964, 91282, 88143,272389], 'Clicks': [3128, 3131, 2580, 8839]}, index=pd.to_datetime(['6/1/2015', '6/8/2015', '6/15/2015', '1/1/2020']))
df.index.name = 'Date'

Produces

            Clicks  Impressions
Date                           
2015-06-01    3128        92964
2015-06-08    3131        91282
2015-06-15    2580        88143
2020-01-01    8839       272389

How can I change the 2020-01-01 to be a string that says Total?

What I want to achieve is this:

            Clicks  Impressions
Date                           
2015-06-01    3128        92964
2015-06-08    3131        91282
2015-06-15    2580        88143
Total         8839       272389

More context df.index.dtype is of datatype dtype('<M8[ns]') I think I can access the index row label by this df.index[-1] which tells me it's a Timestamp('2020-01-01 00:00:00').

But if I try to do something like this, it does not work: df.index[-1] = 'Total'

Error:

Traceback (most recent call last):
  File "<pyshell#8>", line 1, in <module>
    df.index[-1] = 'Total'
  File "C:\Python34\lib\site-packages\pandas\core\index.py", line 922, in __setitem__
    raise TypeError("Indexes does not support mutable operations")
TypeError: Indexes does not support mutable operations

This post seems to have what you are looking for: http://stackoverflow.com/questions/19851005/rename-pandas-dataframe-index — Aziz Alto, Jun 23 '15 at 17:34
Thanks for the tip. Ultimately, `df.rename(index={df.index[-1]: 'Total'})` did change the index value to `Total` on this sample data I posted in my question. But, when I attempt to use it in my primary app I'm working on, it didn't work for me. I think @bleh is right on that the issue is handling multiple data types in the same array. Thanks nevertheless. — Jarad, Jun 23 '15 at 18:45

kennes · Accepted Answer · 2015-06-23T18:22:19.243

Here's one way to do that:

In [154]: %paste
import pandas as pd
df = pd.DataFrame({'Impressions': [92964, 91282, 88143,272389], 'Clicks': [3128, 3131, 2580, 8839]}, index=pd.to_datetime(['6/1/2015', '6/8/2015', '6/15/2015', '1/1/2020']))
df.index.name = 'Date'

## -- End pasted text --

In [155]: df = df.reset_index()

In [156]: df['Date'] = df['Date'].astype(object)

In [157]: df['Date'] = df.Date.dt.date

In [158]: df.ix[3,0] = 'Total'

In [159]: df.index = df.Date

In [160]: df.drop(['Date'], axis=1, inplace=True)

In [161]: df
Out[161]: 
            Clicks  Impressions
Date                           
2015-06-01    3128        92964
2015-06-08    3131        91282
2015-06-15    2580        88143
Total         8839       272389

The issue is trying to handle multiple data types in the same array. You need to cast the series into an object type.

score 0 · Answer 2 · answered Dec 13 '20 at 19:51

Couple years later, in hindsight, I was approaching this problem without knowing that Pandas pivot table has a margins parameter which can add a label like "total" and provide the sum totals row.

df.head(3).pivot_table(['Impressions', 'Clicks'], index=df.index[:-1],
                       aggfunc='sum', margins=True, margins_name='Total')

                     Clicks  Impressions
Date                                    
2015-06-01 00:00:00    3128        92964
2015-06-08 00:00:00    3131        91282
2015-06-15 00:00:00    2580        88143
Total                  8839       272389

But to more accurately answer the question of "How to assign a specific label to a datetime index at a certain row", you basically can't because the entire index is a datatype of DatetimeIndex so all elements need to be this type.

To get around this fact, you can do this:

idx = df.index.strftime('%Y-%m-%d')
idx.values[-1] = 'Total'
df.index = idx

Result

            Impressions  Clicks
2015-06-01        92964    3128
2015-06-08        91282    3131
2015-06-15        88143    2580
Total            272389    8839

Python Pandas Dataframe Set specific datetime row label to string in index?

2 Answers2