CODEWITHSUNDEEP
pythonpandas
FFLS
FFLS

Reputation: 574

Counting the repeated values in one column base on other column

Using Panda, I am dealing with the following CSV data type:

f,f,f,f,f,t,f,f,f,t,f,t,g,f,n,f,f,t,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,t,t,t,nowin
t,f,f,f,f,f,f,f,f,f,t,f,g,f,b,f,f,t,f,f,f,f,f,t,f,t,f,f,f,f,f,f,f,t,f,n,won
t,f,f,f,t,f,f,f,t,f,t,f,g,f,b,f,f,t,f,f,f,t,f,t,f,t,f,f,f,f,f,f,f,t,f,n,won
f,f,f,f,f,f,f,f,f,f,t,f,g,f,b,f,f,t,f,f,f,f,f,t,f,t,f,f,f,f,f,f,f,t,f,n,nowin
t,f,f,f,t,f,f,f,t,f,t,f,g,f,b,f,f,t,f,f,f,t,f,t,f,t,f,f,f,f,f,f,f,t,f,n,won
f,f,f,f,f,f,f,f,f,f,t,f,g,f,b,f,f,t,f,f,f,f,f,t,f,t,f,f,f,f,f,f,f,t,f,n,win

For this part of the raw data, I was trying to return something like:

Column1_name -- t -- counts of nowin = 0

Column1_name -- t -- count of wins = 3

Column1_name -- f -- count of nowin = 2 

Column1_name -- f -- count of win = 1

Based on this idea get dataframe row count based on conditions I was thinking in doing something like this:

print(df[df.target == 'won'].count())

However, this would return always the same number of "wons" based on the last column without taking into consideration if this column it's a "f" or a "t". In other others, I was hoping to use something from Panda dataframe work that would produce the idea of a "group by" from SQL, grouping based on, for example, the 1st and last column.

Should I keep pursing this idea of should I simply start using for loops?

If you need, the rest of my code:

import pandas as pd


url = "https://archive.ics.uci.edu/ml/machine-learning-databases/chess/king-rook-vs-king-pawn/kr-vs-kp.data"

df = pd.read_csv(url,names=[
                       'bkblk','bknwy','bkon8','bkona','bkspr','bkxbq','bkxcr','bkxwp','blxwp','bxqsq','cntxt','dsopp','dwipd',
                        'hdchk','katri','mulch','qxmsq','r2ar8','reskd','reskr','rimmx','rkxwp','rxmsq','simpl','skach','skewr',
                        'skrxp','spcop','stlmt','thrsk','wkcti','wkna8','wknck','wkovl','wkpos','wtoeg','target'
                        ])


features = ['bkblk','bknwy','bkon8','bkona','bkspr','bkxbq','bkxcr','bkxwp','blxwp','bxqsq','cntxt','dsopp','dwipd',
        'hdchk','katri','mulch','qxmsq','r2ar8','reskd','reskr','rimmx','rkxwp','rxmsq','simpl','skach','skewr',
        'skrxp','spcop','stlmt','thrsk','wkcti','wkna8','wknck','wkovl','wkpos','wtoeg','target']


# number of lines 
#tot_of_records = np.size(my_data,0) 
#tot_of_records = np.unique(my_data[:,1])

#for item in my_data:
#    item[:,0]
num_of_won=0
num_of_nowin=0

for item in df.target:
    if item == 'won':
        num_of_won = num_of_won + 1
    else:
        num_of_nowin = num_of_nowin + 1

print(num_of_won)
print(num_of_nowin)        

print(df[df.target == 'won'].count())  

#print(df[:1])
#print(df.bkblk.to_string(index=False))
#print(df.target.unique())
#ini_entropy = (() + ())

Upvotes: 0

Views: 105

Answers (1)

pkapka
pkapka

Reputation: 5346

This could work -

outdf = df.apply(lambda x: pd.crosstab(index=df.target,columns=x).to_dict())

Basically we are going in on each feature column and making a crosstab with target column

enter image description here

Hope this helps! :)

Upvotes: 1

Related Questions