Strip whitespace from strings in a column

Question

I am using python csvkit to compare 2 files like this:

df1 = pd.read_csv('input1.csv', sep=',\s+', delimiter=',', encoding="utf-8")
df2 = pd.read_csv('input2.csv', sep=',\s,', delimiter=',', encoding="utf-8")
df3 = pd.merge(df1,df2, on='employee_id', how='right')
df3.to_csv('output.csv', encoding='utf-8', index=False)

Currently I am running the file through a script before hand that strips spaces from the employee_id column.

An example of employee_ids:

Is there a way to get csvkit to do it and save me a step?

Trenton McKinney · Accepted Answer · 2023-08-12 20:45:40Z

151

You can strip() an entire Series in Pandas using .str.strip():

df1['employee_id'] = df1['employee_id'].str.strip()
df2['employee_id'] = df2['employee_id'].str.strip()

This will remove leading/trailing whitespaces on the employee_id column in both df1 and df2

Alternatively, modify the read_csv lines to use skipinitialspace=True

df1 = pd.read_csv('input1.csv', sep=',\s+', delimiter=',', encoding="utf-8", skipinitialspace=True)
df2 = pd.read_csv('input2.csv', sep=',\s,', delimiter=',', encoding="utf-8", skipinitialspace=True)

It looks like you are attempting to remove spaces in a string containing numbers, which can be accomplished with pandas.Series.str.replace:

df1['employee_id'] = df1['employee_id'].str.replace(" ", "")
df2['employee_id'] = df2['employee_id'].str.replace(" ", "")

edited Aug 12, 2023 at 20:45

Trenton McKinney

63.2k41 gold badges170 silver badges213 bronze badges

answered Apr 10, 2017 at 20:11

Andy♦

50.8k62 gold badges181 silver badges240 bronze badges

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

Comments

Stephen Rauch · Accepted Answer · 2017-04-10 20:22:42Z

37

You can do the strip() in pandas.read_csv() as:

pandas.read_csv(..., converters={'employee_id': str.strip})

And if you need to only strip leading whitespace:

pandas.read_csv(..., converters={'employee_id': str.lstrip})

And to remove all spaces:

def strip_spaces(a_str_with_spaces):
    return a_str_with_spaces.replace(' ', '')

pandas.read_csv(..., converters={'employee_id': strip_spaces})

edited Apr 10, 2017 at 20:22

answered Apr 10, 2017 at 20:15

Stephen Rauch♦

50.1k32 gold badges118 silver badges143 bronze badges

2 Comments

t3chb0t Over a year ago

Would it be pythonic if I used my own converter that either returns the result of str.strip or None? I'm importing data from Excel and it would be great if I could turn empty cells into None without additional steps, but I'm not sure whether this kind of magic is legal.

Stephen Rauch Over a year ago

dataframe cells can be None if desired.

Andy · Accepted Answer · 2017-04-10 20:12:09Z

12

Df['employee']=Df['employee'].str.strip()

edited Apr 10, 2017 at 20:12

Andy♦

50.8k62 gold badges181 silver badges240 bronze badges

answered Apr 10, 2017 at 20:11

Vipin

5,2833 gold badges44 silver badges70 bronze badges

Collectives™ on Stack Overflow

Strip whitespace from strings in a column

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related