Conditional Search Multiple Excel Files – Excel Python Automation – Five Minute Python Scripts

    By
    Derrick Sherrill
    -
    11
    30



    In this video we’ll cover how to can use Python and Pandas to conditionally search for values across multiple spreadsheets and return multiple values back when the conditional is true.

    If you have any comments or suggestions for the next video, please let me know! I appreciate you all.

    Kite helps fund the channel, thanks for checking them out and supporting me —
    ā­ Kite is a free AI-powered coding assistant that will help you code faster and smarter. The Kite plugin integrates with all the top editors and IDEs to give you smart completions and documentation while youā€™re typing. https://www.kite.com/get-kite/?utm_medium=referral&utm_source=youtube&utm_campaign=derricksherrill&utm_content=description-only

    The code used in this video on GitHub:
    https://github.com/Derrick-Sherrill/DerrickSherrill.com/blob/master/search_excel.py

    One of my videos talking about conditional statements:

    Thanks so much for all the support!! 2600+ SUBSCRIBERS! You all are incredible. Thanks so much for your continued support. It means the world.

    *****************************************************************
    Full code from the video:

    import numpy as np
    import pandas as pd

    excel_file = ‘Pandas_Workbook.xlsx’
    df = pd.read_excel(excel_file)
    print(df)

    print(df[‘Name’].where(df[‘Occupation’] == ‘Programmer’))
    programmers = df[‘Name’].where(df[‘Occupation’] == ‘Programmer’)
    print(programmers.dropna())

    excel_files = [‘Pandas_Workbook.xlsx’,’Pandas_Workbook_copy.xlsx’,’Pandas_Workbook_copy_2.xlsx’]

    for individual_excel_file in excel_files:
    df = pd.read_excel(individual_excel_file)
    programmers = df[‘Name’].where(df[‘Occupation’] == ‘Programmer’).dropna()
    print(“File Name” + individual_excel_file)
    print(programmers)

    *****************************************************************
    Code from this tutorial and all my others can be found on my GitHub:
    https://github.com/Derrick-Sherrill/DerrickSherrill.com

    Check out my website:
    https://www.derricksherrill.com/

    If you liked the video – please hit the like button. It means more than you know. Thanks for watching and thank you for all your support!!

    Always looking for suggestions on what video to make next — leave me a comment with your project! Happy Coding!

    source

    Previous articleDER STANDARD Karriere-Einblicke: IT Business Analyst bei der Raiffeisen Bank International
    Next articleaws boto3 automation | list ebs volumes using boto3 filter method
    Derrick Sherrill

    RELATED ARTICLESMORE FROM AUTHOR

    30 COMMENTS

    2. Hi Derrick, thanks for the Vid, please can i make a vid request.basically i have a dictionary on my script but i would like to convert the dictionary into an excel sheet and my code reference the excel file as dictionary instead of it living in my code. i want other people to be able to update the excel dictionary instead of requesting me to update my code.

    10. i faced this error
      "Traceback (most recent call last):
      File "<pyshell#39>", line 1, in <module>
      df = pd.read_excel(Excel_file)
      File "C:UsershuaweiAppDataLocalProgramsPythonPython38libsite-packagespandasutil_decorators.py", line 299, in wrapper
      return func(*args, **kwargs)
      File "C:UsershuaweiAppDataLocalProgramsPythonPython38libsite-packagespandasioexcel_base.py", line 336, in read_excel
      io = ExcelFile(io, storage_options=storage_options, engine=engine)
      File "C:UsershuaweiAppDataLocalProgramsPythonPython38libsite-packagespandasioexcel_base.py", line 1071, in _init_
      ext = inspect_excel_format(
      File "C:UsershuaweiAppDataLocalProgramsPythonPython38libsite-packagespandasioexcel_base.py", line 949, in inspect_excel_format
      with get_handle(
      File "C:UsershuaweiAppDataLocalProgramsPythonPython38libsite-packagespandasiocommon.py", line 651, in get_handle
      handle = open(handle, ioargs.mode)
      FileNotFoundError: [Errno 2] No such file or directory: 'ppp' "
      what is the cause?

    26. Here is a task for you if you can do:
      There are 2 Excel files with same headers say old order and new order.
      Headers are like:
      ID, Dish, Your Cost, Your Tax, My Cost, My Tax
      Now there is an ID say 15 with multiple dish like tea, coffee, sandwich and cost and tax in all 4 columns. Now ID and dish remain same in both excel but cost or tax is changing. So I need that particular row where there is a change and in output file I need the data from new file I mean new cost.

      Can you do that? Up until now you are doing easy checks.. so it's a real task!!