CODEWITHSUNDEEP
pythonnumpypandas
Daniel
Daniel

Reputation: 19547

A clean/efficient way of summing rows by index in python pandas

Hello I have a dataframe and I am trying to add and subtract rows by an index.

First the data in easy to copy format:

data = [['Name1','Obj1','Ind1',10,5,3,6],['Name1','Obj1','Ind2',10,5,2,1],['Name1','Obj1','Ind3',10,5,5,2],['Name1','Obj2','Ind1',15,7,33,15],['Name1','Obj2','Ind2',15,7,15,9],['Name1','Obj2','Ind3',15,7,32,9]]

Then the dataframe:

>>> df = pd.DataFrame(data,columns=['Name','Object','Index','Const1','Const2','Method1','Method2'])
>>> df
    Name Object Index  Const1  Const2  Method1  Method2
0  Name1   Obj1  Ind1      10       5        3        6
1  Name1   Obj1  Ind2      10       5        2        1
2  Name1   Obj1  Ind3      10       5        5        2
3  Name1   Obj2  Ind1      15       7       33       15
4  Name1   Obj2  Ind2      15       7       15        9
5  Name1   Obj2  Ind3      15       7       32        9

This is a truncated df where there is only a single "Name", but in the real df there can be many. Although "Index" is limited to only a few values. In this limited case I would like to manipulate the "Method" columns by grouping by "Name" and "Object" and then taking Ind1-Ind2-Ind3.

My original way of doing this is as follows:

>>> for ind in ['Ind2','Ind3']:
...     for meth in ['Method1','Method2']:
...             df[meth][df['Index']==ind] *= -1
...
>>> df
    Name Object Index  Const1  Const2  Method1  Method2
0  Name1   Obj1  Ind1      10       5        3        6
1  Name1   Obj1  Ind2      10       5       -2       -1
2  Name1   Obj1  Ind3      10       5       -5       -2
3  Name1   Obj2  Ind1      15       7       33       15
4  Name1   Obj2  Ind2      15       7      -15       -9
5  Name1   Obj2  Ind3      15       7      -32       -9

df['Const1'] /= 3
df['Const2'] /= 3

>>> df.groupby(['Name','Object']).sum()
              Const1  Const2  Method1  Method2
Name  Object
Name1 Obj1        10      5       -4        3
      Obj2        15      7      -14       -3

Is there a better way of doing this with python pandas?

Upvotes: 0

Views: 176

Answers (1)

Phillip Cloud
Phillip Cloud

Reputation: 25692

Assuming you want to divide Const1 and Const2 by their non-null counts within each group (so as to preserve their value later when summing):

In [20]: data = [['Name1','Obj1','Ind1',10,5,3,6],
   ....:         ['Name1','Obj1','Ind2',10,5,2,1],
   ....:         ['Name1','Obj1','Ind3',10,5,5,2],
   ....:         ['Name1','Obj2','Ind1',10,5,33,15],
   ....:         ['Name1','Obj2','Ind2',10,5,15,9],
   ....:         ['Name1','Obj2','Ind3',10,5,32,9]]

In [21]: df = DataFrame(data,columns=['Name','Object','Index','Const1','Const2','Method1','Method2'])
In [22]: df
Out[22]:
    Name Object Index  Const1  Const2  Method1  Method2
0  Name1   Obj1  Ind1      10       5        3        6
1  Name1   Obj1  Ind2      10       5        2        1
2  Name1   Obj1  Ind3      10       5        5        2
3  Name1   Obj2  Ind1      10       5       33       15
4  Name1   Obj2  Ind2      10       5       15        9
5  Name1   Obj2  Ind3      10       5       32        9

In [23]: df.loc[df.Index.isin(['Ind2', 'Ind3']), ['Method1', 'Method2']] *= -1

In [24]: def plyr(df):
   ....:     df = df.copy()
   ....:     df['Const1'] /= float(df.Const1.count())
   ....:     df['Const2'] /= float(df.Const2.count())
   ....:     return df
   ....:

In [25]: df.groupby(['Name', 'Object']).apply(lambda x: plyr(x)._get_numeric_data().sum())
Out[25]:
              Const1  Const2  Method1  Method2
Name  Object
Name1 Obj1        10       5       -4        3
      Obj2        10       5      -14       -3

Upvotes: 2

Related Questions