title: Pandas - Spreading different values in a column on many columns
body: <p>I have the following table:</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>Option 1</th>
  <th>Option 2</th>
  <th>Option 3</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>A</td>
  <td>X</td>
  <td>1</td>
  </tr>
  <tr>
  <td>B</td>
  <td>X</td>
  <td>1</td>
  </tr>
  <tr>
  <td>A</td>
  <td>Y</td>
  <td>1</td>
  </tr>
  <tr>
  <td>C</td>
  <td>X</td>
  <td>1</td>
  </tr>
  <tr>
  <td>B</td>
  <td>Y</td>
  <td>1</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>I need to split the values of each option on different columns based on the translation as follows:
  Option 1 values A or B becomes opt1000 with values opt1000-A and opt1000-B
  Option 1 value C becomes opt1001 with value opt1001-C
  Etc. as shown below:</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>Opt1000</th>
  <th>Opt1001</th>
  <th>Opt2000</th>
  <th>Opt2001</th>
  <th>Opt3000</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>opt1000-A</td>
  <td>NO-opt1001</td>
  <td>opt2000-X</td>
  <td>NO-opt2001</td>
  <td>1</td>
  </tr>
  <tr>
  <td>opt1000-B</td>
  <td>NO-opt1001</td>
  <td>opt2000-X</td>
  <td>NO-opt2001</td>
  <td>1</td>
  </tr>
  <tr>
  <td>opt1000-A</td>
  <td>NO-opt1001</td>
  <td>NO-opt2000</td>
  <td>opt-2001-y</td>
  <td>1</td>
  </tr>
  <tr>
  <td>NO-opt1000</td>
  <td>opt1001-C</td>
  <td>opt2000-X</td>
  <td>NO-opt2001</td>
  <td>1</td>
  </tr>
  <tr>
  <td>opt-1000-B</td>
  <td>NO-opt1001</td>
  <td>NO-opt2000</td>
  <td>opt2001-Y</td>
  <td>1</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>I have a translation file as follows:</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>Ancient Option name</th>
  <th>Ancient Value</th>
  <th>New option name</th>
  <th>New value</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>Option 1</td>
  <td>A</td>
  <td>Opt1000</td>
  <td>opt1000-A</td>
  </tr>
  <tr>
  <td>Option 1</td>
  <td>B</td>
  <td>Opt1000</td>
  <td>opt1000-B</td>
  </tr>
  <tr>
  <td>Option 1</td>
  <td>C</td>
  <td>Opt1001</td>
  <td>opt1001-C</td>
  </tr>
  <tr>
  <td>Option 2</td>
  <td>X</td>
  <td>opt2000</td>
  <td>opt2000-X</td>
  </tr>
  <tr>
  <td>Option 2</td>
  <td>Y</td>
  <td>opt2001</td>
  <td>opt2001-Y</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>Does anyone have an idea of how I could automate this to be performant? (My table has 20 columns and a 1000 lines).</p>
  <hr />
  <p>I was thinking of first extracting unique option values to create a new dataframe with the new column names. (By checking the new option name in the translation table and adding the name to a set).
  Then filling the data frame with 0 (for example, or NaN), then placing the new values one by one in the new dataframe by scanning each value in the original dataframe. When all values are translated, the remaining 0 or NaN will bere placed by NO_new-option-name_.</p>
  <p>Let me know if it is clear or need more details.</p>