Data processing and analysis system and method based on Python

Abstract

The present invention discloses a data processing and analysis system and method based on Python, which belongs to the field of data processing technology. The technical problem to be solved is how to provide a data processing and analysis method based on Python that can meet various business scenarios and needs. It includes a data access module, a check update module, a data reading module, a data processing module, a data writing module, a prediction analysis module, a log output module and a code structure module. According to the data magnitude and the periodic update characteristics of the source table, the full amount or incremental mode is selected to read data from the source table, and the read data is cleaned, converted and processed. According to the data magnitude and the periodic update characteristics of the target table, the pre-processed data is written into the target table. At the same time, based on the historical data in the target table, the passenger flow can be predicted by an exponential smoothing algorithm, so as to strengthen the staffing during peak hours and ensure that users receive quality services.

Description

Technical Field

[0001] This invention relates to the field of data processing technology, specifically to a data processing and analysis system and method based on Python. Background Technology

[0002] With the booming development of the big data field, more and more enterprises are embarking on the stage of digital transformation, focusing on the governance and analysis of business data, and building data warehouses suitable for their own businesses. However, due to their multi-source nature and complexity, raw data often does not meet the conditions for direct analysis and needs to undergo a series of cleaning, transformation, and processing processes to obtain data assets that can be used by enterprises and realize data-driven business.

[0003] Traditional data processing tools and methods often have limitations when facing complex analysis and application scenarios, failing to meet personalized data processing needs. Given Python's flexibility and efficiency in data processing, as well as its relatively simple development and deployment, the technical challenge lies in providing a data processing and analysis method based on Python that can meet diverse business scenarios and requirements. Summary of the Invention

[0004] The technical objective of this invention is to address the above-mentioned shortcomings by providing a Python-based data processing and analysis system and method, thereby solving the technical problem of how to provide a data processing and analysis method based on Python that can meet various business scenarios and needs.

[0005] In a first aspect, the present invention provides a Python-based data processing and analysis system, comprising:

[0006] The data access module is used to configure database connection information according to the project environment and data warehouse level;

[0007] The update check module is used to check the data update status of the source table within a specified time period, and to regard the source table with data updates within the specified time period as having periodic update characteristics.

[0008] The data reading module is used to select full or incremental mode to read data from the source table based on the data volume and whether the source table has periodic update characteristics.

[0009] The data processing module is used to preprocess the data, which involves cleaning, transforming and processing the data to obtain preprocessed data.

[0010] The data writing module is used to select full or incremental mode to write the preprocessed data to the target table based on the data volume and whether the target table has periodic update characteristics.

[0011] The predictive analysis module is used to predict passenger flow trends based on historical data in the target table and through an exponential smoothing algorithm.

[0012] The log output module is used to output logs of different levels to different files. The logs record routine information and error messages during the execution of the data access module, the check and update module, the data processing module, the data writing module, and the predictive analysis module.

[0013] The code structure module is used to build code based on the data access module, check and update module, data reading module, data processing module, data writing module, predictive analysis module, and log output module, forming a project file including a main program file, function library, configuration file, and log file. The main program file contains the core script file of the data processing logic, the function library is a custom high-reusability data processing function, including data read and write functions and data cleaning functions, and the configuration file includes a file storing database connection information and a script file for setting log levels.

[0014] Preferably, the data access module is used to configure database connection information according to the project environment and data warehouse level, represent the database connection information in JSON format, use the read_json function of the pandas library to convert the read database connection information into dataframe format, and use the create_engine function under the sqlalchemy module to create a database connection engine object.

[0015] Preferably, the update check module is used to perform the following to check the data update status within a specified time period in the source table:

[0016] Read the time corresponding to the latest record in the source table and calculate its time difference with the current time.

[0017] Compare the time difference with the specified time period to determine whether the source table has been updated within the current time period;

[0018] When there are multiple source tables, as long as there is an updated record in any one of the source tables, it is determined that the data in the source table has been updated.

[0019] As a preferred option, for the data reading module, the full data mode is: write the source table name and the specified column name as string variables, and output the query statement in a formatted manner;

[0020] The incremental mode works as follows: the datetime module is used to obtain the string of the current date, the string of the current date is used as the judgment condition for the timestamp and written into the query statement, and the pandas library's read_sql function is used to convert the read data into dataframe format in combination with the database connection information and the query statement.

