Multi-individual accurate correspondence method and system based on medical element association backstepping

Abstract

The invention relates to the technical field of medical big data analysis, in particular to a multi-individual accurate correspondence method and system based on medical element association backstepping. According to the method, initial data are refined into two types of variable quantity and constant quantity, and data integrity and correctness checking is completed by performing font checking on constant quantity'fixed characters' and performing quantity checking on variable quantity'variable factors'; a checking and checking mode of four-layer nesting, mutual quality verification and layer-by-layer progressive is applied, correct input of fonts of'fixed characters' is guaranteed through two-round input character checking, correct input of the number of'variable factors' is guaranteed through two-round logic relation checking, logicality is good, the operation speed is high, and the processing efficiency is high. Through a three-layer traversal loop nesting mode, screening correspondence of a main screening field individual and other font field individuals is completed, a complex and tedious screening comparison function is achieved through a simple and efficient function code, and the problems that a function formula of a traditional method is tedious and long, and integration and expansibility are poor are solved.

Description

Technical Field

[0001] The present invention relates to the technical field of medical big data analysis, and in particular to a method and system for accurately matching multiple individuals based on medical element correlation inference. Background Art

[0002] In the healthcare sector, a complex network of interactions exists between factors such as pathogenic factors, treatment methods, medications, and medical devices. These factors, while causally linked, often constrain each other. Therefore, when formulating treatment plans, doctors must consider comprehensive considerations such as the interactions of pathogenic factors, the synergies and antagonisms of treatment factors, and the response strategies of complex systems. This has led to the development of complex medical databases containing the corresponding relationships between multiple factors. Each element in the database often contains multiple individual elements, and in practical applications, the problem of accurately matching these multiple individuals based on the inference of medical element associations often arises. For example, diseases, medications, treatment contraindications, and surgical instruments are four common elements in medical databases. Diseases include digestive system diseases such as liver disease, pancreatic disease, intestinal disease, and stomach disease; urinary system diseases such as nephritis, renal failure, and prostate disease. There are thousands of different types of diseases. Here, DI represents the disease name, and DI01, DI02, DI03, and so on represent individual diseases. Similarly, DT represents therapeutic drugs, and DT01, DT02, DT03, and so on represent individual drugs. UT represents treatment contraindications, and UT01, UT02, UT03, and so on represent individual contraindications. SC represents surgical consumables, and SC01, SC02, SC03, and so on represent individual consumables. Here, DI, DT, UT, and SC are called elements, and DI01, DI02, DI03, DT01, DT02, DT03, UT01, UT02, UT03, SC01, SC02, and SC03 are called individuals. In practical applications, one-to-many or many-to-one correspondences often occur between individuals, that is, the basic correspondences of DI01→DT01, DT02→UT01, UT02, UT03→SC01, SC02 are generated; the solution goal is to establish a precise correspondence between a single DT element individual and a single DI, UT, or SC element individual based on the above basic correspondences.

[0003] Chinese patent document CN202510146730.0 describes a "method and system for standardizing multiple elements based on field association." This method uses Python to split, deduplicate, sort, and merge data by filtering row by row in Excel and then summarizing them by column. Its shortcomings are: (1) It lacks an initial data format check mechanism. When there are errors in the initial input data, the function output result is unreliable. (2) During the row-by-row filtering process in Excel, the traditional "if function + character merge" approach is used to filter the data, and then the result data is summarized by column. The function formula is lengthy, the integration is average, and the scalability is poor. (3) During the function execution process, Python needs to be called to split, deduplicate, sort, and merge the data, which requires data transfer between different programs, resulting in cumbersome steps and poor operation efficiency. Summary of the Invention

[0004] Technical purpose: In order to overcome the deficiencies in the prior art, the present invention provides a method for accurately corresponding multiple individuals based on the reverse inference of medical element association, so as to solve the problems raised in the background technology such as the lack of initial data format check, lengthy row-by-row screening function formulas in Excel, and poor integration and general operating efficiency.

[0005] Technical solution: To achieve the above objectives, one of the objectives of the present invention is to provide a method for accurate correspondence between multiple individuals based on medical element association inference, which is applicable to accurate correspondence between individuals in an EXCEL data table containing ZZZ fields, where ZZZ ≥ 3; a single field data contains one or more field individuals, and individuals are separated by a "separator"; cell data format checking is completed through EXCEL, and accurate correspondence between multiple individuals based on field association inference is achieved through Python; it is characterized by including:

[0006] [S1] Perform format checks on summary cells, including: checking the font of "fixed characters" and the quantity of "variable factors";

[0007] [S2] Establishing the corresponding relationship between individuals in the primary screening field and individuals in other fields;

[0008] [S2.1] Initial data reading: read the EXCEL data table using Python and name it the initial data matrix initialMatrix; the first row of the EXCEL data table is the field name column, the second to 13th rows are filled with field contents, and the 14th row is a summary row of the field contents from the second to 13th rows separated by a "delimiter";

[0009] [S2.2] Read the primary filter field summary cell data, read the primary filter field summary cell data that passed the format check in step [S1] filterFields[num_fF] = initialMatrix

[12] [num_fF], where num_fF ranges from 0 to (ZZZ-1);

[0010] [S2.3] Sort and organize the data of the summary cells of the num_fF+1th primary filter field. When num_fF=0, filterFields[0]=initialMatrix

[12] [0]. Through splitting, deduplication, sorting, and merging operations, a Python list textArray consisting of individual primary filter fields is obtained. The individual primary filter field is represented as textArray[num_tA]. The value range of num_tA is: 0~(textArray 元素数量 -1);

[0011] [S2.4] Create a new num_fF+1th main screening field screening result matrix, with the number of main screening field individuals textArray in step [S2.3] 元素数量 is the number of matrix rows, and the number of fields ZZZ in the EXCEL data table described in step [S1] is the number of matrix columns, and a new blank matrix dedicated to storing the screening results is created, which is represented as the screening result matrix resultMatrix;

[0012] [S2.5] The individual search of the num_fF+1th primary screening field corresponds to and assigns a value to the screening result matrix;

