CODEWITHSUNDEEP
pythonpandasdataframe
nnnnmmm
nnnnmmm

Reputation: 243

Assign value to cell in pandas dataframe

I am iterating through the file and want to set value of cell in DataFrame based on index and column.

f = "D:/Technical_Data/1pep.csv"
df = pd.read_csv(f, header=0, sep=',')
save_file = "D:/Result_of_TA/" + "def.csv"
qbfile = open(save_file,"r")
for aline in qbfile.readlines():
    values = aline.split(",")
    if values[58].strip()=='BUY' :
       no_of_shares = price/float(values[4])
    if values[58].strip()=='SELL' :
        price = no_of_shares * float(values[4]) 
    df.ix[values[0],'Price'] = price
qbfile.close()
df.to_csv(save_file)

I am getting below error

  File "pandas\index.pyx", line 137, in pandas.index.IndexEngine.get_loc (pandas\index.c:3979)
  File "pandas\index.pyx", line 152, in pandas.index.IndexEngine.get_loc (pandas\index.c:3782)
  File "pandas\index.pyx", line 178, in pandas.index.IndexEngine._get_loc_duplicates (pandas\index.c:4213)
  File "pandas\index.pyx", line 385, in pandas.index.Int64Engine._maybe_get_bool_indexer (pandas\index.c:7683)
KeyError: '20150101'

The column 0 in both file is index

Upvotes: 1

Views: 939

Answers (1)

tmthydvnprt
tmthydvnprt

Reputation: 10748

I recommend reading in both csv files with pandas instead of looping thru save_file. You can then create various indexing schemes and calculations to move data from qb to 1pep. You can structure your code something like this

import pandas as pd

# File locations
pep1_file = 'D:/Technical_Data/1pep.csv'
qb_file = 'D:/Result_of_TA/def.csv.csv'

# Read csv files
pep1 = pd.read_csv(pep1_file, header=0, sep=',')
qb = pd.read_csv(qb_file, header=0, sep=',')

# Process data    
# Find rows with buy in 58th col?
buy_index = qb.iloc[:, 58].str.contains('BUY')

# Find rows with sell in 58th col?
sell_index = qb.iloc[:, 58].str.contains('SELL')

# Get something from 4th col in buy rows?
something_from_purchase = qb.ix[buy_index, 4].astype(float)

# Derive number of shares 
# Where do you get your initial price from?
# It is used before it is assigned if buy row happens first in the original code?
no_of_shares = price / something_from_purchase

# Get something from 4th col in sell rows?
something_from_sale = qb.ix[sell_index, 4].astype(float)

# Derive price
# Where do you get your initial no_of_shares from?
# It is used before it is assigned if sell row happens first in the original code?
price = no_of_shares * something_from_sale

# Assign pep1 price based on qb index
pep1.loc[qb.index, 'Price'] = price

# Then write csv file
# Are sure you want to overwrite?
pep1.to_csv(qb_file)

Right now your code is not clear. There are some initial value dependancies (no_of_shares and price) that seem to be circular and some of the calculations seem possibly backwards. Without some example data or an explanation of the structure, concrete code cannot be suggested.

Upvotes: 1

Related Questions