How to Specify the 'In Between Columns' sum in a precise notation

user_2458922 0
Viewed 56
Proj_Com_Sum   comp_1   comp_2    comp_3   Proj_Val_sum  val_1  val_2  val_3 
70              10         20      35       67            20      30    15
100             50         30      25       70            25      30    15

Given the above as Pandas DataFrame df, I would like to add a Colunm Com_total , Val_total , Proj_Tot_Diff

Where

Com_total = comp_1 + comp_2 + comp_3
Val_total = val_1 + val_2 + val_3
Proj_Tot_Diff = Com_total - Proj_Com_Sum

Since I have about comp .. it would be a long code to write

Com_total = comp_1 + comp_2 + comp_3 .. comp_58

Please Note comp_1..comp_2 may not follow a regex pattern. it could be some State Names like Florida, NY, etc. All we know is 2nd colunm to 58th column are to be added.

Hence, I want Some code like

 df['Com_total']= df[ col 2:58 ].sum
 # Whats the correct Syntax

How to Specify the In Between Columns in a precise notation. Please help with correct Syntax

pandas python
3 Answers

You can using slicing if your column headers are ordered properly, however it safer if you use @piRSquared's method using filter:

df['Com_total'] = df.loc[:,'comp_1':'comp_3'].sum(1)
df['Val_total'] = df.loc[:,'val_1':'val_3'].sum(1)
df['Proj_Tot_diff'] = df['Com_total'] - df['Proj_Com_Sum']
print(df)

OUtput:

   Proj_Com_Sum  comp_1  comp_2  comp_3  Proj_Val_sum  val_1  val_2  val_3  \
0            70      10      20      35            67     20     30     15   
1           100      50      30      25            70     25     30     15   

   Com_total  Val_total  Proj_Tot_diff  
0         65         65             -5  
1        105         70              5
user_63615310

filter and assign

df.assign(
    Com_total=df.filter(regex='comp_\d+').sum(1),
    Val_total=df.filter(regex='val_\d+').sum(1),
    Proj_Tot_Diff=lambda d: d.Com_total - d.Proj_Com_Sum
)

  Proj_Com_Sum  comp_1  comp_2  comp_3  Proj_Val_sum  val_1  val_2  val_3  \
0            70      10      20      35            67     20     30     15   
1           100      50      30      25            70     25     30     15   

   Com_total  Val_total  Proj_Tot_Diff  
0         65         65             -5  
1        105         70              5
user_23366540

Edit: as in your edit. to get sum from consecutive columns 2nd - 58th you just use .iloc with 1:58 on columns because integer loc starts from 0 and iloc ignores the right edge number.

df['Com_total'] = df.iloc[:,1:58].sum(1)

Original:
This is a crazy/fun solution using extract on column names and groupby, sum. Finally, join back to df.

df.join(df.groupby(df.columns.str.extract('(comp_|val_)'), axis=1).sum(axis=1) \
          .add_suffix('total').assign(Proj_Tot_Diff= lambda x: x.comp_total - df.Proj_Com_Sum))


Out[1958]:
   Proj_Com_Sum  comp_1  comp_2  comp_3  Proj_Val_sum  val_1  val_2  val_3  \
0            70      10      20      35            67     20     30     15
1           100      50      30      25            70     25     30     15

   comp_total  val_total  Proj_Tot_Diff
0          65         65             -5
1         105         70              5
user_101892140
Related