import pandas as pd
import numpy as np

# 從CSV文件導(dǎo)入數(shù)據(jù)
df = pd.read_csv('data.csv')

# 從Excel文件導(dǎo)入數(shù)據(jù)
df_excel = pd.read_excel('data.xlsx', sheet_name='Sheet1')

# 從SQL數(shù)據(jù)庫導(dǎo)入數(shù)據(jù)
from sqlalchemy import create_engine
engine = create_engine('sqlite:///database.db')
df_sql = pd.read_sql('SELECT * FROM table_name', engine)

# 查看數(shù)據(jù)基本信息
print(f"數(shù)據(jù)形狀: {df.shape}")
print(f"列名: {df.columns.tolist()}")
print(df.info())

# 處理缺失值
print("缺失值統(tǒng)計(jì):")
print(df.isnull().sum())

# 刪除缺失值
df_cleaned = df.dropna()

# 填充缺失值
df_filled = df.fillna(method='ffill')  # 前向填充
# 或
df_filled = df.fillna(df.mean())  # 使用均值填充

# 處理重復(fù)值
df_no_duplicates = df.drop_duplicates()

# 數(shù)據(jù)類型轉(zhuǎn)換
df['date_column'] = pd.to_datetime(df['date_column'])
df['category_column'] = df['category_column'].astype('category')

# 處理異常值
from scipy import stats
df_no_outliers = df[(np.abs(stats.zscore(df['numeric_column'])) < 3)]

# 數(shù)據(jù)標(biāo)準(zhǔn)化
from sklearn.preprocessing import StandardScaler
scaler = StandardScaler()
df_scaled = scaler.fit_transform(df[['numeric_column1', 'numeric_column2']])

# 使用describe()獲取描述性統(tǒng)計(jì)
print(df.describe())

# 計(jì)算單個(gè)統(tǒng)計(jì)量
mean_value = df['column_name'].mean()
median_value = df['column_name'].median()
std_value = df['column_name'].std()
min_value = df['column_name'].min()
max_value = df['column_name'].max()
quantile_25 = df['column_name'].quantile(0.25)

# 多列統(tǒng)計(jì)
stats_summary = df.agg({
    'column1': ['mean', 'min', 'max'],
    'column2': ['mean', 'std']
})

# 使用NumPy進(jìn)行統(tǒng)計(jì)計(jì)算
array_data = np.array(df['column_name'])
np_mean = np.mean(array_data)
np_std = np.std(array_data)

# 基本分組操作
grouped = df.groupby('category_column')

# 對(duì)分組后的數(shù)據(jù)應(yīng)用聚合函數(shù)
grouped_mean = grouped.mean()
grouped_sum = grouped.sum()

# 多列分組
multi_grouped = df.groupby(['category1', 'category2'])
multi_grouped_stats = multi_grouped.agg({'numeric_column': ['mean', 'sum', 'count']})

# 對(duì)不同列應(yīng)用不同聚合函數(shù)
custom_agg = df.groupby('category_column').agg({
    'numeric_column1': 'mean',
    'numeric_column2': ['min', 'max'],
    'numeric_column3': 'sum'
})

# 使用自定義聚合函數(shù)
def data_range(series):
    return series.max() - series.min()

custom_range = df.groupby('category_column')['numeric_column'].agg(data_range)

# 創(chuàng)建基本透 視表
pivot_table = pd.pivot_table(df, 
                            values='value_column', 
                            index='row_category', 
                            columns='column_category', 
                            aggfunc='mean')

# 多重聚合函數(shù)
multi_func_pivot = pd.pivot_table(df,
                                 values=['value1', 'value2'],
                                 index='row_category',
                                 columns='column_category',
                                 aggfunc={'value1': 'mean', 'value2': 'sum'})

# 添加邊際總計(jì)
pivot_with_margins = pd.pivot_table(df,
                                   values='value_column',
                                   index='row_category',
                                   columns='column_category',
                                   aggfunc='sum',
                                   margins=True,
                                   margins_name='總計(jì)')

# 處理缺失值
pivot_filled = pd.pivot_table(df,
                             values='value_column',
                             index='row_category',
                             columns='column_category',
                             aggfunc='mean',
                             fill_value=0)

# 計(jì)算相關(guān)系數(shù)
correlation_matrix = df.corr()
specific_correlation = df['column1'].corr(df['column2'])

