Replace string with condition in pandas dataframe index

Question

I often use this kind of line which create or replace a column and assign a value according to a condition:

df.loc[df['somecolumn'].str.endswith('_s'), 'somecolumn'] = '_sp'

I would like to do the same thing, but for the index column. My specific question is how do I refer to the index column?

df.loc[df.index.str.endswith('_s'), 'index column name?'] = '_sp'

I tried using df.index.name, but it creates a new column instead of changing the values within the index column.

You could add another line: df = df.set_index('index column name?') — Nick Tallant
– Nick Tallant, Commented Feb 22, 2019 at 16:00
df.columns return the column names, but I want to call the df.index.name without creating a new column — OP40
– OP40, Commented Feb 22, 2019 at 16:02
yes of course I could add a new line with set_index, but it will be 2 lines instead of 1... — OP40
– OP40, Commented Feb 22, 2019 at 16:04

Karn Kumar · Accepted Answer · 2020-05-16 18:17:01Z

5

As i told in the comment section, You don't really need to use index.str.endswith until strictly it needs to be rather use anchors like for start ^ and endswith $ that should do a Job for you.

Just taking @Scott's sample for consideration.

df.index.str.replace(r'_s$', '_sp', regex=True)

I'm retaining this answer here for the sake of posterity ..

edited May 16, 2020 at 18:17

answered Feb 22, 2019 at 16:50

Karn Kumar

8,8343 gold badges32 silver badges61 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Scott Boston · Accepted Answer · 2019-02-22 15:57:30Z

3

IIUC,

import pandas as pd
import numpy as np

df = pd.DataFrame(np.random.randint(0,100,(5,5)), columns=['a_s','b','c_s','d','e'], index=['A','B_s','C','D_s','E_s'])

df.columns = df.columns.str.replace('_s','_sp')
df.index = df.index.str.replace('_s','_sp')

print(df)

Output:

      a_sp   b  c_sp   d   e
A       51  80    48  93  34
B_sp    96  16    73  15  29
C       27  85    35  93  69
D_sp    92  79    90  71  85
E_sp     4  63     2  77  14

answered Feb 22, 2019 at 15:57

Scott Boston

154k15 gold badges160 silver badges207 bronze badges

5 Comments

OP40 Over a year ago

I want to do that with a condition like the one I wrote: if ".str.endswith('_s')"

Scott Boston Over a year ago

There is no need but, you could do something like this: df.index[df.index.str.endswith('_s')].str.replace('_s','_sp')

Karn Kumar Over a year ago

Even better to use regex method df.columns.str.replace(r'_s$', '_sp', regex=True) with startswith ^ and for endswith $.

OP40 Over a year ago

df.index[df.index.str.endswith('_s')].str.replace('_s','_sp') does not work because I need to do df.index = df.index[df.index.str.endswith('_s')].str.replace('_s','_sp') and there is a length mismatch

Karn Kumar Over a year ago

for the Scott +1 as well as i used to follow his tricks as well.

OP40 · Accepted Answer · 2019-02-22 16:48:16Z

2

As suggested by pygo, this does the trick perfectly:

df.index = df.index.str.replace(r'_s$', '_sp', regex=True)

answered Feb 22, 2019 at 16:48

OP40

1351 gold badge2 silver badges6 bronze badges

Collectives™ on Stack Overflow

Replace string with condition in pandas dataframe index

3 Answers 3

Comments

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related