[0021] Preferably, the data processing module is used to remove duplicate and missing data based on the drop_duplicates and dropna functions;

[0022] The data processing module is used to convert the content and format of corresponding fields of the data according to requirements, including:

[0023] Date Conversion: Converts a timestamp type field to a date type;

[0024] Precision Conversion: Standardize the units and precision of field values ​​by combining the statistical scope of the indicators;

[0025] Dictionary conversion: Replacing the target column with a dimension that has business meaning based on the key-value correspondence in the dictionary;

[0026] Function mapping: Replaces the target column with a dimension that has business meaning according to a custom mapping rule;

[0027] The data processing module is used to perform the following data processing: select the corresponding summary column, value column and aggregation function according to the statistical indicators to be summarized, and calculate the indicators through the groupby or pivot_table function.

[0028] Preferably, the predictive analysis module is used to perform the following passenger flow trend prediction:

[0029] A training set is constructed based on the date index column and the corresponding passenger flow in the target table;

[0030] A passenger flow prediction model is constructed based on the exponential smoothing algorithm. The passenger flow prediction model is used to predict the output passenger flow by taking the preprocessed data in the target table as input.

[0031] Based on the training set, the SimpleExpSmoothing.fit and predict functions in the statsmodels library are used to train and predict passenger flow. During the model training and passenger flow prediction process, the smoothing parameter is traversed within the range of 0 to 1. The model parameters are determined based on the sum of squared residuals, and the deviation between the actual and predicted values ​​is visualized using the plot function in the matplotlib library.

[0032] Preferably, for target tables with a volume smaller than a threshold or without periodic update characteristics, the data writing module is used to select a full write mode and execute the following:

[0033] The clear statement is formatted and output by the table name of the target table, and then the old data is cleared by combining it with the execute function;

[0034] The preprocessed data in dataframe format is written to the target table using the append mode of the to_sql function;

[0035] Choose whether to synchronize the indexed columns to the target table as needed;

[0036] For a target table that is periodically updated, the data writing module is used to write data to the target table in incremental mode and performs the following:

[0037] The delete statement is formatted and output using the target table name, timestamp field, and current date, and then the execute function is used to clear the existing records of the current day in the target table.

[0038] If the index needs to be synchronized to the target table, reset the index in the following format: update date_original index, and write the preprocessed data in dataframe format to the target table using the append mode of the to_sql function;

[0039] If the index does not need to be synchronized to the target table, the preprocessed data in dataframe format can be written directly to the target table using the append mode of the to_sql function.

[0040] Preferably, the log output module is used to perform the following:

[0041] Use the logging module to create log objects, configure handler objects at different levels, and set the format of the log content;

[0042] Combine decorator functions and try-except statements to catch exceptions and record general information and error messages during script execution.

[0043] Preferably, the code structure includes a script structure and a directory structure;

[0044] The script structure includes: declaring the source table and target table at the beginning of the script, implementing update checking and log output functions with the help of decorator functions, and encapsulating the data processing logic in the main function;

[0045] The directory structure consists of the main program file, function library, configuration file, and log file. The naming rule for the main program file is: abbreviation of the data warehouse level corresponding to the target table + abbreviation of the business domain + abbreviation of the analysis subject. The naming rule for the configuration file and function library is: public + abbreviation of the described object.

[0046] In a second aspect, the present invention provides a Python-based data processing and analysis method for performing data processing and analysis through a Python-based data processing and analysis system as described in any of the first aspects, wherein the data processing and analysis includes the following steps:

[0047] Data access: Configure database connection information according to the project environment and data warehouse level;

[0048] Check for updates: Check the data update status of the source table within the specified time period. The source table with data updates within the specified time period is considered to have periodic update characteristics. If the source table has periodic update characteristics, proceed to the next data reading step; otherwise, pause processing.

[0049] Data reading: Depending on the data volume and whether the source table has periodic update characteristics, choose either full or incremental mode to read data from the source table;

[0050] Data processing: Preprocess the data read from the source table. The preprocessing involves cleaning, transforming, and processing the data to obtain the preprocessed data.

