CODEWITHSUNDEEP
pythonpandas
kevingduck
kevingduck

Reputation: 531

In a pandas dataframe, how do you add a value to the end of each row in iterrows?

Say I have a simple dataframe with products, current prices and average prices. I want to use the current and average prices to calculate a retail price. My attempt is:

import csv
from pandas import Series, DataFrame
import pandas as pd
import os

frame = pd.read_csv('/ ... /data.csv')

for index, row in frame.iterrows():    
  product = row['product']
  price = row['price']    
  ave_price = row['ave_price']
  weight_price = 2.0
  max_price = ave_price * weight_price

  retail_price = max_price / (1.0 + 2.0 * price / ave+price)
  retail_total = rs_price * 1.0875

frame.to_csv('/Users ... /output.csv', encoding ='utf-8')

How do I get the retail_total and add it in such a way that I can print the entire dataframe with the products, current prices, average prices AND retail prices?

When I try to do it, it just populates ALL of the products' retail price as the last one in the list of products:

Upvotes: 1

Views: 1296

Answers (2)

Jianxun Li
Jianxun Li

Reputation: 24752

import pandas as pd
import numpy as np

# simulate some artificial data
# ===========================================
np.random.seed(0)
product = np.random.choice(list('ABCDE'), size=10)
price = np.random.randint(100, 200, size=10)
avg_price = np.random.randint(100, 200, size=10)
df = pd.DataFrame(dict(product=product, price=price, avg_price=avg_price))
df

   avg_price  price product
0        125    188       E
1        177    112       A
2        172    158       D
3        109    165       D
4        120    139       D
5        180    187       B
6        169    146       D
7        179    188       C
8        147    181       E
9        164    137       A


# processing
# ===========================================
# some constant parameters
weight_price = 2.0

df['retail_price'] = df['avg_price'] * weight_price / (1.0 + 2.0 * df['price'] / df['avg_price'])

df['retail_total'] = df['retail_price'] * 1.0875

df

   avg_price  price product  retail_price  retail_total
0        125    188       E       62.3752       67.8331
1        177    112       A      156.2544      169.9266
2        172    158       D      121.2459      131.8549
3        109    165       D       54.1276       58.8637
4        120    139       D       72.3618       78.6935
5        180    187       B      116.9675      127.2022
6        169    146       D      123.9089      134.7509
7        179    188       C      115.4631      125.5661
8        147    181       E       84.9077       92.3371
9        164    137       A      122.8128      133.5589

# df.to_csv()

Upvotes: 2

galath
galath

Reputation: 5975

Add a column to the frame:

frame['retail_price'] = Series(np.zeros(len(frame)), index=frame.index)

Then store the value for each row inside the for loop

for index, row in frame.iterrows():    
  product = row['product']
  price = row['price']    
  ave_price = row['ave_price']
  weight_price = 2.0
  max_price = ave_price * weight_price

  retail_price = max_price / (1.0 + 2.0 * price / ave_price)
  retail_total = retail_price * 1.0875

  row['retail_price'] = retail_price

Upvotes: 2

Related Questions