CODEWITHSUNDEEP
pythonpandas
Dela
Dela

Reputation: 115

multiply a column by a number in pandas skip or make it zeros if the column does not exists

So I have table whuich I created a pivot from After the pivot I made the index, columns and used one of the column values as columns from which the expected counts are represented. I'd like to multiply each column(values turn columns) to a figure, but because some of the values were not created at that point in time, the column with the specified name did not appear; making the multiplication process run into errors, is there a way I could skip that error or make the the column be Created but show zeros after the multiplication, in cases where the values are not in the converted column

df

**Name Fruit**  
Jane Apple 
Celi Banana 
Pete Mango
Fred grape

pivot

Name Apple Banana   Mango   grape 
Jane  1      0        0      0
Celi  0      1        0      0
Pete  0      0        1      0
Fred  0      0        0      1



pivot['total']=(pivot['Apple '].multiply(14)+ 
                pivot['Banana ].multiply(110)+
                 pivot['Mango'].multiply(18)+
                 pivot['grape].multiply(10)+
                 pivot['peach].multiply(140))

Expected this:

Name Apple Banana   Mango   grape  total
Jane  1      0        0       0      14
Celi  0      1        0       0      110
Pete  0      0        1       0      18
Fred  0      0        0       1      10

But getting errors due to peach not being in the values. is there a way I could skip that error or make the the column be Created but show zeros after the multiplication, in cases where the values are not in the converted column . Any help on this would be appreciated

Upvotes: 0

Views: 99

Answers (1)

jezrael
jezrael

Reputation: 862761

Use DataFrame.dot with mapping columns names:

d = {'Apple':14,'Banana':110,'Mango':18,'grape':10,'peach':140}

df1 = pivot.select_dtypes(np.number)
pivot['total'] = df1.dot(df1.columns.map(d).fillna(0))
#alternative solution
#pivot['total'] = df1.mul(df1.columns.map(d).fillna(0)).sum(axis=1)
print (pivot)
   Name  Apple  Banana  Mango  grape  total
0  Jane      1       0      0      0     14
1  Celi      0       1      0      0    110
2  Pete      0       0      1      0     18
3  Fred      0       0      0      1     10

Or with Series.map for helper column and then use DataFrame.pivot_table:

d = {'Apple':14,'Banana':110,'Mango':18,'grape':10,'peach':140}
pivot = (df.assign(new = df['Fruit'].map(d))
           .pivot_table(index='Name', columns='Fruit', values='new', aggfunc='sum'))

Upvotes: 2

Related Questions