4

I have a df containing several columns, but two of them look like:

number output colour
1         1    green
2         1    red
3         1    orange
4         0    green
5         1    green

I need to find all possible combinations and the number of occurences there so I expect something like

1green    2
1red      1
1orange   1
0green    1

Using this python: Combination of two Columns I found all possible combinations, but I do not know how to do the sum. Or is there any solution to do it in one step?

THank you

Improve this question

2 Answers 2

Reset to default
4

Try:

pd.value_counts([*zip(df.output, df.colour)])

(1, green)     2
(0, green)     1
(1, orange)    1
(1, red)       1
dtype: int64
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

3

Use Series.value_counts with joined columns:

s = (df.output.astype(str) + df.colour).value_counts()
print (s)
1green     2
1red       1
0green     1
1orange    1
dtype: int64

Or is possible use GroupBy.size:

s = df.groupby(['output','colour']).size()
print (s)
output  colour
0       green     1
1       green     2
        orange    1
        red       1
dtype: int64

Or DateFrame.value_counts

s = df[['output','colour']].value_counts()
print (s)
output  colour
1       green     2
0       green     1
1       orange    1
        red       1
dtype: int64

Last for DataFrame use:

s = s.reset_index(name='count')

EDIT:

For all combinations use:

s = df.groupby(['output','colour']).size().unstack(fill_value=0).stack()
print (s)
output  colour
0       green     1
        orange    0
        red       0
1       green     2
        orange    1
        red       1
dtype: int64

s = df[['output','colour']].value_counts().unstack(fill_value=0).stack()
print (s)
output  colour
0       green     1
        orange    0
        red       0
1       green     2
        orange    1
        red       1
dtype: int64
Improve this answer

8 Comments

Wow! df[['output','colour']].value_counts() That's new. When did they add that functionality?
@piRSquared - yes, exactly.
@piRSquared I think v1.1+ I discovered this just yesterday too :)
thank you, that are so elegant solutions!
@ZuzanaTelefony - You are welcome! Btw, it seems your nick is like in czech or slovak language, Telefony means phones ;)
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.