Pandas - Strip white space

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?

`df1.employee_id = df1.employee_id.str.strip()` – Taylor Apr 10 '17 at 20:10 — Taylor, Apr 10 '17 at 20:10
What about `skipinitialspace=True` in `read_csv`? – Jammeth_Q Apr 10 '17 at 20:12 — Jammeth_Q, Apr 10 '17 at 20:12

Andy · Accepted Answer · 2017-04-10T20:21:21.090

100

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, you can modify your read_csv lines to also 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. You can do this by:

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

edited Apr 10 '17 at 20:21

answered Apr 10 '17 at 20:11

Andy

46,308
56
161
219

Would this approach still work if the space was not either trailing or leading? ie '23 4883 2'? – fightstarr20 Apr 10 '17 at 20:14
No. `strip()` only works on leading and trailing white space. – Andy Apr 10 '17 at 20:16
Can I use regex or similar instead? – fightstarr20 Apr 10 '17 at 20:18
1

@fightstarr20, See my latest edit. That replaces spaces with nothing. Does that accomplish what you are looking for? Your column will still be a string, but you can solve that by using `astype(int)` after the spaces have been removed. – Andy Apr 10 '17 at 20:22
That is perfect, thank you for the examples, i am sure the split() solution will come in handy at some point as well – fightstarr20 Apr 10 '17 at 20:27
@Andy thanks for your great answer. skipinitialspace=True was a great suggestion! – Kalle Oct 25 '19 at 08:53
Had a question - do I have to assign to new variable or can I just do something like `test['stripvaluesfromcol'].str.strip()` – Jonnyboi Oct 15 '21 at 17:52

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

26

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

44,696
30
102
125

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

8

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

edited Apr 10 '17 at 20:12

Andy

46,308
56
161
219

answered Apr 10 '17 at 20:11

Vipin

4,443
3
32
57

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

7

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

81
1
1

Pandas - Strip white space

4 Answers4

Linked

Related