How to generate dataframe column based on comparison of values in other columns?-CodePudding

I am trying to generate a new dataframe column based on a comparison of values in other dataframe columns. However, when I run the below code I get an error saying "Can only compare identically-labeled DataFrame objects". What am I doing wrong here as far as populating the "Suggest" column?

for Home in Target_List:
    if Target_Frame.loc[[home], ['Price']] > Target_Frame.loc[[home], ['High']]:
        Target_Frame.loc[[home], ['Suggest']] = "Sell"
    elif Target_Frame.loc[[home], ['Price']] < Target_Frame.loc[[home], ['Low']]:
        Target_Frame.loc[[home], ['Suggest']] = "Buy"
    elif Target_Frame.loc[[home], ['Median']] < Target_Frame.loc[[home], ['Price']] < Target_Frame.loc[[home], ['High']]:
        Target_Frame.loc[[home], ['Suggest']] = "Hold"
    elif Target_Frame.loc[[home], ['Low']] < Target_Frame.loc[[home], ['Price']] < Target_Frame.loc[[home], ['Median']]:
        Target_Frame.loc[[home], ['Suggest']] = "Review"

CodePudding user response：

If target_list is a dataframe, you can do what you want likes this:

target_frame = pd.DataFrame()
for i, row in target_list.iterrows():
    if row["Price"] > row["High"]:
        target_frame.at[i, "Suggest"] = "Sell"
    elif row["Price"] < row["Low"]:
        target_frame.at[i, "Suggest"] = "Buy"
    elif row["Median"] < row["Price"] < row["High"]:
        target_frame.at[i, "Suggest"] = "Hold"
    elif row["Low"] < row["Price"] < row["Median"]:
        target_frame.at[i, "Suggest"] = "Review"

CodePudding user response：

You could significantly simplify your logic by using an apply function:

Using:

home High Low Median Price
A     200 100    130   220
B     220 120    150   100
C     300 210    240   230
D     400 280    350   380 
E     300 210    240   200
F     200 100    130   220
G     220 120    150   240
H     300 210    240   270
I     400 280    350   380

Create your suggester function with the simplified logic:

def suggester(x):
    if x['Price'] > x['High']:
        return "Sell"
    elif x['Price'] < x['Low']:
        return "Buy"
    elif x['Median'] < x['Price']:
        return "Hold"
    
    return "Review"

Target homes:

target_list = list('ABCDGH')

Apply the function and get your suggestions:

df['Suggest'] = df[df['home'].isin(target_list)].apply(suggester, axis=1)
print(df)

  home  High  Low  Median  Price Suggest
0    A   200  100     130    220    Sell
1    B   220  120     150    100     Buy
2    C   300  210     240    230  Review
3    D   400  280     350    380    Hold
4    E   300  210     240    200     NaN
5    F   200  100     130    220     NaN
6    G   220  120     150    240    Sell
7    H   300  210     240    270    Hold
8    I   400  280     350    380     NaN

CodePudding user response：

Here's an approach that avoids a loop and in some ways simplifies the suggesting logic by overwriting

import pandas as pd
import numpy as np

#Create example data
np.random.seed(1)
num_houses = 20

house_names = ['house_{}'.format(i 1) for i in range(num_houses)]
house_prices = np.random.randint(100,1000,num_houses)
low_prices = np.random.randint(100,1000,num_houses)
high_prices = low_prices   np.random.randint(100,1000,num_houses)
med_prices = (low_prices high_prices)/2

Target_Frame = pd.DataFrame({
    'Home':house_names,
    'Price':house_prices,
    'High':high_prices,
    'Low':low_prices,
    'Median':med_prices,
})



#First suggest to buy everything
Target_Frame['Suggest'] = 'Buy'

#If the price is more than the low, overwrite these rows with 'Review'
Target_Frame.loc[Target_Frame['Price'] > Target_Frame['Low'], 'Suggest'] = 'Review'

#If the price is more than the median, overwrite these rows with 'Hold'
Target_Frame.loc[Target_Frame['Price'] > Target_Frame['Median'], 'Suggest'] = 'Hold'

#If the price is more than the High, overwrite these rows with 'Sell'
Target_Frame.loc[Target_Frame['Price'] > Target_Frame['High'], 'Suggest'] = 'Sell'

Target_Frame

Output: