1

I have 2 dataframes of numerical data. Given a value from one of the columns in the second df, I would like to look up the index for the value in the first df. More specifically, I would like to create a third df, which contains only index labels - using values from the second to look up its coordinates from the first.

listso = [[21,101],[22,110],[25,113],[24,112],[21,109],[28,108],[30,102],[26,106],[25,111],[24,110]]
data = pd.DataFrame(listso,index=list('abcdefghij'), columns=list('AB'))
rollmax = pd.DataFrame(data.rolling(center=False,window=5).max())

So for the third df, I hope to use the values from rollmax and figure out which row they showed up in data. We can call this third df indexlookup.

For example, rollmax.ix['j','A'] = 30, so indexlookup.ix['j','A'] = 'g'.

Thanks!

John Zwinck
  • 239,568
  • 38
  • 324
  • 436
MJS
  • 1,573
  • 3
  • 17
  • 26

1 Answers1

1

You can build a Series with the indexing the other way around:

mapA = pd.Series(data.index, index=data.A)

Then mapA[rollmax.ix['j','A']] gives 'g'.

John Zwinck
  • 239,568
  • 38
  • 324
  • 436
  • John - good idea, thanks for responding. Do you think my best answer for a large data set is to go this route and live with looping over each column to create the "lookup" series? – MJS Oct 17 '16 at 12:48
  • Also - in the cases where there are duplicate values, is it possible to default to the first or last occurrence ? – MJS Oct 17 '16 at 13:25
  • @MJS: Yes I think this is a reasonable approach. For the dupes thing, try http://pandas.pydata.org/pandas-docs/stable/generated/pandas.Series.duplicated.html – John Zwinck Oct 18 '16 at 02:21
  • Thanks again John. Last question - the mapA[rollmax.ix['j','A']] gives 'g' solution doesn't seem to translate to an entire dataframe. How can I fill the indexlookup dataframe with all of the appropriate index names from the rollmax:data mapping above ? – MJS Oct 18 '16 at 03:43
  • @MJS: I'll leave it to you to figure that one out, or post a new question about it if you're stuck. – John Zwinck Oct 18 '16 at 04:03
  • That was my original question – MJS Oct 18 '16 at 11:01