Copy row values of Data Frame along rows till not null and replicate the consecutive not null value further

Question

I have a Dataframe as shown below

   A   B   C      D
0  1   2   3.3   4
1 NaT NaN NaN NaN
2 NaT NaN NaN NaN
3  5   6   7      8
4 NaT NaN NaN NaN
5 NaT NaN NaN NaN
6  9   1   2      3 
7 NaT NaN NaN NaN
8 NaT NaN NaN NaN

I need to copy the first row values (1,2,3,4) till the non-null row with index 2. Then, copy row values (5,6,7,8) till the non-null row with index 5 and copy (9,1,2,3) till row with index 8 and so on. Is there any way to do this in Python or Pandas. Quick help appreciated! Also is necessary not replace column D

Column C ffill gives 3.3456 as value for next row

Expected Output:

 A B C     D
0 1 2 3.3 4
1 1 2 3.3 NaN
2 1 2 3.3 NaN
3 5 6 7 8
4 5 6 7 NaN
5 5 6 7 NaN
6 9 1 2 3 
7 9 1 2 NaN
8 9 1 2 NaN

jezrael · Accepted Answer · 2021-09-17T11:50:59.767

2

Question was changed, so for forward filling all columns without D use Index.difference with ffill for columns names in list:

cols = df.columns.difference(['D'])

df[cols] = df[cols].ffill()

Or create mask for all columns names without D:

mask = df.columns != 'D'

df.loc[:, mask] = df.loc[:, mask].ffill()

EDIT: I cannot replicate your problem:

df = pd.DataFrame({'a':[2114.201789, np.nan, np.nan, 1]})

print (df)
             a
0  2114.201789
1          NaN
2          NaN
3     1.000000

print (df.ffill())
             a
0  2114.201789
1  2114.201789
2  2114.201789
3     1.000000

edited Sep 17 '21 at 11:50

answered Sep 17 '21 at 11:13

jezrael

822,522
95
1,334
1,252

Thanks. Also the floating values are rounded improperly irrespective of the values when using ffill – Avinash Sep 17 '21 at 11:24
Issue is with the floating numbers now – Avinash Sep 17 '21 at 11:31
@Avinash - hmmm, can you be more specific? – jezrael Sep 17 '21 at 11:32
@Avinash - Need `D` column integers - integers with `NaN`s ? Or some column has first row `NaN`, so ffill not repalce first values? – jezrael Sep 17 '21 at 11:33
I had a value 1.53 for one of DF columns. when used ffill(), it populates as 1.52345. not the same 1.53 – Avinash Sep 17 '21 at 11:34
D column is not needed. we can ignore that – Avinash Sep 17 '21 at 11:34
@Avinash - hmmm, I think `1.53` is `1.52345`, only not diplayed last digits. – jezrael Sep 17 '21 at 11:35
Yeah it's difficult to get the exact same 1.53 for all without adding any decimal places – Avinash Sep 17 '21 at 11:38
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/237209/discussion-between-avinash-and-jezrael). – Avinash Sep 17 '21 at 11:40
@Avinash - How working `df['C'] = df['C'].round(2)` ? It seems problem is precision of displayed values [link](https://stackoverflow.com/a/43217982/2901002) – jezrael Sep 17 '21 at 11:40
@ jezrael, I also thought of using round, but got stuck up with a column value which has 2114.201789 as source value and the result after ffill was 2114.201904. Both are different even after round – Avinash Sep 17 '21 at 11:43
I try test it. Interesting – jezrael Sep 17 '21 at 11:44
@Avinash - cannot replicate `df = pd.DataFrame({'a':[2114.201789, np.nan, np.nan, 1]})` – jezrael Sep 17 '21 at 11:45
@Avinash - one trick is multiple by 10000, ffill and then divide. `df['C'] = df['C'].mul(10000).ffill().div(10000)` – jezrael Sep 17 '21 at 11:47
@Avinash - yes, but need `df.astype(str).mask(df.isna()).ffill()` – jezrael Sep 17 '21 at 11:52
@Avinash - btw, memory issue should be converted to strings like multiple and divide. The bes test both solutions – jezrael Sep 17 '21 at 11:53
Sure. I will test it. Thanks in advance!! – Avinash Sep 17 '21 at 11:54

Copy row values of Data Frame along rows till not null and replicate the consecutive not null value further

1 Answers1