I have the following dataset in a .csv file:
feature1, feature2, feature3, feature4
0, 42, 2, 1000
2, 13, ?, 997
1, 30, ?, 861
2, 29, ?, ?
I would like to create a pandas dataframe or a numpy array where I don't have the features with an x% of unknown data (where x was specified previously in code).
By using replace and dropna (PS, you need using the parameter thresh in dropna )
import pandas as pd
import numpy as np
df.replace('?', np.NaN).dropna(axis=1,thresh=0.75*len(df)) # for you example , we only accpet one NA here
Out[735]:
feature1 feature2 feature4
0 0 1 100.0
1 2 2 900.0
2 1 3 861.0
3 2 4 NaN
Data Input
df = pd.DataFrame({'feature1': [0,2,1,2], 'feature2': [1,2,3,4],'feature3':[2,'?','?','?'],'feature4':[100,900,861,'?']})
I'm going to assume those '?' are null values. If they aren't, do something like this:
df = df.apply(pd.to_numeric, errors='coerce')
Now, we can make a function that takes a dataframe and a threshold. What we want to do is use loc with a boolean series that tells us which columns have sufficient data representation.
drp = lambda d, x: d.loc[:, d.isnull().mean() < x]
drp(df, .5)
feature1 feature2 feature4
0 0 42 1000.0
1 2 13 997.0
2 1 30 861.0
3 2 29 NaN
If you insist that '?' stay that way... and we can also include NaN
d = df.mask(df.astype(object).eq('?'))
drp = lambda d, x: d.loc[:, d.isnull().mean() < x]
drp(d, .5)
? are not null values.This is probably the easiest way to solve it, that if I understand your question correctly.
You can change ? to NaN using np.nan, then use df.loc and df.isnull to select the columns you need.
df.replace(to_replace= '\?', value=np.nan, inplace=True, regex=True)
df = df.loc[:, (df.isnull().sum() <= len(df) / 4)]
print (df)
feature1 feature2 feature4
0 0 42 1000
1 2 13 997
2 1 30 861
3 2 29 NaN
?with something? Is that your question?NaNvalues. This is important because the dataframe currently has mixed types