CODEWITHSUNDEEP
pandas
user15050871
user15050871

Reputation:

how to do weighted average in pandas?

I have a few rows (30 rows) and many columns. I wanna find a weighted sum for each row. that is I wanna multiply row j with a_j and sum with others. It is easy to do manually but I wanna do that as a function. is it possible?

data

index c1. c2.   c3. 
a    1    2     3
b    2    10    0
c   3    2      3
d   3     3     3
e   2    1     0

I have the list of constant: cons=[0.1,0.2,0.3,0.4,0.5]

I want a new row which gives me this as in c1 column. for other column calculation is the same.

   new_col
   1*0.1  +  2*0.1 +    3*0.1 +3*0.4   +2*0.5

I did this

cons=[0.1,0.2,0.3,0.4,0.5]
for i in range(len(cons)):
    for j in range(df.shape[1]):
        df.iloc[i,j]=df.iloc[i,j]*cons[i]


Error: IndexError: single positional indexer is out-of-bounds

why I have this error and how should I do the sum part?

Upvotes: 1

Views: 38

Answers (1)

jezrael
jezrael

Reputation: 862641

Use DataFrame.mul with sum:

cons=[0.1,0.2,0.3,0.4,0.5]

df = df.mul(cons, axis=0).sum(axis=1)
print (df)
index
a    0.6
b    2.4
c    2.4
d    3.6
e    1.5
dtype: float64

If need sum per rows:

cons=[0.1,0.2,0.3,0.4,0.5]

out = df.mul(cons, axis=0).sum(axis=0)
print (out)
c1.    3.6
c2.    4.5
c3.    2.4
dtype: float64

out = df.T.dot(cons)
print (out)
c1.    3.6
c2.    4.5
c3.    2.4
dtype: float64

EDIT: I think you need:

print (df)
       c1.  c2.  c3.
index               
a        1    2    3
b        2   10    0
c        3    2    3
d        3    3    3
e        2    1    0

cons=[0.1,0.2,0.3]

df1 = df.mul(cons, axis=1)
print (df1)
       c1.  c2.  c3.
index               
a      0.1  0.4  0.9
b      0.2  2.0  0.0
c      0.3  0.4  0.9
d      0.3  0.6  0.9
e      0.2  0.2  0.0

out = df1.sum(axis=1)
print (out)
index
a    1.4
b    2.2
c    1.6
d    1.8
e    0.4
dtype: float64

out = df.dot(cons)
print (out)
index
a    1.4
b    2.2
c    1.6
d    1.8
e    0.4
dtype: float64

Upvotes: 1

Related Questions