CODEWITHSUNDEEP
pythonpandas
bigbug
bigbug

Reputation: 59464

How to fillna the last row of each group in Pandas?

I have a dataframe df whose last row of each group (groupby STK_ID) is NaN :

>>> print df
                   sales  opr_pft  net_pft
STK_ID RPT_Date                           
002138 20130331   2.0703   0.3373   0.2829
       20130630      NaN      NaN      NaN
       20130930   7.4993   1.2248   1.1630
       20140122      NaN      NaN      NaN
600004 20130331  11.8429   3.0816   2.1637
       20130630  24.6232   6.2152   4.5135
       20130930  37.9673   9.2088   6.6463
       20140122      NaN      NaN      NaN
600809 20130331  27.9517   9.9426   7.5182
       20130630  40.6460  13.9414   9.8572
       20130930  53.0501  16.8081  11.8605
       20140122      NaN      NaN      NaN

Now I want fillna the last row of each group with its previous row, the result should be like this:

                   sales  opr_pft  net_pft
STK_ID RPT_Date                           
002138 20130331   2.0703   0.3373   0.2829
       20130630      NaN      NaN      NaN    **(Not fillna this row)**
       20130930   7.4993   1.2248   1.1630
       20140122   7.4993   1.2248   1.1630
600004 20130331  11.8429   3.0816   2.1637
       20130630  24.6232   6.2152   4.5135
       20130930  37.9673   9.2088   6.6463
       20140122  37.9673   9.2088   6.6463
600809 20130331  27.9517   9.9426   7.5182
       20130630  40.6460  13.9414   9.8572
       20130930  53.0501  16.8081  11.8605
       20140122  53.0501  16.8081  11.8605

I almost get it done by: df.groupby(level=0).apply(lambda grp: grp.fillna(method='ffill')), which generate below:

                   sales  opr_pft  net_pft
STK_ID RPT_Date                           
002138 20130331   2.0703   0.3373   0.2829
       20130630   2.0703   0.3373   0.2829
       20130930   7.4993   1.2248   1.1630
       20140122   7.4993   1.2248   1.1630
600004 20130331  11.8429   3.0816   2.1637
       20130630  24.6232   6.2152   4.5135
       20130930  37.9673   9.2088   6.6463
       20140122  37.9673   9.2088   6.6463
600809 20130331  27.9517   9.9426   7.5182
       20130630  40.6460  13.9414   9.8572
       20130930  53.0501  16.8081  11.8605
       20140122  53.0501  16.8081  11.8605

That's not what I want for it fillna all through the rows within the groups. So How to fillna the last row of each group in Pandas ?

Upvotes: 3

Views: 1890

Answers (1)

Alvaro Fuentes
Alvaro Fuentes

Reputation: 17455

You can use another function in the groupby:

def f(g):
    last = len(g.values)-1
    g.iloc[last,:] = g.iloc[last-1,:]
    return g
print df.groupby(level=0).apply(f)

Output:

                   sales  opr_pft  net_pft
STK_ID RPT_Date                           
2138   20130331   2.0703   0.3373   0.2829
       20130630      NaN      NaN      NaN
       20130930   7.4993   1.2248   1.1630
       20140122   7.4993   1.2248   1.1630
600004 20130331  11.8429   3.0816   2.1637
       20130630  24.6232   6.2152   4.5135
       20130930  37.9673   9.2088   6.6463
       20140122  37.9673   9.2088   6.6463
600809 20130331  27.9517   9.9426   7.5182
       20130630  40.6460  13.9414   9.8572
       20130930  53.0501  16.8081  11.8605
       20140122  53.0501  16.8081  11.8605

Upvotes: 6

Related Questions