Is there a Python pandas function for retrieving a specific value of a dataframe based on its conten-CodePudding

I've got multiple excels and I need a specific value but in each excel, the cell with the value changes position slightly. However, this value is always preceded by a generic description of it which remains constant in all excels.

I was wondering if there was a way to ask Python to grab the value to the right of the element containing the string "xxx".

CodePudding user response：

Yes, you can use Python to read data from Excel files and search for a specific string in the file. To do this, you can use the openpyxl library, which allows you to read and write data to Excel files.

Here's an example of how you could use openpyxl to read an Excel file and search for a specific string:

import openpyxl

# Open the Excel file
wb = openpyxl.load_workbook('file.xlsx')

# Select the active sheet in the workbook
sheet = wb.active

# Iterate over the cells in the sheet
for row in sheet.rows:
    for cell in row:
        # Check if the cell contains the string "xxx"
        if "xxx" in cell.value:
            # Print the value of the cell to the right of the one containing "xxx"
            print(cell.offset(column=1).value)

In this code, we open the Excel file using openpyxl.load_workbook, select the active sheet in the workbook using wb.active, and then iterate over the cells in the sheet using a nested for loop.

For each cell, we check if it contains the string "xxx" using the in keyword. If it does, we use the offset method to get the cell to the right of the current cell and print its value.

This code will search for the string "xxx" in all the cells in the Excel file and print the value of the cell to the right of it, if it exists.

I hope this helps!

CodePudding user response：

try iterating over the excel files (I guess you loaded each as a separate pandas object?) somehting like for df in [dataframe1, dataframe2...dataframeN].

Then you could pick the column you need (if the column stays constant), e.g. - df['columnX'] and find which index it has: df.index[df['columnX']=="xxx"]. Maybe will make sense to add .tolist() at the end, so that if "xxx" is a value that repeats more than once, you get all occurances in alist.

The last step would be too take the index 1 to get the value you want.

Hope it was helpful. In general I would highly suggest to be more specific in your questions and provide code / examples.