CODEWITHSUNDEEP
pythonpandas
PV8
PV8

Reputation: 6260

Divide several columns with the same column name ending by one other column in python

I have a smiliar question to this one.

I have a dataframe with several rows, which looks like this:

Name   TypA   TypB ... TypF TypA_value TypB_value ... TypF_value Divider
1       1      1        NaN     10        5             NaN         5
2       NaN    2        NaN     NaN       20            NaN         10

and I want to divide all columns with the ending "value" by the column "Divider", how can I do so? One trick would be to use the sorting, to use the answer from above, but is there a direct way for it? That I do not need to sort the dataframe.

The outcome would be:

Name   TypA   TypB ... TypF TypA_value TypB_value ... TypF_value Divider
1       1      1        NaN     2        1             0         5
2       NaN    2        NaN     0        2             0         10

So a NaN will lead to a 0.

Upvotes: 1

Views: 397

Answers (3)

Resham Wadhwa
Resham Wadhwa

Reputation: 129

Taking two sample columns A and B :

import pandas as pd
import numpy as np

a={ 'Name':[1,2],
    'TypA':[1,np.nan],
    'TypB':[1,2],
    'TypA_value':[10,np.nan],
    'TypB_value':[5,20],
    'Divider':[5,10]
   }
df = pd.DataFrame(a)
cols_all = df.columns

Find columns for which calculations are to be done. Assuming there all have 'value' and an underscore :

cols_to_calc = [c for c in cols_all if '_value' in c]

For these columns: first, divide with the divider column then replace nan with 0 in those columns.

for c in cols_to_calc:
    df[c] = df[c] / df.Divider
    df[c] = df[c].fillna(0)

Upvotes: 0

yatu
yatu

Reputation: 88236

You could select the columns of interest using DataFrame.filter, and divide as:

value_cols = df.filter(regex=r'_value$').columns
df[value_cols] /= df['Divider'].to_numpy()[:,None]
# df[value_cols] = df[value_cols].fillna(0)

print(df)

   Name  TypA  TypB  TypF  TypA_value  TypB_value  TypF_value  Divider
0     1   1.0     1   NaN         2.0         1.0         NaN        5
1     2   NaN     2   NaN         NaN         2.0         NaN       10

Upvotes: 2

Shubham Sharma
Shubham Sharma

Reputation: 71689

Use DataFrame.filter to filter the columns like value from dataframe then use DataFrame.div along axis=0 to divide it by column Divider, finally use DataFrame.update to update the values in dataframe:

d = df.filter(like='_value').div(df['Divider'], axis=0).fillna(0)
df.update(d)

Result:

   Name  TypA  TypB  TypF  TypA_value  TypB_value  TypF_value  Divider
0     1   1.0     1   NaN         2.0         1.0         0.0        5
1     2   NaN     2   NaN         0.0         2.0         0.0       10

Upvotes: 2

Related Questions