1

In the following dataframe I have 3 columns, index, adj, and typ. the TYP column relates to the index. So every (x,1) multiindex has a value of PWR, and every (x,2) multiindex has a value of GND. 

 index       Adj             TYP  
 (x,1)       (x, 2)          PWR
 (x,1)       (x, 3)          PWR
 (x,1)       (x, 5)          PWR
 (x,1)       (x, 6)          PWR
 (x,1)       (x, 7)          PWR
 (x,2)       (x, 1)          GND
 (x,2)       (x, 3)          GND
 (x,2)       (x, 4)          GND
 (x,2)       (x, 5)          GND
 (x,2)       (9, 6)          GND
 (x,2)       (x, 7)          GND

I want to figure out how to use the relationship between index and TYP to populate a fourth column called 'Adj. TYP'. This column will have the relationship between ADJ and TYP. If we were doing it for only indices (x,1) and (x,2) the resulting table would be below. This is cumbersome to explain in text but essentially the Adj column contains the same data as the index column, just not really in the same order. I want to populate the 4th column with the matching TYP.

 index       Adj            TYP     Adj. TYP
 (x,1)       (x,2)          PWR     GND
 (x,1)       (x,3)          PWR
 (x,1)       (x,5)          PWR
 (x,1)       (x,6)          PWR
 (x,1)       (x,7)          PWR
 (x,2)       (x,1)          GND     PWR
 (x,2)       (x,3)          GND
 (x,2)       (x,4)          GND
 (x,2)       (x,5)          GND
 (x,2)       (9,6)          GND
 (x,2)       (x,7)          GND

I have attempted to use map, but this won't let me reindex a multivalued index.

df1['Adj. TYP'] = df1['Adj'].map(df1[index])
MaxB
  • 428
  • 1
  • 8
  • 24
  • 1
    Please take a look at [this question](https://stackoverflow.com/questions/20109391/how-to-make-good-reproducible-pandas-examples) with some tips on how to produce good, reproducible pandas examples for StackOverflow. – juanpa.arrivillaga Feb 11 '19 at 20:33
  • 1
    I'm finding it hard to follow your query, but think that what you actually need to use here is the ```merge``` function, specifying the two columns that you are merging on. ```df1.merge(df2, left_on=['lkey1','lkey2'], right_on=['rkey1','rkey2'])``` – HadynB Feb 11 '19 at 21:32
  • Would the answer below be helpful? – Zanshin Feb 13 '19 at 18:14

1 Answers1

1

you might try this;

create a 'lookup' dataframe:

df_ = df[['index', 'TYP']].drop_duplicates()

map the value from your lookup-table to df, where there is a match, and remove NaN's:

df['Adj. TYP'] = df['Adj'].map(df_.set_index('index')['TYP'])

df.fillna('')

      Adj  TYP  index Adj. TYP
0   (x,2)  PWR  (x,1)      GND
1   (x,3)  PWR  (x,1)         
2   (x,5)  PWR  (x,1)         
3   (x,6)  PWR  (x,1)         
4   (x,7)  PWR  (x,1)         
5   (x,1)  GND  (x,2)      PWR
6   (x,3)  GND  (x,2)         
7   (x,4)  GND  (x,2)         
8   (x,5)  GND  (x,2)         
9   (9,6)  GND  (x,2)         
10  (x,7)  GND  (x,2)
Zanshin
  • 1,262
  • 1
  • 14
  • 30