pandas dataframe return first word in string for column

Question

I have a dataframe:

df = pd.DataFrame({'id' : ['abarth 1.4 a','abarth 1 a','land rover 1.3 r','land rover 2',
                           'land rover 5 g','mazda 4.55 bl'], 
                   'series': ['a','a','r','','g', 'bl'] })

I would like to remove the 'series' string from the corresponding id, so the end result should be:

Final result should be 'id': ['abarth 1.4','abarth 1','land rover 1.3','land rover 2','land rover 5', 'mazda 4.55']

Currently I am using df.apply:

df.id = df.apply(lambda x: x['id'].replace(x['series'], ''), axis =1)

But this removes all instances of the strings, even in other words, like so: 'id': ['brth 1.4','brth 1','land ove 1.3','land rover 2','land rover 5', 'mazda 4.55']

Should I somehow mix and match regex with the variable inside df.apply, like so?

df.id = df.apply(lambda x: x['id'].replace(r'\b' + x['series'], ''), axis =1)

piRSquared · Accepted Answer · 2016-05-29 00:33:46Z

33

Use str.split and str.get and assign using loc only where df.make == ''

df.loc[df.make == '', 'make'] = df.id.str.split().str.get(0)

print df

               id    make
0      abarth 1.4  abarth
1        abarth 1  abarth
2  land rover 1.3   rover
3    land rover 2   rover
4    land rover 5   rover
5      mazda 4.55   mazda

answered May 29, 2016 at 0:33

piRSquared

296k68 gold badges509 silver badges654 bronze badges

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

Comments

stealthyninja · Accepted Answer · 2020-04-03 19:47:17Z

13

It's simple. Use as follows:

df['make'] = df['id'].str.split(' ').str[0]

edited Apr 3, 2020 at 19:47

stealthyninja

10.4k11 gold badges56 silver badges61 bronze badges

answered Apr 3, 2020 at 19:25

Qazi Basheer

1271 silver badge2 bronze badges

Comments

Veronica · Accepted Answer · 2020-07-20 02:22:34Z

3

IDK why but with the part below

df.loc[df.make == '', 'make']

OR

df.loc[df['make'] == '', 'make']

I get the error - KeyError: 'make'

So instead I did (in case someone sees the same error):

df['make'] = df['id']
df['make'] = df.id.str.split().str.get(0)

Worked for me.

answered Jul 20, 2020 at 2:22

Veronica

1811 silver badge11 bronze badges

Comments

Parfait · Accepted Answer · 2016-05-29 00:07:37Z

1

Consider a regex solution with loc where it extracts everything before first space:

df.loc[df['make']=='', 'make'] = df['id'].str.extract('(.*) ', expand=False)

Alternatively, use numpy's where which allows the if/then/else conditional logic:

df['make'] = np.where(df['make']=='', 
                      df['id'].str.extract('(.*) ', expand=False), 
                      df['make'])

answered May 29, 2016 at 0:07

Parfait

108k19 gold badges102 silver badges138 bronze badges

Comments

Cleb · Accepted Answer · 2016-05-29 00:38:34Z

0

If I got your question correctly you can just use replace function:

df.make = df.make.replace("", test.id)

edited May 29, 2016 at 0:38

Cleb

26.2k23 gold badges128 silver badges164 bronze badges

answered May 28, 2016 at 23:59

Aamir Khan

577 bronze badges

1 Comment

Parfait Over a year ago

OP requires first word of id column.

Collectives™ on Stack Overflow

pandas dataframe return first word in string for column

5 Answers 5

Comments

Comments

Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

Comments

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related