Aggregate Pandas DataFrame based on condition that uses multiple columns?
<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>

<p>You could use <a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.core.groupby.DataFrameGroupBy.agg.html" rel="nofollow"><code>DFGroupby.agg</code></a> like you have done before followed by writing a generic function which computes the necessary requirements with the help of <a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.Series.str.startswith.html" rel="nofollow"><code>str.startswith</code></a> and returns the required frame as shown:</p>

<pre><code>def compute_multiple_condition(row):
    if row['LABEL'].startswith('X'):
        return row['sum']
    elif row['LABEL'].startswith('L'):
          return row['count']
    else:
        return 0

df = df.groupby(['K','LABEL'])['VALUE'].agg({'sum': 'sum', 'count': 'count'}).reset_index()
df['FINAL_VALUE'] = df.apply(compute_multiple_condition, axis=1).astype(int)
df = df[['K', 'LABEL', 'FINAL_VALUE']]
df

   K LABEL  FINAL_VALUE
0  A   X12            4
1  B   L31            2
2  B   X21            1
</code></pre>

python pandas dataframe