📊 Veri Analizi Kodları

# Agile Data Science Sprint Planı (2 haftalık iterasyon)

## Sprint 1: Data Discovery & Quick Wins
Week 1:
- [ ] Stakeholder interviews ve problem definition
- [ ] Data source mapping ve access setup
- [ ] Initial EDA ve data quality assessment
- [ ] Quick baseline model (simple heuristics)

Week 2:
- [ ] Data cleaning pipeline oluşturma
- [ ] Feature engineering brainstorming
- [ ] İlk ML model denemeleri
- [ ] Preliminary results stakeholder'lara sunma

## Sprint 2: Model Development
Week 3:
- [ ] Advanced feature engineering
- [ ] Multiple algorithm experimentation
- [ ] Hyperparameter tuning başlangıcı
- [ ] Cross-validation setup

Week 4:
- [ ] Model performance optimization
- [ ] Business metrics ile model alignment
- [ ] Model interpretability analizi
- [ ] Production readiness assessment

## Sprint 3: Deployment & Optimization
Week 5:
- [ ] Model deployment pipeline
- [ ] A/B testing framework setup
- [ ] Monitoring dashboard oluşturma
- [ ] Performance tracking

Week 6:
- [ ] Production deployment
- [ ] Live monitoring başlatma
- [ ] Feedback collection
- [ ] Next iteration planning

SELECT TOP (100) PERCENT 
    derivedtbl_1.adet, 
    derivedtbl_1.event, 
    dbo.table_gunler.tarih
FROM (
    SELECT TOP (100) PERCENT 
        dbo.Table_antep_trabzon_sakarya_events.event,
        dbo.[Dataset 1_SMS_RAW].date,
        COUNT(dbo.[Dataset 1_SMS_RAW].NUMBER_OF_REFUGEE_SMS) AS adet
    FROM dbo.[Dataset 1_SMS_RAW] 
    INNER JOIN dbo.Base_Station_Location 
        ON dbo.[Dataset 1_SMS_RAW].OUTGOING_SITE_ID = dbo.Base_Station_Location.BTS_ID 
    FULL OUTER JOIN dbo.Table_antep_trabzon_sakarya_events 
        ON dbo.[Dataset 1_SMS_RAW].date = dbo.Table_antep_trabzon_sakarya_events.date 
        AND dbo.Base_Station_Location.MX_SAHAIL = dbo.Table_antep_trabzon_sakarya_events.city
    GROUP BY 
        dbo.Base_Station_Location.MX_SAHAIL,
        dbo.Table_antep_trabzon_sakarya_events.event,
        dbo.[Dataset 1_SMS_RAW].date
    HAVING (dbo.Base_Station_Location.MX_SAHAIL = 'GAZIANTEP')
) AS derivedtbl_1 
FULL OUTER JOIN dbo.table_gunler 
    ON derivedtbl_1.date = dbo.table_gunler.tarih
ORDER BY dbo.table_gunler.tarih

# Veriyi %70 eğitim, %30 test olarak bölelim
xtrain_70_sms_full = df_sms_full['adet'].fillna(method="pad").head(int(len(df_sms_full)*0.7))
xtest_30_sms_full = df_sms_full['adet'].fillna(method="pad").tail(int(len(df_sms_full)*0.3))

ytrain_70_sms_full = df_sms_full['event'].fillna(value=0).head(int(len(df_sms_full)*0.7))
ytest_30_sms_full = df_sms_full['event'].fillna(value=0).tail(int(len(df_sms_full)*0.3))

# Veri boyutlarını kontrol edelim
print(f"📊 Eğitim verisi boyutu: {len(xtrain_70_sms_full)}")
print(f"🧪 Test verisi boyutu: {len(xtest_30_sms_full)}")
print(f"✅ Toplam veri: {len(df_sms_full)}")

# Büyük CSV dosyalarını chunk'lara bölerek işleme
import pandas as pd
from tqdm import tqdm

def process_large_csv(file_path, chunk_size=10000):
    """
    Büyük CSV dosyalarını memory-efficient şekilde işler
    """
    processed_data = []
    
    # Chunk'lara bölerek oku
    for chunk in tqdm(pd.read_csv(file_path, chunksize=chunk_size)):
        # Veri temizleme
        chunk_cleaned = chunk.dropna()
        chunk_cleaned['processed_date'] = pd.to_datetime(chunk_cleaned['date'])
        
        # Feature engineering
        chunk_cleaned['hour'] = chunk_cleaned['processed_date'].dt.hour
        chunk_cleaned['day_of_week'] = chunk_cleaned['processed_date'].dt.dayofweek
        
        processed_data.append(chunk_cleaned)
    
    # Tüm chunk'ları birleştir
    final_data = pd.concat(processed_data, ignore_index=True)
    return final_data

# Kullanım
df_processed = process_large_csv('large_dataset.csv', chunk_size=50000)

import plotly.graph_objects as go
import plotly.express as px
from plotly.subplots import make_subplots

