CODEWITHSUNDEEP
python-3.xpandasdataframereplace
sabarish jackson
sabarish jackson

Reputation: 149

Overwrite a value in the .Xlsx if a pariculat string is found using Pandas Python

I have an excel sheet and I can get the necessary block of cell using the below piece of code.

   import pandas as pd

    ENVIRONMENT="SIMM"
    excel_file_name=r'C:\Users\spadravsasthu\Desktop\SVN\HSRI-DEV\TEST\checklist.xlsx'
    workbook_sheet = pd.read_excel(excel_file_name,sheet_name='Sheet1',keep_default_na=False)
    df=pd.DataFrame(workbook_sheet,columns=['Environment', 'Available chunk start value','Available chunk end value'])
df['Available chunk start value'] = np.where((df.Environment == 'SIMM'),
                                             df['Available chunk start value'].str.replace('776021800','123456789'),
                                             df['Available chunk start value'])

Below is the output of df

    Environment Available chunk start value Available chunk end value
   7         SIMM                         NaN                 799900000

The original values was

 Environment Available chunk start value Available chunk end value
       7         SIMM        776021800                  799900000

From the above, I want to change 776021800 to 123456789 in the dataframe, but it making it as NaN. Any idea on how can that be done using Pandas!

Upvotes: 0

Views: 39

Answers (1)

Bertil Johannes Ipsen
Bertil Johannes Ipsen

Reputation: 1766

You can use np.where to conditionally replace right in the original dataframe, then save that to Excel.

df = pd.DataFrame({
    'Environment': ['SIMM', 'notSIMM', 'SIMM'],
    'start_value': ['793951420', '793951420', 'something else']
})

print(df)

  Environment     start_value
0        SIMM       793951420
1     notSIMM       793951420
2        SIMM  something else

ENVIRONMENT = 'SIMM'
df['start_value'] = np.where(df.Environment.str.match(ENVIRONMENT), 
                             df['start_value'].str.replace('793951420','793956789'),
                             df['start_value'])
print(df)

  Environment     start_value
0        SIMM       793956789  <-- only this changed
1     notSIMM       793951420
2        SIMM  something else

Edit: For your specific case (note, this saves output to new sheet Sheet2:

import pandas as pd

ENVIRONMENT="SIMM"
excel_file_name=r'C:\Users\spadravsasthu\Desktop\SVN\HSRI-DEV\TEST\checklist.xlsx'
df = pd.read_excel(excel_file_name,sheet_name='Sheet1',keep_default_na=False)
df = df[['Environment', 'Available chunk start value','Available chunk end value']]
df['Available chunk start value'] = np.where(df.Environment.str.match(ENVIRONMENT),
                                             df['Available chunk start value'].str.replace('793951420','793956789'),
                                             df['Available chunk start value'])

with pd.ExcelWriter(excel_file_name, mode='a') as writer:  
    df.to_excel(writer, sheet_name='Sheet2')

Upvotes: 1

Related Questions