I want to fill empty cells in Visit and X1 for each Subject with Previous value and return a dataframe.
Subject Visit X1 X2
A aaa 164 16
A creamy 167 168
A
B yyy 173 176
B ice cream 1760 178
B 1788
B ccc 17 17
C cream 1788 1789
C doo 1789 179
output would be like :
Subject Visit X1 X2
A aaa 164 16
A creamy 167 168
A creamy 167
B yyy 173 176
B ice cream 1760 178
B ice cream 1760 1788
B ccc 17 17
C cream 1788 1789
C doo 1789 179
I tried :
df.fillna(method='ffill')
but it is not working and doesn't return the dataframe.
You need groupby with ffill:
In [2960]: df = df.replace('', np.nan)
In [2963]: df[['Visit', 'X1']] = df.groupby('Subject')[['Visit', 'X1']].ffill()
In [2964]: df
Out[2964]:
Subject Visit X1 X2
0 A aaa 164.0 16.0
1 A creamy 167.0 168.0
2 A creamy 167.0 NaN
3 B yyy 173.0 176.0
4 B ice_cream 1760.0 178.0
5 B ice_cream 1760.0 1788.0
6 B ccc 17.0 17.0
7 C cream 1788.0 1789.0
8 C doo 1789.0 179.0