Adding values from a dataframe-A A.column1 by matching values in A.column2 to the B.column1 name of another dataframe B
<p>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 <code>A.Shares.values</code>. I get an error when trying the below code. Alternatively, would it be possible to loop through the df A rows using <code>join</code> command constraint to match df A's column values to column names of df B similar to SQL queries?  </p>

<p><code>import pandas as pd</code></p>

<p><code>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</code></p>

<p><code>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</code></p>

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

<p>Expected Result </p>

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

<p>Attempt </p>

<pre><code>    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 
</code></pre>

<p>first of all, I highly suggest you to read <a href="http://stackoverflow.com/questions/20109391/how-to-make-good-reproducible-pandas-examples">how-to-make-good-reproducible-pandas-examples</a></p>

<p>I think you could use pivot such has:</p>

<pre><code>B = A.pivot('Date','Symbol','Shares')
</code></pre>

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

python pandas join dataframe