# 計(jì)算協(xié)方差
covariance_matrix = df.cov()
specific_covariance = df['column1'].cov(df['column2'])

# 可視化相關(guān)矩陣
import matplotlib.pyplot as plt
import seaborn as sns

plt.figure(figsize=(10, 8))
sns.heatmap(correlation_matrix, 
            annot=True, 
            cmap='coolwarm', 
            center=0,
            square=True)
plt.title('變量相關(guān)性熱圖')
plt.tight_layout()
plt.show()

from scipy import stats

# T檢驗(yàn)（單樣本）
t_stat, p_value = stats.ttest_1samp(df['column'], popmean=0)
print(f"T統(tǒng)計(jì)量: {t_stat}, P值: {p_value}")

# T檢驗(yàn)（雙樣本獨(dú)立）
t_stat, p_value = stats.ttest_ind(df['group1'], df['group2'])
print(f"T統(tǒng)計(jì)量: {t_stat}, P值: {p_value}")

# 卡方檢驗(yàn)
from scipy.stats import chi2_contingency
contingency_table = pd.crosstab(df['category1'], df['category2'])
chi2, p, dof, expected = chi2_contingency(contingency_table)
print(f"卡方值: {chi2}, P值: {p}")

# ANOVA方差分析
f_stat, p_value = stats.f_oneway(df['group1'], df['group2'], df['group3'])
print(f"F統(tǒng)計(jì)量: {f_stat}, P值: {p_value}")

import statsmodels.api as sm
from sklearn.linear_model import LinearRegression
from sklearn.metrics import mean_squared_error, r2_score

# 簡(jiǎn)單線性回歸
X = df[['independent_var']]
y = df['dependent_var']

# 添加常數(shù)項(xiàng)
X = sm.add_constant(X)

# 創(chuàng)建模型并擬合
model = sm.OLS(y, X).fit()

# 查看模型摘要
print(model.summary())

# 使用scikit-learn進(jìn)行回歸
lr_model = LinearRegression()
lr_model.fit(X, y)

# 預(yù)測(cè)與評(píng)估
y_pred = lr_model.predict(X)
mse = mean_squared_error(y, y_pred)
r2 = r2_score(y, y_pred)

print(f"均方誤差: {mse}")
print(f"R2分?jǐn)?shù): {r2}")

# 多元回歸
X_multi = df[['var1', 'var2', 'var3']]
X_multi = sm.add_constant(X_multi)
multi_model = sm.OLS(y, X_multi).fit()
print(multi_model.summary())

# 設(shè)置可視化風(fēng)格
plt.style.use('seaborn-v0_8')
plt.rcParams['font.sans-serif'] = ['SimHei']  # 支持中文顯示
plt.rcParams['axes.unicode_minus'] = False  # 解決負(fù)號(hào)顯示問題

# 直方圖
plt.figure(figsize=(10, 6))
plt.hist(df['numeric_column'], bins=30, alpha=0.7, edgecolor='black')
plt.title('數(shù)據(jù)分布直方圖')
plt.xlabel('數(shù)值')
plt.ylabel('頻數(shù)')
plt.grid(True, alpha=0.3)
plt.tight_layout()
plt.show()

# 箱線圖
plt.figure(figsize=(10, 6))
df.boxplot(column='numeric_column', by='category_column')
plt.title('按類別分組的數(shù)據(jù)分布')
plt.suptitle('')  # 移除自動(dòng)標(biāo)題
plt.tight_layout()
plt.show()

# 密度圖
plt.figure(figsize=(10, 6))
df['numeric_column'].plot(kind='density')
plt.title('數(shù)據(jù)密度分布')
plt.xlabel('數(shù)值')
plt.grid(True, alpha=0.3)
plt.tight_layout()
plt.show()

# 散點(diǎn)圖
plt.figure(figsize=(10, 6))
plt.scatter(df['var1'], df['var2'], 
           alpha=0.6, 
           c=df['var3'],  # 顏色映射第三維
           cmap='viridis',
           s=50)  # 點(diǎn)大小
plt.colorbar(label='第三變量')
plt.title('變量間關(guān)系散點(diǎn)圖')
plt.xlabel('變量1')
plt.ylabel('變量2')
plt.grid(True, alpha=0.3)
plt.tight_layout()
plt.show()

