how to fill dataframe with former value?

Question

I import the data from an excel file. But the format of merged cells in excel file does not match in python. Therefore, I have to modify the data in python.

for example: the data I import in python looks like

0   aa
1   NaN
2   NaN
3   NaN
4   b
5   NaN
6   NaN
7   NaN
8   NaN
9   ccc
10  NaN
11  NaN
12  NaN
13  dd
14  NaN
15  NaN
16  NaN

the result I want is:

0   aa
1   aa
2   aa
3   aa
4   b
5   b
6   b
7   b
8   b
9   ccc
10  ccc
11  ccc
12  ccc
13  dd
14  dd
15  dd
16  dd

I tried to use for loop to fix the problem. But it took lots of time and I have a huge dataset. I do not know if there is a faster way to do it.

the data type should be string instead of float, sorry about that — Waynexu, Jun 27 '19 at 06:54
If you have an addition to your question, it's best to edit the question and add it there, not in a comment. — Itamar Mushkin, Jun 27 '19 at 06:55
@Itamar Mushkin I have modified the picture for better understanding my question, thanks for the help — Waynexu, Jun 27 '19 at 07:02

score 1 · Accepted Answer · answered Jun 27 '19 at 06:52

1

Looks like .fillna() is your friend – quoting the documentation::

We can also propagate non-null values forward or backward.

>>> df
     A    B   C  D
0  NaN  2.0 NaN  0
1  3.0  4.0 NaN  1
2  NaN  NaN NaN  5
3  NaN  3.0 NaN  4
>>> df.fillna(method='ffill')
    A   B   C   D
0   NaN 2.0 NaN 0
1   3.0 4.0 NaN 1
2   3.0 4.0 NaN 5
3   3.0 3.0 NaN 4

answered Jun 27 '19 at 06:52

AKX

152,115
15
115
172

Ah, beat me to it :-) – Itamar Mushkin Jun 27 '19 at 06:54
@AKX can you please help me revise the question format again? – Waynexu Jun 27 '19 at 07:03

score 0 · Answer 2 · answered Jun 27 '19 at 06:52

0

This is exactly the use of the .fillna() function in pandas

answered Jun 27 '19 at 06:52

Itamar Mushkin

2,803
2
16
32

score 0 · Answer 3 · answered Jun 27 '19 at 11:56

You can get your desired result with the help of apply AND fillna methods :-

import pandas as pd
import numpy as np

df = pd.DataFrame(data = {'A':['a', np.nan, np.nan, 'b', np.nan]})

l = []
def change(value): 
    if value == "bhale":
        value = l[-1]
        return value
    else:        
        l.append(value)
        return value

# First converting NaN values into any string value like `bhale` here
df['A'] = df['A'].fillna('bhale')  
df["A"] = df['A'].apply(change)   # Using apply method.
df

I hope it may help you.

how to fill dataframe with former value?

3 Answers3