Pandas columns headers split

Question

I have a dataframe with colums header made up of 3 tags which are split by '__'

E.g

   A__2__66    B__4__45
0
1
2
3
4
5

I know I cant split the header and just use the first tag with this code; df.columns=df.columns.str.split('__').str[0]

giving:

Is there a way I can use a combination of the tags, for example 1 and 3.

giving

   A__66    B__45
0
1
2
3
4
5

I've trided the below but its not working

df.columns=df.columns.str.split('__').str[0]+'__'+df.columns.str.split('__').str[2]

RomanPerekhrest · Accepted Answer · 2019-11-13 15:43:34Z

4

With specific regex substitution:

In [124]: df.columns.str.replace(r'__[^_]+__', '__')                                                                          
Out[124]: Index(['A__66', 'B__45'], dtype='object')

answered Nov 13, 2019 at 15:43

RomanPerekhrest

93.1k4 gold badges75 silver badges112 bronze badges

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

Comments

jezrael · Accepted Answer · 2019-11-13 15:45:05Z

3

Use Index.map with f-strings for select first and third values of lists:

df.columns = df.columns.str.split('__').map(lambda x: f'{x[0]}__{x[2]}')
print (df)
   A__66  B__45
0    NaN    NaN
1    NaN    NaN
2    NaN    NaN
3    NaN    NaN
4    NaN    NaN
5    NaN    NaN

answered Nov 13, 2019 at 15:45

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

1 Comment

jezrael Over a year ago

@Erfan - yop, if need last value of lists then it is necessary.

anky · Accepted Answer · 2019-11-13 15:50:12Z

2

Also you can try split and join:

df.columns=['__'.join((i[0],i[-1])) for i in df.columns.str.split('__')]
#Columns: [A__66, B__45]

answered Nov 13, 2019 at 15:50

anky

75.3k11 gold badges46 silver badges76 bronze badges

Comments

Erfan · Accepted Answer · 2019-11-13 15:53:57Z

2

I found your own solution perfectly fine, and probably most readable. Just needs a little adjustment

df.columns = df.columns.str.split('__').str[0] + '__' + df.columns.str.split('__').str[-1]

Index(['A__66', 'B__45'], dtype='object')

Or for the sake of efficiency, we do not want to call str.split twice:

lst_split = df.columns.str.split('__')
df.columns = lst_split.str[0] + '__' + lst_split.str[-1]

Index(['A__66', 'B__45'], dtype='object')

answered Nov 13, 2019 at 15:53

Erfan

43.3k10 gold badges75 silver badges86 bronze badges

Collectives™ on Stack Overflow

Pandas columns headers split

4 Answers 4

Comments

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related