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?

score 136 · Accepted Answer · edited Aug 12 '23 at 20:45

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(" ", "")

Stephen Rauch · Answer 2 · 2017-04-10T20:22:42.880

35

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 '17 at 20:22

answered Apr 10 '17 at 20:15

Stephen Rauch

47,830
31
106
135

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_. – t3chb0t Sep 30 '22 at 18:38
dataframe cells can be None if desired. – Stephen Rauch Oct 01 '22 at 18:33

score 10 · Answer 3 · edited Apr 10 '17 at 20:12

10

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

edited Apr 10 '17 at 20:12

Andy

49,085
60
166
233

answered Apr 10 '17 at 20:11

Vipin

4,851
3
35
65

score 8 · Answer 4 · answered Apr 05 '20 at 21:33

8

The best and easiest way to remove blank whitespace in pandas dataframes is :-

df1 = pd.read_csv('input1.csv')

df1["employee_id"]  = df1["employee_id"].str.strip()

That's it

answered Apr 05 '20 at 21:33

Saeed Khan

109
1
2

score 2 · Answer 5 · answered Jun 09 '22 at 19:35

2

In a dataframe (df) there may be multiple column name that have ' SPACE '. One of the general and easy way to do away with that is :- (df is the dataframe)

df.columns = df.columns.str.replace(' ', '')

answered Jun 09 '22 at 19:35

Sourav Ghosh

39
3

1

This is not strip. this makes "f oo" to "foo" – Gabrial David Nov 29 '22 at 19:08

JoeSchr · Answer 6 · 2023-03-14T15:22:27.830

2

also there is skipinitialspace

Skip spaces after delimiter.

from: https://pandas.pydata.org/docs/reference/api/pandas.read_csv.html

Example:

df1 = pd.read_csv('input1.csv', skipinitialspace=True)

edited Mar 14 '23 at 15:22

answered Feb 07 '23 at 14:20

JoeSchr

1,067
1
9
14

Strip whitespace from strings in a column

6 Answers6

Linked

Related