Pandas(Python) : Fill empty cells with with previous row value?

Question

I want to fill empty cells with with previous row value if they start with number. For example, I have

    Text    Text         30      Text    Text                 Text    Text                 Text    Text         31      Text    Text     Text    Text         31      Text    Text                 Text    Text                 Text    Text         32      Text    Text     Text    Text                 Text    Text                 Text    Text                 Text    Text                 Text    Text 

I however, want to have

Text    Text     30      Text    Text     30      Text    Text     30      Text    Text     31      Text    Text Text    Text     31      Text    Text     31      Text    Text     31      Text    Text     32      Text    Text Text    Text             Text    Text             Text    Text             Text    Text             Text    Text 

I tried to reach this by using this code:

data = pd.read_csv('DATA.csv',sep='\t', dtype=object, error_bad_lines=False) data = data.fillna(method='ffill', inplace=True) print(data) 

but it did not work.

Is there anyway to do this?

Answer 1

First, replace your empty cells with NaNs:

df[df[0]==""] = np.NaN 

Now, Use ffill():

df.fillna(method='ffill') #       0 #0  Text #1    30 #2    30 #3    30 #4    31 #5  Text #6    31 #7    31 #8    31 #9    32 

Answer 2

I think you can first get NaN instead whitespaces:

df.Text = df.Text[df.Text.str.strip() != '']  print (df)     Text Text.1       0     30   Text  Text 1    NaN   Text  Text 2    NaN   Text  Text 3     31   Text  Text 4   Text   Text   NaN 5     31   Text  Text 6    NaN   Text  Text 7    NaN   Text  Text 8     32   Text  Text 9   Text   Text   NaN 10   NaN   Text  Text 11   NaN   Text  Text 12   NaN   Text  Text 13   NaN   Text  Text 

Then use ffill (same as fillna with parameter ffill), get to_numeric for where for replace NaN if not numeric forward filling NaN, last replace NaN by empty string by fillna:

orig = df.Text.copy() df.Text = df.Text.ffill() mask1 = pd.to_numeric(df.Text, errors='coerce') df.Text = df.Text.where(mask1, orig).fillna('') print (df)     Text Text.1       0     30   Text  Text 1     30   Text  Text 2     30   Text  Text 3     31   Text  Text 4   Text   Text   NaN 5     31   Text  Text 6     31   Text  Text 7     31   Text  Text 8     32   Text  Text 9   Text   Text   NaN 10         Text  Text 11         Text  Text 12         Text  Text 13         Text  Text