[0051] Data writing: Depending on the data volume and whether the target table has periodic update characteristics, select full or incremental mode to write the preprocessed data to the target table. If there is a need for passenger flow prediction, execute the next prediction analysis.

[0052] Data analysis; predicting passenger flow trends based on historical data in the target table using an exponential smoothing algorithm;

[0053] Log output: Different levels of logs are output to different files. The logs record general information and error messages during the data access, update check, data reading, data processing, data writing, and data analysis execution process.

[0054] The data processing and analysis system and method based on Python of the present invention have the following advantages:

[0055] 1. For source tables that are periodically updated, incremental mode read / write can save memory.

[0056] 2. Check the update status of the source table within the specified time period, and decide whether to perform subsequent processing operations based on the update identifier to avoid duplicate data processing;

[0057] 3. Utilize historical data to predict passenger flow trends in order to increase staffing during peak periods and ensure users receive high-quality service;

[0058] 4. By using decorator functions to implement update checking and log output functions, the extensibility and maintainability of the code are improved, and new functions can be added without changing the underlying code.

[0059] 5. For frequently used functions, such as reading, writing, and cleaning data, custom functions are used for encapsulation, which reduces the maintenance cost of the code. Attached Figure Description

[0060] To more clearly illustrate the technical solutions in the embodiments of the present invention, the drawings used in the description of the embodiments or the prior art will be briefly introduced below. Obviously, the drawings described below are only some embodiments of the present invention. For those skilled in the art, other drawings can be obtained based on these drawings without creative effort.

[0061] The invention will be further described below with reference to the accompanying drawings.

[0062] Figure 1 This is a flowchart of a Python-based data processing and analysis method, as shown in Example 2. Detailed Implementation

[0063] The present invention will be further described below with reference to the accompanying drawings and specific embodiments, so that those skilled in the art can better understand and implement the present invention. However, the embodiments are not intended to limit the present invention. In the absence of conflict, the embodiments of the present invention and the technical features in the embodiments can be combined with each other.

[0064] This invention provides a Python-based data processing and analysis system and method to address the technical problem of how to provide a data processing and analysis method based on Python that can meet various business scenarios and needs.

[0065] Example 1:

[0066] This invention discloses a Python-based data processing and analysis system, comprising a data access module, an update check module, a data reading module, a data processing module, a data writing module, a predictive analysis module, a log output module, and a code structure module.

[0067] The data access module is used to configure database connection information according to the project environment and data warehouse level.

[0068] In this embodiment, the data access module is used to configure database connection information according to the project environment and data warehouse level, represent the database connection information in JSON format, use the read_json function of the pandas library to convert the read database connection information into dataframe format, and use the create_engine function under the sqlalchemy module to create a database connection engine object.

[0069] The update check module is used to check the data update status of the source table within a specified time period, and the source table with data updates within the specified time period is regarded as having periodic update characteristics.

[0070] In this embodiment, the update check module is used to perform the following to check the data update status of the source table within a specified time period: read the time corresponding to the latest record in the source table and calculate the time difference between it and the current time; compare the time difference with the specified time period to determine whether the source table has been updated within the current time period; when there are multiple source tables, as long as there is an updated record in any one source table, it is determined that the data in the source table has been updated.

[0071] The data reading module is used to select full or incremental mode to read data from the source table based on the data volume and whether the source table has periodic update characteristics.

[0072] In this embodiment, for the data reading module, the full mode involves writing the source table name and specified column name as string variables and formatting the query statement for output. The incremental mode involves using the datetime module to obtain the string of the current date, using the string of the current date as a timestamp condition in the query statement, and combining the database connection information and the query statement, using the pandas library's read_sql function to convert the read data into a dataframe format.

[0073] The data processing module is used to preprocess the data, cleaning, transforming and processing the data to obtain preprocessed data.

[0074] In this embodiment, the data processing module is used to clean, transform, and process the data read from the source table. For data cleaning, the `drop_duplicates` and `dropna` functions are used to remove duplicate and missing data. For data transformation, the content and format of fields are converted according to the analysis requirements. Since data transformation scenarios are quite diverse, the following common transformation operations are given as examples: Date conversion refers to converting timestamp type fields to date type, facilitating trend analysis; Precision conversion refers to standardizing the unit and precision of field values ​​based on the statistical caliber of the indicator; Dictionary replacement refers to replacing the target column with a dimension that has business meaning based on the correspondence between keys and values ​​in the dictionary; Function mapping refers to replacing the target column with a dimension that has business meaning based on a custom mapping rule. For data processing, for statistical indicators that require categorization and summarization, the corresponding summary column, value column, and aggregate function are selected, and functions such as `groupby` or `pivot_table` are used to calculate the indicators.