[0013] [S2.5.1] Search the num_tA+1th individual. When num_tA=0, use the primary screening field individual textArray[num_tA] described in step [S2.3] as the search value, and traverse the primary screening field columns of the initial data matrix described in step [S2.1] from top to bottom in a loop; the primary screening field columns of the initial data matrix are represented as initialMatrix[numRow_iM]; the value range of numRow_iM is: 0~(initialMatrix 矩阵行数 -2); the column cells of the primary filter field of the initial data matrix are represented as initialMatrix[numRow_iM][num_fF];

[0014] [S2.5.2] Assign the result of the search for the num_tA+1th individual to the screening result matrix. If the search expression textArray[num_tA]in initialMatrix[numRow_iM][num_fF] evaluates to True, write all other field cell data corresponding to the numRow_iM rows into the newly created screening result matrix described in step [S2.4];

[0015] If the filter result matrix cell resultMatrix[num_tA][num_fF] is empty, a direct replacement write rule is run; the direct replacement write rule includes: if the number of field cell columns cols_single is equal to num_fF, it means that the cols_single column is the main filter field column, and the assignment formula is: resultMatrix[num_tA][num_fF]=textArray[num_tA].split("separator"); conversely, if the number of field cell columns cols_single is not equal to num_fF, it means that the cols_single column is another field column, and the assignment formula under this condition is: resultMatrix[num_tA][cols_single]=initialMatrix[numRow_iM][cols_single].split("separator");

[0016] If the screening result matrix cell resultMatrix[num_tA][num_fF] is not empty, the end addition writing rule is run: the end addition writing rule includes: if the number of field cell columns cols_single is equal to num_fF, the assignment formula is: resultMatrix[num_tA][num_fF]+=textArray[num_tA].split("separator"); on the contrary, if the number of field cell columns cols_single is not equal to num_fF, the assignment formula under this condition is: resultMatrix[num_tA][cols_single]+=initialMatrix[numRow_iM][cols_single].split("separator");

[0017] [S2.5.3] Individual search for the remaining num_tA-1 primary screening fields. When num_tA is 1 to (num_tA-1), repeat steps [S2.5.1] to [S2.5.2] to complete the individual search for the remaining num_tA-1 primary screening fields.

[0018] [S2.6] Store the results of the num_fF+1th primary filter field and write the filter result matrix described in step [S2.5] into Excel;

[0019] [S2.7] Search and correspond the summary cell data of the remaining num_fF main filter fields. When num_fF is 1 to (ZZZ-1), loop through steps [S2.3] to [S2.6] to complete the search and correspondence of the summary cell data of the remaining num_fF main filter fields, and achieve accurate correspondence of multiple individuals based on the reverse inference of medical element association.

[0020] Furthermore, the step [S1] performs a glyph check on the "fixed characters", including two steps: A round of input character check and B round of input character check; the step [S1] performs a quantity check on the "variable factors", including two steps: C round of logical relationship check and D round of logical relationship check; the above-mentioned A round, B round, C round, and D round of checks and verifications are nested and progressive; through the A round and B round of input character check, it is ensured that the glyph input of the "fixed characters" is correct; through the C round and D round of logical relationship check, it is ensured that the quantity input of the "variable factors" is correct; set ZZZ fields to be stored in an EXCEL table, the cell where the field name of field 1 is located is H1, the location of the summary cell of field 1 is H14, the cell where the field name of field 2 is located is I1, the location of the summary cell of field 2 is I14, and so on for other fields in the ZZZ fields; all formulas in this step are based on the location of the summary cell of field 2 being I14;

[0021] [S1.1] Round A input character verification: check whether the two fixed characters "identifier" and "separator" entered are correct. The verification function is shown in Formula 1:

[0022] =IF(ISNUMBER(VALUE(SUBSTITUTE(SUBSTITUTE(I14,RIGHT(I1,2),""),"Separator",""))),"Round A passed, enter round B to enter character verification","Incorrect input of abbreviation or separator") formula 1;

[0023] [S1.2] Round B input character check: check whether the input "delimiter" contains the Chinese symbol ",", English symbol ",", or English symbol ".". The nested test function based on Formula 1 is shown in Formula 2:

[0024] =IF(ISNUMBER(VALUE(SUBSTITUTE(SUBSTITUTE(I14,RIGHT(I1,2),""),"Separator",""))),IF(ISNUMBER(FIND(",",I14)),"There is an incorrect input of Chinese character ",", please verify",IF(ISNUMBER(FIND(",",I14)),"There is an incorrect input of English character ",", please verify",IF(ISNUMBER(FIND(".",I14)),"There is an incorrect input of English character ".", please verify","Round B passed, entering round C for logical relationship verification"))),"Incorrect input of abbreviation or separator") Formula 2;

[0025] [S1.3] Round C logic relationship check, check whether the input times of the two keywords "identifier" and "separator" are consistent with the logical times 标识符 =times 分隔符 +1, the test function after nesting based on formula 2 is shown in formula 3:

[0026] =IF(ISNUMBER(VALUE(SUBSTITUTE(SUBSTITUTE(I14,RIGHT(I1,2),""),"Separator",""))),IF(ISNUMBER(FIND(",",I14)),"There is an incorrect input of Chinese characters ",", please verify",IF(ISNUMBER(FIND(",",I14)),"There is an incorrect input of English characters ",", please verify",IF(ISNUMBER(FI ND(".",I14)),"There is an incorrectly entered English character ".", please verify",IF(SUM(N(MID(I14,COLUMN(1:1),2)=RIGHT(I1,2)))=SUM(N(MID(I14,ROW($1:$9999),1)="Separator"))+1,"C round passed, enter D round logical relationship verification","The relationship between the number of "abbreviation" and "separator" is incorrect")))),"The abbreviation or separator is entered incorrectly") Formula 3;

[0027] [S1.4] Round D logic relationship check, check the "total digits" times of the summary cell all Enter times with "delimiter" 分隔符 Is it logical times? all =5*times 分隔符 +4, the test function after nesting based on formula 3 is shown in formula 4:

