1

I’m really stuck on what should be an easy problem.

I have an excel workbook that I’m making an update to 2 Columns for one record for the clean_data sheet. From there, I’m saving and closing the file.

After that, I’m trying to pull in the updated roll up sheet values as a data frame (graphs_rolling) which has formulas utilizing the clean_data sheet.

When I view the data frame, all the values are Nan. I can open the exel file and see the updated values on the graphs_rolling sheet. What can I do to fix the data frame to populate with values?

Code is shown below:

import pandas as pd
import openpyxl
from openpyxl import load_workbook
 
 
#Import Data with Correct Rows and Columns for SSM Commercial 
book = load_workbook('//CPI Projects//Test//SampleSSM//NewSSM.xlsx')
writer = pd.ExcelWriter('//CPI Projects//Test//SampleSSM//NewSSM.xlsx', engine = 'openpyxl') 
writer.book = book
 
df1 = pd.read_excel('//CPI Projects//Test//SampleSSM//NewSSM.xlsx',sheet_name='clean_data')
df1.loc[df1['ev_id']==20201127, 'commercial_weight'] = 0 df1.loc[df1['ev_id']==20201127, 'commercial'] = 0
book.remove(book['clean_data'])
df1.to_excel(writer, sheet_name = 'clean_data',index=False)
writer.save()
writer.close()
 
df5 = pd.read_excel('//CPI Projects//Test//SampleSSM//NewSSM.xlsx',sheet_name='graphs_rolling_avg',skiprows=30)
print(df5)
Josh
  • 11
  • 1
  • It may need [a refresh on the formulas](https://stackoverflow.com/questions/40893870/refresh-excel-external-data-with-python) before reopening based on [this question and answers](https://stackoverflow.com/questions/59837519/pandas-read-excel-returning-nan-for-cells-having-formula). – MDR Aug 07 '21 at 22:14
  • I forgot to mention both data sheets are in same workbook. When I go into excel the values are already updated. I can see them, but the read function isn’t pulling them in. Also, any external connections I won’t have access to since the excel file is sent to me as an output. But, the 2 sheets are in same workbook already. – Josh Aug 07 '21 at 22:24
  • I thought the bit linked about `...the exact same issue as you when reading an excel file that has been appended to using excel writer. It happens because...` sounded similar to what you said above. Since the `df5 = pd.read_excel...` happens immediately afterwards, maybe break the program in two and open+save in Excel before the `df5 = pd.read_excel...` line runs and see if you still get NaNs. Or add `import time` at the top and then above the `df5 = pd.read_excel...` line put time.sleep(60) and during that time open and save in Excel. Only things I can think of. – MDR Aug 07 '21 at 22:39

0 Answers0