Python/Pandas equivalent to Excel SUMIFS function with two datasets involved-CodePudding

I have a SUMIFS function involving two excel sheets for calculations =SUMIFS('Sheet_1'!$D:$D, 'Sheet_2'!$A:$A,">"&'Some_Value', 'Sheet_2'!$B:$B,"<="&'Some_Value_2')

I am able to implement the SUMIFS function in pandas with the use of df.query() method where all the fields are from same excel sheet. df.query([criterias])['column_to_be_summed_up'].sum() How to figure out the SUMIFS with multiple criteria from multiple sheets?

Not able to figure out the syntax or logic for this

CodePudding user response：

you have to read different sheets with different df names first as following:

import pandas as pd
xls = pd.ExcelFile('path_to_file.xls')
df1 = pd.read_excel(xls, 'Sheet1')
df2 = pd.read_excel(xls, 'Sheet2')

And then, you have to join these to different Dataframes. I've given inner method as an example but for your case outer join can be usefull

df = pd.merge(df1, df2, how='inner', on='{column_name'))

And after this process you can query your dataframe.