Count values and create new dataframe

Question

I have a dataframe that looks like this:

df
    Daily Risk Score
0   13.0
1   10.0
2   25.0
3   7.0
4   18.0
... ...
672 14.0
673 9.0
674 15.0
675 6.0
676 13.0

I want to count the number of times a value of 0<x<9, 9<x<17 and >=17 occurs. I tried doing this:

df1=pd.cut(df['Daily Risk Score'], bins=[0, 9, 17, np.inf], labels=['Green','Orange','Red'])

However, all this does is change the value to the label. What I want is a new dataframe that just has the counts of the values like this:

df1
Green   Orange   Red
x   y   z

What am I missing to accomplish this task?

David Erickson · Accepted Answer · 2020-07-07 20:37:06Z

1

Use .groupby and .transpose at the end of this code.

df1 = pd.cut(df['Daily Risk Score'],
             bins=[0, 9, 17, np.inf],
             labels=['Green','Orange','Red']).reset_index(). \
             groupby('Daily Risk Score').count().transpose()
df1

output:

 Daily Risk Score   Green   Orange  Red
 index              3       4       2

edited Jul 7, 2020 at 20:37

answered Jul 7, 2020 at 20:26

David Erickson

16.7k2 gold badges21 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Mohak Gangwani · Accepted Answer · 2020-07-07 20:32:27Z

0

I tried it with a bit different method. Its easy too. Try it out and let me know if you face any issue/error. Here you go:

df["col"] = 0
for i in range(len(df)):
    if 0<df["Daily Risk Score"][i]<9:
        df["col"][i] = "0<Daily Risk Score<9"
    elif 9<df["Daily Risk Score"][i]<17:
        df["col"][i] = "9<Daily Risk Score<17"
    elif 9<df["Daily Risk Score"][i]<17:
        df["col"][i] = "Daily Risk Score>=17"
    else:
        df["col"][i] = "other"
df["col"].value_counts()
df.drop(columns=["col"])

answered Jul 7, 2020 at 20:32

Mohak Gangwani

1041 silver badge8 bronze badges

Comments

Pygirl · Accepted Answer · 2020-07-07 20:39:45Z

0

Try:

df1=df.groupby(pd.cut(df['Daily Risk Score'], bins=[0, 9, 17, np.inf], labels=['Green','Orange','Red'])).size()

df1:

          Daily Risk Score
Green     3
Orange    5
Red       2
dtype: int64

OR

df1=df.groupby(pd.cut(df['Daily Risk Score'], bins=[0, 9, 17, np.inf], labels=['Green','Orange','Red'])).size()
df2 = pd.DataFrame(df1.reset_index().values.T)
df2.columns = df2.iloc[0]
df2 = df2[1:]

df2:

    Green   Orange  Red
1   3       5       2

edited Jul 7, 2020 at 20:39

answered Jul 7, 2020 at 20:30

Pygirl

13.4k6 gold badges36 silver badges48 bronze badges

Collectives™ on Stack Overflow

Count values and create new dataframe

3 Answers 3

Comments

Comments

OR

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

OR

Comments

Your Answer

Sign up or log in

Post as a guest

Related