I have a pandas dataframe as shown below
CID RefID Date Group MID
100 1 1/01/2021 A 101
100 2 3/01/2021 A nan
100 3 4/01/2021 A nan
100 4 15/01/2021 A nan
I want to transform the 'MID' column in a way that :
MID RefID = MID and so on for all the other values. I have used .shift()
method but didn't get the desired output
Expected output:
CID RefID Date Group MID
100 1 1/01/2021 A 101
100 2 3/01/2021 A 102
100 3 4/01/2021 A 104
100 4 15/01/2021 A 107
CodePudding user response:
Try cumsum
and shift
:
>>> df['MID'] = df['MID'].ffill() df['RefID'].shift(fill_value=0).cumsum()
>>> df
CID RefID Date Group MID
0 100 1 1/01/2021 A 101.0
1 100 2 3/01/2021 A 102.0
2 100 3 4/01/2021 A 104.0
3 100 4 15/01/2021 A 107.0
>>>