Im trying to divide some columns by fixed number (1000) and remove commas, also change mix type into int with the second last code line. Except the list of columns, I do have other columns that are being deleted after executing the code. How can I keep other columns?

df_1 = pd.read_excel(os.path.join(directory,'copy.xlsm'), sheet_name= "weekly",header= None)
df_1 = df_1.drop(df_1.columns[[0,1]], axis=1)

df_1.columns = df_1.loc[3].rename(None)
df_1 = df_1.drop(range(5))
columns =["A","B","D", "G"]
df_1=df_1.loc[:len(df_1) - 2, columns].replace(',', '', regex=True).apply(pd.to_numeric) / 1000

df_1.to_csv(directory 'new.csv', index=False, header= True)

CodePudding user response：

Your problem is in this part:

df_1 = df_1.loc[...]...

You're overriding the original value of df_1 to a subset of your columns (and it seems that you're losing some rows too) when using this selector: [:len(df_1) - 2, columns]. You only need to update the values of that selection:

df_1.loc[...] = df_1.loc[...]...

By using loc as the target value to store the result of your operation, you're only modifying those rows and columns with the values where they should be.

Your code should contain this line instead (added for clarity):

df_1.loc[:len(df_1) - 2, columns] = df_1.loc[:len(df_1) - 2, columns].replace(',', '', regex=True).apply(pd.to_numeric) / 1000