sda sda - 2 years ago 63
Python Question

Adding values from a dataframe-A A.column1 by matching values in A.column2 to the B.column1 name of another dataframe B

enter image description here

I have two dataframes (df) A and B. df A has a column called 'Symbol' with non-unique stock-ticker-symbols as values in random order and the corresponding amount of buy or sell quantities in another column called 'Shares'; it is indexed by non-negative integers. df B, indexed by dates in the same date-order as df A and same number of rows as df A, has the same ticker symbols as df A as unique column names. I need to populate all df B rows with the amount of stock purchase or sell amounts from corresponding

. I get an error when trying the below code. Alternatively, would it be possible to loop through the df A rows using
command constraint to match df A's column values to column names of df B similar to SQL queries?

import pandas as pd

bCN = B.dtypes.index # list of column names in df B to be used for populating its stock quantity based on matching values from df A

A = pd.DataFrame({'Date': ['2011-01-14', '2011-01-19', '2011-01-19'],
'Symbol': ['AAPL', 'AAPL', 'IBM'], 'Order':['BUY','SELL','BUY'],'Shares':[1500, 1500, 4000]}) #example of A

B = pd.DataFrame({'AAPL':[0,0,0],'IBM': [0,0,0], index = pd.date_range(start, end)}) #example of B

for index, row in A.iterrows():
for symbol in bCN:
if A['Symbol'][row] == symbol:
B[symbol][row] = A['Shares'][row]

Answer Source

first of all, I highly suggest you to read how-to-make-good-reproducible-pandas-examples

I think you could use pivot such has:

B = A.pivot('Date','Symbol','Shares')

Since image of dataframe are hard to copy paste I can't show you the exact result you could get using this method

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download