How to subtract selected columns from one certain column?-CodePudding

Im trying to subtract selected columns from one certain column, but its giving me an error "ValueError: Expected a 1D array, got an array with shape (3, 4)". My csv file looks like this:

   Number  Nimi  Summa  Kino
0       1   Jan   30.5  12.5
1       2  Pets   30.5  12.5
2       3  Anni   30.5  12.5

Because there can be unlimited amount of columns created by the user I need to select every column except "Number, Nimi and Summa". Outcome should be every column that comes after "Summa" needs to be subtracted from "Summa". I hope this makes sense.

df = pd.read_csv ('eelarve.csv')
columns_dont_want = ["Number", "Nimi", "Summa"]
select = [x for x in df.columns if x not in columns_dont_want]
df["Summa järel"] = df["Summa"] - df.loc[:, select]
df.to_csv('eelarve.csv', index=False)

CodePudding user response：

You can do drop rsub:

df.drop(columns=columns_dont_want).rsub(df['Summa'], axis=0)

output:

   Kino
0  18.0
1  18.0
2  18.0

To join the other columns:

out = (df[columns_dont_want]
       .join(df.drop(columns=columns_dont_want)
               .rsub(df['Summa'], axis=0))
      )

output:

   Number  Nimi  Summa  Kino
0       1   Jan   30.5  18.0
1       2  Pets   30.5  18.0
2       3  Anni   30.5  18.0

CodePudding user response：

I added more columns with data to the df to make sure the code below worked.

import pandas as pd

df = pd.DataFrame({'Number': [1, 2, 3],
                    'Nimi': ['Jan', 'Pets', 'Anni'],
                    'Summa': [30.5, 30.5, 30.5],
                    'Kino': [12.5, 12.5, 12.5],
                    'Test1': [0, 1, 2],
                    'Test2': [0.1, 0.2, 0.3]
                    })

columns_dont_want = ["Number", "Nimi", "Summa"]
select = [x for x in df.columns if x not in columns_dont_want]

df['Summa järel'] = df['Summa'] - df[select].sum(axis=1)

Output is:

   Number  Nimi  Summa  Kino  Test1  Test2  Summa järel
0       1   Jan   30.5  12.5      0    0.1         17.9
1       2  Pets   30.5  12.5      1    0.2         16.8
2       3  Anni   30.5  12.5      2    0.3         15.7