Clever Geek Handbook

Filter data rows based on column length

I have a pandas dataframe like this:

df = pd.DataFrame([ [1,2], [np.NaN,1], ['test string1', 5]], columns=['A','B'] )

df
              A  B
0             1  2
1           NaN  1
2  test string1  5

I am using pandas 0.20. What is the most efficient way to delete any rows where "any" of its column values ​​have length> 10?

len ('test string1') 12

So, for the above, for example, I expect an output like this:

df
              A  B
0             1  2
1           NaN  1
+3


source to share


4 answers


If based on a column A 

In [865]: df[~(df.A.str.len() > 10)]
Out[865]:
     A  B
0    1  2
1  NaN  1


If based on all columns

In [866]: df[~df.applymap(lambda x: len(str(x)) > 10).any(axis=1)]
Out[866]:
     A  B
0    1  2
1  NaN  1
+3


source 


In [42]: df
Out[42]:
              A  B                         C          D
0             1  2                         2 2017-01-01
1           NaN  1                       NaN 2017-01-02
2  test string1  5  test string1test string1 2017-01-03

In [43]: df.dtypes
Out[43]:
A            object
B             int64
C            object
D    datetime64[ns]
dtype: object

In [44]: df.loc[~df.select_dtypes(['object']).apply(lambda x: x.str.len().gt(10)).any(1)]
Out[44]:
     A  B    C          D
0    1  2    2 2017-01-01
1  NaN  1  NaN 2017-01-02

Explanation:

df.select_dtypes(['object'])

only selects columns object

( str

) dtype:

In [45]: df.select_dtypes(['object'])
Out[45]:
              A                         C
0             1                         2
1           NaN                       NaN
2  test string1  test string1test string1

In [46]: df.select_dtypes(['object']).apply(lambda x: x.str.len().gt(10))
Out[46]:
       A      C
0  False  False
1  False  False
2   True   True


now we can "fill" it like this:

In [47]: df.select_dtypes(['object']).apply(lambda x: x.str.len().gt(10)).any(axis=1)
Out[47]:
0    False
1    False
2     True
dtype: bool

Finally, we can only select rows where the value is False

:

In [48]: df.loc[~df.select_dtypes(['object']).apply(lambda x: x.str.len().gt(10)).any(axis=1)]
Out[48]:
     A  B    C          D
0    1  2    2 2017-01-01
1  NaN  1  NaN 2017-01-02
+3


source


Use the apply series function to save them:

df = df[df['A'].apply(lambda x: len(x) <= 10)]

+1


source


I had to drop the line for Diego's answer to work:

df = df[df['A'].apply(lambda x: len(str(x)) <= 10)]
+1


source






More articles:


All Articles