def create_sales_dashboard(df):
    """
    Satış verisi için kapsamlı dashboard oluşturur
    """
    # Ana figür layout'u
    fig = make_subplots(
        rows=2, cols=2,
        subplot_titles=('Günlük Satışlar', 'Kategori Dağılımı', 
                       'Müşteri Segmentleri', 'Trend Analizi'),
        specs=[[{"type": "scatter"}, {"type": "pie"}],
               [{"type": "bar"}, {"type": "scatter"}]]
    )
    
    # Günlük satış trendi
    fig.add_trace(
        go.Scatter(x=df['date'], y=df['sales'],
                  mode='lines+markers',
                  name='Günlük Satış',
                  line=dict(color='#1f77b4', width=3)),
        row=1, col=1
    )
    
    # Kategori pasta grafiği
    category_counts = df['category'].value_counts()
    fig.add_trace(
        go.Pie(labels=category_counts.index, 
               values=category_counts.values,
               name="Kategoriler"),
        row=1, col=2
    )
    
    # Layout güncellemeleri
    fig.update_layout(
        title_text="📊 E-Ticaret Satış Dashboard",
        title_x=0.5,
        showlegend=True,
        height=800
    )
    
    return fig

# Dashboard'ı göster
dashboard = create_sales_dashboard(sales_df)
dashboard.show()

import joblib
import pandas as pd
from sklearn.model_selection import train_test_split
from sklearn.ensemble import RandomForestClassifier
from sklearn.metrics import classification_report
import mlflow
import mlflow.sklearn

class MLPipeline:
    def __init__(self, model_name="customer_churn_model"):
        self.model_name = model_name
        self.model = None
        
    def prepare_data(self, df):
        """Veri ön işleme"""
        # Feature engineering
        df['tenure_years'] = df['tenure'] / 12
        df['charges_per_month'] = df['total_charges'] / df['tenure']
        
        # Kategorik değişkenleri encode et
        df_encoded = pd.get_dummies(df, drop_first=True)
        
        return df_encoded
    
    def train_model(self, X_train, y_train):
        """Model eğitimi"""
        with mlflow.start_run():
            # Model oluştur
            self.model = RandomForestClassifier(
                n_estimators=100,
                max_depth=10,
                random_state=42
            )
            
            # Eğit
            self.model.fit(X_train, y_train)
            
            # MLflow'a kaydet
            mlflow.sklearn.log_model(self.model, self.model_name)
            mlflow.log_params({
                "n_estimators": 100,
                "max_depth": 10
            })
            
        return self.model
    
    def predict(self, X_new):
        """Tahmin yapma"""
        if self.model is None:
            raise ValueError("Model henüz eğitilmedi!")
            
        predictions = self.model.predict(X_new)
        probabilities = self.model.predict_proba(X_new)
        
        return predictions, probabilities
    
    def save_model(self, filepath):
        """Modeli kaydet"""
        joblib.dump(self.model, filepath)
        print(f"Model {filepath} konumuna kaydedildi")

# Kullanım örneği
pipeline = MLPipeline()
X_train, X_test, y_train, y_test = train_test_split(X, y, test_size=0.2)
model = pipeline.train_model(X_train, y_train)
pipeline.save_model("churn_model.pkl")

import pandas as pd
from kafka import KafkaConsumer, KafkaProducer
import json
from datetime import datetime
import redis

class RealTimeAnalytics:
    def __init__(self):
        self.consumer = KafkaConsumer(
            'user_events',
            bootstrap_servers=['localhost:9092'],
            value_deserializer=lambda x: json.loads(x.decode('utf-8'))
        )
        self.redis_client = redis.Redis(host='localhost', port=6379, db=0)
        
    def process_events(self):
        """Gerçek zamanlı event işleme"""
        for message in self.consumer:
            event_data = message.value
            
            # Event tipine göre işlem
            if event_data['event_type'] == 'page_view':
                self.update_page_views(event_data)
            elif event_data['event_type'] == 'purchase':
                self.update_sales_metrics(event_data)
            elif event_data['event_type'] == 'user_signup':
                self.update_user_metrics(event_data)
                
    def update_page_views(self, event):
        """Sayfa görüntüleme sayaçlarını güncelle"""
        page = event['page']
        timestamp = datetime.now().strftime('%Y-%m-%d:%H')
        
        # Redis'te sayaçları güncelle
        self.redis_client.incr(f"page_views:{page}:{timestamp}")
        self.redis_client.incr(f"total_page_views:{page}")
        
    def get_realtime_stats(self):
        """Anlık istatistikleri getir"""
        current_hour = datetime.now().strftime('%Y-%m-%d:%H')
        
        stats = {
            'current_hour_views': self.redis_client.get(f"page_views:home:{current_hour}") or 0,
            'total_views': self.redis_client.get("total_page_views:home") or 0,
            'active_users': self.redis_client.scard("active_users")
        }
        
        return stats

# Kullanım
analytics = RealTimeAnalytics()
analytics.process_events()  # Sürekli çalışır