# 配對(duì)圖
import seaborn as sns
sns.pairplot(df[['var1', 'var2', 'var3', 'category_column']], 
             hue='category_column',  # 按類別著色
             diag_kind='hist',
             palette='viridis')
plt.suptitle('變量配對(duì)關(guān)系圖', y=1.02)
plt.tight_layout()
plt.show()

# 熱力圖
plt.figure(figsize=(10, 8))
correlation_matrix = df.corr()
sns.heatmap(correlation_matrix,
            annot=True,
            cmap='coolwarm',
            center=0,
            square=True,
            fmt='.2f')
plt.title('變量相關(guān)性熱力圖')
plt.tight_layout()
plt.show()

# 時(shí)間序列折線圖
df_time = df.set_index('date_column')
plt.figure(figsize=(12, 6))
df_time['value_column'].plot()
plt.title('時(shí)間序列趨勢(shì)')
plt.xlabel('日期')
plt.ylabel('數(shù)值')
plt.grid(True, alpha=0.3)
plt.tight_layout()
plt.show()

# 移動(dòng)平均平滑
rolling_mean = df_time['value_column'].rolling(window=7).mean()
rolling_std = df_time['value_column'].rolling(window=7).std()

plt.figure(figsize=(12, 6))
plt.plot(df_time.index, df_time['value_column'], label='原始數(shù)據(jù)', alpha=0.5)
plt.plot(df_time.index, rolling_mean, label='7天移動(dòng)平均', color='red')
plt.fill_between(df_time.index, 
                rolling_mean - rolling_std,
                rolling_mean + rolling_std,
                color='red', alpha=0.2)
plt.title('時(shí)間序列與移動(dòng)平均')
plt.xlabel('日期')
plt.ylabel('數(shù)值')
plt.legend()
plt.grid(True, alpha=0.3)
plt.tight_layout()
plt.show()

# 使用高效的數(shù)據(jù)類型
df_memory_optimized = df.astype({
    'category_column': 'category',
    'integer_column': 'int32',
    'float_column': 'float32'
})

# 使用分塊處理大型文件
chunk_size = 10000
chunk_reader = pd.read_csv('large_file.csv', chunksize=chunk_size)

results = []
for chunk in chunk_reader:
    result = chunk.groupby('category_column')['value_column'].sum()
    results.append(result)

final_result = pd.concat(results).groupby(level=0).sum()

# 使用Dask處理超大規(guī)模數(shù)據(jù)
import dask.dataframe as dd
dask_df = dd.read_csv('very_large_file.csv')
dask_result = dask_df.groupby('category_column')['value_column'].mean().compute()

# 使用并行處理
from concurrent.futures import ProcessPoolExecutor
import numpy as np

def process_chunk(chunk):
    return chunk.groupby('category')['value'].sum()

def parallel_groupby(df, group_column, value_column, n_jobs=4):
    chunks = np.array_split(df, n_jobs)
    with ProcessPoolExecutor(max_workers=n_jobs) as executor:
        results = list(executor.map(process_chunk, chunks))
    return pd.concat(results).groupby(level=0).sum()

from pandas_profiling import ProfileReport

# 生成自動(dòng)化數(shù)據(jù)報(bào)告
profile = ProfileReport(df, title='數(shù)據(jù)探索性分析報(bào)告')
profile.to_file('data_analysis_report.html')

# 使用Jupyter Notebook進(jìn)行交互式分析
# 在Jupyter中可以使用以下魔法命令
# %matplotlib inline
# %timeit # 測(cè)試代碼運(yùn)行時(shí)間

# 創(chuàng)建分析函數(shù)模板
def analyze_dataset(df, target_var=None):
    """
    自動(dòng)化數(shù)據(jù)集分析函數(shù)
    """
    results = {}
    
    # 基本信息
    results['shape'] = df.shape
    results['dtypes'] = df.dtypes.to_dict()
    results['missing_values'] = df.isnull().sum().to_dict()
    
    # 描述性統(tǒng)計(jì)
    results['description'] = df.describe().to_dict()
    
    # 相關(guān)性分析
    if target_var and target_var in df.columns:
        correlations = df.corr()[target_var].sort_values(ascending=False)
        results['correlations'] = correlations.to_dict()
    
    return results

# 使用模板生成報(bào)告
analysis_results = analyze_dataset(df, target_var='target_column')