CODEWITHSUNDEEP
pythonexcelpandasdataframemath
RSM
RSM

Reputation: 673

Python: Calculate mathematical values in new row in dataframe based on few specific previous rows

I have the below pandas dataframe:

Input:

        A              B        C
        Expense        2        3
        Sales          5        6
        Travel         8        9

My Expected Output is:

        A              B        C
        Expense        2        3
        Sales          5        6
        Travel         8        9
        Total Exp      10       12

The last tow is basically total of row 1 and row 3. This is a very simplified example, i actually have to perform complex calculation on a huge dataframe.

Is there a way in python to perform such calculation?

Upvotes: 0

Views: 56

Answers (1)

jezrael
jezrael

Reputation: 863156

You can select rows by positions with DataFrame.iloc and sum, then assign to new row:

df.loc[len(df.index)] = df.iloc[0] + df.iloc[2]

Or:

df.loc[len(df.index)] = df.iloc[[0,2]].sum()

print (df)
   A   B   C
0  1   2   3
1  4   5   6
2  7   8   9
3  8  10  12

EDIT: First idea is create index by A column, so you can use loc with new value of A, but last step is convert index to column by reset_index:

df = df.set_index('A')
df.loc['Total Exp'] = df.iloc[[0,2]].sum()
df = df.reset_index()
print (df)
           A   B   C
0    Expense   2   3
1      Sales   5   6
2     Travel   8   9
3  Total Exp  10  12

Similar is possible selecting by loc by labels - here Expense and Travel:

df = df.set_index('A')
df.loc['Total Exp'] = df.loc[['Expense', 'Travel']].sum()
df = df.reset_index()
print (df)
           A   B   C
0    Expense   2   3
1      Sales   5   6
2     Travel   8   9
3  Total Exp  10  12

Or is possible filter out first column with 1: and add value back by Series.reindex:

df.loc[len(df.index)] = df.iloc[[0,2], 1:].sum().reindex(df.columns, fill_value='Total Exp')
print (df)
          A   B   C
0    Expense   2   3
1      Sales   5   6
2     Travel   8   9
3  Total Exp  10  12

Or you can set value of A separately:

s = df.iloc[[0,2]].sum()
s.loc['A'] = 'Total Exp'
df.loc[len(df.index)] = s
print (df)
           A   B   C
0    Expense   2   3
1      Sales   5   6
2     Travel   8   9
3  Total Exp  10  12

Upvotes: 1

Related Questions