I have a dataframe like this:
>>> d
Out[28]:
A B C D E
2017-06-08 20:39:00 1260.00 1903-08-12 00:00:00 230.00 245.00 19954.55
2017-06-08 20:40:00 1260.00 1330 230.00 245.00 19966.51
2017-06-08 20:48:00 1260.00 1320 230.00 240.00 19961.00
2017-06-08 21:02:00 1240.00 1330 230.00 245.00 19951.38
2017-06-08 21:06:00 1240.00 1340 5.00 240.00 19966.84
2017-06-08 21:07:00 1240.00 1350 220.00 230.00 20000.24
2017-06-08 21:08:00 1250.00 1370 220.00 230.00 20004.66
2017-06-11 20:31:00 1220.00 1280 235.00 245.00 19913.86
I want to remove all the values (except in column A) that are of type datetime.datetime (here the first one in column B). I tried the following but didn't work (intention was to convert datetime to nan and remove nan values later):
d[type(d)==pd.datetime]=np.nan
I also tried this with each individual columns , i.e. the following:
df=d['B'].copy()
df[type(df)==pd.datetime]=np.nan
A simple boolean indexing is not sufficient. You'll need to check the datetime for each item.
Input:
In [239]: df
Out[239]:
Col1 Col2
0 1903-08-12 00:00:00 1
1 1 abc
2 2 2
3 1234 1234
4 abc 1903-08-12 00:00:00
Option 1
Using df.apply and pd.to_datetime, followed by df.isnull and boolean indexing. Use df.dropna to drop rows with NaN.
In [290]: df[df.apply(pd.to_datetime, errors='coerce').isnull()].dropna()
Out[290]:
Col1 Col2
1 1 abc
2 2 2
3 1234 1234
Option 2
A direct application of pd.datetime (not using df.apply):
In [57]: df[pd.to_datetime(df.stack(), 'coerce').unstack().isnull()].dropna()
Out[57]:
Col1 Col2
1 1 abc
2 2 2
3 1234 1234
Option 3
Using df.mask (thank you piRSquared!)
In [62]: df.mask(pd.to_datetime(df.stack(), 'coerce').notnull().unstack()).dropna()
Out[62]:
Col1 Col2
1 1 abc
2 2 2
3 1234 1234
Option 4
You can use df.applymap
In [240]: df[~df.applymap(lambda x: isinstance(x, pd.datetime))].dropna()
Out[240]:
Col1 Col2
1 1 abc
2 2 2
3 1234 1234
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments