Complicated refer to another table
<p>I have dataframe shown in below:
column name 'Types'shows each types dified </p>

<p>I would like to add another column named 'number' defined as below.</p>

<pre><code>df=pd.DataFrame({'Sex':['M','F','F','M'],'Age':[30,31,33,32],'Types':['A','C','B','D']})

Out[8]: 

    Age Sex  Types
0   30   M      A
1   31   F      C
2   33   F      B
3   32   M      D
</code></pre>

<p>and I have another male table below;
each column represents Types!</p>

<p>(It was difficult to create table for me, Are there another easy way to create?)</p>

<pre><code>table_M = pd.DataFrame(np.arange(20).reshape(4,5),index=[30,31,32,33],columns=["A","B","C","D","E"])
table_M.index.name="Age(male)"

         A      B      C      D      E
Age(male)                                   
30       0      1      2      3      4
31       5      6      7      8      9
32      10     11     12     13     14
33      15     16     17     18     19
</code></pre>

<p>and I have female table below; </p>

<pre><code>table_F = pd.DataFrame(np.arange(20,40).reshape(4,5),index=[30,31,32,33],columns=["A","B","C","D","E"])
table_F.index.name="Age(female)"

        A      B      C      D      E
Age(female)                                   
30      20     21     22     23     24
31      25     26     27     28     29
32      30     31     32     33     34
33      35     36     37     38     39
</code></pre>

<p>so I would like to add 'number' column as shown below;</p>

<pre><code>    Age Sex  Types   number
0   30   M      A      0 
1   31   F      C     27
2   33   F      B     36
3   32   M      D     13
</code></pre>

<p>this number column refer to female and male table. for each age , Type, and Sex.
It was too complicated for me.
Can I ask how to add 'number' column?</p>

<p>Another way to do this:</p>

<pre><code>In [60]: df['numbers'] = df.apply(lambda x: table_F.loc[[x.Age]][x.Types].iloc[0] if x.Sex == 'F' else table_M.loc[[x.Age]][x.Types].iloc[0], axis = 1)

In [60]: df
Out[60]: 
   Age Sex Types  numbers
0   30   M     A        0
1   31   F     C       27
2   33   F     B       36
3   32   M     D       13
</code></pre>

python pandas dataframe