CODEWITHSUNDEEP
pythonpandasdataframe
adeniran827
adeniran827

Reputation: 65

Compare two columns of the same dataframe and returns a different column of the same dataframe

Unable to extract individual values from Column (Week) but a single value works.

u = eurusd.loc[eurusd['Local time'] == pd.to_datetime("2014-01-08 03:00:00",format="%Y-%m-%d %H:%M:%S")].Close
print(u)

output:

70275    1.36075
Name: Close, dtype: float64

But when i try this:

u = eurusd.loc[eurusd['Local time'] == pd.to_datetime(eurusd['Week'],format="%Y-%m-%d %H:%M:%S")].Close
print(u)

output:

Series([], Name: Close, dtype: float64)

I also tried doing the same task with the apply method, but it seems to just compare the columns row by row, not an iterative one

eurusd['ResultClose'] = eurusd.apply(lambda eurusd: eurusd if eurusd['Local time'] == "2014-01-08 03:00:00" else np.nan,axis=1)

To double check code:

eurusd.isnull().sum()

output (Shows that no values were inserted in the column):

Local time        0
Close             0
ResultClose       8760
dtype: int64

The tables below give a visual of what am trying to achieve.

Initial table

Local time Close Week
2014-01-01 00:00:00 1.37410 2014-01-08 00:00:00
2014-01-01 01:00:00 1.37410 2014-01-08 01:00:00
2014-01-01 02:00:00 1.37410 2014-01-08 02:00:0
2014-01-08 03:00:00 1.36075 2014-03-08 02:00:0

Final table

Local time Close Week ResultClose
2014-01-01 00:00:00 1.37410 2014-01-08 00:00:00 1.36075
2014-01-01 01:00:00 1.37410 2014-01-08 01:00:00 .
2014-01-01 02:00:00 1.37410 2014-01-08 02:00:00 .
2014-01-08 03:00:00 1.36075 2014-03-08 02:00:0 .

Upvotes: 0

Views: 60

Answers (1)

Anurag Dabas
Anurag Dabas

Reputation: 24314

Firstly convert 'Local time' and 'Week' to datetime dtype by using to_datetime() method:

eurusd['Local time']=pd.to_datetime(eurusd['Local time'])
eurusd['Week']=pd.to_datetime(eurusd['Week'])

Now use boolean masking and between() method:

mask=eurusd['Local time'].between(eurusd.loc[0,'Week'],eurusd.loc[len(eurusd)-1,'Week'])
value=eurusd.loc[mask,'Close'].reset_index(drop=True)

Finally use assign() method:

eurusd=eurusd.assign(ResultClose=value)

Now if you print eurusd you will get your desired output:

        Local time          Close           Week               ResultClose
0   2014-01-01 00:00:00     1.37410     2014-01-08 00:00:00     1.36075
1   2014-01-01 01:00:00     1.37410     2014-01-08 01:00:00     NaN
2   2014-01-01 02:00:00     1.37410     2014-01-08 02:00:00     NaN
3   2014-01-08 03:00:00     1.36075     2014-03-08 02:00:00     NaN

Upvotes: 1

Related Questions