How to get/check number of digits of a row in a df.query()?

Question

I have a dataframe df like:

number | col1
123    | a
1234   | b
567    | c

I want to count how many rows have more or less than 3 digits in column "number". The column as dtype = int64.

When I try:

count = df.query(
    """
    ... 'some other filters' or \
    number.str.len() != 3
    """
)
print(count)

I get:

AttributeError: Can only use .str accessor with string values!

Trying to convert to str() with

count = df.query(
    """
    ... 'some other filters' or \
    str(number).str.len() != 3
    """
)
print(count)

ValueError: "str" is not a supported function

Converting to string would also count negative signs so that's not really a solution anyway.

Removing that check for len = 3 removes all error messages and prints the count.

How can I count the digits in a dataframe query without converting to str and without apply (way too slow for my amount of rows)?

Paul Brennan · Answer 1 · 2020-12-14T13:01:44.623

3

df = pd.DataFrame({'number':[123,1234,567], 'col' : ['a','b','c']})
df['numLen'] = df['number'].apply(lambda x : len(str(x)))

gives

    number  col numLen
0   123     a   3
1   1234    b   4
2   567     c   3

print(df[(df['numLen']) > 3 & (df['number']) > 0].count()) for the items great than number lenth 3

or df[(df['number']) >= 100].count() which will give you all numbers with more than 3 digits

edited Dec 14 '20 at 13:01

answered Dec 14 '20 at 12:56

Paul Brennan

2,638
4
19
26

1

This is using apply which takes too long for my dataset. Also it would count the negative sign which is wrong. – Vega Dec 14 '20 at 13:00
put the simplistic answer at the end which will give you all the numbers of a length over 3 and no negatives, just count the numbers over 100... – Paul Brennan Dec 14 '20 at 13:06

score 1 · Accepted Answer · answered Dec 14 '20 at 13:07

In [25]: df = pd.DataFrame({'number':[123, 25, 1234, -165, -99, 567], 'col' : ['a','b','c', 'd', 'e', 'f']})

In [26]: df
Out[26]:
   number col
0     123   a
1      25   b
2    1234   c
3    -165   d
4     -99   e
5     567   f

In [27]: df.query("100 <= abs(number) <= 999")['number'].size
Out[27]: 3

How to get/check number of digits of a row in a df.query()?

2 Answers2