[0075] The data writing module is used to write the preprocessed data to the target table in either full or incremental mode, depending on the data volume and whether the target table has periodic update characteristics.

[0076] As a specific implementation of the data writing module, for target tables with a volume smaller than the threshold or without periodic update characteristics, the data writing module selects the full write mode and executes the following: the clear statement is formatted and output by the target table name, and then the old data is cleared by combining the execute function; the preprocessed data in dataframe format is written to the target table by the append mode of the to_sql function; and whether to synchronize the index columns to the target table as needed.

[0077] For target tables with periodic update characteristics, the data writing module is used to write data to the target table in incremental mode and executes the following: The delete statement is formatted and output using the target table name, timestamp field, and current date, and the execute function is used to clear all existing records for the current day in the target table; if the index needs to be synchronized to the target table, the index is reset according to the following format: update date_original index, and the preprocessed data in dataframe format is written to the target table using the append mode of the to_sql function; if the index does not need to be synchronized to the target table, the preprocessed data in dataframe format is directly written to the target table using the append mode of the to_sql function.

[0078] The predictive analytics module is used to predict passenger flow trends based on historical data in the target table and through an exponential smoothing algorithm.

[0079] In this embodiment, the predictive analysis module uses historical data to predict passenger flow trends in order to strengthen staffing during peak periods and ensure users receive high-quality service. Specifically, the predictive analysis module performs the following passenger flow trend prediction: It constructs a training set based on the date index column and corresponding passenger flow in the target table; it constructs a passenger flow prediction model based on the exponential smoothing algorithm, which uses preprocessed data from the target table as input to predict the output passenger flow; based on the training set, it trains the passenger flow prediction model and predicts passenger flow using the SimpleExpSmoothing.fit and predict functions from the statsmodels library. During model training and passenger flow prediction, the smoothing parameter iterates through values ​​between 0 and 1, determines the model parameters based on the sum of squared residuals, and visualizes the deviation between the actual and predicted values ​​using the plot function from the matplotlib library.

[0080] The log output module is used to output logs of different levels to different files. The logs record routine information and error messages during the execution of the data access module, check and update module, data processing module, data writing module, and predictive analysis module.

[0081] In this embodiment, the log output module is used to perform the following: create a log object using the logging module, configure handler objects at different levels, and set the format of the log content; capture exceptions using decorator functions and try-except statements, and record general information and error messages during script execution.

[0082] The code structure module is used to build code based on the data access module, check and update module, data reading module, data processing module, data writing module, predictive analysis module, and log output module, forming a project file including a main program file, function library, configuration file, and log file. The main program file contains the core script file of the data processing logic, the function library is a custom high-reusability data processing function, including data read and write functions and data cleaning functions, and the configuration file includes a file storing database connection information and a script file for setting log levels.

[0083] In this embodiment, the code structure includes a script structure and a directory structure. The script structure includes: declaring the source and target tables at the beginning of the script; using decorator functions to implement update checks and log output; and encapsulating the data processing logic using the `main` function. The directory structure consists of the main program file, function libraries, configuration files, and log files. The naming convention for the main program file is: abbreviation of the data warehouse level corresponding to the target table + abbreviation of the business domain + abbreviation of the analysis subject. The naming convention for the configuration files and function libraries is: `public` + abbreviation of the described object.

[0084] This embodiment uses a Python-based data processing and analysis system. Considering the data volume and the periodic update characteristics of the source table, it selects either full or incremental mode to read data from the source table. After cleaning, transforming, and processing the read data, the preprocessed data is written to the target table based on the data volume and the target table's periodic update characteristics. Simultaneously, based on historical data in the target table, an exponential smoothing algorithm can be used to predict passenger flow, allowing for increased staffing during peak periods to ensure users receive high-quality service.

[0085] Example 2:

