I have two pandas dataframes one called orders and another one called daily_prices.
daily_prices is as follows:
AAPL GOOG IBM XOM 2011-01-10 339.44 614.21 142.78 71.57 2011-01-13 342.64 616.69 143.92 73.08 2011-01-26 340.82 616.50 155.74 75.89 2011-02-02 341.29 612.00 157.93 79.46 2011-02-10 351.42 616.44 159.32 79.68 2011-03-03 356.40 609.56 158.73 82.19 2011-05-03 345.14 533.89 167.84 82.00 2011-06-03 340.42 523.08 160.97 78.19 2011-06-10 323.03 509.51 159.14 76.84 2011-08-01 393.26 606.77 176.28 76.67 2011-12-20 392.46 630.37 184.14 79.97
orders is as follows:
direction size ticker prices 2011-01-10 Buy 1500 AAPL 339.44 2011-01-13 Sell 1500 AAPL 342.64 2011-01-13 Buy 4000 IBM 143.92 2011-01-26 Buy 1000 GOOG 616.50 2011-02-02 Sell 4000 XOM 79.46 2011-02-10 Buy 4000 XOM 79.68 2011-03-03 Sell 1000 GOOG 609.56 2011-03-03 Sell 2200 IBM 158.73 2011-06-03 Sell 3300 IBM 160.97 2011-05-03 Buy 1500 IBM 167.84 2011-06-10 Buy 1200 AAPL 323.03 2011-08-01 Buy 55 GOOG 606.77 2011-08-01 Sell 55 GOOG 606.77 2011-12-20 Sell 1200 AAPL 392.46
Index of both dataframes is datetime.date.
prices column in the orders dataframe was added by using a list comprehension to loop through all the orders and look up the specific ticker for the specific date in the daily_prices data frame and then adding that list as a column to the orders dataframe. I would like to do this using an array operation rather than something that loops. can it be done? I tried to use:
daily_prices.ix[dates, tickers]
but this returns a matrix of cartesian product of the two lists. I want it to return a column vector of only the price of a specified ticker for a specified date.
Answers:
Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.
Method 1
Use our friend lookup, designed precisely for this purpose:
In [17]: prices
Out[17]:
AAPL GOOG IBM XOM
2011-01-10 339.44 614.21 142.78 71.57
2011-01-13 342.64 616.69 143.92 73.08
2011-01-26 340.82 616.50 155.74 75.89
2011-02-02 341.29 612.00 157.93 79.46
2011-02-10 351.42 616.44 159.32 79.68
2011-03-03 356.40 609.56 158.73 82.19
2011-05-03 345.14 533.89 167.84 82.00
2011-06-03 340.42 523.08 160.97 78.19
2011-06-10 323.03 509.51 159.14 76.84
2011-08-01 393.26 606.77 176.28 76.67
2011-12-20 392.46 630.37 184.14 79.97
In [18]: orders
Out[18]:
Date direction size ticker prices
0 2011-01-10 00:00:00 Buy 1500 AAPL 339.44
1 2011-01-13 00:00:00 Sell 1500 AAPL 342.64
2 2011-01-13 00:00:00 Buy 4000 IBM 143.92
3 2011-01-26 00:00:00 Buy 1000 GOOG 616.50
4 2011-02-02 00:00:00 Sell 4000 XOM 79.46
5 2011-02-10 00:00:00 Buy 4000 XOM 79.68
6 2011-03-03 00:00:00 Sell 1000 GOOG 609.56
7 2011-03-03 00:00:00 Sell 2200 IBM 158.73
8 2011-06-03 00:00:00 Sell 3300 IBM 160.97
9 2011-05-03 00:00:00 Buy 1500 IBM 167.84
10 2011-06-10 00:00:00 Buy 1200 AAPL 323.03
11 2011-08-01 00:00:00 Buy 55 GOOG 606.77
12 2011-08-01 00:00:00 Sell 55 GOOG 606.77
13 2011-12-20 00:00:00 Sell 1200 AAPL 392.46
In [19]: prices.lookup(orders.Date, orders.ticker)
Out[19]:
array([ 339.44, 342.64, 143.92, 616.5 , 79.46, 79.68, 609.56,
158.73, 160.97, 167.84, 323.03, 606.77, 606.77, 392.46])
All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0