title: pandas alternative to sql case when statements
body: <p>I'm new to pandas and moving from sql.
  I have a problem, where I'm trying to replace sql-case when statements with pandas
  At a high level, I have a input data frame and a reference table. I create computed columns based on the ref. table
  Example
  Input data
  ------------+-----------+----+------------+-----+------+
  |</p>
  
  <pre><code> STUDENT_ID | UG_MAJOR  | C1 |     C2     | C3  |  C4  |
  +------------+-----------+----+------------+-----+------+
  |        123 | MATH      | A  | 8000-10000 | 12% | 9000 |
  |        234 | ALL_OTHER | B  | 1500-2000  | 10% | 1500 |
  |        345 | ALL_OTHER | A  | 2800-3000  | 8%  | 2300 |
  |        456 | ALL_OTHER | A  | 8000-10000 | 12% | 3200 |
  |        980 | ALL_OTHER | C  | 1000-2500  | 15% | 2700 |
  +------------+-----------+----+------------+-----+------+
  </code></pre>
  
  <p>Ref Data</p>
  
  <pre><code>---------+---------+---------+
  | REF_COL | REF_VAL | REF_SCR |
  +---------+---------+---------+
  | C1      | A       |      10 |
  | C1      | B       |      20 |
  | C1      | C       |      30 |
  | C1      | NULL    |       0 |
  | C1      | MISSING |       0 |
  | C1      | A       |      20 |
  | C1      | B       |      30 |
  | C1      | C       |      40 |
  | C1      | NULL    |      10 |
  | C1      | MISSING |      10 |
  | C2      | &lt;1000   |       0 |
  | C2      | &gt;1000   |      20 |
  | C2      | &gt;7000   |      30 |
  | C2      | &gt;9500   |      40 |
  | C2      | MISSING |       0 |
  | C2      | NULL    |       0 |
  | C3      | &lt;3%     |       5 |
  | C3      | &gt;3%     |      10 |
  | C3      | &gt;5%     |     100 |
  | C3      | &gt;7%     |     200 |
  | C3      | &gt;10%    |     300 |
  | C3      | NULL    |       0 |
  | C3      | MISSING |       0 |
  | C4      | &lt;5000   |      10 |
  | C4      | &gt;5000   |      20 |
  | C4      | &gt;10000  |      30 |
  | C4      | &gt;15000  |      40 |
  +---------+---------+---------+
  </code></pre>
  
  <h2>Expected output</h2>
  
  <pre><code>----------+-----------+----+------------+-----+------+--------+--------+--------+---------+
  | Req.Output |           |    |            |     |      |        |        |        |         |
  +------------+-----------+----+------------+-----+------+--------+--------+--------+---------+
  | STUDENT_ID | UG_MAJOR  | C1 | C2         | C3  | C4   | C1_SCR | C2_SCR | C3_SCR | TOT_SCR |
  | 123        | MATH      | A  | 8000-10000 | 12% | 9000 |        |        |        |         |
  | 234        | ALL_OTHER | B  | 1500-2000  | 10% | 1500 |        |        |        |         |
  | 345        | ALL_OTHER | A  | 2800-3000  | 8%  | 2300 |        |        |        |         |
  | 456        | ALL_OTHER | A  | 8000-10000 | 12% | 3200 |        |        |        |         |
  | 980        | ALL_OTHER | C  | 1000-2500  | 15% | 2700 |        |        |        |         |
  +------------+-----------+----+------------+-----+------+--------+--------+--------+---------+
  </code></pre>
  
  <p>Traditional SQL way is </p>
  
  <pre><code>select student_id, 
  UG_MAJOR, 
  C1,
  case 
  when UG_MAJOR ='MATH' AND when C1 IS NULL THEN 0
  when UG_MAJOR ='MATH' AND when C1 ='MISSING' THEN 0
  when UG_MAJOR ='MATH' AND when C1 ='A' THEN 10
  when UG_MAJOR ='MATH' AND when C1 ='B' THEN 20
  when UG_MAJOR ='MATH' AND when C1 ='C' THEN 30
  
  when UG_MAJOR ='ALL_OTHER' AND when C1 IS NULL THEN 0
  when UG_MAJOR ='ALL_OTHER' AND when C1 ='MISSING' THEN 0
  when UG_MAJOR ='ALL_OTHER' AND when C1 ='A' THEN 20
  when UG_MAJOR ='ALL_OTHER' AND when C1 ='B' THEN 30
  when UG_MAJOR ='ALL_OTHER' AND when C1 ='C' THEN 40
  
  ELSE 'TBD' END AS C1_SCR,
  
  C2,
  CASE 
  WHEN C2 IS NULL THEN 0
  WHEN C2 ='Missing' OR C2 = . THEN 0
  WHEN C2&lt;=1000 THEN 0
  WHEN C2 &gt;1000 AND C2&lt;=7000 THEN 20
  WHEN C2 &gt;7000 AND C2&lt;=9500 THEN 30
  WHEN C2 &gt;9500 THEN 40
  ELSE 'TBD' 
  END AS C2_SCR
  
  FROM REF_INPUT
  GROUP BY 1,2,3,4,5,6
  </code></pre>
  
  <p>I'm wondering if there is an elegant way to handle in pandas ?
  Thanks
  Par</p>