Filter column in pandas and convert to float

Question

I have a pandas dataframe, which contains some pretty infiltered data

df['Q53']
OUTPUT:
0      Hvor mange timer træner din virksomhed medarbe...
3                                                    NaN
4                                                    NaN
5                                                    NaN
6                                                      2
7                                                    NaN
8                                                     10
9                                                    NaN
10                                                    50
11                                                   NaN
12                                                     ?
13                                                     ?
14                                                     8
15                                                   NaN
16                                                     2
17                                                     0
18                                                     1
19                                                     1
20                                                     5
21                                             7x3 timer
22                                                   NaN
23                                               8 timer
24                                                   NaN
25                                                     0
26                                                     8
27                                                   NaN

the issue now, is that i want to just have the integers left in the column, and then cast them as a float, so i can do some data visualization with the column.

I was wondering if i could do some standardized filtering, but i could not get get it to work.

Is there an operation, where i can filter out all NaN and String values, and just be left with a value, that could be casted into a float or int

Values `8 timer, 7x3 timer` is matched to some number? Or not? — jezrael, Sep 26 '19 at 08:50
@jezrael `8 timer` should be interpreted to just `8` and `7X3 timer` to `21`. However this might be out of scope for the question — Kristoffer Tølbøll, Sep 26 '19 at 09:11
hmm, if interesting about solution the best post new question. — jezrael, Sep 26 '19 at 09:28
@jezrael i tried implementing your solution, and i see, that maybe it would work better, can you post it again, than i will accept it? — Kristoffer Tølbøll, Sep 26 '19 at 09:29

score 2 · Answer 1 · answered Sep 26 '19 at 08:51

2

You can check if isdigit to select only True columns.

df[df['Q53'].apply(lambda x: str(x).isdigit())]

answered Sep 26 '19 at 08:51

BernardL

5,162
7
28
47

1

Based on OP's additional request in the comments it is obvious this will not work, rather than using `eval`, I think its better to understand all the criteria in your combinations before updating the answer. – BernardL Sep 26 '19 at 08:58

jezrael · Accepted Answer · 2019-09-26T10:14:04.253

2

Convert values to numeric and then remove misisng values:

df['Q53'] = pd.to_numeric(df['Q53'], errors='coerce')
df = df.dropna(subset=['Q53'])
print (df)
     Q53
6    2.0
8   10.0
10  50.0
14   8.0
16   2.0
17   0.0
18   1.0
19   1.0
20   5.0
25   0.0
26   8.0

EDIT: I sugggest not use eval, here is solution for extract numeric with x between, convert to floats and multiple between.

Then replace missing values with all numeric extracted by \d+ patter in regex and converted to numbers.

Last remove missing values by DataFrame.dropna:

dfx = df['Q53'].astype(str).str.extract('(\d+)x(\d+)').astype(float).prod(axis=1, min_count=1)

num = df['Q53'].astype(str).str.extract('(\d+)', expand=False)

df['Q53'] = dfx.fillna(pd.to_numeric(num, errors='coerce'))
df = df.dropna(subset=['Q53'])
print (df)
     Q53
6    2.0
8   10.0
10  50.0
14   8.0
16   2.0
17   0.0
18   1.0
19   1.0
20   5.0
21  21.0
23   8.0
25   0.0
26   8.0

edited Sep 26 '19 at 10:14

answered Sep 26 '19 at 09:10

jezrael

822,522
95
1,334
1,252

I tried adding this solution to another column with larger values, and i get the values in this format. `6 5.000000e+05 8 1.000000e+05 10 1.000000e+05 16 5.000000e+08 18 2.000000e+06 19 0.000000e+00 20 2.500000e+06 2` – Kristoffer Tølbøll Sep 26 '19 at 09:32
yes the isue, is that it is of type `object` and not string – Kristoffer Tølbøll Sep 26 '19 at 10:01
@baileyhaldwin - So not diplay problem? If write to file, still `e` ? – jezrael Sep 26 '19 at 10:02
here is the error message `AttributeError: Can only use .str accessor with string values, which use np.object_ dtype in pandas` – Kristoffer Tølbøll Sep 26 '19 at 10:03
So `pd.set_option('display.float_format', lambda x: '%.3f' % x)` convert values to strings? If remove it, then get `e` numbers? – jezrael Sep 26 '19 at 10:04
before all the other function calls? – Kristoffer Tølbøll Sep 26 '19 at 10:05
I think yes. It only change diplaying of floats. – jezrael Sep 26 '19 at 10:06
1

i addedd `fx = df['Q53'].astype(str)str.extract('(\d+)x(\d+)').astype(float).prod(axis=1, min_count=1)` and made it work – Kristoffer Tølbøll Sep 26 '19 at 10:13

Filter column in pandas and convert to float

2 Answers2