Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
630 views
in Technique[技术] by (71.8m points)

python - What is the recommended way to compute a weighted sum of selected columns of a pandas dataframe?

For example, I would like to compute the weighted sum of columns 'a' and 'c' for the below matrix, with weights defined in the dictionary w.

df = pd.DataFrame({'a': [1,2,3], 
                   'b': [10,20,30], 
                   'c': [100,200,300],
                   'd': [1000,2000,3000]})
w = {'a': 1000., 'c': 10.}

I figured out some options myself (see below), but all look a bit complicated. Isn't there a direct pandas operation for this basic use-case? Something like df.wsum(w)?


I tried pd.DataFrame.dot, but it raises a value error:

df.dot(pd.Series(w))
# This raises an exception:
# "ValueError: matrices are not aligned"

The exception can be avoided by specifying a weight for every column, but this is not what I want.

w = {'a': 1000., 'b': 0., 'c': 10., 'd': 0. }
df.dot(pd.Series(w)) # This works

How can one compute the dot product on a subset of columns only? Alternatively, one could select the columns of interest before applying the dot operation, or exploit the fact that pandas/numpy ignores nans when computing (row-wise) sums (see below).

Here are three methods that I was able to spot out myself:

w = {'a': 1000., 'c': 10.}

# 1) Create a complete lookup W.
W = { c: 0. for c in df.columns }
W.update(w)
ret = df.dot(pd.Series(W))

# 2) Select columns of interest before applying the dot product.
ret = df[list(w.keys())].dot(pd.Series(w))

# 3) Exploit the handling of NaNs when computing the (row-wise) sum
ret = (df * pd.Series(w)).sum(axis=1)
# (df * pd.Series(w)) contains columns full of nans

Was I missing an option?

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

You could use a Series as in your first example, just use reindex afterwards:

import pandas as pd

df = pd.DataFrame({'a': [1,2,3],
                   'b': [10,20,30],
                   'c': [100,200,300],
                   'd': [1000,2000,3000]})

w = {'a': 1000., 'c': 10.}
print(df.dot(pd.Series(w).reindex(df.columns, fill_value=0)))

Output

0    2000.0
1    4000.0
2    6000.0
dtype: float64

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...