“This is the sixth day of my participation in the Gwen Challenge in November. Check out the details: The Last Gwen Challenge in 2021.”

1 Batch ascending sort

If you’ve ever used Excel, you’re no stranger to sorting.

As shown in the figure below, if you select any cell in the column to be sorted, there will be an ascending button inside, you can sort the material code column in ascending order.

Obviously, this is not too difficult to use in Excel, so how do you sort data in Python?

Sort_values ()

The sort_values() function does the sorting of the data in the PANDAS module.

The code is as follows:

import xlwings as xw import pandas as pd app = xw.App(visible = False,add_book = False) workbook = App.books.open (' books.xlsx ') worksheet = workbook.sheets for I in worksheet: Expand ('table').options(pd.dataframe). Value result = values.sort_values(by=' MDB ') i.range('A1').value = result workbook.save() workbook.close() app.quit()Copy the code

The workbook file path set in line 4 can be changed as required.

Lines six through nine are the core function that sorts all the worksheets in the workbook in ascending order.

Sort_values () is the DataFrame object function in the PANDAS module. Sort_values () is used to sort a data area by the data of a row or column field. The syntax is as follows:

sort_values(by=’##’,axis=0,ascending=True,inplace=False,na_position=’last’)

If you want a descending sort, use the parameter ascending in the sort_values() function to specify the sort.

Result = values.sort_values(by=' material code ', Ascending = False)Copy the code

2 Filter data in Excel

Python is used to classify the detailed data by item name and sum the sales amount of each item:

import xlwings as xw import pandas as pd app = xw.App(visible = False,add_book = False) workbook = App.books.open (' XLSX ') worksheet = workbook. Sheets for I, J in enumerate(worksheet): values = j,range('A1').options(pd.DataFrame,header = 1,index = False,expand ='table').value data = Values,reindex(columns=[' columns ',' date ',' amount ']) table = table.append(data,ignore_index = True) table = table.groupby(' columns ') new_workbook = xw.books.add() for idx,group in table: new_worksheet = new_workbook.sheets.add(idx) new_worksheet['A1'].options(index = False).value = group last_cell = new_worksheet['A1'].wxpand('table').last_cell last_row = last_cell.row last_column = last_cell.column last_column_letter  = chr(64 + last_column) sum_cell_name = '{}{}'.format(last_column_letter,last_row+1) sum_last_row_name = '{}{}'.format(last_column_letter,last_row) formula = '=SUM({}2:{})'.format(last_column_letter,sum_last_row_name) New_worksheet [sum_cell_name].formula = formula new_worksheet.autofit() new_workbook.save(' XLSX ') workbook.close() app.quit()Copy the code

The column headings in line 9 must match the actual column headings in the worksheet, and the order can be adjusted as required.

The column in line 11 used for filtering, “Product,” can be changed to another column as required.

The Excel formula constructed in line 22 is used to SUM the purchase amount and can be changed to perform other calculations based on actual needs, for example, changing “SUM” to “AVERAGE” is an AVERAGE, changing “MAX” to a maximum, and so on.

Reindex () is a function in the pandas module that changes the order of rows and columns. Groupby () is used to group data.