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

Question

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 A.Shares.values. I get an error when trying the below code. Alternatively, would it be possible to loop through the df A rows using join 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

Expected Result

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

Attempt

    B = A.pivot('Date','Symbol','Shares')
    B = B.fillna(value = 0)
    B['Cash'] = pd.Series([0]*len(B.index),index=B.index)
    for index, row in A.iterrows():    
        if row['Order'] == 'SELL':
            B.loc[row, A['Symbol']] *= -1

Please see attached image of A and B `head` from console – sda Oct 09 '16 at 16:30 — sda, Oct 09 '16 at 16:30
did you try pivot? `A.pivot('Date','Symbol','Shares')` – Steven G Oct 09 '16 at 16:42 — Steven G, Oct 09 '16 at 16:42

score 0 · Answer 1 · edited May 23 '17 at 12:16

0

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

edited May 23 '17 at 12:16

Community

1
1

answered Oct 09 '16 at 16:49

Steven G

16,244
8
53
77

Thank you for the link to the question-formatting-guidance page. – sda Oct 09 '16 at 20:19

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

1 Answers1