[0086] This invention discloses a Python-based data processing and analysis method, which performs data processing and analysis using the system disclosed in Example 1, and includes the following steps:

[0087] S100, Data Access: Configure database connection information according to the project environment and data warehouse level;

[0088] S200, Check for updates: Check the data update status of the source table within the specified time period. The source table with data updates within the specified time period is considered to have periodic update characteristics. If the source table has periodic update characteristics, proceed to the next data reading step; otherwise, pause processing.

[0089] S300, Data Reading: Depending on the data volume and whether the source table has periodic update characteristics, select full or incremental mode to read data from the source table;

[0090] S400, Data Processing: Preprocess the data read from the source table. The preprocessing involves cleaning, transforming, and processing the data to obtain preprocessed data.

[0091] S500, Data Writing: Depending on the data volume and whether the target table has periodic update characteristics, select full or incremental mode to write the preprocessed data to the target table. If there is a need for passenger flow prediction, execute the next prediction analysis.

[0092] S600, Data Analysis; Based on historical data in the target table, predict passenger flow trends using an exponential smoothing algorithm;

[0093] S700 Log Output: Outputs logs of different levels to different files. The logs record general information and error messages during the data access, update check, data reading, data processing, data writing, and data analysis execution process.

[0094] In step S100 of this embodiment, data access is performed by configuring database connection information according to the project environment and data warehouse level. The database connection information is represented in JSON format. The read_json function of the pandas library is used to convert the read database connection information into a dataframe format, and the create_engine function under the sqlalchemy module is used to create a database connection engine object.

[0095] Step S200 checks and updates the source table, specifically as follows: read the time corresponding to the latest record in the source table and calculate the time difference between it and the current time; compare the time difference with the specified time period to determine whether the source table has been updated within the current time period; when there are multiple source tables, as long as there is an updated record in any one source table, it is determined that the data in the source table has been updated.

[0096] Step S300 data reading includes two modes: full mode and incremental mode.

[0097] The full query mode involves setting the source table name and specified column names as string variables and formatting the query statement for output. The incremental query mode uses the `datetime` module to retrieve the current date as a string, uses this string as a timestamp condition in the query statement, and combines the database connection information and the query statement with the `read_sql` function from the pandas library to convert the read data into a dataframe format.

[0098] The incremental mode works as follows: the datetime module is used to obtain the string of the current date, the string of the current date is used as the judgment condition for the timestamp and written into the query statement, and the pandas library's read_sql function is used to convert the read data into dataframe format in combination with the database connection information and the query statement.

[0099] Step S400, data processing, involves cleaning, transforming, and manipulating the data read from the source table. For data cleaning, the `drop_duplicates` and `dropna` functions are used to remove duplicate and missing data. For data transformation, the content and format of fields are converted according to analytical needs. Since data transformation scenarios are diverse, the following common transformation operations are given as examples: Date conversion refers to converting timestamp type fields to date type for easier trend analysis; Precision conversion refers to standardizing the unit and precision of field values ​​based on the statistical caliber of the indicator; Dictionary replacement refers to replacing the target column with a dimension that has business meaning based on the correspondence between keys and values ​​in the dictionary; Function mapping refers to replacing the target column with a dimension that has business meaning based on a custom mapping rule. For data processing, for statistical indicators that require categorization and summarization, the corresponding summary column, value column, and aggregate function are selected, and functions such as `groupby` or `pivot_table` are used to calculate the indicators.

[0100] Step S500 data writing includes two modes: full and incremental.

[0101] For target tables with a volume smaller than the threshold or without periodic update characteristics, the full write mode is selected and executed as follows: the clear statement is formatted and output by the target table name, and then the old data is cleared by combining it with the execute function; the preprocessed data in dataframe format is written to the target table by the append mode of the to_sql function; and whether to synchronize the index columns to the target table is selected as needed.

[0102] For target tables with periodic update characteristics, write to the target table in incremental mode, and execute as follows: Format the delete statement using the target table name, timestamp field, and current date, and use the execute function to clear existing records for the current day in the target table; if the index needs to be synchronized to the target table, reset the index according to the following format: update date_original index, and write the preprocessed data in dataframe format to the target table using the append mode of the to_sql function; if the index does not need to be synchronized to the target table, directly write the preprocessed data in dataframe format to the target table using the append mode of the to_sql function.

