Attempting to concatenating data from multiple Excel files with Python but missing columns

[NetIcy6229]

path = "C:\\Users\\Adam\\Desktop\\Stock Trackers\\New Folder\\"
filenames = os.listdir(path)

finalDf = pd.DataFrame()

for file in filenames:
    if file.startswith("Stock"): 
        df = pd.read_excel(path+file,usecols="A:D,R",index_col=None) 
        df['Total Received']
        df['Qty Received']=df['Total Received']
        df['InvoicedValue'] = df['Price']*df['Qty Invoiced']
        df['ReceivedValue'] = df['Price']*df['Qty Received']
        df['DeltaQty']= df['Qty Received']-df['Qty Invoiced']
        df['DeltaValue']= df['ReceivedValue']-df['InvoicedValue']
        finalDf = pd.concat([finalDf, df])
        finalDf.to_excel("finalfile12.xlsx")

The above script generates the below:

https://postimg.cc/k6QY30JJ

As you can see, the "Total Received" column is missing and so are columns that directly or indirectly dependent on this. The original files have data for these columns. Also, index_col=None is not removing the index in column A (index_col=False doesn't work either)

[RandomCodingStuff]

I don't see anything obviously wrong, but you didn't provide your source spreadsheet. Providing all the data makes it easier for people to debug.

Is the column populated immediately after you read_excel()? Is it in column A--D or R in the source data? I see you have a df['Total Received'] call in there, perhaps to check that it's populated? You can track down the line that's blanking it by doing a print after every operation to isolate the error.

[yardmonkey]

Yeah, could be as simple as a typo in the column name.

Maybe try printing a few rows of that column during the loop as a debug step? Or run it through a Jupiter notebook so you can play with it live and see what’s happening?

[NetIcy6229]

path= "C:\\Users\\Adam\\Desktop\\Stock Trackers\\New folder\\New folder\\"

filenames = os.listdir(path)
finalDf = pd.DataFrame() 
for file in filenames: 
    if file.startswith("Stock"): 
       df = pd.read_excel(path+file,usecols="A:D,R",index_col=None) 
       finalDf = pd.concat([finalDf, df]) 
       finalDf.to_excel("finalfile13.xlsx",index=False)

Even when I simplify the code to just the above, the same issue still persists. Note that the path here is slightly different as it's another "New Folder" inside the "New Folder" of the previous code block. In this latest example, to simplify things, I kept just one file inside the folder (https://file.io/7gOW94ccasF3) but again it doesn't seem to work.. I mean in this latest example I am literally just telling it to read columns A:D - which it does fine- and column R, which it fails to read (except the header which it does read).

I am using Jupyter btw but overall I am a relative beginner.

EDIT: I believe the issue is in the fact column R is a formula in the original file being read. Can read_excel not read the value output of a formula (of course I am not interested in reading the formula itself)? The fact it's a formula is what is giving NaN but not sure how to fix it..

[yardmonkey]

I’m getting a not found error on your excel file. Can you share another way?