Pandas rename one value as other value in a column and add corresponding values in the other column

Question

So, I have a pandas data frame:

        df = 
                 a                   b          c
                 a1                 b1         c1
                 a2                 b2         c1
                 a2                 b3         c2
                 a2                 b4         c2

I want to rename a2 into a1 and then group by a and c and add the corresponding values of b

       df =
                a                  b           c
                a1                 b1+b2       c1
                a1                 b3+b4       c2

So, something like this

      df  =
                a               value           c
                a1              10              c1
                a2              20              c1
                a2              50              c2
                a2              60              c2

      df  =
               a                value            c
               a1               30               c1
               a1               110              c2

How to do this?

Answer 1

What about

>>> res = df.replace({"a": {"a2": "a1"}}).groupby(["a", "c"], as_index=False).sum()
>>> res

    a   c  value
0  a1  c1     30
1  a1  c2    110

which first replaces "a2"s with "a1" in only a column and then groups by and sums.

To get the original column order back, we can reindex:

>>> res.reindex(df.columns, axis=1)

    a  value   c
0  a1     30  c1
1  a1    110  c2

Answer 2

Try this:

df.groupby([df['a'].replace({'a2':'a1'}),'c']).sum().reset_index()