Replace zeros in a column with string from the row above (Python/Pandas)

Question

I would like to replace the 0 with the string from the same column, previous row. Eg: 0 under Sheffield should read Sheffield. I am working with pandas.

file = file[['Branch', 'Type' ,'total']]
#replace NaN with 0 
file.fillna(0).tail(6)
Out[48]: 
     Branch                     Type  total

394   Sheffield  Sum of Resend to Branch      0
395           0   Number of PV Enquiries     83
396   Wakefield  Sum of Resend to Branch      0
397           0   Number of PV Enquiries     38
398        York  Sum of Resend to Branch      1
399           0   Number of PV Enquiries     59

I have tried:
a) #create a  series for that column and replace
branch = file.iloc[ :, 0]
branch.replace(0, branch(-1))
# why is this series not callable?

b)# I tried a loop in the dataframe
for item in file:
    if "Branch" == 0:
        replace(0, "Branch"[-1])
# I am unsure how to refer to the row above

sacuL · Accepted Answer · 2018-07-24 17:31:47Z

Use replace with the method ffill

file_df['Branch'].replace(to_replace='0', method='ffill', inplace=True)

>>> file_df
        Branch                     Type  total
394  Sheffield  Sum of Resend to Branch      0
395  Sheffield   Number of PV Enquiries     83
396  Wakefield  Sum of Resend to Branch      0
397  Wakefield   Number of PV Enquiries     38
398       York  Sum of Resend to Branch      1
399       York   Number of PV Enquiries     59

Or, since it looks like you already replaced the NaN with 0, you could omit that step and just use ffill. i.e. if your original dataframe looks like:

>>> file_df
        Branch                     Type  total
394  Sheffield  Sum of Resend to Branch      0
395        NaN   Number of PV Enquiries     83
396  Wakefield  Sum of Resend to Branch      0
397        NaN   Number of PV Enquiries     38
398       York  Sum of Resend to Branch      1
399        NaN   Number of PV Enquiries     59

use:

file_df['Branch'].ffill(inplace=True)

Note that I called your dataframe file_df rather than file to not mask the python builtin

Collectives™ on Stack Overflow

Replace zeros in a column with string from the row above (Python/Pandas)

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related