CODEWITHSUNDEEP
pythonpandasdataframeshift
Anna_20011094
Anna_20011094

Reputation: 1

Using a shift() to compare rows in a Pandas Dataframe

I would like to find the nearest 'Quote' for each 'Trade' in the column Type, and use shift() to pull in data from Bid price of the nearest "Quote" to Prevailing price of "Trade".

import pandas as pd
df = pd.DataFrame(data=[['Quote', '1.4', '1.4'],
                        ['Quote', '1.6', '1.6'],
                        ['Trade', '10.0', 'NaN'],
                        ['Quote', '1.7', '1.7'],
                        ['Trade', '11.0', 'NaN'],
                        ['Trade', '11.0', 'NaN'],
                       ['Trade', '11.0', 'NaN']],
                  columns=['Type', 'Bid price', 'Prevailing price'])
df['Prevailing price'] = df['Bid price'].shift().where(df['Type'] == 'Trade',df['Bid price'])
print df

I am getting the error in the output: for continuous 'Trade' , the result is based on the Bid price of the previous' Trade ' instead of Bid price of the nearest "Quote", like index 4-6.

Type    Bid price   Prevailing price
0   Quote   1.4     1.4
1   Quote   1.6     1.6
2   Trade   10.0    1.6
3   Quote   1.7     1.7
4   Trade   11.0    1.7
5   Trade   11.0    11.0
6   Trade   11.0    11.0

Desired Output:

Type    Bid price   Prevailing price
0   Quote   1.4     1.4
1   Quote   1.6     1.6
2   Trade   10.0    1.6
3   Quote   1.7     1.7
4   Trade   11.0    1.7
5   Trade   11.0    1.7
6   Trade   11.0    1.7

Thank you for your help.

Upvotes: 0

Views: 416

Answers (1)

jezrael
jezrael

Reputation: 863341

If possible replace Trade rows in Bid price by missing values by Series.mask and then forward filling last non missing values by ffill use:

df['Prevailing price'] = df['Bid price'].mask(df['Type'] == 'Trade').ffill()
print (df)
    Type Bid price Prevailing price
0  Quote       1.4              1.4
1  Quote       1.6              1.6
2  Trade      10.0              1.6
3  Quote       1.7              1.7
4  Trade      11.0              1.7
5  Trade      11.0              1.7
6  Trade      11.0              1.7

Upvotes: 1

Related Questions