title: Aggregate Pandas DataFrame based on condition that uses multiple columns?
body: <pre><code>import pandas as pd
  
  data = {
      "K": ["A", "A", "B", "B", "B"],
      "LABEL": ["X123", "X123", "X21", "L31", "L31"],
      "VALUE": [1, 3, 1, 2, 5.0]
  }
  
  df = pd.DataFrame.from_dict(data)
  
  output = """
     K LABEL  VALUE
  0  A   X12    1.0
  1  A   X12    3.0
  2  B   X21    1.0
  3  B   L31    2.0
  4  B   L31    5.0
  """
  </code></pre>
  
  <h1>Transformation steps</h1>
  
  <p>For each group ( grouped by K ), find FINAL_VALUE defined below.</p>
  
  <p>Where LABEL are or two types X__ and L__</p>
  
  <pre><code># if LABEL is X___ then FINAL_VALUE = sum(VALUE)
  # if LABEL is L___ then FINAL_VALUE = count(VALUE)
  # else FINAL_VALUE = 0
  </code></pre>
  
  <p>Result of transformation</p>
  
  <pre><code>expected_output = """
  K  LABEL  FINAL_VALUE
  A    X12            4
  B    X21            1
  B    L31            2
  """
  </code></pre>
  
  <p>How can I achieve this using Pandas ?</p>
  
  <p><strong>EDIT1</strong>: Partially working</p>
  
  <pre><code>In [17]: df.groupby(["K", "LABEL"]).agg({"VALUE": {"VALUE_SUM": "sum", "VALUE_COUNT": "count"}})
  Out[17]: 
                VALUE          
          VALUE_COUNT VALUE_SUM
  K LABEL                      
  A X12             2       4.0
  B L31             2       7.0
    X21             1       1.0
  </code></pre>
  
  <p><strong>EDIT2:</strong> Using <code>reset_index()</code> to fill up the dataframe</p>
  
  <pre><code>In [18]: df2 = df.groupby(["K", "LABEL"]).agg({"VALUE": {"VALUE_SUM": "sum", "VALUE_COUNT": "count"}})
  
  In [21]: df2.reset_index()
  Out[21]: 
     K LABEL       VALUE          
             VALUE_COUNT VALUE_SUM
  0  A   X12           2       4.0
  1  B   L31           2       7.0
  2  B   X21           1       1.0
  </code></pre>
  
  <p><strong>EDIT3:</strong> Final solution using <code>df.apply()</code></p>
  
  <pre><code>In [59]: df3 = df2.reset_index()
  
  In [60]: df3["FINAL_VALUE"] = df3.apply(lambda x: x["VALUE"]["VALUE_SUM"] if x["LABEL"].str.startswith("X").any() else x["VALUE"]["VALUE_COUNT"] , axis=1)
  
  In [61]: df3[["K", "LABEL", "FINAL_VALUE"]]
  Out[61]: 
     K LABEL FINAL_VALUE
  
  0  A   X12         4.0
  1  B   L31         2.0
  2  B   X21         1.0
  </code></pre>