Aggregation of DataFrame in Python Pandas?

user_14906835 0
Viewed 52

I have DataFrame like below:

df = pd.DataFrame({"ID" : ["1", "1", "1", "2", "2", "2", "1"],
                   "status" : ["ac", "not", "not", "ac", np.NaN, "ac", "oth"]})

And I need to build DataFrame with columns like below:

  1. NumberAcc - Number of ID with status = "ac"
  2. NumberNaN - Number of ID with status = NanN (missing -> np.nan)
  3. NumberOther - Number of ID with staatus other than "ac" or np.nan (means "not" or "oth")

Could you help me to build DF like below?

enter image description here

pandas aggregation dataframe sum python
2 Answers

You can use a conditional mask to replace anything which is not ac or np.nan as Other and groupby.value_counts , then unstack and format with add_prefix

u = df['status'].where(df['status'].eq("ac")|df['status'].isna(),"Other")

out = (u.groupby(df['ID']).value_counts(dropna=False).unstack(fill_value=0)
        .add_prefix("Number_").reset_index().rename_axis(None,axis=1))

Or;

a = pd.Series(np.select([df['status'].eq("ac"),df['status'].isna()],
              ['acc',np.nan],'other'))
out = (a.groupby(df['ID']).value_counts(dropna=True).unstack(fill_value=0)
        .add_prefix("Numnber_").reset_index())

print(out)

  ID  Number_nan  Number_Other  Number_ac
0  1           0             3          1
1  2           1             0          2

A similar logic but with crosstab as suggested by @Shubham:

u = df['status'].where(df['status'].eq("ac")|df['status'].isna(),"Other")
out = (pd.crosstab(df['ID'],u.fillna("NAN"),dropna=False)
   .add_prefix("Number_").rename_axis(None).reset_index())
user_98406370

You could create the columns via assign, before grouping on the 'ID' and summing:

     (df.assign(NumberAcc=df.status.eq("ac"),
                NumberNaN=df.status.isna(),
                NumberOther=lambda df: ~(df.NumberAcc | df.NumberNaN))
        .groupby("ID")
        .sum())

    NumberAcc   NumberNaN   NumberOther
ID          
1       1           0           3
2       2           1           0
user_71757130
Related