[0103] Step S600 predictive analysis uses historical data to predict passenger flow trends in order to increase staffing during peak periods and ensure that users receive high-quality service.

[0104] In a specific implementation, the following steps are taken to predict passenger flow trends: a training set is constructed based on the date index column and the corresponding passenger flow in the target table; a passenger flow prediction model is constructed based on the exponential smoothing algorithm, which is used to predict the output passenger flow using the preprocessed data in the target table as input; based on the training set, the passenger flow prediction model is trained and passenger flow is predicted using the SimpleExpSmoothing.fit and predict functions in the statsmodels library. During the model training and passenger flow prediction process, the smoothing parameter is iterated through the range of 0 to 1, the model parameters are determined based on the sum of squared residuals, and the deviation between the actual value and the predicted value is visualized using the plot function in the matplotlib library.

[0105] Step S700 log output execution is as follows: The logging module is used to create a log object, configure different levels of handler objects, and set the format of the log content; exceptions are captured by combining decorator functions and try-except statements, and general information and error messages during script execution are recorded.

[0106] The present invention has been shown and described in detail above with reference to the accompanying drawings and preferred embodiments. However, the present invention is not limited to these disclosed embodiments. Based on the above multiple embodiments, those skilled in the art will know that more embodiments of the present invention can be obtained by combining the means in the different embodiments described above, and these embodiments are also within the protection scope of the present invention.

Claims

1. A data processing and analysis system based on Python, characterized in that: include: A data access module, which is used to configure database connection information according to the project environment and data warehouse level; A checking and updating module, which is used to check the data update status in the source table within a specified time period, and regard the source table with data update within the specified time period as having periodic update characteristics; A data reading module, wherein the data reading module is used to select a full or incremental mode to read the data of the source table according to the data level and whether the source table has a periodic update characteristic; A data processing module, wherein the data processing module is used to preprocess the data, clean, convert and process the data through preprocessing to obtain preprocessed data; A data writing module, which is used to select a full or incremental mode to write the pre-processed data into the target table according to the data level and whether the target table has periodic update characteristics; A prediction analysis module, which is used to predict the passenger flow trend based on the historical data in the target table through an exponential smoothing algorithm; A log output module, which is used to output logs of different levels to different files. The log records general information and error prompts during the execution of the data access module, the check and update module, the data processing module, the data writing module, and the prediction and analysis module; A code structure module is used to construct code based on a data access module, a check and update module, a data reading module, a data processing module, a data writing module, a predictive analysis module, and a log output module to form an engineering file including a main program file, a function library, a configuration file, and a log file. The main program file contains a core script file of data processing logic, the function library is a customized high-reusability data processing function, including data reading and writing functions and data cleaning functions, and the configuration file includes a file for storing database connection information and a script file for setting the log level.

2. The Python-based data processing and analysis system according to claim 1, characterized in that: The data access module is used to configure database connection information according to the project environment and data warehouse level, express the database connection information in json format, apply the read_json function of the pandas library to convert the read database connection information into dataframe format, and apply the create_engine function under the sqlalchemy module to create a database connection engine object.

3. The Python-based data processing and analysis system according to claim 1, characterized in that: The checking and updating module is used to perform the following operations to check the data update status in the specified time period in the source table: Read the time corresponding to the latest record in the source table and calculate the time difference between it and the current time; Compare the time difference with the specified time period to determine whether the source table has been updated within the current time period; When there are multiple source tables, as long as there is an update record in one of the source tables, it is determined that the data in the source table is updated.

4. The Python-based data processing and analysis system according to any one of claims 1 to 3, characterized in that: For the data reading module, the full mode is: write the source table name and the specified column name as string variables, and format the query statement for output; The incremental mode is: use the datetime module to obtain the string of today's date, write the string of today's date as the judgment condition of the timestamp into the query statement, combine the database connection information and the query statement, and use the read_sql function of the pandas library to convert the read data into the dataframe format.

