title: Python 3 Pandas fast lookup in dictionary for column
body: <p>I have a Pandas DataFrame where I need to add new columns of data from lookup Dictionaries. I am looking for the fastest way to do this. I have a way that works using <code>DataFrame.map()</code> with a lambda but I wanted to know if this was the best practice and best performance I could achieve. I am used to doing with work with <code>R</code> and the excellent <code>data.table</code> library. I am working in a Jupyter notebook which is what is letting me use <code>%time</code> on the final line.</p>
  
  <p>Here is what I have:</p>
  
  <pre><code>import numpy as np
  import pandas as pd
  
  np.random.seed(123)
  num_samples = 100_000_000
  
  ids = np.arange(0, num_samples)
  states = ['Oregon', 'Michigan']
  cities = ['Portland', 'Detroit']
  
  state_data = {
      0:{'Name': 'Oregon', 'mean': 100, 'std_dev': 5},
      1:{'Name': 'Michigan', 'mean':90, 'std_dev': 8}
  }
  
  city_data = {
      0:{'Name': 'Portland', 'mean': 8, 'std_dev':3},
      1:{'Name': 'Detroit','mean': 4, 'std_dev':3}
  }
  
  state_df = pd.DataFrame.from_dict(state_data,orient='index')
  print(state_df)
  
  city_df = pd.DataFrame.from_dict(city_data,orient='index')
  print(city_df)
  
  sample_df = pd.DataFrame({'id':ids})
  sample_df['state_id'] = np.random.randint(0, 2, num_samples)
  sample_df['city_id'] = np.random.randint(0, 2, num_samples)
  %time sample_df['state_mean'] = sample_df['state_id'].map(state_data).map(lambda x : x['mean'])
  </code></pre>
  
  <p>The last line is what I am most focused on.</p>
  
  <p>I have also tried the following but saw no significant performance difference:</p>
  
  <pre><code>%time sample_df['state_mean'] = sample_df['state_id'].map(lambda x : state_data[x]['mean'])
  </code></pre>
  
  <p>What I ultimately want is to get <code>sample_df</code> to have columns for each of the states and cities. So I would have the following columns in the table:</p>
  
  <pre><code>id | state | state_mean | state_std_dev | city | city_mean | city_std_dev
  </code></pre>