在 python 中计算 DataFrame 的每一列中的非零值

我有一个 python-anda-DataFrame，其中第一列是 "user_id"，其余的列是标记(从 "Tag_0"到 "Tag_122")。

我有以下格式的数据:

UserId  Tag_0   Tag_1
7867688 0   5
7867688 0   3
7867688 3   0
7867688 3.5 3.5
7867688 4   4
7867688 3.5 0

我的目标是为每个 user _ id 实现 Sum(Tag)/Count(NonZero(Tags))

df.groupby('user_id').sum()，给我 sum(tag)，但是我对计算非零值毫无头绪

是否可以在一个命令中实现 Sum(Tag)/Count(NonZero(Tags))？

在 MySQL 中，我可以这样做:-

select user_id, sum(tag)/count(nullif(tag,0)) from table group by 1

如有任何帮助，我将不胜感激。

139494 次浏览
小开

To count nonzero values, just do (column!=0).sum(), where column is the data you want to do it for. column != 0 returns a boolean array, and True is 1 and False is 0, so summing this gives you the number of elements that match the condition.

So to get your desired result, do

df.groupby('user_id').apply(lambda column: column.sum()/(column != 0).sum())
小开

My favorite way of getting number of nonzeros in each column is

df.astype(bool).sum(axis=0)

For the number of non-zeros in each row use

df.astype(bool).sum(axis=1)

(Thanks to Skulas)

If you have nans in your df you should make these zero first, otherwise they will be counted as 1.

df.fillna(0).astype(bool).sum(axis=1)

(Thanks to SirC)

小开

Why not use np.count_nonzero?

  1. To count the number of non-zeros of an entire dataframe, np.count_nonzero(df)
  2. To count the number of non-zeros of all rows np.count_nonzero(df, axis=0)
  3. To count the number of non-zeros of all columns np.count_nonzero(df, axis=1)

It works with dates too.

小开

I know this question is old but it seems OP's aim is different from the question title:

My aim is to achieve Sum(Tag)/Count(NonZero(Tags)) for each user_id...

For OP's aim, we could replace 0 with NaN and use groupby + mean (this works because mean skips NaN by default):

out = df.replace(0, np.nan).groupby('UserId', as_index=False).mean()

Output:

    UserId  Tag_0  Tag_1
0  7867688    3.5  3.875

提交答案