Count_values with where condition to count values of one column with respect to other

Question

I am doing some EDA on my data which is pandas data-frame and I am stuck at this point. I don't know how to count values of infants where party== republican or democrat. I want to apply value_counts on all columns where arty== republican or democrat.

   party infants water budget physician salvador religious satellite 
republican  n     y      n         y        y         y         n   
republican  n     y      n         y        y         y         n   
democrat    ?     y      y         ?        y         y         n   
democrat    n     y      y         n        ?         y         n   
democrat    y     y      y         n        y         y         n

Answer should be like

party           infants 
democrat.    y=1, n=1, ?=1
republican   y=0, n=2, ?=0

like that for all columns. I have tried something like that.

infants_count = data["infants"].value_counts().where(data['party']=="democrat")
infants_count.loc['y']

I have tried this but It doesn't work. I don't know where I am mistaken. — shahid hamdam, Sep 15 '18 at 16:27
Possible duplicate of [count the frequency that a value occurs in a dataframe column](https://stackoverflow.com/questions/22391433/count-the-frequency-that-a-value-occurs-in-a-dataframe-column) — Sheldore, Sep 15 '18 at 16:29
@Bazingaa above mentioned question can be answered with data.infants.value_counts(). But my case is different — shahid hamdam, Sep 15 '18 at 16:30
@Bazingaa I am trying it like that. grouped = data.groupby('party') print (grouped.count()) print (grouped.infants.value_counts()). It seems to work but does't it? — shahid hamdam, Sep 15 '18 at 16:35
I wish I could try solving it but currently I will have to write your DataFrame myself in a code form. — Sheldore, Sep 15 '18 at 16:37
I can't graph it like that. party infants democrat y 156 n 102 ? 9 republican n 134 y 31 ? 3 — shahid hamdam, Sep 15 '18 at 16:39

Scott Boston · Accepted Answer · 2018-09-15T18:45:40.183

2

Let's try:

df_out = df.groupby('party')['infants'].value_counts().unstack(0).fillna(0)

df_out.apply(lambda x: ','.join(x.index +'='+x.astype(int).astype(str)))\
      .rename('infant')\
      .reset_index()

Output:

        party       infant
0    democrat  ?=1,n=1,y=1
1  republican  ?=0,n=2,y=0

Update to answer comment below:

df_out = df.groupby('party')['infants'].value_counts().unstack(-1).fillna(0)

df_out

Output:

infants       ?    n    y
party                    
democrat    1.0  1.0  1.0
republican  0.0  2.0  0

Plotting

df_out = df.groupby('party')['infants'].value_counts().unstack(-1).fillna(0)
ax = df_out.plot.bar(stacked=True)

Output:

Select a single value:

df_out.loc['democrat','y']

Output:

1.0

edited Sep 15 '18 at 18:45

answered Sep 15 '18 at 16:58

Scott Boston

147,308
15
139
187

Thanks you, It is doing what I want but can i get value of y or n from infant column separately? – shahid hamdam Sep 15 '18 at 17:03
there are 16 columns like infants. how this dataframe will work for graphs? – shahid hamdam Sep 15 '18 at 17:10
can you write a code to show me how i can get y for democrat from infants? please remember there are many columns like infants. – shahid hamdam Sep 15 '18 at 17:11

Count_values with where condition to count values of one column with respect to other

1 Answers1

Update to answer comment below: