Pandas: What's the quickest way to find spaces + unusal characters in column values?

user_12689598 0
Viewed 188

in Pandas, I have a column col_one that originally contained comma-seperated values in each cell.

['a, b, e, g, o', 'a, b, d', 'a, b, c, f, g', 'a, b, c, f', 'a, c, e', 'a, b, c, o', 'b, c, h, n', 'a, b, c, g, o', 'a, b, c, f', 'a, b, c, g, h, o', 'b', 'a, b, f, m', 'a, b, c, g, h', 'a, b, d, f, g', 'a, c, n', 'j', 'b, c, f', 'a, b, g, l', 'b', 'b', 'a, b, d, e ', 'a, b, c', 'a, b, e, g', 'a, b, c, d, f, g', 'd, k, l', 'a, b, c, f, g ', 'a, b, c, f', 'a, b, c, d,  g', 'b, d, e', 'b, d', 'a', 'b, o', 'c, o', 'b, c, o', 'c', 'a, g, i', 'b, c, n', 'a, b', 'b, c, o, n', 'b, c, h', 'a, b, c, f, g, h', 'a, b, c, d', 'a, b, d', 'a, e, g', 'a, b, c, e, g, k, m', 'b, c, o', 'a, b, f, k', 'd, l', 'a, b, l', 'a, b, c', 'a', 'c, d, g, l', 'b, d, e, o', 'b, d', 'a, b, c, d, e, f, o', 'b', 'a, b, c, f', 'b, c, g', 'b, c, g, k', 'a', 'c', 'b, c, o', 'b, c, n, o']

I used str.split(', ').explode().value_counts() .reset_index() to get an count of the individual letters. But in the resulting table, some letters appear twice, presumably because the string contains trailing spaces. Unfortunately these are not visible in the the Jupyter Notebook display of the resulting table as they are just blanks.

Using this

col_one_list = df["letter"].tolist()
print (col_one_list)

gave me a list of all values counted. In this list, I was able to spot a trailing space ("g "). But how could I have done this better?

['b', 'a', 'c', 'g', 'd', 'f', 'o', 'e', 'n', 'h', 'l', 'k', 'm', 'j', 'g ', ' g', 'e ', 'i']
jupyter-notebook pandas data-cleaning python
2 Answers

You can replace the white spaces with '' then continue with split-explode-value_counts, or you can also use get_dummies:

s.str.replace('\s+', '').str.get_dummies(',').sum()

Output:

a    36
b    49
c    35
d    15
e     9
f    13
g    18
h     5
i     1
j     1
k     4
l     5
m     2
n     5
o    13
dtype: int64
user_42384080

I would look at your exploded series to see which values have trailing spaces:

letter_series = pd.Series(['b', 'a', 'c', 'g', 'd', 'f', 'o', 'e', 'n', 'h', 'l', 'k', 'm', 'j', 'g ', ' g', 'e ', 'i'])    
letter_series.str.endswith(' ')

or see which values are longer than one character

letter_series.str.len()
user_87213890
Related