Nsryjdtyk

I have the following table:
https://ibb.co/DMGCgD2

I want to split (Property Type) to two column count_Single_Family and count_Multi_Family in each zip code:

I used this code but I don't know what to do next:

b=Combineddf[Combineddf['Property Type']=='Multi-Family'].count()
a=Combineddf[Combineddf['Property Type']=='Single Family'].count()

I can do this with sql but I keep trying to get it to work with pandas but no luck. Thank you for the help.
Edit*
Input:

    import pandas as pd

input= pd.DataFrame({'Zip Code': [90001, 90001, 90001,90002,90002,90002], 

                   'Total Males': [28468, 28468, 28468,43533,43533,43533],

                  'Total Female': [32135, 32135, 32135,54354,54354,54354],

                   'Property Type': ['Multi-Family', 'Multi-Family', 'Single Family','Single Family','Single Family','Multi-Family']

                  })

input

Desired output:

    out = pd.DataFrame({'Zip Code': [90001, 90001, 90001,90002,90002,90002], 

                       'Total Males': [28468, 28468, 28468,43533,43533,43533],

                      'Total Female': [32135, 32135, 32135,54354,54354,54354],

                       'Multi-Family': [2, 2, 2,1,1,1],

                       'Single Family': [1, 1, 1,2,2,2],



                      })

out

pd.crosstab + merge

Pandas has a method to cross-tabulate based on an index and values. You then need only merge these results with your original dataframe.

df_cross = pd.crosstab(df['Zip Code'], df['Property Type'])



res = df.merge(df_cross, left_on='Zip Code', right_index=True)



print(res)



   Zip Code  Total Males  Total Female  Property Type  Multi-Family  

0     90001        28468         32135   Multi-Family             2   

1     90001        28468         32135   Multi-Family             2   

2     90001        28468         32135  Single Family             2   

3     90002        43533         54354  Single Family             1   

4     90002        43533         54354  Single Family             1   

5     90002        43533         54354   Multi-Family             1   



   Single Family  

0              1  

1              1  

2              1  

3              2  

4              2  

5              2  

This seems to yield the right solution:

v = input.groupby(['Zip Code','Property Type']).count().reset_index().pivot(index='Zip Code',columns = 'Property Type',values = 'Total Males')



pd.merge(input,v, how = 'inner', left_on = 'Zip Code', right_on ='Zip Code').drop('Property Type',axis = 1)

Another solution:

(pd.concat([input.set_index('Zip Code'), 

            input.groupby('Zip Code')['Property Type']

                 .value_counts()

                 .unstack()], axis=1)

).reset_index()



   Zip Code  Total Males  Total Female  Property Type  Multi-Family  Single Family

0     90001        28468         32135   Multi-Family             2              1

1     90001        28468         32135   Multi-Family             2              1

2     90001        28468         32135  Single Family             2              1

3     90002        43533         54354  Single Family             1              2

4     90002        43533         54354  Single Family             1              2

5     90002        43533         54354   Multi-Family             1              2