0

I don't know if this is possible but I have a data frame like this one:

df

State  County  Homicides Man Woman Not_Register

Gto    Celaya     2       2    0      0 
NaN    NaN        8       4    2      2
NaN    NaN        3       2    1      0
NaN    Yiriria    2       1    1      0
Nan    Acambaro   1       1    0      0
Sin    Culiacan   3       1    1      1
NaN    Nan        5       4    0      1
Chih    Juarez    1       1    0      0

I want to group by State, County, Man Women, Homicides and Not Register. Like this:

State  County  Homicides Man Woman Not_Register

Gto    Celaya     13      8     3     2
Gto    Yiriria    2       1    1      0
Gto    Acambaro   1       1    0      0
Sin    Culiacan   8       5    1      2
Chih    Juarez    1       1    0      0

So far, I been able to group by State and County and fill the rows with NaN with the right name of the county and State. My result and code:

import numpy as np
import math

df = df.fillna(method ='pad')  #To repeat the name of the State and County with the right order

#To group 
df = df.groupby(["State","County"]).agg('sum')
df =df.reset_index()
df

State  County  Homicides 

Gto    Celaya     13      
Gto    Yiriria    2       
Gto    Acambaro   1       
Sin    Culiacan   8       
Chih    Juarez    1

But When I tried to add the Men and woman

df1 = df.groupby(["State","County", "Man", "Women", "Not_Register"]).agg('sum')

df1 =df.reset_index()
df1

My result is repeating the Counties not giving me a unique County for State, How can I resolve this issue?

Thanks for your help

coding
  • 917
  • 2
  • 12
  • 25
  • Does this answer your question? [Pandas group-by and sum](https://stackoverflow.com/questions/39922986/pandas-group-by-and-sum) – Suryaveer Singh Aug 29 '20 at 21:42

1 Answers1

0

Change to

df[['Homicides','Man','Woman','Not_Register']]=df[['Homicides','Man','Woman','Not_Register']].apply(pd.to_numeric,errors = 'coerce')

df = df.groupby(['State',"County"]).sum().reset_index()
BENY
  • 317,841
  • 20
  • 164
  • 234
  • @coding do above before groupby sum – BENY Aug 29 '20 at 21:21
  • Thank you, is working in the way that I want. I will take a deeper look at your code to understand what is happening and just not copy. Thanks again – coding Aug 29 '20 at 21:33