code

file_path = '/content/drive/MyDrive/datamining/Mag6PlusEarthquakes_1900-2013.xlsx'
df = pd.read_excel(file_path)

# カラム名を確認（例：'Magnitude' がマグニチュード列の場合）
print(df.columns)

import matplotlib.pyplot as plt
import seaborn as sns
import pandas as pd

# 「eastern Kazakhstan」を除外
df_cleaned = df[df['place'] != 'eastern Kazakhstan']

# 地震発生件数Top10のplace
top_places = df_cleaned['place'].value_counts().head(10).index
df_top = df_cleaned[df_cleaned['place'].isin(top_places)]

# NST（平均）と RMS（Q3）を集計
place_summary = df_top.groupby('place').agg({
    'nst': 'mean',
    'rms': lambda x: x.quantile(0.75)
}).rename(columns={'rms': 'rms_q3'})

# x軸の順序を、RMSのQ3の昇順で並び替え
place_summary = place_summary.sort_values('rms_q3')

# グラフ描画
fig, ax1 = plt.subplots(figsize=(12, 6))

# 棒グラフ（NST）
color1 = 'tab:blue'
ax1.bar(place_summary.index, place_summary['nst'], color=color1)
ax1.set_ylabel('Average NST', color=color1, fontsize=14, fontweight='bold')
ax1.tick_params(axis='y', labelcolor=color1)
ax1.set_xticklabels(place_summary.index, rotation=45, ha='right', fontsize=12, fontweight='bold')

# 折れ線グラフ（RMS Q3）
ax2 = ax1.twinx()
color2 = 'tab:red'
ax2.plot(place_summary.index, place_summary['rms_q3'], color=color2, marker='o', linewidth=2)
ax2.set_ylabel('RMS (Q3)', color=color2, fontsize=14, fontweight='bold')
ax2.tick_params(axis='y', labelcolor=color2)

# タイトルも太く・大きく
plt.title('Average NST and RMS (Q3) by Place (Top 10 Places)', fontsize=20, fontweight='bold')
plt.tight_layout()
plt.show()