Pandas - how to remove spaces in each column in a dataframe?
<p>I'm trying to remove spaces, apostrophes, and double quote in each column data using this for loop</p>

<p><code>for c in data.columns:
    data[c] = data[c].str.strip().replace(',', '').replace('\'', '').replace('\"', '').strip()</code></p>

<p>but I keep getting this error:</p>

<p><code>AttributeError: 'Series' object has no attribute 'strip'</code></p>

<p>data is the data frame and was obtained from an excel file</p>

<p><code>xl = pd.ExcelFile('test.xlsx');
data = xl.parse(sheetname='Sheet1')</code></p>

<p>Am I missing something?  I added the <code>str</code> but that didn't help.  Is there a better way to do this.</p>

<p>I don't want to use the column labels, like so <code>data['column label']</code>, because the text can be different.  I would like to iterate each column and remove the characters mentioned above.</p>

<p>incoming data:</p>

<p><code>id        city      country
1       Ontario       Canada
2     Calgary '        Canada'
3   'Vancouver        Canada</code></p>

<p>desired output:</p>

<p><code>id  city      country
1   Ontario     Canada
2   Calgary     Canada
3   Vancouver   Canada</code></p>

<p><strong>UPDATE:</strong> using your sample DF:</p>

<pre><code>In [80]: df
Out[80]:
   id         city   country
0   1      Ontario    Canada
1   2    Calgary '   Canada'
2   3  'Vancouver    Canada

In [81]: df.replace(r'[,\"\']','', regex=True).replace(r'\s*([^\s]+)\s*', r'\1', regex=True)
Out[81]:
   id       city country
0   1    Ontario  Canada
1   2    Calgary  Canada
2   3  Vancouver  Canada
</code></pre>

<p><strong>OLD answer:</strong></p>

<p>you can use <code>DataFrame.replace()</code> method:</p>

<pre><code>In [75]: df.to_dict('r')
Out[75]:
[{'a': ' x,y ', 'b': 'a"b"c', 'c': 'zzz'},
 {'a': "x'y'z", 'b': 'zzz', 'c': '  ,s,,'}]


In [76]: df
Out[76]:
       a      b       c
0   x,y   a"b"c     zzz
1  x'y'z    zzz    ,s,,

In [77]: df.replace(r'[,\"\']','', regex=True).replace(r'\s*([^\s]+)\s*', r'\1', regex=True)
Out[77]:
     a    b    c
0   xy  abc  zzz
1  xyz  zzz    s
</code></pre>

<p><code>r'\1'</code> - is a <a href="http://www.regular-expressions.info/replacebackref.html" rel="nofollow">numbered capturing RegEx group</a></p>

python pandas