Vectorized look-up of values in Pandas dataframe
23
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.
python pandas numpy vectorization
edited Nov 19 '18 at 23:17
denfromufa
3,388431103
asked Dec 15 '12 at 14:51
luckyfoolluckyfool
3953410
add a comment |
23
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.
python pandas numpy vectorization
edited Nov 19 '18 at 23:17
denfromufa
3,388431103
asked Dec 15 '12 at 14:51
luckyfoolluckyfool
3953410
add a comment |
23
23
23
15
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.
python pandas numpy vectorization
edited Nov 19 '18 at 23:17
denfromufa
3,388431103
asked Dec 15 '12 at 14:51
luckyfoolluckyfool
3953410
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.
python pandas numpy vectorization
python pandas numpy vectorization
edited Nov 19 '18 at 23:17
denfromufa
3,388431103
asked Dec 15 '12 at 14:51
luckyfoolluckyfool
3953410
edited Nov 19 '18 at 23:17
denfromufa
3,388431103
asked Dec 15 '12 at 14:51
luckyfoolluckyfool
3953410
edited Nov 19 '18 at 23:17
denfromufa
3,388431103
edited Nov 19 '18 at 23:17
denfromufa
3,388431103
edited Nov 19 '18 at 23:17
denfromufa
3,388431103
3,388431103
asked Dec 15 '12 at 14:51
luckyfoolluckyfool
3953410
asked Dec 15 '12 at 14:51
luckyfoolluckyfool
3953410
asked Dec 15 '12 at 14:51
luckyfoolluckyfool
3953410
3953410
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
41
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])
answered Dec 15 '12 at 15:47
Wes McKinneyWes McKinney
55.5k1911494
3
I was trying various fancy ways to do it myself i should have known you already implemented it . Thanks for this awesome package Wes. Makes life so much easier. Can't wait to see what you'll come up with next.
– luckyfool
Dec 15 '12 at 18:37
When usingDateTimefor both theordersand thepricesdataframes as an index, I get "TypeError: object of type 'datetime.datetime' has no len()" with slightly different code:myval = prices.lookup(order[0], order[1])whereordercomes from afor order in orders. So in my case, order would be 1d rather than 2d as in your example above (orders). Is it wrong usage or how can it be fixed? (I want to get a matching entry for a single date and ticker symbol (out of the orders dataframe) from the prices dataframe that has exactly that information.)
– Andreas Reiff
Dec 17 '12 at 6:29
2
I'm not sure if this will get noticed here but it makes sense to try here first: I would like to do something close but I need to match a series value with the series indexed by days to a dataframe indexed by date time. I get "series object has no attribute lookup." So something like df['d'] = df.index.date -> df['x'] = ts.lookup(df.d)
– M T
Jul 16 '14 at 0:13
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f13893227%2fvectorized-look-up-of-values-in-pandas-dataframe%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
41
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])
answered Dec 15 '12 at 15:47
Wes McKinneyWes McKinney
55.5k1911494
3
I was trying various fancy ways to do it myself i should have known you already implemented it . Thanks for this awesome package Wes. Makes life so much easier. Can't wait to see what you'll come up with next.
– luckyfool
Dec 15 '12 at 18:37
When usingDateTimefor both theordersand thepricesdataframes as an index, I get "TypeError: object of type 'datetime.datetime' has no len()" with slightly different code:myval = prices.lookup(order[0], order[1])whereordercomes from afor order in orders. So in my case, order would be 1d rather than 2d as in your example above (orders). Is it wrong usage or how can it be fixed? (I want to get a matching entry for a single date and ticker symbol (out of the orders dataframe) from the prices dataframe that has exactly that information.)
– Andreas Reiff
Dec 17 '12 at 6:29
2
I'm not sure if this will get noticed here but it makes sense to try here first: I would like to do something close but I need to match a series value with the series indexed by days to a dataframe indexed by date time. I get "series object has no attribute lookup." So something like df['d'] = df.index.date -> df['x'] = ts.lookup(df.d)
– M T
Jul 16 '14 at 0:13
add a comment |
41
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])
answered Dec 15 '12 at 15:47
Wes McKinneyWes McKinney
55.5k1911494
3
I was trying various fancy ways to do it myself i should have known you already implemented it . Thanks for this awesome package Wes. Makes life so much easier. Can't wait to see what you'll come up with next.
– luckyfool
Dec 15 '12 at 18:37
When usingDateTimefor both theordersand thepricesdataframes as an index, I get "TypeError: object of type 'datetime.datetime' has no len()" with slightly different code:myval = prices.lookup(order[0], order[1])whereordercomes from afor order in orders. So in my case, order would be 1d rather than 2d as in your example above (orders). Is it wrong usage or how can it be fixed? (I want to get a matching entry for a single date and ticker symbol (out of the orders dataframe) from the prices dataframe that has exactly that information.)
– Andreas Reiff
Dec 17 '12 at 6:29
2
I'm not sure if this will get noticed here but it makes sense to try here first: I would like to do something close but I need to match a series value with the series indexed by days to a dataframe indexed by date time. I get "series object has no attribute lookup." So something like df['d'] = df.index.date -> df['x'] = ts.lookup(df.d)
– M T
Jul 16 '14 at 0:13
add a comment |
41
41
41
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])
answered Dec 15 '12 at 15:47
Wes McKinneyWes McKinney
55.5k1911494
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])
answered Dec 15 '12 at 15:47
Wes McKinneyWes McKinney
55.5k1911494
answered Dec 15 '12 at 15:47
Wes McKinneyWes McKinney
55.5k1911494
answered Dec 15 '12 at 15:47
Wes McKinneyWes McKinney
55.5k1911494
answered Dec 15 '12 at 15:47
Wes McKinneyWes McKinney
55.5k1911494
55.5k1911494
3
I was trying various fancy ways to do it myself i should have known you already implemented it . Thanks for this awesome package Wes. Makes life so much easier. Can't wait to see what you'll come up with next.
– luckyfool
Dec 15 '12 at 18:37
When usingDateTimefor both theordersand thepricesdataframes as an index, I get "TypeError: object of type 'datetime.datetime' has no len()" with slightly different code:myval = prices.lookup(order[0], order[1])whereordercomes from afor order in orders. So in my case, order would be 1d rather than 2d as in your example above (orders). Is it wrong usage or how can it be fixed? (I want to get a matching entry for a single date and ticker symbol (out of the orders dataframe) from the prices dataframe that has exactly that information.)
– Andreas Reiff
Dec 17 '12 at 6:29
2
I'm not sure if this will get noticed here but it makes sense to try here first: I would like to do something close but I need to match a series value with the series indexed by days to a dataframe indexed by date time. I get "series object has no attribute lookup." So something like df['d'] = df.index.date -> df['x'] = ts.lookup(df.d)
– M T
Jul 16 '14 at 0:13
add a comment |
3
I was trying various fancy ways to do it myself i should have known you already implemented it . Thanks for this awesome package Wes. Makes life so much easier. Can't wait to see what you'll come up with next.
– luckyfool
Dec 15 '12 at 18:37
When usingDateTimefor both theordersand thepricesdataframes as an index, I get "TypeError: object of type 'datetime.datetime' has no len()" with slightly different code:myval = prices.lookup(order[0], order[1])whereordercomes from afor order in orders. So in my case, order would be 1d rather than 2d as in your example above (orders). Is it wrong usage or how can it be fixed? (I want to get a matching entry for a single date and ticker symbol (out of the orders dataframe) from the prices dataframe that has exactly that information.)
– Andreas Reiff
Dec 17 '12 at 6:29
2
I'm not sure if this will get noticed here but it makes sense to try here first: I would like to do something close but I need to match a series value with the series indexed by days to a dataframe indexed by date time. I get "series object has no attribute lookup." So something like df['d'] = df.index.date -> df['x'] = ts.lookup(df.d)
– M T
Jul 16 '14 at 0:13
3
3
I was trying various fancy ways to do it myself i should have known you already implemented it . Thanks for this awesome package Wes. Makes life so much easier. Can't wait to see what you'll come up with next.
– luckyfool
Dec 15 '12 at 18:37
I was trying various fancy ways to do it myself i should have known you already implemented it . Thanks for this awesome package Wes. Makes life so much easier. Can't wait to see what you'll come up with next.
– luckyfool
Dec 15 '12 at 18:37
When using
DateTime for both the orders and the prices dataframes as an index, I get "TypeError: object of type 'datetime.datetime' has no len()" with slightly different code: myval = prices.lookup(order[0], order[1]) where order comes from a for order in orders. So in my case, order would be 1d rather than 2d as in your example above (orders). Is it wrong usage or how can it be fixed? (I want to get a matching entry for a single date and ticker symbol (out of the orders dataframe) from the prices dataframe that has exactly that information.)– Andreas Reiff
Dec 17 '12 at 6:29
When using
DateTime for both the orders and the prices dataframes as an index, I get "TypeError: object of type 'datetime.datetime' has no len()" with slightly different code: myval = prices.lookup(order[0], order[1]) where order comes from a for order in orders. So in my case, order would be 1d rather than 2d as in your example above (orders). Is it wrong usage or how can it be fixed? (I want to get a matching entry for a single date and ticker symbol (out of the orders dataframe) from the prices dataframe that has exactly that information.)– Andreas Reiff
Dec 17 '12 at 6:29
2
2
I'm not sure if this will get noticed here but it makes sense to try here first: I would like to do something close but I need to match a series value with the series indexed by days to a dataframe indexed by date time. I get "series object has no attribute lookup." So something like df['d'] = df.index.date -> df['x'] = ts.lookup(df.d)
– M T
Jul 16 '14 at 0:13
I'm not sure if this will get noticed here but it makes sense to try here first: I would like to do something close but I need to match a series value with the series indexed by days to a dataframe indexed by date time. I get "series object has no attribute lookup." So something like df['d'] = df.index.date -> df['x'] = ts.lookup(df.d)
– M T
Jul 16 '14 at 0:13
add a comment |
draft saved
draft discarded
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f13893227%2fvectorized-look-up-of-values-in-pandas-dataframe%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
