Pandas groupby manipulation

user_15623779 0
Viewed 65

Folks, i have the input:

       0     3     4     5     6
52   JJAA  4.50  4.50  4.50  4.50
92   JJAA  9.00  9.00  9.00  7.50
103  JJAA  1.50  1.50  1.50  1.50
150  JJAT     9     9     9  10.5
164  JJSP  7.50  7.50  7.50  7.50
34   JJSP  4.50  4.50  4.50  4.50
21   JJSP  7.50  7.50  7.50  7.50
135  JJSP  6.00  6.00  6.00  4.50

I want to count how many equals number have in columns 3,4,5 and 6 for eath type of the colum 0.

expected output:

     0     3     4   
0  JJAA  1.50    4
1  JJAA  4.50    4
3  JJAA  7.50    1   
4  JJAA  9.00    3
5  JJAT  9.00    3
6  JJAT  10.5    1
7  JJSP  4.50    5
8  JJSP  6.00    3
9  JJSP  7.50    8
pandas python
2 Answers

You can melt the dataframe, then group by column 0 and the values to get the size of each group.

df.melt(id_vars='0').groupby(['0','value']).size().reset_index(name='count')

Output

      0  value  count
0  JJAA    1.5      4
1  JJAA    4.5      4
2  JJAA    7.5      1
3  JJAA    9.0      3
4  JJAT    9.0      3
5  JJAT   10.5      1
6  JJSP    4.5      5
7  JJSP    6.0      3
8  JJSP    7.5      8
user_47183500

You can stack columns 3 - 6 by .stack() then group by column 0 and get the item counts within group by .value_counts(), as follows:

df.set_index('0').stack().groupby('0').value_counts(sort=False)

Output:

0         
JJAA  1.5     4
      4.5     4
      7.5     1
      9.0     3
JJAT  9.0     3
      10.5    1
JJSP  4.5     5
      6.0     3
      7.5     8
dtype: int64
user_150706970
Related