Struggling with basic dataframe manipulation (Python)

Question

I am a complete newbie at Python, and I'm struggling despite having Googled for quite a while. I know it should not be this difficult. I have a Dataframe called abc that looks like this:

   PO_DATE      PO_ITEM_NUMBER     PO_PRICE       PO_QTY
----------------------------------------------------------------
0  01/15/2017   ABC123             1.55           1000
1  01/25/2017   DEF456             5.55           500

I know the max PO_PRICE = 5.55, which I can find using:

max_PO_Price = abc["PO_PRICE"].max()

All I want to be able to do is identify which row has the max PO_PRICE, locate the PO_QTY field for that row, and decrement it by 100. I keep wanting to envision this dataframe is a 2-D array, but, it's not liking that, and, I know it's not an array, as there are different data types involved. I've been screwing this up now for too long, so, finally decided to post. I hope someone can forgive my lack of knowledge and point me in the right direction.

Thanks.

What should happen if multiple rows share the same maximum price? — Simon Fromme, Oct 19 '20 at 19:13

score 0 · Answer 1 · answered Oct 19 '20 at 19:15

0

you should be able to do it with this :

index_max=abc.loc[abc["PO_price"]==abc["PO_price"].max()].index
for i in index_max
  abc.at[i,"PO_QTY"]=abc[i,"PO_QTY"]-100

answered Oct 19 '20 at 19:15

NHL

277
1
6

artemis · Accepted Answer · 2020-10-19T19:34:39.213

0

I think you want:

abc.loc[abc.PO_PRICE == abc["PO_PRICE"].max(), ['PO_QTY']] = abc['PO_QTY']-100

An example below:

# Import pandas
import pandas as pd

# Create dataframe from example and priunt
abc = pd.DataFrame({'PO_Date':['01/15/2017', '01/25/2017'], 'PO_ITEM_NUMBER': ['ABC123', 'DEF456'], 'PO_PRICE':[1.55, 5.55], 'PO_QTY':[1000, 500]})
print(abc)

# Find which row has the max PO Price, and then subtract the quantity by 100
abc.loc[abc.PO_PRICE == abc['PO_PRICE'].max(), ['PO_QTY']] = abc['PO_QTY']-100

# Print it out
print(abc)

Yields:

      PO_Date PO_ITEM_NUMBER  PO_PRICE  PO_QTY
0  01/15/2017         ABC123      1.55    1000
1  01/25/2017         DEF456      5.55     500

      PO_Date PO_ITEM_NUMBER  PO_PRICE  PO_QTY
0  01/15/2017         ABC123      1.55    1000
1  01/25/2017         DEF456      5.55     400

You should consider looking at this post, which my answer was influenced from. Additionally, the documentation on .loc might be helpful.

edited Oct 19 '20 at 19:34

answered Oct 19 '20 at 19:16

artemis

6,857
11
46
99

This worked great. Now I need to sit down and try to analyze how you came up with this. I don't think I would have been able to, so, I need to carefully go through it all. I greatly appreciate both of you who replied so promptly . You have no idea how much that just assisted me. THANK YOU! – Clare Oct 19 '20 at 19:28
I made an edit to the post to hopefully explain things better. If this answered your question, be sure to select it as the correct answer so it might help others. – artemis Oct 19 '20 at 19:33
Truly, thank you . This is beyond helpful. I did up vote it too, but, the system said because I'm a newbie it wouldn't display it or something.... :-( – Clare Oct 19 '20 at 19:38

Struggling with basic dataframe manipulation (Python)

2 Answers2