5. The Python-based data processing and analysis system according to any one of claims 1 to 3, characterized in that: The data processing module is used to delete duplicate and missing data based on drop_duplicates and dropna functions; The data processing module is used to convert the content and format of the corresponding fields of the data according to the requirements, including: Date conversion: convert the timestamp type field to date type; Precision conversion: standardize the unit and precision of the field value based on the statistical caliber of the indicator; Dictionary transformation: replace the target column with a dimension with business meaning based on the correspondence between keys and values ​​in the dictionary; Function mapping: replace the target column with a dimension with business meaning according to the customized mapping rules; The data processing module is used to perform the following data processing: according to the statistical indicators that need to be summarized, the corresponding summary columns, value columns and aggregation functions are selected, and the indicator calculation is realized through the groupby or pivot_table function.

6. The Python-based data processing and analysis system according to any one of claims 1 to 3, characterized in that: The prediction analysis module is used to perform the following passenger flow trend prediction: Construct a training set based on the date index column and the corresponding passenger flow in the target table; Building a passenger flow prediction model based on an exponential smoothing algorithm, the passenger flow prediction model is used to take the pre-processed data in the target table as input and predict the output passenger flow; Based on the training set, the passenger flow prediction model is trained and predicted through the SimpleExpSmoothing.fit and predict functions in the statsmodels library. During the model training and passenger flow prediction process, the smoothing parameter is traversed in the range of 0 to 1, the model parameters are determined according to the residual sum of squares, and the deviation between the actual value and the predicted value is visualized based on the plot function of the matplotlib library.

7. The Python-based data processing and analysis system according to claim 4, characterized in that: For a target table whose magnitude is less than a threshold or has no periodic update characteristics, the data writing module is used to select a full write mode and execute as follows: Format the clear statement using the target table name, and then use the execute function to clear the old data. Write the preprocessed data in dataframe format to the target table through the append mode of the to_sql function; Choose whether to synchronize the index column to the target table as needed; For a target table with periodic update characteristics, the data writing module is used to write the target table in incremental mode and is performed as follows: The delete statement is formatted and outputted using the table name, timestamp field, and current date of the target table, and the execute function is used to clear the existing records of the current day in the target table. If the index needs to be synchronized to the target table, reset the index in the following format: update date_original index, and write the pre-processed data in dataframe format to the target table through the append mode of the to_sql function; If the index does not need to be synchronized to the target table, the preprocessed data in dataframe format is directly written to the target table through the append mode of the to_sql function.

8. The Python-based data processing and analysis system according to any one of claims 1 to 3, characterized in that: The log output module is used to perform the following: Apply the logging module to create log objects, configure handler objects of different levels, and set the format of log content; Combine the decorator function with the try-except statement to capture exceptions and record general information and error prompts during script execution.

9. The Python-based data processing and analysis system according to any one of claims 1 to 3, characterized in that: The code structure includes a script structure and a directory structure; The script structure includes: declaring the source table and the target table at the beginning of the script, using the decorator function to implement the check update and log output functions, and using the main function to encapsulate the logic of data processing; The directory structure consists of the main program file, function library, configuration file and log file. The naming rule of the main program file is: the abbreviation of the data warehouse level corresponding to the target table + the abbreviation of the business domain + the abbreviation of the analysis subject. The naming rule of the configuration file and function library is: public + the abbreviation of the description object.

10. A data processing and analysis method based on Python, characterized in that: Used for performing data processing and analysis by a Python-based data processing and analysis system as described in any one of claims 1 to 9, the data processing and analysis comprising the following steps: Data access: configure database connection information according to the project environment and data warehouse level; Check update: Check the data update status in the specified time period in the source table, and regard the source table with data update in the specified time period as having periodic update characteristics. If the source table has periodic update characteristics, execute the next step of data reading, otherwise suspend processing; Data reading: If the data volume and whether the source table has periodic update characteristics are considered, select full or incremental mode to read the data of the source table; Data processing: preprocess the data read from the source table, clean, convert and process the data through preprocessing to obtain preprocessed data; Data writing: According to the data volume and whether the target table has the characteristics of periodic update, select full or incremental mode to write the pre-processed data to the target table. If there is a passenger flow forecasting requirement, perform the next step of forecasting analysis. Data analysis: Based on the historical data in the target table, the passenger flow trend is predicted by exponential smoothing algorithm; Log output: Logs of different levels are output to different files. The logs record general information and error prompts of data access, check update, data reading, data processing, data writing and data analysis execution process.

Images