How can I use the row containing a string as the dataframe column's titles, e.g:
EXCEL DATA:
FIELDS | VALUES |
---|---|
F1 | V1 |
F2 | V2 |
F3 | V3 |
F4 | V4 |
With F2 as sought value, my Dataframe should be as below:
F2 | V2 |
---|---|
F3 | V3 |
F4 | V4 |
How can I achieve this please, I have tried many scripts with xlwings?
sheet_name='Sheet1'
df = pd.read_excel(file, sheet_name=sheet_name.strip())
search_value='F2'
row_index=df[df.iloc[:,0]==search_value].index[0]
app = xw.App()
wb = app.books.open(file)
# Specify the starting row containing the value
starting_row = row_index
data_range = wb.sheets['Sheet1'].range(f'A{starting_row}').options(pd.DataFrame, header=True, index=False, expand='table').value
wb.close()
app.quit()
print('############### DATAFRAME #################')
print(data_range)
I have used starting_row = row_index +3 and it's ok.
sheet_name='Sheet1'
df = pd.read_excel(file, sheet_name=sheet_name.strip())
search_value='F2'
row_index=df[df.iloc[:,0]==search_value].index[0]
app = xw.App()
wb = app.books.open(file)
# Specify the starting row containing the value
starting_row = row_index + 3
data_range = wb.sheets['Sheet1'].range(f'A{starting_row}').options(pd.DataFrame, header=True, index=False, expand='table').value
wb.close()
app.quit()
print('############### DATAFRAME #################')
print(data_range)