Welcome to Excel Jet Consult blog post. In this tutorial, we are going to continue in learning how to use the Python in Cell to append filter multiple table. Let’s get started.
Dataset
In the screenshot below, we have got three transactions tables. Note the three tables are formatted officially as an Excel Table using the CTRL + T. The names of each Tables are: Sales2021, Sales2022 and Sales2023 respectively and that can be found in the Table Design contextual ribbon tab.
PY Function
The PY function runs Python code on a secure Microsoft Cloud runtime. The result is returned as either a Python object or an Excel value.
Syntax: =PY(python_code,return_type)
Based on Microsoft Excel documentation on the new PY function, the Python code can reference values in Excel by using the custom Python function xl(). The xl() function supports referencing the following Excel objects.
- Ranges
- Names
- Tables
- Power Query connections
The xl() function supports an optional headers argument. The headers argument specifies whether the first row has headers. For example, xl(“A1:Z10”, headers=True) indicates that cells A1:Z1 are headers.
Append Filtered List using PY Function in Excel
To append filtered list using PY functions, in cell O1, type in =PY and press the tab key
Then, execute this python code:
df1=xl(“sales2021[#All]”, headers=True)
df2 = xl(“sales2022[#All]”, headers=True)
df3 = xl(“sales2023[#All]”, headers=True)
df1_filtered = df1[df1[‘Sales’]>=15000]
df2_filtered = df2[df2[‘Sales’]>=15000]
df3_filtered = df3[df3[‘Sales’]>=15000]
result = pd.concat([df1_filtered,df2_filtered,df3_filtered], ignore_index=True)
result
Explanation:
- The df1=xl(“sales2021[#All]”, headers=True) load data from sales2021 Excel table into a Pandas DataFrame (df1). The headers=True argument implies that the first row of the Excel data contains column headers.
- The df2=xl(“sales2022[#All]”, headers=True) load data from sales2022 Excel table into a Pandas DataFrame (df2). The headers=True argument implies that the first row of the Excel data contains column headers.
- The df3=xl(“sales2023[#All]”, headers=True) load data from sales2022 Excel table into a Pandas DataFrame (df2). The headers=True argument implies that the first row of the Excel data contains column headers.
- The df1_filtered = df1[df1[‘Sales’]>=15000] filters df1 to retain only the rows where the Sales column has values greater than or equal to 15,000. The filtered data is stored in a new DataFrame called df1_filtered.
- The df2_filtered = df2[df2[‘Sales’]>=15000] filters df1 to retain only the rows where the Sales column has values greater than or equal to 15,000. The filtered data is stored in a new DataFrame called df2_filtered.
- The df3_filtered = df3[df3[‘Sales’]>=15000] filters df1 to retain only the rows where the Sales column has values greater than or equal to 15,000. The filtered data is stored in a new DataFrame called df3_filtered.
- The result = pd.concat([df1_filtered, df2_filtered, df3_filtered], ignore_index=True) concatenates (stacks vertically) the three filtered DataFrames, df1_filtered, df2_filtered, and df3_filtered, into a single DataFrame named result and we used ignore_index=True argument to reset the index of the resulting DataFrame so that it starts from 0.
- Finally, we printed the result of the DataFrame, which contains the concatenated and filtered data from the three Excel sources.
Click CTRL + Enter to commit the python code into cell O1.
That will return a DataFrame. To turn the Python DataFrame to Excel Value, to the left of formula bar, click on the Python Object and select Excel Value. Voila. We have the Excel Value in the cell as seen in the screenshot below. As seen in the status bar, the SUM is £5,722,537.00
See you in the next video