I want to add dataframe to excel every time the code executes, in the last row available in the sheet. Here is the code I am using:
import pandas as pd
import pandas
from openpyxl import load_workbook
def append_df_to_excel(df, excel_path):
df_excel = pd.read_excel(excel_path)
result = pd.concat([df_excel, df], ignore_index=True)
result.to_excel(excel_path)
data_set1 = {
'Name': ['Rohit', 'Mohit'],
'Roll no': ['01', '02'],
'maths': ['93', '63']}
df1 = pd.DataFrame(data_set1)
append_df_to_excel(df1, r'C:\Users\kashk\OneDrive\Documents\ScreenStocks.xlsx')
My desired output(after 3 code runs):
Rohit 1 93
Mohit 2 63
Rohit 1 93
Mohit 2 63
Rohit 1 93
Mohit 2 63
But what I get:
Unnamed: 0.1 Unnamed: 0 Name Roll no maths
0 0 0 Rohit 1 93
1 1 1 Mohit 2 63
2 2 Rohit 1 93
3 3 Mohit 2 63
4 Rohit 1 93
5 Mohit 2 63
Not sure where I am going wrong.
CodePudding user response:
It's happening because in a default situation these functions like to_excel
or to_csv
(and etc.) add a new column with index. So every time you save the file, it adds a new column.
That's why you just should change the raw where you save your dataframe to a file.
result.to_excel(excel_path, index=False)