So this dataset has 2 million records of patients. I've been asked to make every variable dichotomic, and that part is done, but any patient can have multiple records so I have to group them by the patient. When I perform this I lose data; any idea why? This doesn't happen in every field:
I am adding an image of a sample dataframe to perfom the groupby('npaciente), and then you can see that for each column in valu_ counts it is not returnig anything in the complicacionescronicas column
I think there is problem after aggregate max you get all NaNs, so value_counts return empty Series:
df = pd.DataFrame({'A':[1,1,0,np.nan],
'npatience':[np.nan,np.nan,4,5],
'C':[1,0,np.nan,np.nan],
'D':[1,3,5,7]})
print (df)
A C D npatience
0 1.0 1.0 1 NaN
1 1.0 0.0 3 NaN
2 0.0 NaN 5 4.0
3 NaN NaN 7 5.0
print (df.A.value_counts())
1.0 2
0.0 1
Name: A, dtype: int64
print (df.C.value_counts())
0.0 1
1.0 1
Name: C, dtype: int64
g = df.groupby('npatience').max()
print (g)
A C D
npatience
4.0 0.0 NaN 5
5.0 NaN NaN 7
print (g.C)
npatience
4.0 NaN
5.0 NaN
Name: C, dtype: float64
#check if in column are all values NaNs
print (g.C.isnull().all())
True
print (g.A)
npatience
4.0 0.0
5.0 NaN
Name: A, dtype: float64
print (g.C.value_counts())
Series([], Name: C, dtype: int64)
print (g.A.value_counts())
0.0 1
Name: A, dtype: int64
print (g.C.value_counts(dropna=False))
NaN 2
Name: C, dtype: int64
print (g.A.value_counts(dropna=False))
NaN 1
0.0 1
Name: A, dtype: int64
EDIT:
groupby by default remove NaNs rows (cannot groups by NaNs), so it is same as call drop before groupby:
g = df.dropna(subset=['npatience'])
print (g)
A C D
npatience
4.0 0.0 NaN 5
5.0 NaN NaN 7
print (g.C)
2 NaN
3 NaN
Name: C, dtype: float64
#check if in column are all values NaNs
print (g.C.isnull().all())
True
And solution for groupby without remove NaNs is replace NaNs by value (which is not in df) like 1000:
g = df.fillna(1000).groupby('npatience').max()
print (g)
A C D
npatience
4.0 0.0 1000.0 5
5.0 1000.0 1000.0 7
1000.0 1.0 1.0 3
print (g.C.value_counts())
1000.0 2
1.0 1
Name: C, dtype: int64
Of course you are losing data when you doing aggregation (groupby + max) - that's normal.
Demo:
In [5]: df = pd.DataFrame(np.random.randint(0,5,(5,3)), columns=list('abc'))
In [6]: df
Out[6]:
a b c
0 4 1 4
1 4 3 4
2 1 1 0
3 3 3 0
4 4 0 2
In [7]: df.b.value_counts()
Out[7]:
3 2
1 2
0 1
Name: b, dtype: int64
In [8]: df.c.value_counts()
Out[8]:
4 2
0 2
2 1
Name: c, dtype: int64
after aggregation:
In [9]: g = df.groupby('a').max()
In [10]: g
Out[10]:
b c
a
1 1 0
3 3 0
4 3 4
In [11]: g.b.value_counts()
Out[11]:
3 2
1 1
Name: b, dtype: int64
In [12]: g.c.value_counts()
Out[12]:
0 2
4 1
Name: c, dtype: int64
