Adding new column, based on values of another column pandas

Question

Can anyone help, i'm new to Python so bear with me.

My data looks like this but has all the region information available. I'm trying to create a new column 'actual price' that works out the price based on the region. as for every entry I have each price for every region. is this possible.

data = [[1, 'EDF', 'Eastern', 400, 500, 300], [2, 'EDF', 'Southern', 200, 100, 300], [3, 'NPower', 
        'Eastern', 600, 500, 700]] 


df = pd.DataFrame(data, columns = ['ID', 'Supplier', 'Region', 'Av Price', 'Eastern Price',  
'Southern Price']) 

df

sorry that would have been helpful so ID Supplier Region Av Price Eastern Price Southern Price Price 1 EDF Eastern 400 500 300 500 2 EDF Southern 200 100 300 300 THe idea is to get rid of all the regional prices and just have the actual price if that makes sense. — em456
– em456, Commented Jun 1, 2020 at 14:33

anky · Accepted Answer · 2020-06-01 14:39:48Z

2

IIUC, you can do df.lookup here after adding " Price" to the values of the Region column to match the column names of the Price by region:

m = df.loc[:,df.columns.str.endswith("Price")]
df['actual_Price'] = m.lookup(df.index,df['Region'].add(" Price"))

print(df)
   ID Supplier    Region  Av Price  Eastern Price  Southern Price  \
0   1      EDF   Eastern       400            500             300   
1   2      EDF  Southern       200            100             300   
2   3   NPower   Eastern       600            500             700   

   actual_Price  
0           500  
1           300  
2           500

answered Jun 1, 2020 at 14:39

anky

75.3k11 gold badges46 silver badges76 bronze badges

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

5 Comments

em456 Over a year ago

It appears this has worked but I have a warning SettingWithCopyWarning: A value is trying to be set on a copy of a slice from a DataFrame.

anky Over a year ago

@LMR is your dataframe a reference of any other dataframe or is it the first/source dataframe?

em456 Over a year ago

it was created from taking columns from 2 other dataframes if that makes sense?

anky Over a year ago

@LMR guess there is some issue there. use .copy() when referencing the other dataframes, check this

em456 Over a year ago

Thank you @anky that has solved the problem, you have probably jsut saved me a pile of time trying to work it out

Roy2012 · Accepted Answer · 2020-06-01 14:34:28Z

1

I believe this is what you're looking for:

df["actual_price"] = np.where(df.Region == "Eastern", df["Eastern Price"], df["Southern Price"])

result:

answered Jun 1, 2020 at 14:34

Roy2012

12.7k3 gold badges28 silver badges38 bronze badges

Comments

Shubham Sharma · Accepted Answer · 2020-06-01 16:12:53Z

1

Use, np.select:

conditions = [df['Region'].eq(reg) for reg in df['Region'].unique()]
choices = [df[f'{reg} Price'] for reg in df['Region'].unique()]
df['actual_price'] = np.select(conditions, choices)

Result:

# print(df)
   ID Supplier    Region  Av Price  Eastern Price  Southern Price  actual_price
0   1      EDF   Eastern       400            500             300           500
1   2      EDF  Southern       200            100             300           300
2   3   NPower   Eastern       600            500             700           500

edited Jun 1, 2020 at 16:12

answered Jun 1, 2020 at 14:59

Shubham Sharma

71.8k6 gold badges26 silver badges58 bronze badges

Collectives™ on Stack Overflow

Adding new column, based on values of another column pandas

3 Answers 3

5 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

5 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related