0
$\begingroup$

I have 2 pandas datasets which have 'RF' field in common. The optionsdata variable is ~60K, which map to at most the histdata fields of 5K. Using np.where is incredibly slow:

for j in range(0,len(optionsdata)):
    optionsmap[j] = np.where(histdata.ix['RF'].str.match(optionsdata.RF[j]))[0][0]

Is there a much faster way to do this? All I need is the row # for each value in optionsdata which corresponds to the RF row in histdata.

I should note the field compared is a string such as 'NYMEX_01_MAR_2016'

$\endgroup$
3
  • 1
    $\begingroup$ I think you may be misunderstanding what np.where(x) is meant for. The full signature version where(condition,x,y) returns an array the same shape as its inputs, with elements containing either x[i] if c[i] is true or y[i] otherwise. The short version contains an array containing every index where condition is nonzero. You're basically evaluating a string match (slow) for your whole array in order to assign each element. $\endgroup$
    – origimbo
    Commented Mar 8, 2016 at 23:41
  • $\begingroup$ As why I am asking for help here - do you have a better method to propose? I'd like to hear. $\endgroup$
    – Matt
    Commented Mar 9, 2016 at 1:28
  • $\begingroup$ I think the lazy answer is probably to generate a dictionary with keys taken from the histdata and values of the row numbers. Not that I'm saying this is the best way mind you, but it still ought to be faster. $\endgroup$
    – origimbo
    Commented Mar 9, 2016 at 1:40

1 Answer 1

1
$\begingroup$

It is not 100% clear to me what your dataframes look like, but I believe you could do this with the merge function of pandas. Your code would be

optionsmap = pandas.merge(optionsdata,histdata,on="RF",how="left")
$\endgroup$
2
  • 1
    $\begingroup$ That is a quite incredible task there in 1 line of code! However, I'm not trying to merge the 2 dataframes together, just get the row # where RF matches - due to memory constraints, I'd rather keep the 2 dataframes separate. Building on your suggestion @LKlevin I came up with the solution. The whole thing takes probably 1/10 of a second now. Many thanks! RFmap = pd.DataFrame(histdata.RF) RFmap.insert(0,'RFmap',np.arange(0,len(RFmap))) optionsdata = pd.merge(optionsdata, RFmap, on="RF", how="left") $\endgroup$
    – Matt
    Commented Mar 9, 2016 at 18:43
  • 1
    $\begingroup$ FYI although I asked the question with optionsmap, the solution posted instead makes the optionsdata.RFrow the row index to histdata. This should be a built in pandas function... $\endgroup$
    – Matt
    Commented Mar 9, 2016 at 21:58

Not the answer you're looking for? Browse other questions tagged or ask your own question.