Python, Pandas - Calculations with referances to other rows

Question

Rather than build a large CVS file completely by hand, I'm trying to build it with some panda magic. My current problem is that I'm trying to calculate the cost and creating 'x', and it need 'n' number of 'y' which costs 'z'

The current dataframe is structured as Item(x), Price(z), Material(y),MaterialSum(n)

The Material is within the data frame as it's own item

df['Cost']  = (df[df.Item == df['Material']].iloc[0])['Price'] * df['MaterialSum']

I've devised this code to build the cost column, however, it only uses the first row's material throughout the data frame, rather than each rows individual material column. Any tips on how to overcome it?

Hard to understand this way. Please provide a sample table and the expected output. Get some quick cues from this SO post: https://stackoverflow.com/questions/20109391/how-to-make-good-reproducible-pandas-examples — YOLO, Feb 26 '18 at 19:00

score 0 · Answer 1 · answered Feb 26 '18 at 19:07

If I understand your problem correctly, this is one solution:

import pandas as pd, numpy as np

df = pd.DataFrame([[1, np.nan, 'A', 10],
                   [2, np.nan, 'B', 20],
                   [3, np.nan, 'C', 30],
                   [np.nan, 15, 'A', np.nan],
                   [np.nan, 10, 'B', np.nan],
                   [np.nan, 20, 'C', np.nan]],
                  columns = ['Item', 'Price', 'Material', 'MaterialSum'])

#    Item  Price Material  MaterialSum
# 0   1.0    NaN        A         10.0
# 1   2.0    NaN        B         20.0
# 2   3.0    NaN        C         30.0
# 3   NaN   15.0        A          NaN
# 4   NaN   10.0        B          NaN
# 5   NaN   20.0        C          NaN

prices = df[df['Item'].isnull()].set_index('Material')['Price']
df['Cost']  = df['Material'].map(prices) * df['MaterialSum']

#    Item  Price Material  MaterialSum   Cost
# 0   1.0    NaN        A         10.0  150.0
# 1   2.0    NaN        B         20.0  200.0
# 2   3.0    NaN        C         30.0  600.0
# 3   NaN   15.0        A          NaN    NaN
# 4   NaN   10.0        B          NaN    NaN
# 5   NaN   20.0        C          NaN    NaN

Python, Pandas - Calculations with referances to other rows

1 Answers1