[0028] =IF(ISNUMBER(VALUE(SUBSTITUTE(SUBSTITUTE(I14,RIGHT(I1,2),""),"Separator",""))),IF(ISNUMBER(FIND(",",I14)),"There is an incorrect input of Chinese character ",", please verify",IF(ISNUMBER(FIND(",",I14)),"There is an incorrect input of English character ",", please verify",IF(ISNUMBER(FIND(".",I14)),"There is an incorrect input of English character "."", please verify",IF (SUM(N(MID(I14,COLUMN(1:1),2)=RIGHT(I1,2)))=SUM(N(MID(I14,ROW($1:$9999),1)=“Separator”))+1,IF(5*(SUM(N(MID(I14,ROW($1:$10003),1)=“Separator”)))+4=LEN(I14),"Verification passed","The relationship between the total number of digits and the number of "separator" is not logical"),"The relationship between the number of "abbreviation" and "separator" is wrong")))),"Incorrect input of abbreviation or separator") Formula 4;

[0029] [S1.5] Execute steps [S1.1] to [S1.4] for the summary cells corresponding to the remaining ZZZ-1 fields in the Excel table in turn to complete the format check of the summary cells of all ZZZ fields.

[0030] Furthermore, the separator is “.”

[0031] A second object of the present invention is to provide a system for accurately corresponding multiple individuals based on medical element correlation and reverse deduction, applying the method for accurately corresponding multiple individuals based on medical element correlation and reverse deduction as described in any one of claims 1 to 3, comprising: [M1] a cell format checking module and [M2] a field individual screening and corresponding module;

[0032] [M1] Cell format check module: Its function is to check the format of the EXCEL data table; the specific method is: run steps [S1.5] to [S1.5] in sequence, and check whether the "fixed character" font is correctly input through two rounds of character input verification; check whether the "variable factor" quantity is correctly input through two rounds of logical relationship verification and superposition;

[0033] [M2] Field individual screening correspondence module: Its function is to establish the correspondence between individuals in the main screening field and individuals in other fields. It runs steps [S2.1] to [S2.7] in sequence. Through the seven steps of "initial data reading", "main screening field summary cell data reading", "main screening field summary cell data sorting and arranging", "creating a main screening field screening result matrix", "main screening field individual search correspondence and assignment", "main screening field screening result storage", and "remaining main screening field summary cell data search correspondence", it establishes the correspondence between individuals in the main screening field and individuals in other fields and writes it into the EXCEL data table, finally completing the precise correspondence of multiple individuals based on the medical element association inversion.

[0034] The beneficial effects of the present invention are:

[0035] 1. The method and system for accurately matching multiple individuals based on medical element correlation and inference, provided by the present invention, breaks down initial data into two types: variable and constant. It creatively proposes to verify the format of the initial data by performing glyph verification on the constant "fixed characters" and quantitative verification on the variable "variable factors." This effectively ensures the integrity of the initial data verification and the credibility of the final calculation results.

[0036] 2. The method and system for accurately matching multiple individuals based on medical element correlation and reverse inference, provided by the present invention, employs a four-layer nested, mutually verified, and progressive verification approach. The A and B rounds of character input verification ensure that the "fixed character" glyphs are correctly input, while the C and D rounds of logical relationship verification ensure that the "variable factor" quantity is correctly input. This method features good function logic, fast operation speed, and high processing efficiency.

[0037] 3. The method and system for precise correspondence of multiple individuals based on medical element association inference provided by the present invention completes the correspondence between the main screening field individuals and the individual screening of other font fields through a three-layer traversal loop nesting method, and realizes the complex and tedious screening and comparison function through concise and efficient function code, effectively solving the problems of lengthy function formulas and poor integration and scalability in traditional Excel methods. BRIEF DESCRIPTION OF THE DRAWINGS

[0038] In order to more clearly illustrate the embodiments of the present invention or the technical solutions in the prior art, the following briefly introduces the drawings required for use in the embodiments or the description of the prior art. Obviously, the drawings described below are only some embodiments of the present invention. For ordinary technicians in this field, other drawings can be obtained based on these drawings without paying any creative work.

[0039] Figure 1 This is a flowchart of a method for accurately matching multiple individuals based on the correlation and inference of medical elements;

[0040] Figure 2 This is a functional block diagram of the cell format check module of the multiple individual precise correspondence system based on the reverse inference of medical element association;

[0041] Figure 3 It is a functional block diagram of the individual screening module for multiple individual precise correspondence system fields based on the reverse inference of medical element association;

[0042] Figure 4 This is a schematic diagram of the python programming code for the steps of the method for accurately matching multiple individuals based on the correlation and inference of medical factors [S2]. DETAILED DESCRIPTION

[0043] The following is combined with Figure 1 To the attached Figure 3 The principles and features of the present invention are described, and the examples given are only used to explain the present invention and are not used to limit the scope of the present invention.

[0044] Table 1: Basic element information table

[0045]

[0046]

[0047] First, encode the basic elements in Table 1. The basic elements (disease name, therapeutic drugs, contraindications, surgical consumables) are represented by the corresponding English abbreviations:

[0048] Disease name——Disease's name——DI;

[0049] Drug therapy——DT;

[0050] Usage taboo——UT;

[0051] Surgical consumables——SC;

[0052] In the basic elements, individuals are numbered by Arabic numerals 1, 2, 3, etc., and the separator "," is used to separate the individuals. The basic field information table after encoding is shown in Table 2;

[0053] Table 2: Encoded basic field information table

[0054]

[0055]

[0056] As shown in Table 2, row 14 is a column-by-column summary of rows 1 to 13 separated by ",". The summary cell consists of two parts: "fixed characters" and "variable factors". Among them, "fixed characters" include "identifiers" and "separators", and "variable factors" include "number of identifiers", "number of identifiers", and "number of separators". The specific explanation is shown in Table 3:

[0057] Table 3: Examples of explanations of variable factors and fixed characters

[0058]

[0059] It should be noted that the results of the function feedback in the embodiment of the present invention are enclosed in brackets [] to provide a clear identification.

[0060] Example 1 is a method for accurately matching multiple individuals based on medical element correlation inference disclosed by the present invention, such as Figure 1 As shown, including:

[0061] [S1] Perform format check on summary cells, including: checking the font of "fixed characters" and checking the quantity of "variable factors"; the font check on "fixed characters" includes two steps: round A input character check and round B input character check; the quantity check on "variable factors" includes two steps: round C logical relationship check and round D logical relationship check; the above rounds A, B, C and D checks are nested and progressive; specifically, the fixed characters in the summary cells are checked through rounds A and B input character checks, that is, the input characters are checked. Whether the "identifier" and "separator" are correct, so as to ensure that the "fixed character" font is input correctly; through the C round logical relationship check, check whether the logical relationship between the number of occurrences of the two keywords "identifier" and "separator" is reasonable; through the D round logical relationship check, check whether the logical relationship between the "total digits" of the summary cell and the number of occurrences of the "separator" corresponds; through the superposition of the C and D round logical relationship checks, it can be ensured that the number of "variable factors" is input correctly; in this embodiment, Table 2 corresponds to the summary cells of 4 fields. Here, the I14 cell is taken as an example to perform the format check of the summary cell, such as Figure 2 As shown:

[0062] [S1.1] Round A input character verification: check whether the two fixed characters "identifier" and "separator" entered are correct. The verification function is shown in Formula 1:

[0063] =IF(ISNUMBER(VALUE(SUBSTITUTE(SUBSTITUTE(I14,RIGHT(I1,2),""),"、",""))),"Round A passed, enter round B for character verification","Incorrect input of abbreviation or separator") formula 1;

[0064] The principle of the input character verification step in round A is: replace all fixed characters "identifier" and "separator" with blanks; if the input is correct, a string of numbers will remain in the cell after the replacement function is run, and then the cell content is verified by the function to see if it is a number; among them: the function SUBSTITUTE(SUBSTITUTE(I14,RIGHT(I1,2),""),"、","") part is to replace all fixed characters "identifier" and "separator" with blanks. The function ISNUMBER(VALUE(SUBSTITUTE(SUBSTITUTE(I14,RIGHT(I1,2),""),"、",""))) part is to use the VALUE() function to convert the text string representing the number into a number, and use the ISNUMBER() function to determine whether the result of the VALUE() function operation is a number. The if function is then used to determine the flow. If the result of the ISNUMBER() function operation is not a number, it means that the summary cell contains other characters in addition to "identifiers," "separators," and numbers, and therefore does not comply with the first-level input character verification logic. Obviously, the other characters are incorrectly entered. At this point, the function returns the following: ["abbreviation" or "separator" input error]. If the result of the ISNUMBER() function operation is a number, it means that the summary cell consists of several "identifiers," several "separators," and several "numbers," which complies with the first-level input character verification logic, and the function enters round B of input character verification.

[0065] Substitute the data in cell I14 in Table 2 into Formula 1 as a summary cell. The result of the SUBSTITUTE(SUBSTITUTE(I14,RIGHT(I1,2),""),"、","") part of the function is [0102020304040105030203040201040105]; the result of the ISNUMBER(VALUE(SUBSTITUTE(SUBSTITUTE(I14,RIGHT(I1,2),""),"、",""))) part of the function is [TRUE]; therefore, the function returns [Round A passed, entering Round B input character check], which means that the data in cell I14 passed the Formula 1 test, that is, passed the Round A input character check;

[0066] Table 4: Basic field information table 4

[0067]

[0068] Here is another example that fails to pass the A-round input character check. As shown in Table 4, the data in cell I14 in Table 4 is substituted into Formula 1 as a summary cell. The result of the SUBSTITUTE(SUBSTITUTE(I14,RIGHT(I1,2),""),"、","") part of the function is [DH0102020304040105030203040201040105]; the result of the ISNUMBER(VALUE(SUBSTITUTE(SUBSTITUTE(I14,RIGHT(I1,2),""),"、",""))) part of the function is [FALSE]; therefore, the data in cell I14 fails to pass the Formula 1 check and reports the error ["Incorrect input of abbreviation or separator"], which means it fails the A-round input character check.

[0069] [S1.2] Round B input character check: check whether the input "delimiter" contains the Chinese symbol ",", English symbol ",", or English symbol ".". The nested test function based on Formula 1 is shown in Formula 2:

[0070] =IF(ISNUMBER(VALUE(SUBSTITUTE(SUBSTITUTE(I14,RIGHT(I1,2),""),"、",""))),IF(ISNUMBER(FIND(",",I14)),"There is an incorrectly entered Chinese character ","", please verify",IF(ISNUMBER(FIND(",",I14)),"There is an incorrectly entered English character ","", please verify",IF(ISNUMBER(FIND(".",I14)),"There is an incorrectly entered English character "."", please verify","Round B passed, entering round C for logical relationship verification"))),"Incorrect abbreviation or separator input") Formula 2;

[0071] The reason for the B-round input character verification step is that in an Excel cell, when the cell content is a string of numbers mixed with Chinese characters ",", English characters ",", and English characters ".", the result of the ISNUMBER() function operation on the above cell is [TRUE]; the principle of the B-round input character verification is:

[0072] First, use the FIND(",",I14) function to find out whether there is an incorrectly entered Chinese character ","; if I14 contains the Chinese character ",", the data in cell I14 cannot pass the test of formula 2 and an error message is displayed: "There is an incorrectly entered Chinese character "," please verify."

[0073] Secondly, if I14 does not contain the Chinese character ",", use the FIND(",",I14) function to find out whether there is an incorrectly entered English character ","; if I14 contains an English character ",", the data in cell I14 cannot pass the test of formula 2 and an error message is displayed: "There is an incorrectly entered English character "," please verify."

[0074] Third, if I14 does not contain the English symbol "," , use the FIND(".",I14) function to find whether there is an incorrectly entered English symbol "."; if I14 contains the English symbol ".", the data in cell I14 cannot pass the test of Formula 2 and an error message is displayed: "There is an incorrectly entered English symbol ".", please verify."

[0075] Fourth, if I14 does not contain the English symbol ".", it means that the summary cell meets the B-round input character verification logic, and the function enters the C-round logical relationship verification.

[0076] Substitute the data in cell I14 in Table 2 as a summary cell into Formula 2. Since the data in cell I14 in Table 2 does not contain the Chinese symbol ",", the English symbol ",", or the English symbol ".", the data in cell I14 in Table 2 passes the test in Formula 2, that is, it passes the B round of input character inspection.

[0077] Table 5: Basic field information Table 5

[0078]

[0079] Here is another example that fails to pass the B-round input character check, as shown in Table 5. Substitute the data in cell I14 in Table 5 as a summary cell into Formula 2. The result of the calculation of the SUBSTITUTE(SUBSTITUTE(I14,RIGHT(I1,2),""),"、","") part in the function is [01,02020304040105030203040201040105]; the result of the ISNUMBER(VALUE(SUBSTITUTE(I14,RIGHT(I1,2),""),"、","") part in the function is [01,02020304040105030203040201040105]; The result of the TE(SUBSTITUTE(I14,RIGHT(I1,2),""),"、",""))) operation is [TRUE]; therefore, the data in cell I14 in Table 5 passed the A-round input character check and entered the B-round input character check; in the B-round input character check, the FIND(",",I14) function found the incorrectly entered Chinese character “,”, so the data in cell I14 in Table 5 cannot pass the formula 2 check and reports the error [Incorrectly entered Chinese character “,”, please verify].

[0080] [S1.3] Round C logic relationship check, check whether the input times of the two keywords "identifier" and "separator" are consistent with the logical times 标识符 =times分隔符 +1, the test function after nesting based on formula 2 is shown in formula 3:

[0081] =IF(ISNUMBER(VALUE(SUBSTITUTE(SUBSTITUTE(I14,RIGHT(I1,2),""),"、",""))),IF(ISNUMBER(FIND(",",I14)),"There is an incorrect input of Chinese characters ","", please verify",IF(ISNUMBER(FIND(",",I14)),"There is an incorrect input of English characters ","", please verify",IF(ISNUMBER(FI ND(".",I14)),"There is an incorrectly entered English character ".", please verify",IF(SUM(N(MID(I14,COLUMN(1:1),2)=RIGHT(I1,2)))=SUM(N(MID(I14,ROW($1:$9999),1)="、"))+1,"Round C passed, enter round D for logical relationship verification","The relationship between "abbreviation" and "separator", "number of times" is incorrect")))),"The abbreviation or separator is entered incorrectly") Formula 3;

[0082] In Formula 3, SUM(N(MID(I14,COLUMN(1:1),2)=RIGHT(I1,2))) represents the number of times "Identifier" appears; SUM(N(MID(I14,ROW($1:$9999),1)="、")) represents the number of times "Separator" appears. If the function verification of Formula 3 passes, it means that the input counts of the two keywords "Identifier" and "Separator" meet the logic of the C round logical relationship verification. At this time, the function feedback is [C round passed, enter D round logical relationship verification], that is, it passed the C round logical relationship verification and entered the D round logical relationship verification. If the function verification of Formula 3 fails, it means that the input counts of the two keywords "Identifier" and "Separator", " do not meet the logic of the C round logical relationship verification. Therefore, it can be inferred that there is a phenomenon of repeated entry or missing entry of "Identifier" and "Separator" in the summary cell. At this time, the function feedback is [The relationship between the number of "Abbreviation" and "Separator", "is incorrect"].

[0083] Substitute the data in cell I14 in Table 2 as a summary cell into Formula 3. The function feedback shows that the number of occurrences of "identifier DT" in cell I14 in Table 2 is 17, and the number of occurrences of "separator, " is 16. Therefore, the data in cell I14 in Table 2 passes the test of Formula 3, that is, it passes the C round of logical relationship verification.

[0084] Table 6: Basic field information Table 6

[0085]

[0086] Here is another example that fails to pass the B-round input character check, as shown in Table 6. Substitute the data in cell I14 in Table 6 as a summary cell into Formula 3. The result of the SUBSTITUTE(SUBSTITUTE(I14,RIGHT(I1,2),""),"、","") operation in the function is [0102020304040105030203040201040105]; the result of the ISNUMBER(VALUE(SUBSTITUTE(SUBSTITUTE(I14,RIGHT(I1,2),""),"、",""))) operation in the function is [TRUE]; therefore, the data in cell I14 in Table 6 passes the A-round input character check and enters the B-round input character check. ; Substitute the data of cell I14 in Table 6 as the summary cell into Formula 2. Since the data of cell I14 in Table 6 does not contain the Chinese symbol ",", the English symbol ",", and the English symbol ".", cell I14 in Table 6 passes the B round of input character verification and enters the C round of logical relationship verification; Substitute the data of cell I14 in Table 6 as the summary cell into Formula 3. The result of the SUM(N(MID(I14,COLUMN(1:1),2)=RIGHT(I1,2))) part of the function is 18, which means that the number of times "identifier" appears is 18; the result of the SUM(N(MID(I14,ROW($1:$9999),1)="、")) part of the function is 16, which means that the number of times "separator" appears is 16, which cannot meet the logical times. 标识符 =times 分隔符 +1, so the function feedback [the number of times "abbreviation" and "separator" are incorrect]

[0087] [S1.4] Round D logic relationship check, check the "total digits" times of the summary cell all Enter times with "delimiter" 分隔符 Is it logical times? all =5*times 分隔符 +4, the test function after nesting based on formula 3 is shown in formula 4:

[0088] =IF(ISNUMBER(VALUE(SUBSTITUTE(SUBSTITUTE(I14,RIGHT(I1,2),""),"、",""))),IF(ISNUMBER(FIND(",",I14)),"There is an incorrect input of Chinese characters ","", please verify",IF(ISNUMBER(FIND(",",I14)),"There is an incorrect input of English characters ","", please verify",IF(ISNUMBER(FIND(".",I14)),"There is an incorrect input of English characters "."", please verify",I F(SUM(N(MID(I14,COLUMN(1:1),2)=RIGHT(I1,2)))=SUM(N(MID(I14,ROW($1:$9999),1)="、"))+1,IF(5*(SUM(N(MID(I14,ROW($1:$10003),1)="、")))+4=LEN(I14),"Verification passed","The relationship between the total number of digits and the number of "、" is not logical"),"The relationship between the "abbreviation character" and the "separator", "number of times" is incorrect")))),"The abbreviation character or separator is entered incorrectly") Formula 4;

[0089] The principle of checking the logical relationship in round D is: the total number of digits in the summary cell times all Enter the times value with the "delimiter" keyword 分隔符 The following relationship exists: times all =4*(times 分隔符 +1)+times 分隔符 =5times 分隔符 +4, that is, the total number of test digits times all Enter the times value with the "delimiter" keyword 分隔符 Is it logical? Among them, SUM(N(MID(I14,ROW($1:$9999),1)="、")) represents the number of times the "separator" appears; LEN(I14) represents the total number of digits in the cell.

[0090] Substitute the data in cell I14 in Table 2 as the summary cell into formula 4. The function returns the number of times the "separator" appears in cell I14 in Table 2. 分隔符 is 16, the total number of digits in the cell is times all is 84, which is consistent with times all =5*times 分隔符 +4 checks the logic, so the data in cell I14 in Table 2 passes the verification function of formula 4 and the feedback is [Verification Passed], which means it has passed the D round of logical relationship verification.

[0091] Table 7: Basic field information Table 7

[0092]

[0093] Here is another example that fails to pass the B-round input character check, as shown in Table 7. Substitute the data of cell I14 in Table 7 into Formula 4 as a summary cell. The result of the SUBSTITUTE(SUBSTITUTE(I14,RIGHT(I1,2),""),"、","") part of the function is [0102020304040105030203040201040105]; the result of the ISNUMBER(VALUE(SUBSTITUTE(SUBSTITUTE(I14,RIGHT(I1,2),""),"、",""))) part of the function is [TRUE]; therefore, the data of cell I14 in Table 7 passes the A-round input character check and enters the B-round input character check. Verify; Substitute the data of cell I14 in Table 7 as a summary cell into Formula 2. Since the data of cell I14 in Table 7 does not contain the Chinese symbol "," , the English symbol "," , and the English symbol ".", cell I14 in Table 7 passes the B round of input character verification and enters the C round of logical relationship verification; Substitute the data of cell I14 in Table 7 as a summary cell into Formula 3. The result of the SUM(N(MID(I14,COLUMN(1:1),2)=RIGHT(I1,2))) part of the function is 18, which means that the number of times "identifier" appears is 18; the result of the SUM(N(MID(I14,ROW($1:$9999),1)="、")) part of the function is 17, which means that the number of times "separator" appears is 17, which meets the times requirement. 标识符 =times 分隔符 +1 logical relationship, so cell I14 in Table 7 passed the C round of logical relationship verification and entered the D round of logical relationship verification; substitute the data of cell I14 in Table 7 as the summary cell into Formula 4, and the function feedback will return the number of times the "separator" appears in cell I14 in Table 2 分隔符 is 17, the total number of digits in the cell is times all is 87, which does not match the times all =5*times 分隔符 +4 checks the logic, so the data in cell I14 in Table 2 is checked through formula 4, which feedbacks [the relationship between the total number of digits and the number of “,” is not logical].

[0094] At this point, step [S1] of checking the format of the summary cell is completed, that is, the format checking step [S1] ensures that the original input data is correct. Then, step [S2] is entered to establish the corresponding relationship between individuals in the main screening field and individuals in other fields. In this embodiment, step [S2] is calculated based on the data in the encoded basic field information table in Table 2. The Python programming code to implement the function of step [S2] is as follows: Figure 4 As shown:

[0095] [S2.1] Initial data reading, such as Figure 1 As shown, the EXCEL data table is read by python, and the result initialMatrix read from Table 2 is shown in Table 8;

[0096] Table 8: Initial Matrix initialMatrix

[0097]

[0098]

[0099] [S2.2] Read the summary cell data of the primary filter field. Read the summary cell data of the primary filter field that passed the format check in step [S1] (filterFields[num_fF] = initialMatrix

[12] [num_fF]). The result data is shown in Table 9.

[0100] Table 9: Main filter field summary cell data reading results filterFields

[0101]

[0102] [S2.3] Sort and organize the data of the first primary filter field summary cell. When num_fF = 0, filterFields[0] = initialMatrix

[12] [0]. Through splitting, deduplication, sorting, and merging operations, the Python list textArray = 'DI01, DI02, DI03, DI04, DI05, DI06, DI07, DI08, DI09, DI10, DI11, DI12' consisting of the individual primary filter fields is obtained;

[0103] [S2.4] Create the first primary screening field screening result matrix, using the primary screening field individual number textArray described in step [S2.3] 元素数量 =12 is the number of matrix rows, and the number of fields in the EXCEL data table described in step [S1] is 4 as the number of matrix columns, and a new blank matrix resultMatrix with 12 rows and 4 columns for storing the screening results is created; Figure 3As shown:

[0104] [S2.5] The first main screening field contains the individual item-by-item search corresponding to the assigned screening result matrix;

[0105] [S2.5.1] First individual search: when num_tA = 0, use the primary screening field individual textArray[0] = 'DI01' described in step [S2.3] as the search value, and traverse the primary screening field columns of the initial data matrix described in step [S2.1] from top to bottom; that is, search whether each value from initialMatrix[0][0] to initialMatrix

[11] [0] contains 'DI01';

[0106] [S2.5.2] Assign the first individual search result to the filter result matrix. If the search expression textArray[0]ininitialMatrix[0][0] evaluates to True, write all other field cell data corresponding to row 0 into the newly created filter result matrix described in step [S2.4];

[0107] Here, resultMatrix[0][0] is empty, so the direct replacement write rule is executed, resultMatrix[0][0]=textArray[0].split("、")=['DI01'];

[0108] Here, resultMatrix[0][1] is empty, so the direct replacement write rule is executed, resultMatrix[0][1]=initialMatrix[0][1].split("、")=['DT01、DT02'];

[0109] Here, resultMatrix[0][2] is empty, so the direct replacement write rule is executed, resultMatrix[0][2]=initialMatrix[0][2].split("、")=['UT01、UT02'];

[0110] Here, resultMatrix[0][3] is empty, so the direct replacement write rule is executed, resultMatrix[0][3]=initialMatrix[0][3].split("、")=['SC01、SC02'];

[0111] [S2.5.3] Individual search for the remaining 11 primary screening fields. When num_tA is 1 to 11, repeat steps [S2.5.1] to [S2.5.2] to complete the individual search for the remaining 11 primary screening fields.

[0112] At this time, resultMatrix=[[['DI01'],['DT01','DT02'],['UT01','UT02'],['SC01','SC0 2']],[['DI02'],['DT02'],['UT01'],['SC03','SC05']],[['DI03'],['DT03','DT0 4'],['UT03'],['SC02','SC04']],[['DI04'],['DT04'],['UT01'],['SC01']],[[' DI05'],['DT01','DT05'],['UT02'],['SC03']],[['DI06'],['DT03'],['UT02','UT 03'],['SC05']],[['DI07'],['DT02'],['UT01'],['SC02']],[['DI08'],['DT03', 'DT04'],['UT03'],['SC04']],[['DI09'],['DT02'],['UT01'],['SC01','SC02','S C04']],[['DI10'],['DT01'],['UT01','UT03'],['SC03']],[['DI11'],['DT04'], ['UT03'],['SC01','SC04']],[['DI12'],['DT01','DT05'],['UT01'],['SC02']]];

[0113] [S2.6] The first primary filter field filter results are stored, and the filter result matrix described in step [S2.5] is written to Excel, as shown in Table 10:

[0114] Table 10: Corresponding results of the first primary screening field disease name DI individual and field association

[0115] Disease name DI Treatment drug DT Treatment contraindications UT Surgical consumables SC DI01 DT01, DT02 UT01, UT02 SC01, SC02 DI02 DT02 UT01 SC03, SC05 DI03 DT03, DT04 UT03 SC02, SC04 DI04 DT04 UT01 SC01 DI05 DT01, DT05 UT02 SC03 DI06 DT03 UT02, UT03 SC05 DI07 DT02 UT01 SC02 DI08 DT03, DT04 UT03 SC04 DI09 DT02 UT01 SC01, SC02, SC04 DI10 DT01 UT01, UT03 SC03 DI11 DT04 UT03 SC01, SC04 DI12 DT01, DT05 UT01 SC02

[0116] [S2.7] Search for the corresponding summary cell data of the remaining three primary filter fields. When num_fF is 1 to 3, loop through steps [S2.3] to [S2.6] to complete the search for the corresponding summary cell data of the remaining num_fF primary filter fields. The results of the second primary filter field are shown in Table 11, the results of the third primary filter field are shown in Table 12, and the results of the fourth primary filter field are shown in Table 13.

[0117] Table 11: Corresponding results of the association between the second main screening field therapeutic drug DT individual and field

[0118]

[0119] Table 12: Corresponding results of the third main screening field, treatment contraindications, UT individuals and fields

[0120]

[0121] Table 13: Corresponding results of the association between the fourth main screening field surgical consumables SC individual and the field

[0122]

[0123] The above description is only a preferred embodiment of the present invention and is not intended to limit the present invention. Any modifications, equivalent substitutions, improvements, etc. made within the spirit and principles of the present invention should be included in the scope of protection of the present invention.

Claims

1. A method for accurately matching multiple individuals based on the inverse correlation of medical elements, applicable to accurate matching between individuals in an Excel data table containing ZZZ fields, where ZZZ ≥ 3; A single field data contains one or more field individuals, and the individuals are separated by "delimiters". The cell data format is checked through EXCEL, and the precise correspondence of multiple individuals based on field association and reverse deduction is achieved through Python. The features include: [S1] Perform format checks on summary cells, including checking the font shape of "fixed characters" and the quantity of "variable factors"; [S2] Establishing the corresponding relationship between individuals in the primary screening field and individuals in other fields; [S2.1] Initial data reading: read the EXCEL data table using Python and name it the initial data matrix initialMatrix; the first row of the EXCEL data table is the field name column, the second to 13th rows are filled with field contents, and the 14th row is a summary row of the field contents from the second to 13th rows separated by "delimiters"; [S2.2] Read the primary filter field summary cell data, read the primary filter field summary cell data that passed the format check in step [S1] filterFields[num_fF] = initialMatrix[12][num_fF], where num_fF ranges from 0 to (ZZZ-1); [S2.3] Sort and organize the data of the summary cells of the num_fF+1th primary filter field. When num_fF=0, filterFields[0]=initialMatrix[12][0]. Through splitting, deduplication, sorting, and merging operations, a Python list textArray consisting of individual primary filter fields is obtained. The individual primary filter field is represented as textArray[num_tA]. The value range of num_tA is: 0~(textArray 元素数量 -1); [S2.4] Create a new num_fF+1th main screening field screening result matrix, with the number of main screening field individuals textArray in step [S2.3] 元素数量 is the number of matrix rows, and the number of fields ZZZ in the EXCEL data table described in step [S1] is the number of matrix columns, and a new blank matrix dedicated to storing the screening results is created, which is represented as the screening result matrix resultMatrix; [S2.5] The num_fF+1th primary screening field contains the individual item-by-item search corresponding to the assigned screening result matrix; [S2.5.1] Search the num_tA+1th individual. When num_tA=0, use the primary screening field individual textArray[num_tA] described in step [S2.3] as the search value, and traverse the primary screening field columns of the initial data matrix described in step [S2.1] from top to bottom in a loop; the primary screening field columns of the initial data matrix are represented as initialMatrix[numRow_iM]; the value range of numRow_iM is: 0~(initialMatrix 矩阵行数 -2); the column cells of the primary filter field of the initial data matrix are represented as initialMatrix[numRow_iM][num_fF]; [S2.5.2] Assign the result of the search for the num_tA+1th individual to the screening result matrix. If the search expression textArray[num_tA]in initialMatrix[numRow_iM][num_fF] evaluates to True, write all other field cell data corresponding to the numRow_iM rows into the newly created screening result matrix described in step [S2.4]; If the filter result matrix cell resultMatrix[num_tA][num_fF] is empty, a direct replacement write rule is run; the direct replacement write rule includes: if the number of field cell columns cols_single is equal to num_fF, it means that the cols_single column is the main filter field column, and the assignment formula is: resultMatrix[num_tA][num_fF]=textArray[num_tA].split("separator"); conversely, if the number of field cell columns cols_single is not equal to num_fF, it means that the cols_single column is another field column, and the assignment formula under this condition is: resultMatrix[num_tA][cols_single]=initialMatrix[numRow_iM][cols_single].split("separator"); If the screening result matrix cell resultMatrix[num_tA][num_fF] is not empty, the end addition write rule is run: the end addition write rule includes: if the field cell column number cols_single is equal to num_fF, the assignment formula is: resultMatrix[num_tA][num_fF]+=textArray[num_tA].split("separator"); conversely, if the field cell column number cols_single is not equal to num_fF, the assignment formula under this condition is: resultMatrix[num_tA][cols_single]+=initialMatrix[numRow_iM][cols_single].split("separator"); [S2.5.3] Individual search for the remaining num_tA-1 primary screening fields. When num_tA is 1 to (num_tA-1), repeat steps [S2.5.1] to [S2.5.2] to complete the individual search for the remaining num_tA-1 primary screening fields. [S2.6] Store the results of the num_fF+1th primary filter field and write the filter result matrix described in step [S2.5] into Excel; [S2.7] Search and correspond the summary cell data of the remaining num_fF main filter fields. When num_fF is 1 to (ZZZ-1), loop through steps [S2.3] to [S2.6] to complete the search and correspondence of the summary cell data of the remaining num_fF main filter fields, and achieve accurate correspondence of multiple individuals based on the reverse inference of medical element association.

2. The method for accurately matching multiple individuals based on medical element correlation inference according to claim 1 is characterized by: The step [S1] performs a glyph check on the "fixed characters", including two steps: A round of input character check and B round of input character check; the step [S1] performs a quantity check on the "variable factors", including two steps: C round of logical relationship check and D round of logical relationship check; the above-mentioned A round, B round, C round, and D round of checks and verifications are a nested progressive relationship; through the A round and B round of input character check, it is ensured that the glyph input of the "fixed characters" is correct; through the C round and D round of logical relationship check, it is ensured that the quantity input of the "variable factors" is correct; set ZZZ fields to be stored in an EXCEL table, the cell where the field name of field 1 is located is H1, and the location of the summary cell of field 1 is H14, the cell where the field name of field 2 is located is I1, and the location of the summary cell of field 2 is I14, and so on for other fields in the ZZZ fields; all formulas in this step are based on the location of the summary cell of field 2 being I14; [S1.1] Round A input character verification checks whether the two fixed characters entered, "identifier" and "separator", are correct. The verification function is shown in Formula 1: =IF(ISNUMBER(VALUE(SUBSTITUTE(SUBSTITUTE(I14,RIGHT(I1,2),""),"Separator",""))),"Round A passed, enter round B to input character verification","Incorrect input of abbreviation or separator") Formula 1; [S1.2] Round B input character check: check whether the input "delimiter" contains Chinese characters ",", English characters ",", and English characters "". The nested test function based on Formula 1 is shown in Formula 2: =IF(ISNUMBER(VALUE(SUBSTITUTE(SUBSTITUTE(I14,RIGHT(I1,2),""),"Separator",""))),IF(ISNUMBER(FIND(",",I14)),"There is an incorrect input of Chinese characters",",Please verify",IF(ISNUMBER(FIND(",",I14)),"There is an incorrect input of English characters",",Please verify",IF(ISNUMBER(FIND(".",I14)),"There is an incorrect input of English characters".",Please verify","Round B passed, entering round C for logical relationship verification"))),"Incorrect input of abbreviation or separator") Formula 2; [S1.3] Round C logic relationship check, check whether the input times of the two keywords "identifier" and "separator" are consistent with the logical times 标识符 =times 分隔符 +1, the test function after nesting based on formula 2 is shown in formula 3: =IF(ISNUMBER(VALUE(SUBSTITUTE(SUBSTITUTE(I14,RIGHT(I1,2),""),"Separator",""))),IF(ISNUMBER(FIND(",",I14)),"There is an incorrect input of Chinese",",Please verify",IF(ISNUMBER(FIND(",",I14)),"There is an incorrect input of English",",Please verify",IF(ISNUMBER(FI ND(".",I14)),"There is an incorrectly entered English character".",Please verify",IF(SUM(N(MID(I14,COLUMN(1:1),2)=RIGHT(I1,2)))=SUM(N(MID(I14,ROW($1:$9999),1)="Separator"))+1,"C round passed, enter D round logical relationship verification","The relationship between the number of "abbreviation" and "separator" is incorrect")))),"The abbreviation or separator is entered incorrectly") Formula 3; [S1.4] Round D logic relationship check, check the "total digits" times of the summary cell all Enter times with "delimiter" 分隔符 Is it logical times? all =5*times 分隔符 +4, the test function after nesting based on formula 3 is shown in formula 4: =IF(ISNUMBER(VALUE(SUBSTITUTE(SUBSTITUTE(I14,RIGHT(I1,2),""),"Separator",""))),IF(ISNUMBER(FIND(",",I14)),"There is an incorrect input of Chinese",",Please verify",IF(ISNUMBER(FIND(",",I14)),"There is an incorrect input of English",",Please verify",IF(ISNUMBER(FIND(".",I14)),"There is an incorrect input of English".",Please verify",IF (SUM(N(MID(I14,COLUMN(1:1),2)=RIGHT(I1,2)))=SUM(N(MID(I14,ROW($1:$9999),1)="Separator"))+1,IF(5*(SUM(N(MID(I14,ROW($1:$10003),1)="Separator")))+4=LEN(I14),"Verification passed","The relationship between the total number of digits and the number of "separator" is not logical"),"The relationship between the number of "abbreviation" and "separator" is incorrect")))),"The abbreviation or separator is entered incorrectly") Formula 4; [S1.5] Execute steps [S1.1] to [S1.4] for the summary cells corresponding to the remaining ZZZ-1 fields in the Excel table in turn to complete the format check of the summary cells of all ZZZ fields.

3. The method for accurately matching multiple individuals based on medical element correlation inference according to claim 2, characterized in that: The delimiter is ",".

4. A system for accurately matching multiple individuals based on medical element correlation and inference, applying the method for accurately matching multiple individuals based on medical element correlation and inference according to any one of claims 1 to 3, comprising: [M1] Cell format check module and [M2] Field individual screening corresponding module; [M1] Cell format check module: Its function is to check the format of the EXCEL data table. The specific method is: run steps [S1.5] to [S1.5] in sequence, and check whether the "fixed character" font is correctly input through two rounds of character input verification; check whether the "variable factor" quantity is correctly input through two rounds of logical relationship verification and superposition; [M2] Field individual screening correspondence module: Its function is to establish the correspondence between individuals in the main screening field and individuals in other fields. It runs steps [S2.1] to [S2.7] in sequence. Through the seven steps of "initial data reading", "main screening field summary cell data reading", "main screening field summary cell data sorting and arranging", "creating main screening field screening result matrix", "main screening field individual search correspondence and assignment", "main screening field screening result storage", and "remaining main screening field summary cell data search correspondence", it establishes the correspondence between individuals in the main screening field and individuals in other fields and writes it into an EXCEL data table, ultimately completing the precise correspondence of multiple individuals based on the medical element association inversion.

Images