how to groupby in complicated condition in pandas
<p>I have dataframe like this</p>

<pre><code>     A    B    C
0    1    7    a
1    2    8    b
2    3    9    c
3    4   10    a
4    5   11    b
5    6   12    c
</code></pre>

<p>I would like to get groupby result (key=column C) below;</p>

<pre><code>    A    B
d  12   36
</code></pre>

<p>"d" means  a or b ,</p>

<p>so I would like to groupby only with "a" and "b".</p>

<p>and then put together as "d".</p>

<p>when I sum up with all the key elements then drop, it consume much time....</p>

<p>One option is to use <code>pandas</code> <code>where</code> to transform the C column so that where it was <code>a</code> or <code>b</code> becomes <code>d</code> and then you can groupby the transformed column and do the normal summary on it, and if rows with <code>c</code> is not desired, you can simply drop it after the summary:</p>

<pre><code>df_sum = df.groupby(df.C.where(~df.C.isin(['a', 'b']), "d")).sum().reset_index()

df_sum
#   C   A   B
#0  c   9   21
#1  d   12  36

df_sum.loc[df_sum.C == "d"]

#   C   A   B
#1  d   12  36
</code></pre>

<p>To see more clearly how the <code>where</code> clause works:</p>

<pre><code>df.C.where(~df.C.isin(['a','b']), 'd')

# 0    d
# 1    d
# 2    c
# 3    d
# 4    d
# 5    c
# Name: C, dtype: object
</code></pre>

<p>It acts like a replace method and replace <code>a</code> and <code>b</code> with <code>d</code> which will be grouped together when passed to <code>groupby</code> function.</p>

python pandas