Create column names based on existing columns and fill values

Question

I have a dataframe as such:

Year      Col1  Col2  Col3  Col4 ......Col64  Reach
2019       12   17     11   11                 10
2020       10   20     21   33                 10
2021       19   15     22   32                 10
2022       26   9      16   12                 10

I want to append existing columns to the df such as:

df['Col1 Value'] =df['Col1']* df['reach']

df['Col2 Value'] =df['Col2']* df['reach'] . . .

Year      Col1  Col2  Col3  Col4 ......Col64  Reach   Col1 Value Col2 Value...... Col64 Value
2019       12   17     11   11                 10     120          170
2020       10   20     21   33                 10     100          200
2021       19   15     22   32                 10     190          150
2022       26   9      16   12                 10     260           90

I could do this manually but of course that would be inefficient to type this 64 times, is there anyway I can do this otherwise?

jezrael · Accepted Answer · 2022-11-10 06:17:47Z

2

Use Index.difference for all columns names without 'Year','Reach', create subset and multiple by column Reach, then DataFrame.add_suffix and append to original by DataFrame.join:

cols = df.columns.difference(['Year','Reach'], sort=False)

df = df.join(df[cols].mul(df['Reach'], axis=0).add_suffix(' Value'))
print (df)
   Year  Col1  Col2  Col3  Col4  Reach  Col1 Value  Col2 Value  Col3 Value  \
0  2019    12    17    11    11     10         120         170         110   
1  2020    10    20    21    33     10         100         200         210   
2  2021    19    15    22    32     10         190         150         220   
3  2022    26     9    16    12     10         260          90         160   

   Col4 Value  
0         110  
1         330  
2         320  
3         120

Your solution should be rewrite in loop:

for col in df.columns.difference(['Year','Reach'], sort=False):
    df[f'{col} Value'] = df[col] * df['Reach']
print (df)
   Year  Col1  Col2  Col3  Col4  Reach  Col1 Value  Col2 Value  Col3 Value  \
0  2019    12    17    11    11     10         120         170         110   
1  2020    10    20    21    33     10         100         200         210   
2  2021    19    15    22    32     10         190         150         220   
3  2022    26     9    16    12     10         260          90         160   

   Col4 Value  
0         110  
1         330  
2         320  
3         120

edited Nov 10, 2022 at 6:17

answered Nov 10, 2022 at 6:10

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Jahrakal Over a year ago

hi, what would be an alternative if there were more than 30 columns like year and reach which should not get the suffix? that is those columns need to remain the same without any change

Jahrakal Over a year ago

is there any way for me to exclude more columns than year and reach?

jezrael Over a year ago

@Jahrakal - Sure, add excluded columns to lsit, instead cols = df.columns.difference(['Year','Reach'], sort=False) use cols = df.columns.difference(['Year','Reach','Col2','Col5'], sort=False) for add excluded columns.

Jahrakal Over a year ago

hi thanks for the help, is there a way for me to sum each of the individual 'ColX Value' based on year, creating a new column called 'Col X sum', similarly for 'ColY Value'' as 'ColY sum'?

jezrael Over a year ago

@Jahrakal - Not understand

|

Collectives™ on Stack Overflow

Create column names based on existing columns and fill values

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related