Numpy.where on same column resulting in an empty dataframe

Question

I have a date stored in a datetime64[ns] variable called "IBKR_cutoff_date".

I also have a dataframe called prev1_contract:

         date  Price_PREV_1  OI_PREV_1
0  2021-02-22        523.50      52741
1  2021-02-23        528.75      52383
2  2021-02-24        539.25      52842
3  2021-02-25        541.00      51831
4  2021-02-26        531.00      52839
..        ...           ...        ...
95 2021-07-06        598.75      74271
96 2021-07-07        585.50      57216
97 2021-07-08        592.25      31202
98 2021-07-09        610.00       5071
99 2021-07-12        610.00          0

I want to check if dates in column "date" (whose dtype is datetime64[ns]) are greater than IBKR_cutoff_date. If so, dataframe "Price_PREV_1" column must be set as blank (overwriting previous values), otherwise the column's existing value is kept.

I tried:

prev1_contract['Price_PREV_1'] = np.where(prev1_contract['date']>IBKR_cutoff_date, '', prev1_contract['Price_PREV_1'] ).astype(float)

This results in a strange behaviour:

if all dates in "date" column are smaller than IBKR_cutoff_date, then the script works fine (previous dates are kept)
if there is even only one date greater than IBKR_cutoff_date, then the dataframe disappears (if I try to print it or to print its dtypes, nothing comes out as a result).

What am I missing? Thanks

Yeah, it should be throwing an error, something along the lines of ValueError: could not convert string to float: ''. You should use np.NaN to represent missing data in a float column. — ALollz
– ALollz, Commented Jul 23, 2021 at 16:32
You are right: If I remove .astype(float), it works (oddly, with the previous code I didn't get any error). I added astype(float) because otherwise the column dtype changes from float to object and this created some problems with following operations I need to execute on the df. But I can run pd.to_numeric in a separate line and it works flawlessy. So thanks! — younggotti
– younggotti, Commented Jul 23, 2021 at 16:38

IoaTzimas · Accepted Answer · 2021-07-23 16:35:17Z

1

Just remove .astype(float) from the end of your code, as '' cannot be converted to float

answered Jul 23, 2021 at 16:35

IoaTzimas

10.7k2 gold badges15 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Numpy.where on same column resulting in an empty dataframe

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related