Pandas replace selective data with new set

Question

l have a digital elevation map Tiff file and by using Gdal module and pandas , all data are written in dem.xlsx excel file and my purpose is to replace max value of each row with 1 ,other positive values with 0 and negative values with -9999 and then ,write the result in new excel. However, l could not able to replace values and write in new excel.

here is my code:

import pandas as pd
import xlrd
import xlsxwriter
import gdal

layer = gdal.Open(r"C:/Users/owrasa/PycharmProjects/den/dem/dem.tif")
arr=layer.ReadAsArray()
df=pd.DataFrame(arr)

writer1 = pd.ExcelWriter(
    'dem.xlsx', engine='xlsxwriter')
df.to_excel(writer1, 'Sheet1')
writer1.save()
df=pd.read_excel("dem.xlsx")
writer2 = pd.ExcelWriter('output.xlsx',engine='xlsxwriter')


for i in df:
    for a in range(2851):
        if i>0 and i<df.ix[a].max():
            df.replace(i,0)
        elif i==df.ix[a].max():
            df.replace(i,1)
        else:
            df.replace(i,-9999)

    df.to_excel(writer2, 'Sheet1')
    writer2.save()

dem excel file has 2098 row x 2851 column.

Answer 1

try:

df.eq(df.max(1), 0).sub(df.lt(0).mul(9999))