<\/span><\/h3>\n\n\n\n\u4f7f\u7528SQLAlchemy\u5efa\u7acb\u9ad8\u6548\u8fde\u63a5\uff0c\u907f\u514d\u76f4\u63a5\u64cd\u4f5cSQL\u8bed\u53e5\u7684\u590d\u6742\u6027\uff1a<\/p>\n\n\n\n
def get_sqlalchemy_engine():
return create_engine('mysql+pymysql:\/\/root:root@localhost\/qycw?charset=utf8mb4')
def load_financial_data_from_db():
engine = get_sqlalchemy_engine()
query = \"\"\"
SELECT
report_date, revenue, operating_cost, sales_expense,
admin_expense, financial_expense, current_assets,
current_liabilities, total_assets, total_liabilities, net_profit
FROM financial_data
ORDER BY report_date
\"\"\"
df = pd.read_sql(query, engine, parse_dates=['report_date'])
return df<\/code><\/pre>\n\n\n\n<\/span>2. \u6570\u636e\u6e05\u6d17\u4e0e\u5f02\u5e38\u68c0\u6d4b<\/span><\/h3>\n\n\n\n\u8d22\u52a1\u6570\u636e\u5bb9\u4e0d\u5f97\u534a\u70b9\u8bef\u5dee\uff0c\u6211\u4eec\u5b9e\u73b0\u4e09\u91cd\u6e05\u6d17\u673a\u5236\uff1a<\/p>\n\n\n\n
def clean_data(df):
# \u7f3a\u5931\u503c\u68c0\u67e5<\/em>
print(\"\u7f3a\u5931\u503c\u68c0\u67e5:\", df.isnull().sum())
# \u5f02\u5e38\u503c\u68c0\u6d4b\uff08IQR\u65b9\u6cd5\uff09<\/em>
def detect_outliers(series):
Q1 = series.quantile(0.25)
Q3 = series.quantile(0.75)
IQR = Q3 - Q1
lower_bound = Q1 - 1.5 * IQR
upper_bound = Q3 + 1.5 * IQR
return (series < lower_bound) | (series > upper_bound)
# \u65f6\u95f4\u7279\u5f81\u5de5\u7a0b<\/em>
df['\u5b63\u5ea6'] = df['\u65e5\u671f'].dt.quarter
df['\u6708\u4efd'] = df['\u65e5\u671f'].dt.month
return df<\/code><\/pre>\n\n\n\n<\/span>3. \u6307\u6807\u8ba1\u7b97\u5f15\u64ce<\/span><\/h3>\n\n\n\n\u81ea\u52a8\u8ba1\u7b9712\u9879\u6838\u5fc3\u8d22\u52a1\u6307\u6807\uff0c\u8986\u76d6\u76c8\u5229\u80fd\u529b\u3001\u507f\u503a\u80fd\u529b\u548c\u8fd0\u8425\u6548\u7387\uff1a<\/p>\n\n\n\n
def calculate_financial_ratios(df):
# \u76c8\u5229\u80fd\u529b\u6307\u6807<\/em>
df['\u6bdb\u5229\u7387(%)'] = ((df['\u8425\u4e1a\u6536\u5165'] - df['\u8425\u4e1a\u6210\u672c']) \/ df['\u8425\u4e1a\u6536\u5165'] * 100).round(2)
df['\u51c0\u5229\u7387(%)'] = (df['\u51c0\u5229\u6da6'] \/ df['\u8425\u4e1a\u6536\u5165'] * 100).round(2)
# \u507f\u503a\u80fd\u529b\u6307\u6807<\/em>
df['\u6d41\u52a8\u6bd4\u7387'] = (df['\u6d41\u52a8\u8d44\u4ea7'] \/ df['\u6d41\u52a8\u8d1f\u503a']).round(2)
df['\u8d44\u4ea7\u8d1f\u503a\u7387(%)'] = (df['\u603b\u8d1f\u503a'] \/ df['\u603b\u8d44\u4ea7'] * 100).round(2)
# \u8fd0\u8425\u6548\u7387\u6307\u6807<\/em>
df['\u603b\u8d44\u4ea7\u5468\u8f6c\u7387'] = (df['\u8425\u4e1a\u6536\u5165'] \/ df['\u603b\u8d44\u4ea7']).round(2)
return df<\/code><\/pre>\n\n\n\n<\/span>4. \u516d\u7ef4\u53ef\u89c6\u5316\u5206\u6790<\/span><\/h3>\n\n\n\n6\u4e2a\u4e13\u4e1a\u56fe\u8868\uff0c\u5168\u65b9\u4f4d\u5c55\u793a\u4f01\u4e1a\u72b6\u51b5\uff1a<\/p>\n\n\n\n
def visualize_data(df):
plt.figure(figsize=(18, 12))
# 1. \u6536\u5165\u4e0e\u5229\u6da6\u8d8b\u52bf<\/em>
plt.subplot(2, 3, 1)
plt.plot(df['\u65e5\u671f'], df['\u8425\u4e1a\u6536\u5165'], marker='o', label='\u8425\u4e1a\u6536\u5165')
plt.plot(df['\u65e5\u671f'], df['\u51c0\u5229\u6da6'], marker='s', label='\u51c0\u5229\u6da6')
plt.title('\u6536\u5165\u4e0e\u5229\u6da6\u8d8b\u52bf\u5206\u6790')
# 2. \u6210\u672c\u7ed3\u6784\u997c\u56fe<\/em>
plt.subplot(2, 3, 2)
cost_data = df[['\u8425\u4e1a\u6210\u672c', '\u9500\u552e\u8d39\u7528', '\u7ba1\u7406\u8d39\u7528', '\u8d22\u52a1\u8d39\u7528']].sum()
cost_data.plot(kind='pie', autopct='%1.1f%%')
# 3. \u76c8\u5229\u80fd\u529b\u6307\u6807<\/em>
plt.subplot(2, 3, 3)
plt.plot(df['\u65e5\u671f'], df['\u6bdb\u5229\u7387(%)'], label='\u6bdb\u5229\u7387')
plt.plot(df['\u65e5\u671f'], df['\u51c0\u5229\u7387(%)'], label='\u51c0\u5229\u7387')
# 4. \u507f\u503a\u80fd\u529b\u6307\u6807<\/em>
plt.subplot(2, 3, 4)
plt.plot(df['\u65e5\u671f'], df['\u6d41\u52a8\u6bd4\u7387'], label='\u6d41\u52a8\u6bd4\u7387')
plt.plot(df['\u65e5\u671f'], df['\u8d44\u4ea7\u8d1f\u503a\u7387(%)'], label='\u8d44\u4ea7\u8d1f\u503a\u7387')
# 5. \u5b63\u5ea6\u5bf9\u6bd4\u5206\u6790<\/em>
plt.subplot(2, 3, 5)
quarterly = df.groupby('\u5b63\u5ea6').agg({'\u8425\u4e1a\u6536\u5165': 'sum', '\u51c0\u5229\u6da6': 'sum'})
quarterly.plot(kind='bar')
# 6. \u8d44\u4ea7\u8d1f\u503a\u7ed3\u6784<\/em>
plt.subplot(2, 3, 6)
asset_liability = df[['\u603b\u8d44\u4ea7', '\u603b\u8d1f\u503a']].iloc[-1]
asset_liability.plot(kind='bar')
plt.tight_layout()
plt.savefig('\u8d22\u52a1\u5206\u6790\u62a5\u544a.png', dpi=300)<\/code><\/pre>\n\n\n\n<\/span>5. \u667a\u80fd\u62a5\u544a\u751f\u6210<\/span><\/h3>\n\n\n\n\u81ea\u52a8\u751f\u6210\u5305\u542b\u8d22\u52a1\u6458\u8981\u3001\u5065\u5eb7\u8bc4\u4f30\u3001\u8d8b\u52bf\u5206\u6790\u548c\u98ce\u9669\u63d0\u793a\u7684\u4e13\u4e1a\u62a5\u544a\uff1a<\/p>\n\n\n\n
def\u00a0generate_report(df):\n\u00a0 \u00a0 print(\"=\"*50)\n\u00a0 \u00a0 print(\"\u4f01\u4e1a\u8d22\u52a1\u5206\u6790\u62a5\u544a\".center(50))\n\u00a0 \u00a0 print(\"=\"*50)\n\u00a0 \u00a0\u00a0\n\u00a0 \u00a0\u00a0# \u8d22\u52a1\u6570\u636e\u6458\u8981<\/em>\n\u00a0 \u00a0 print(f\"\u5e74\u5ea6\u603b\u8425\u4e1a\u6536\u5165:\u00a0{df['\u8425\u4e1a\u6536\u5165'].sum():.2f}\u4e07\u5143\")\n\u00a0 \u00a0 print(f\"\u5e74\u5ea6\u603b\u51c0\u5229\u6da6:\u00a0{df['\u51c0\u5229\u6da6'].sum():.2f}\u4e07\u5143\")\n\u00a0 \u00a0 print(f\"\u5e73\u5747\u6bdb\u5229\u7387:\u00a0{df['\u6bdb\u5229\u7387(%)'].mean():.2f}%\")\n\u00a0 \u00a0\u00a0\n\u00a0 \u00a0\u00a0# \u8d22\u52a1\u5065\u5eb7\u8bc4\u4f30<\/em>\n\u00a0 \u00a0 avg_current_ratio = df['\u6d41\u52a8\u6bd4\u7387'].mean()\n\u00a0 \u00a0 avg_debt_ratio = df['\u8d44\u4ea7\u8d1f\u503a\u7387(%)'].mean()\n\u00a0 \u00a0\u00a0\n\u00a0 \u00a0 print(f\"\u5e73\u5747\u6d41\u52a8\u6bd4\u7387:\u00a0{avg_current_ratio:.2f}\", end=\" - \")\n\u00a0 \u00a0\u00a0if\u00a0avg_current_ratio >\u00a02:\n\u00a0 \u00a0 \u00a0 \u00a0 print(\"\u507f\u503a\u80fd\u529b\u5f3a\")\n\u00a0 \u00a0\u00a0elif\u00a0avg_current_ratio >\u00a01:\n\u00a0 \u00a0 \u00a0 \u00a0 print(\"\u507f\u503a\u80fd\u529b\u6b63\u5e38\")\n\u00a0 \u00a0\u00a0else:\n\u00a0 \u00a0 \u00a0 \u00a0 print(\"\u507f\u503a\u80fd\u529b\u8f83\u5f31\")\n\u00a0 \u00a0\u00a0\n\u00a0 \u00a0\u00a0# \u98ce\u9669\u63d0\u793a<\/em>\n\u00a0 \u00a0\u00a0if\u00a0df['\u51c0\u5229\u6da6'].min() <\u00a00:\n\u00a0 \u00a0 \u00a0 \u00a0 print(\"\u26a0\ufe0f \u5b58\u5728\u6708\u5ea6\u4e8f\u635f\u60c5\u51b5\uff0c\u9700\u5173\u6ce8\u6210\u672c\u63a7\u5236\")\n\u00a0 \u00a0\u00a0if\u00a0avg_current_ratio <\u00a01.5:\n\u00a0 \u00a0 \u00a0 \u00a0 print(\"\u26a0\ufe0f \u6d41\u52a8\u6bd4\u7387\u504f\u4f4e\uff0c\u77ed\u671f\u507f\u503a\u538b\u529b\u8f83\u5927\")\n\n\n\n<\/code><\/pre>\n\n\n\n\u5b8c\u6574\u4ee3\u7801\u53c2\u8003\uff1a\n\nimport pandas as pd\r\nimport numpy as np\r\nimport matplotlib.pyplot as plt\r\nimport seaborn as sns\r\nfrom datetime import datetime, timedelta\r\nimport pymysql\r\nfrom sqlalchemy import create_engine\r\n\r\n# \u8bbe\u7f6e\u4e2d\u6587\u663e\u793a\r\nplt.rcParams['font.sans-serif'] = ['SimHei'] # \u7528\u6765\u6b63\u5e38\u663e\u793a\u4e2d\u6587\u6807\u7b7e\r\nplt.rcParams['axes.unicode_minus'] = False# \u7528\u6765\u6b63\u5e38\u663e\u793a\u8d1f\u53f7\r\n\r\n# \u6570\u636e\u5e93\u8fde\u63a5\u914d\u7f6e\r\ndef get_conn():\r\n return pymysql.connect(\r\n host='',\r\n user='',\r\n password='',\r\n database='',\r\n charset='utf8',\r\n cursorclass=pymysql.cursors.DictCursor\r\n )\r\n\r\n# \u4f7f\u7528SQLAlchemy\u521b\u5efa\u5f15\u64ce\uff08\u63a8\u8350\u65b9\u5f0f\uff09\r\ndef get_sqlalchemy_engine():\r\n return create_engine('mysql+pymysql:\/\/?charset=utf8')\r\n\r\n# \u521b\u5efa\u8d22\u52a1\u6570\u636e\u8868\uff08\u5982\u679c\u4e0d\u5b58\u5728\uff09\r\ndef create_financial_table():\r\n conn = get_conn()\r\n try:\r\n with conn.cursor() as cursor:\r\n # \u521b\u5efa\u8d22\u52a1\u6570\u636e\u8868\r\n create_table_sql = \"\"\"\r\n CREATE TABLE IF NOT EXISTS financial_data (\r\n id INT AUTO_INCREMENT PRIMARY KEY,\r\n report_date DATE NOT NULL,\r\n revenue DECIMAL(15,2) COMMENT '\u8425\u4e1a\u6536\u5165(\u4e07\u5143)',\r\n operating_cost DECIMAL(15,2) COMMENT '\u8425\u4e1a\u6210\u672c(\u4e07\u5143)',\r\n sales_expense DECIMAL(15,2) COMMENT '\u9500\u552e\u8d39\u7528(\u4e07\u5143)',\r\n admin_expense DECIMAL(15,2) COMMENT '\u7ba1\u7406\u8d39\u7528(\u4e07\u5143)',\r\n financial_expense DECIMAL(15,2) COMMENT '\u8d22\u52a1\u8d39\u7528(\u4e07\u5143)',\r\n current_assets DECIMAL(15,2) COMMENT '\u6d41\u52a8\u8d44\u4ea7(\u4e07\u5143)',\r\n current_liabilities DECIMAL(15,2) COMMENT '\u6d41\u52a8\u8d1f\u503a(\u4e07\u5143)',\r\n total_assets DECIMAL(15,2) COMMENT '\u603b\u8d44\u4ea7(\u4e07\u5143)',\r\n total_liabilities DECIMAL(15,2) COMMENT '\u603b\u8d1f\u503a(\u4e07\u5143)',\r\n net_profit DECIMAL(15,2) COMMENT '\u51c0\u5229\u6da6(\u4e07\u5143)',\r\n created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP\r\n ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='\u4f01\u4e1a\u8d22\u52a1\u6570\u636e\u8868';\r\n \"\"\"\r\n cursor.execute(create_table_sql)\r\n conn.commit()\r\n print(\"\u8d22\u52a1\u6570\u636e\u8868\u521b\u5efa\u6210\u529f\u6216\u5df2\u5b58\u5728\")\r\n except Exception as e:\r\n print(f\"\u521b\u5efa\u8868\u65f6\u51fa\u9519: {e}\")\r\n finally:\r\n conn.close()\r\n\r\n# \u4ece\u6570\u636e\u5e93\u8bfb\u53d6\u8d22\u52a1\u6570\u636e\uff08\u4f7f\u7528SQLAlchemy\uff09\r\ndef load_financial_data_from_db():\r\n engine = get_sqlalchemy_engine()\r\n try:\r\n # \u4f7f\u7528\u82f1\u6587\u5b57\u6bb5\u540d\u907f\u514d\u89e3\u6790\u95ee\u9898\r\n query = \"\"\"\r\n SELECT \r\n report_date,\r\n revenue,\r\n operating_cost,\r\n sales_expense,\r\n admin_expense,\r\n financial_expense,\r\n current_assets,\r\n current_liabilities,\r\n total_assets,\r\n total_liabilities,\r\n net_profit\r\n FROM financial_data\r\n ORDER BY report_date\r\n \"\"\"\r\n # \u4f7f\u7528SQLAlchemy\u5f15\u64ce\u548cparse_dates\u53c2\u6570\r\n df = pd.read_sql(query, engine, parse_dates=['report_date'])\r\n \r\n # \u91cd\u547d\u540d\u5217\u4e3a\u4e2d\u6587\r\n df.columns = [\r\n '\u65e5\u671f', '\u8425\u4e1a\u6536\u5165(\u4e07\u5143)', '\u8425\u4e1a\u6210\u672c(\u4e07\u5143)', '\u9500\u552e\u8d39\u7528(\u4e07\u5143)',\r\n '\u7ba1\u7406\u8d39\u7528(\u4e07\u5143)', '\u8d22\u52a1\u8d39\u7528(\u4e07\u5143)', '\u6d41\u52a8\u8d44\u4ea7(\u4e07\u5143)',\r\n '\u6d41\u52a8\u8d1f\u503a(\u4e07\u5143)', '\u603b\u8d44\u4ea7(\u4e07\u5143)', '\u603b\u8d1f\u503a(\u4e07\u5143)', '\u51c0\u5229\u6da6(\u4e07\u5143)'\r\n ]\r\n return df\r\n except Exception as e:\r\n print(f\"\u4ece\u6570\u636e\u5e93\u8bfb\u53d6\u6570\u636e\u65f6\u51fa\u9519: {e}\")\r\n return pd.DataFrame()\r\n\r\n# \u6570\u636e\u6e05\u6d17\u4e0e\u9884\u5904\u7406\r\ndef clean_data(df):\r\n # \u68c0\u67e5\u7f3a\u5931\u503c\r\n print(\"\u7f3a\u5931\u503c\u68c0\u67e5:\")\r\n print(df.isnull().sum())\r\n def detect_outliers(series):\r\n Q1 = series.quantile(0.25)\r\n Q3 = series.quantile(0.75)\r\n IQR = Q3 - Q1\r\n lower_bound = Q1 - 1.5 * IQR\r\n upper_bound = Q3 + 1.5 * IQR\r\n return (series < lower_bound) | (series > upper_bound)\r\n \r\n print(\"\\n\u5f02\u5e38\u503c\u68c0\u67e5:\")\r\n for col in df.select_dtypes(include=[np.number]).columns:\r\n outliers = detect_outliers(df[col])\r\n if outliers.any():\r\n print(f\"{col}: {outliers.sum()}\u4e2a\u5f02\u5e38\u503c\")\r\n \r\n # \u6dfb\u52a0\u65f6\u95f4\u7279\u5f81\r\n df['\u5b63\u5ea6'] = df['\u65e5\u671f'].dt.quarter\r\n df['\u6708\u4efd'] = df['\u65e5\u671f'].dt.month\r\n return df\r\n\r\n# \u8d22\u52a1\u6307\u6807\u8ba1\u7b97\r\ndef calculate_financial_ratios(df):\r\n # \u76c8\u5229\u80fd\u529b\u6307\u6807\r\n df['\u6bdb\u5229\u7387(%)'] = ((df['\u8425\u4e1a\u6536\u5165(\u4e07\u5143)'] - df['\u8425\u4e1a\u6210\u672c(\u4e07\u5143)']) \/ df['\u8425\u4e1a\u6536\u5165(\u4e07\u5143)'] * 100).round(2)\r\n df['\u51c0\u5229\u7387(%)'] = (df['\u51c0\u5229\u6da6(\u4e07\u5143)'] \/ df['\u8425\u4e1a\u6536\u5165(\u4e07\u5143)'] * 100).round(2)\r\n df['\u8425\u4e1a\u5229\u6da6\u7387(%)'] = ((df['\u51c0\u5229\u6da6(\u4e07\u5143)'] + df['\u8d22\u52a1\u8d39\u7528(\u4e07\u5143)']) \/ df['\u8425\u4e1a\u6536\u5165(\u4e07\u5143)'] * 100).round(2)\r\n \r\n # \u507f\u503a\u80fd\u529b\u6307\u6807\r\n df['\u6d41\u52a8\u6bd4\u7387'] = (df['\u6d41\u52a8\u8d44\u4ea7(\u4e07\u5143)'] \/ df['\u6d41\u52a8\u8d1f\u503a(\u4e07\u5143)']).round(2)\r\n df['\u8d44\u4ea7\u8d1f\u503a\u7387(%)'] = (df['\u603b\u8d1f\u503a(\u4e07\u5143)'] \/ df['\u603b\u8d44\u4ea7(\u4e07\u5143)'] * 100).round(2)\r\n \r\n # \u8fd0\u8425\u6548\u7387\u6307\u6807\r\n df['\u603b\u8d44\u4ea7\u5468\u8f6c\u7387'] = (df['\u8425\u4e1a\u6536\u5165(\u4e07\u5143)'] \/ df['\u603b\u8d44\u4ea7(\u4e07\u5143)']).round(2)\r\n return df\r\n\r\n# \u6570\u636e\u53ef\u89c6\u5316\u5206\u6790\r\ndef visualize_data(df):\r\n # \u521b\u5efa\u56fe\u5f62\u5e03\u5c40\r\n plt.figure(figsize=(18, 12))\r\n \r\n # 1. \u6536\u5165\u4e0e\u5229\u6da6\u8d8b\u52bf\r\n plt.subplot(2, 3, 1)\r\n plt.plot(df['\u65e5\u671f'], df['\u8425\u4e1a\u6536\u5165(\u4e07\u5143)'], marker='o', label='\u8425\u4e1a\u6536\u5165')\r\n plt.plot(df['\u65e5\u671f'], df['\u51c0\u5229\u6da6(\u4e07\u5143)'], marker='s', label='\u51c0\u5229\u6da6')\r\n plt.title('\u6536\u5165\u4e0e\u5229\u6da6\u8d8b\u52bf\u5206\u6790')\r\n plt.xlabel('\u65e5\u671f')\r\n plt.ylabel('\u91d1\u989d(\u4e07\u5143)')\r\n plt.legend()\r\n plt.grid(True)\r\n \r\n # 2. \u6210\u672c\u7ed3\u6784\u5206\u6790\r\n plt.subplot(2, 3, 2)\r\n cost_data = df[['\u8425\u4e1a\u6210\u672c(\u4e07\u5143)', '\u9500\u552e\u8d39\u7528(\u4e07\u5143)', '\u7ba1\u7406\u8d39\u7528(\u4e07\u5143)', '\u8d22\u52a1\u8d39\u7528(\u4e07\u5143)']].sum()\r\n cost_data.plot(kind='pie', autopct='%1.1f%%', startangle=90)\r\n plt.title('\u6210\u672c\u7ed3\u6784\u5206\u6790')\r\n plt.ylabel('')\r\n \r\n # 3. \u76c8\u5229\u80fd\u529b\u6307\u6807\r\n plt.subplot(2, 3, 3)\r\n plt.plot(df['\u65e5\u671f'], df['\u6bdb\u5229\u7387(%)'], marker='o', label='\u6bdb\u5229\u7387')\r\n plt.plot(df['\u65e5\u671f'], df['\u51c0\u5229\u7387(%)'], marker='s', label='\u51c0\u5229\u7387')\r\n plt.title('\u76c8\u5229\u80fd\u529b\u6307\u6807')\r\n plt.xlabel('\u65e5\u671f')\r\n plt.ylabel('\u767e\u5206\u6bd4(%)')\r\n plt.legend()\r\n plt.grid(True)\r\n \r\n # 4. \u507f\u503a\u80fd\u529b\u6307\u6807\r\n plt.subplot(2, 3, 4)\r\n plt.plot(df['\u65e5\u671f'], df['\u6d41\u52a8\u6bd4\u7387'], marker='o', label='\u6d41\u52a8\u6bd4\u7387')\r\n plt.plot(df['\u65e5\u671f'], df['\u8d44\u4ea7\u8d1f\u503a\u7387(%)'], marker='s', label='\u8d44\u4ea7\u8d1f\u503a\u7387')\r\n plt.title('\u507f\u503a\u80fd\u529b\u6307\u6807')\r\n plt.xlabel('\u65e5\u671f')\r\n plt.ylabel('\u6bd4\u7387')\r\n plt.legend()\r\n plt.grid(True)\r\n \r\n # 5. \u5b63\u5ea6\u5bf9\u6bd4\u5206\u6790\r\n plt.subplot(2, 3, 5)\r\n quarterly = df.groupby('\u5b63\u5ea6').agg({\r\n '\u8425\u4e1a\u6536\u5165(\u4e07\u5143)': 'sum',\r\n '\u51c0\u5229\u6da6(\u4e07\u5143)': 'sum'\r\n })\r\n quarterly.plot(kind='bar')\r\n plt.title('\u5b63\u5ea6\u6536\u5165\u4e0e\u5229\u6da6\u5bf9\u6bd4')\r\n plt.xlabel('\u5b63\u5ea6')\r\n plt.ylabel('\u91d1\u989d(\u4e07\u5143)')\r\n plt.xticks(rotation=0)\r\n \r\n # 6. \u8d44\u4ea7\u8d1f\u503a\u7ed3\u6784\r\n plt.subplot(2, 3, 6)\r\n asset_liability = df[['\u603b\u8d44\u4ea7(\u4e07\u5143)', '\u603b\u8d1f\u503a(\u4e07\u5143)']].iloc[-1]\r\n asset_liability.plot(kind='bar', color=['#1f77b4', '#ff7f0e'])\r\n plt.title('\u671f\u672b\u8d44\u4ea7\u8d1f\u503a\u7ed3\u6784')\r\n plt.ylabel('\u91d1\u989d(\u4e07\u5143)')\r\n plt.xticks(rotation=0)\r\n \r\n plt.tight_layout()\r\n plt.savefig('\u8d22\u52a1\u5206\u6790\u62a5\u544a.png', dpi=300)\r\n plt.show()\r\n\r\n# \u751f\u6210\u5206\u6790\u62a5\u544a\r\ndef generate_report(df):\r\n print(\"\\n\" + \"=\"*50)\r\n print(\"\u4f01\u4e1a\u8d22\u52a1\u5206\u6790\u62a5\u544a\".center(50))\r\n print(\"=\"*50)\r\n \r\n # \u57fa\u672c\u8d22\u52a1\u6570\u636e\u6458\u8981\r\n print(\"\\n\u3010\u8d22\u52a1\u6570\u636e\u6458\u8981\u3011\")\r\n print(f\"\u5e74\u5ea6\u603b\u8425\u4e1a\u6536\u5165: {df['\u8425\u4e1a\u6536\u5165(\u4e07\u5143)'].sum():.2f}\u4e07\u5143\")\r\n print(f\"\u5e74\u5ea6\u603b\u51c0\u5229\u6da6: {df['\u51c0\u5229\u6da6(\u4e07\u5143)'].sum():.2f}\u4e07\u5143\")\r\n print(f\"\u5e73\u5747\u6bdb\u5229\u7387: {df['\u6bdb\u5229\u7387(%)'].mean():.2f}%\")\r\n print(f\"\u5e73\u5747\u51c0\u5229\u7387: {df['\u51c0\u5229\u7387(%)'].mean():.2f}%\")\r\n \r\n # \u8d22\u52a1\u5065\u5eb7\u8bc4\u4f30\r\n print(\"\\n\u3010\u8d22\u52a1\u5065\u5eb7\u8bc4\u4f30\u3011\")\r\n avg_current_ratio = df['\u6d41\u52a8\u6bd4\u7387'].mean()\r\n avg_debt_ratio = df['\u8d44\u4ea7\u8d1f\u503a\u7387(%)'].mean()\r\n \r\n print(f\"\u5e73\u5747\u6d41\u52a8\u6bd4\u7387: {avg_current_ratio:.2f}\", end=\" - \")\r\n if avg_current_ratio > 2:\r\n print(\"\u507f\u503a\u80fd\u529b\u5f3a\")\r\n elif avg_current_ratio > 1:\r\n print(\"\u507f\u503a\u80fd\u529b\u6b63\u5e38\")\r\n else:\r\n print(\"\u507f\u503a\u80fd\u529b\u8f83\u5f31\")\r\n \r\n print(f\"\u5e73\u5747\u8d44\u4ea7\u8d1f\u503a\u7387: {avg_debt_ratio:.2f}%\", end=\" - \")\r\n if avg_debt_ratio < 40:\r\n print(\"\u8d1f\u503a\u6c34\u5e73\u4f4e\")\r\n elif avg_debt_ratio < 60:\r\n print(\"\u8d1f\u503a\u6c34\u5e73\u9002\u4e2d\")\r\n else:\r\n print(\"\u8d1f\u503a\u6c34\u5e73\u8f83\u9ad8\")\r\n \r\n # \u8d8b\u52bf\u5206\u6790\r\n print(\"\\n\u3010\u8d8b\u52bf\u5206\u6790\u3011\")\r\n revenue_growth = (df['\u8425\u4e1a\u6536\u5165(\u4e07\u5143)'].iloc[-1] - df['\u8425\u4e1a\u6536\u5165(\u4e07\u5143)'].iloc[0]) \/ df['\u8425\u4e1a\u6536\u5165(\u4e07\u5143)'].iloc[0] * 100\r\n profit_growth = (df['\u51c0\u5229\u6da6(\u4e07\u5143)'].iloc[-1] - df['\u51c0\u5229\u6da6(\u4e07\u5143)'].iloc[0]) \/ df['\u51c0\u5229\u6da6(\u4e07\u5143)'].iloc[0] * 100\r\n \r\n print(f\"\u6536\u5165\u589e\u957f\u7387: {revenue_growth:.2f}%\")\r\n print(f\"\u5229\u6da6\u589e\u957f\u7387: {profit_growth:.2f}%\")\r\n \r\n # \u98ce\u9669\u63d0\u793a\r\n print(\"\\n\u3010\u98ce\u9669\u63d0\u793a\u3011\")\r\n if df['\u51c0\u5229\u6da6(\u4e07\u5143)'].min() < 0:\r\n print(\"\u26a0\ufe0f \u5b58\u5728\u6708\u5ea6\u4e8f\u635f\u60c5\u51b5\uff0c\u9700\u5173\u6ce8\u6210\u672c\u63a7\u5236\")\r\n if avg_current_ratio < 1.5:\r\n print(\"\u26a0\ufe0f \u6d41\u52a8\u6bd4\u7387\u504f\u4f4e\uff0c\u77ed\u671f\u507f\u503a\u538b\u529b\u8f83\u5927\")\r\n if avg_debt_ratio > 70:\r\n print(\"\u26a0\ufe0f \u8d44\u4ea7\u8d1f\u503a\u7387\u8fc7\u9ad8\uff0c\u8d22\u52a1\u98ce\u9669\u8f83\u5927\")\r\n \r\n print(\"\\n\" + \"=\"*50)\r\n print(\"\u62a5\u544a\u751f\u6210\u65f6\u95f4:\", datetime.now().strftime('%Y-%m-%d %H:%M:%S'))\r\n print(\"=\"*50)\r\n\r\n# \u4e3b\u7a0b\u5e8f\r\ndef main():\r\n print(\"\u6b63\u5728\u521b\u5efa\u8d22\u52a1\u6570\u636e\u8868\uff08\u5982\u679c\u4e0d\u5b58\u5728\uff09...\")\r\n create_financial_table()\r\n \r\n print(\"\u6b63\u5728\u4ece\u6570\u636e\u5e93\u8bfb\u53d6\u8d22\u52a1\u6570\u636e...\")\r\n df = load_financial_data_from_db()\r\n if df.empty:\r\n print(\"\u672a\u80fd\u4ece\u6570\u636e\u5e93\u52a0\u8f7d\u6570\u636e\uff0c\u7a0b\u5e8f\u7ec8\u6b62\")\r\n return\r\n \r\n print(\"\u6b63\u5728\u6e05\u6d17\u6570\u636e...\")\r\n df = clean_data(df)\r\n \r\n print(\"\u6b63\u5728\u8ba1\u7b97\u8d22\u52a1\u6307\u6807...\")\r\n df = calculate_financial_ratios(df)\r\n \r\n print(\"\u6b63\u5728\u751f\u6210\u53ef\u89c6\u5316\u56fe\u8868...\")\r\n visualize_data(df)\r\n \r\n print(\"\u6b63\u5728\u751f\u6210\u5206\u6790\u62a5\u544a...\")\r\n generate_report(df)\r\n \r\n # \u4fdd\u5b58\u5904\u7406\u540e\u7684\u6570\u636e\r\n df.to_excel('\u8d22\u52a1\u6570\u636e\u5206\u6790\u7ed3\u679c.xlsx', index=False)\r\n print(\"\\n\u5206\u6790\u5b8c\u6210 \u7ed3\u679c\u5df2\u4fdd\u5b58\u81f3'\u8d22\u52a1\u6570\u636e\u5206\u6790\u7ed3\u679c.xlsx'\u548c'\u8d22\u52a1\u5206\u6790\u62a5\u544a.png'\")\r\n\r\nif __name__ == \"__main__\":\r\n main()<\/code><\/pre>\n\n\n\n\r\n\r\nCREATE TABLE\u00a0`financial_data`\u00a0(\u00a0\u00a0`id`\u00a0int\u00a0NOT NULL AUTO_INCREMENT,\u00a0\u00a0`report_date`\u00a0date NOT NULL,\u00a0\u00a0`revenue`\u00a0decimal(15,2) DEFAULT NULL COMMENT\u00a0'\u8425\u4e1a\u6536\u5165(\u4e07\u5143)',\u00a0\u00a0`operating_cost`\u00a0decimal(15,2) DEFAULT NULL COMMENT\u00a0'\u8425\u4e1a\u6210\u672c(\u4e07\u5143)',\u00a0\u00a0`sales_expense`\u00a0decimal(15,2) DEFAULT NULL COMMENT\u00a0'\u9500\u552e\u8d39\u7528(\u4e07\u5143)',\u00a0\u00a0`admin_expense`\u00a0decimal(15,2) DEFAULT NULL COMMENT\u00a0'\u7ba1\u7406\u8d39\u7528(\u4e07\u5143)',\u00a0\u00a0`financial_expense`\u00a0decimal(15,2) DEFAULT NULL COMMENT\u00a0'\u8d22\u52a1\u8d39\u7528(\u4e07\u5143)',\u00a0\u00a0`current_assets`\u00a0decimal(15,2) DEFAULT NULL COMMENT\u00a0'\u6d41\u52a8\u8d44\u4ea7(\u4e07\u5143)',\u00a0\u00a0`current_liabilities`\u00a0decimal(15,2) DEFAULT NULL COMMENT\u00a0'\u6d41\u52a8\u8d1f\u503a(\u4e07\u5143)',\u00a0\u00a0`total_assets`\u00a0decimal(15,2) DEFAULT NULL COMMENT\u00a0'\u603b\u8d44\u4ea7(\u4e07\u5143)',\u00a0\u00a0`total_liabilities`\u00a0decimal(15,2) DEFAULT NULL COMMENT\u00a0'\u603b\u8d1f\u503a(\u4e07\u5143)',\u00a0\u00a0`net_profit`\u00a0decimal(15,2) DEFAULT NULL COMMENT\u00a0'\u51c0\u5229\u6da6(\u4e07\u5143)',\u00a0\u00a0`created_at`\u00a0timestamp NULL DEFAULT CURRENT_TIMESTAMP,\u00a0 PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=13\u00a0DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='\u4f01\u4e1a\u8d22\u52a1\u6570\u636e\u8868';\r\n\r\n\r\nINSERT INTO\u00a0`qycw`.`financial_data`\u00a0(`id`,\u00a0`report_date`,\u00a0`revenue`,\u00a0`operating_cost`,\u00a0`sales_expense`,\u00a0`admin_expense`,\u00a0`financial_expense`,\u00a0`current_assets`,\u00a0`current_liabilities`,\u00a0`total_assets`,\u00a0`total_liabilities`,\u00a0`net_profit`,\u00a0`created_at`) VALUES (1,\u00a0'2022-01-31',\u00a0524.84,\u00a0295.85,\u00a056.48,\u00a037.62,\u00a09.30,\u00a0776.59,\u00a0478.96,\u00a01653.49,\u00a0853.05,\u00a0125.59,\u00a0'2025-07-30 13:32:21');INSERT INTO\u00a0`qycw`.`financial_data`\u00a0(`id`,\u00a0`report_date`,\u00a0`revenue`,\u00a0`operating_cost`,\u00a0`sales_expense`,\u00a0`admin_expense`,\u00a0`financial_expense`,\u00a0`current_assets`,\u00a0`current_liabilities`,\u00a0`total_assets`,\u00a0`total_liabilities`,\u00a0`net_profit`,\u00a0`created_at`) VALUES (2,\u00a0'2022-02-28',\u00a0527.13,\u00a0286.10,\u00a045.34,\u00a031.21,\u00a04.26,\u00a0627.51,\u00a0371.89,\u00a01297.43,\u00a0931.42,\u00a0160.22,\u00a0'2025-07-30 13:32:21');INSERT INTO\u00a0`qycw`.`financial_data`\u00a0(`id`,\u00a0`report_date`,\u00a0`revenue`,\u00a0`operating_cost`,\u00a0`sales_expense`,\u00a0`admin_expense`,\u00a0`financial_expense`,\u00a0`current_assets`,\u00a0`current_liabilities`,\u00a0`total_assets`,\u00a0`total_liabilities`,\u00a0`net_profit`,\u00a0`created_at`) VALUES (3,\u00a0'2022-03-31',\u00a0454.60,\u00a0257.63,\u00a064.66,\u00a028.87,\u00a010.20,\u00a0657.53,\u00a0372.78,\u00a01522.18,\u00a0784.90,\u00a093.24,\u00a0'2025-07-30 13:32:21');INSERT INTO\u00a0`qycw`.`financial_data`\u00a0(`id`,\u00a0`report_date`,\u00a0`revenue`,\u00a0`operating_cost`,\u00a0`sales_expense`,\u00a0`admin_expense`,\u00a0`financial_expense`,\u00a0`current_assets`,\u00a0`current_liabilities`,\u00a0`total_assets`,\u00a0`total_liabilities`,\u00a0`net_profit`,\u00a0`created_at`) VALUES (4,\u00a0'2022-04-30',\u00a0518.78,\u00a0281.98,\u00a047.08,\u00a026.99,\u00a015.56,\u00a0798.65,\u00a0347.11,\u00a01664.51,\u00a0777.92,\u00a0147.17,\u00a0'2025-07-30 13:32:21');INSERT INTO\u00a0`qycw`.`financial_data`\u00a0(`id`,\u00a0`report_date`,\u00a0`revenue`,\u00a0`operating_cost`,\u00a0`sales_expense`,\u00a0`admin_expense`,\u00a0`financial_expense`,\u00a0`current_assets`,\u00a0`current_liabilities`,\u00a0`total_assets`,\u00a0`total_liabilities`,\u00a0`net_profit`,\u00a0`created_at`) VALUES (5,\u00a0'2022-05-31',\u00a0510.44,\u00a0241.21,\u00a036.72,\u00a030.98,\u00a012.22,\u00a0817.14,\u00a0394.22,\u00a01439.78,\u00a0752.15,\u00a0189.31,\u00a0'2025-07-30 13:32:21');INSERT INTO\u00a0`qycw`.`financial_data`\u00a0(`id`,\u00a0`report_date`,\u00a0`revenue`,\u00a0`operating_cost`,\u00a0`sales_expense`,\u00a0`admin_expense`,\u00a0`financial_expense`,\u00a0`current_assets`,\u00a0`current_liabilities`,\u00a0`total_assets`,\u00a0`total_liabilities`,\u00a0`net_profit`,\u00a0`created_at`) VALUES (6,\u00a0'2022-06-30',\u00a0464.01,\u00a0286.18,\u00a060.57,\u00a031.72,\u00a04.71,\u00a0832.41,\u00a0380.75,\u00a01364.62,\u00a0961.17,\u00a080.83,\u00a0'2025-07-30 13:32:21');INSERT INTO\u00a0`qycw`.`financial_data`\u00a0(`id`,\u00a0`report_date`,\u00a0`revenue`,\u00a0`operating_cost`,\u00a0`sales_expense`,\u00a0`admin_expense`,\u00a0`financial_expense`,\u00a0`current_assets`,\u00a0`current_liabilities`,\u00a0`total_assets`,\u00a0`total_liabilities`,\u00a0`net_profit`,\u00a0`created_at`) VALUES (7,\u00a0'2022-07-31',\u00a0551.55,\u00a0327.94,\u00a041.61,\u00a028.45,\u00a010.99,\u00a0897.55,\u00a0376.04,\u00a01462.87,\u00a0789.37,\u00a0142.56,\u00a0'2025-07-30 13:32:21');INSERT INTO\u00a0`qycw`.`financial_data`\u00a0(`id`,\u00a0`report_date`,\u00a0`revenue`,\u00a0`operating_cost`,\u00a0`sales_expense`,\u00a0`admin_expense`,\u00a0`financial_expense`,\u00a0`current_assets`,\u00a0`current_liabilities`,\u00a0`total_assets`,\u00a0`total_liabilities`,\u00a0`net_profit`,\u00a0`created_at`) VALUES (8,\u00a0'2022-08-31',\u00a0440.19,\u00a0324.38,\u00a063.56,\u00a029.64,\u00a013.01,\u00a0836.16,\u00a0367.74,\u00a01572.28,\u00a01053.80,\u00a09.60,\u00a0'2025-07-30 13:32:21');INSERT INTO\u00a0`qycw`.`financial_data`\u00a0(`id`,\u00a0`report_date`,\u00a0`revenue`,\u00a0`operating_cost`,\u00a0`sales_expense`,\u00a0`admin_expense`,\u00a0`financial_expense`,\u00a0`current_assets`,\u00a0`current_liabilities`,\u00a0`total_assets`,\u00a0`total_liabilities`,\u00a0`net_profit`,\u00a0`created_at`) VALUES (9,\u00a0'2022-09-30',\u00a0498.21,\u00a0346.94,\u00a023.80,\u00a034.11,\u00a010.26,\u00a0770.10,\u00a0404.59,\u00a01102.49,\u00a0878.03,\u00a083.10,\u00a0'2025-07-30 13:32:21');INSERT INTO\u00a0`qycw`.`financial_data`\u00a0(`id`,\u00a0`report_date`,\u00a0`revenue`,\u00a0`operating_cost`,\u00a0`sales_expense`,\u00a0`admin_expense`,\u00a0`financial_expense`,\u00a0`current_assets`,\u00a0`current_liabilities`,\u00a0`total_assets`,\u00a0`total_liabilities`,\u00a0`net_profit`,\u00a0`created_at`) VALUES (10,\u00a0'2022-10-31',\u00a0517.86,\u00a0344.34,\u00a044.82,\u00a025.96,\u00a08.49,\u00a0891.54,\u00a0416.44,\u00a01394.05,\u00a0951.33,\u00a094.25,\u00a0'2025-07-30 13:32:21');INSERT INTO\u00a0`qycw`.`financial_data`\u00a0(`id`,\u00a0`report_date`,\u00a0`revenue`,\u00a0`operating_cost`,\u00a0`sales_expense`,\u00a0`admin_expense`,\u00a0`financial_expense`,\u00a0`current_assets`,\u00a0`current_liabilities`,\u00a0`total_assets`,\u00a0`total_liabilities`,\u00a0`net_profit`,\u00a0`created_at`) VALUES (11,\u00a0'2022-11-30',\u00a0504.85,\u00a0329.06,\u00a042.98,\u00a028.36,\u00a08.82,\u00a0653.65,\u00a0414.81,\u00a01552.21,\u00a0900.51,\u00a095.63,\u00a0'2025-07-30 13:32:21');INSERT INTO\u00a0`qycw`.`financial_data`\u00a0(`id`,\u00a0`report_date`,\u00a0`revenue`,\u00a0`operating_cost`,\u00a0`sales_expense`,\u00a0`admin_expense`,\u00a0`financial_expense`,\u00a0`current_assets`,\u00a0`current_liabilities`,\u00a0`total_assets`,\u00a0`total_liabilities`,\u00a0`net_profit`,\u00a0`created_at`) VALUES (12,\u00a0'2022-12-31',\u00a0488.27,\u00a0257.54,\u00a045.79,\u00a028.29,\u00a07.59,\u00a0783.87,\u00a0420.20,\u00a01877.24,\u00a0917.46,\u00a0149.06,\u00a0'2025-07-30 13:32:21');<\/code><\/pre>\n\n\n\n<\/p>\n\n\n\n
<\/p>\n","protected":false},"excerpt":{"rendered":"
\u5728\u5f53\u4eca\u6570\u636e\u9a71\u52a8\u7684\u5546\u4e1a\u73af\u5883\u4e2d\uff0c\u6570\u636e\u5206\u6790\u662f\u4f01\u4e1a\u51b3\u7b56\u7684\u57fa\u77f3\u3002\u4f46\u5982\u4f55\u9ad8\u6548\u5730\u4ece\u6d77\u91cf\u6570\u636e\u4e2d\u63d0\u53d6\u6709\u4ef7\u503c\u7684\u6d1e\u5bdf\uff1f \u5ba2… \u7ee7\u7eed\u9605\u8bfb \u3010Python\u5b9e\u8df5\u6848\u4f8b\u3011\u7535\u5546\u5e73\u53f0\u6570\u636e\u5206\u6790\u548c\u6316\u6398 -\u5168\u6d41\u7a0b\u6570\u636e\u6e05\u6d17\u4e0e\u805a\u5408\u9500\u552e\u62a5\u8868\u8f93\u51fa<\/span><\/i><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[1],"tags":[28],"views":1025,"_links":{"self":[{"href":"http:\/\/viplao.com\/index.php\/wp-json\/wp\/v2\/posts\/3875"}],"collection":[{"href":"http:\/\/viplao.com\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/viplao.com\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/viplao.com\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/viplao.com\/index.php\/wp-json\/wp\/v2\/comments?post=3875"}],"version-history":[{"count":3,"href":"http:\/\/viplao.com\/index.php\/wp-json\/wp\/v2\/posts\/3875\/revisions"}],"predecessor-version":[{"id":3882,"href":"http:\/\/viplao.com\/index.php\/wp-json\/wp\/v2\/posts\/3875\/revisions\/3882"}],"wp:attachment":[{"href":"http:\/\/viplao.com\/index.php\/wp-json\/wp\/v2\/media?parent=3875"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/viplao.com\/index.php\/wp-json\/wp\/v2\/categories?post=3875"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/viplao.com\/index.php\/wp-json\/wp\/v2\/tags?post=3875"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}