import pandas as pd
import numpy as np
 
users = pd.DataFrame({
    "user_id":[1,2,3,4,5],
    "city":["MSK","SPB","MSK","EKB","SPB"],
    "segment":["A","B","A","B","A"]
})
orders = pd.DataFrame({
    "order_id":[101,102,103,104,105,106],
    "user_id":[1,2,2,3,5,5],
    "price":[100,50,80,0,120,200],
    "created_at": ["2025-09-01","2025-09-01","2025-09-02","2025-09-03","2025-09-04","2025-09-04"]
})
events = pd.DataFrame({
    "user_id":[1,1,2,3,3,3,5],
    "event":["view","buy","view","view","click","buy","view"],
    "ts":["2025-09-01 09:00","2025-09-01 10:15","2025-09-01 13:00","2025-09-02 08:05",
          "2025-09-02 08:06","2025-09-03 11:20","2025-09-03 12:00"]
})
print(users.head(), "\n"); print(orders.head(), "\n"); print(events.head(), "\n")

print("=== 1) IO примеры (комментарии над строками) ===")
tmp_csv = "tmp_users.csv"
users.to_csv(tmp_csv, index=False)            # сохранить CSV без индекса
users_back = pd.read_csv(tmp_csv)             # прочитать назад
print("Сохранено/прочитано CSV:", users_back.shape)

print("\n=== 2) Обзор ===")
print(users.head(3))
print(users.tail(2))
print("shape:", users.shape)
print("columns:", users.columns.tolist())
print(orders.info())
print(orders.describe())
print(orders[["order_id", "user_id"]].corr())

print("\n=== 3) Выборки/фильтры ===")
print(users.loc[users["city"] == "MSK", ["user_id", "segment"]])
print(users.iloc[:3, :2])
print(users.query("city in ['MSK','SPB'] and segment == 'A'"))
print(users.assign(city_upper=users["city"].str.upper()).head())
print(users.rename(columns={"segment": "seg"}).head(2))
print(users.astype({"user_id": "int64"}).dtypes)

print("\n=== 4) Частые метрики ===")
print("value_counts (частоты по городам):\n", users["city"].value_counts())
print("nunique (уникальных пользователей в orders):", orders["user_id"].nunique())
print("describe по price:\n", orders["price"].describe())
print("quantile 0.95 (P95) по price:", orders["price"].quantile(0.95))
print("clip price [0, 150]:\n", orders["price"].clip(0, 150).tolist())

print("\n=== 5) Строковые операции ===")
s = pd.Series(["  msk  ", "SPB", "ekb-1"])
print(s.str.strip())
print(s.str.lower())
print(s.str.contains("sk", case=False))
print(s.str.replace(r"-\\d+", "", regex=True))
print(s.str.split("-").str[0])
print(s.str.extract(r"(msk|spb|ekb)", flags=re.IGNORECASE, expand=False))

print("\n=== 6) Даты/время ===")
orders["created_at"] = pd.to_datetime(orders["created_at"])
ts = (orders
      .set_index("created_at")
      .resample("D")["price"]
      .sum()
      .asfreq("D", fill_value=0))
print("Дневные суммы:\n", ts)
 
print("rolling(2).mean — скользящее среднее по 2 дням:\n", ts.rolling(2, min_periods=1).mean())
print("shift(1) — лаг на 1 день:\n", ts.shift(1))

print("\n=== 7) groupby/agg/transform ===")
agg_city = (users
    .merge(orders, on="user_id", how="left")
    .groupby("city")
    .agg(
        users=("user_id","nunique"),
        orders=("order_id","count"),
        revenue=("price","sum"),
        avg_price=("price","mean")
    )
    .reset_index()
)
print(agg_city)
 
tmp = users.merge(orders, on="user_id", how="left")
tmp["city_avg_price"] = tmp.groupby("city")["price"].transform("mean")
print(tmp.head())
 
flt = (tmp.groupby("city")["order_id"].transform("count") >= 2)
print(tmp[flt].head())

print("\n=== 8) pivot_table / crosstab ===")
pt = pd.pivot_table(
    data=orders.merge(users, on="user_id", how="left"),
    values="price",
    index="city",
    columns="segment",
    aggfunc=["count", "sum", "mean"],
    fill_value=0,
    margins=True,
    margins_name="Total"
)
print("pivot_table (multi-agg):\n", pt)
 
ct = pd.crosstab(users["city"], users["segment"], margins=True, margins_name="Total")
print("crosstab (частоты):\n", ct)
 
wide = pd.DataFrame({"id": [1, 2], "mau": [100, 200], "dau": [10, 20]})
long = wide.melt(id_vars="id", var_name="metric", value_name="value")
print("melt:\n", long)

print("\n=== 9) merge/join — как работают и что за что отвечает ===")
left = pd.DataFrame({"user_id": [1, 2, 3, 4], "L": [10, 20, 30, 40]})
right = pd.DataFrame({"user_id": [2, 3, 5], "R": ["x", "y", "z"]})
 
print("INNER:\n", pd.merge(left, right, on="user_id", how="inner"))
print("LEFT:\n", pd.merge(left, right, on="user_id", how="left"))
print("RIGHT:\n", pd.merge(left, right, on="user_id", how="right"))
print("OUTER:\n", pd.merge(left, right, on="user_id", how="outer"))
 
m_ind = pd.merge(left, right, on="user_id", how="outer", indicator=True)
print("OUTER с _merge:\n", m_ind)
 
semi = pd.merge(left, right[["user_id"]].drop_duplicates(), on="user_id", how="inner")
print("SEMI (left ∩ right по ключам):\n", semi)
 
anti = pd.merge(left, right[["user_id"]].drop_duplicates(), on="user_id", how="left", indicator=True)
anti = anti.loc[anti["_merge"] == "left_only", left.columns]
print("ANTI (left \\ right):\n", anti)
 
idx_left = left.set_index("user_id")
idx_right = right.set_index("user_id")
print("JOIN по индексам:\n", idx_left.join(idx_right, how="left"))
 
try:
    pd.merge(left, right, on="user_id", how="left", validate="one_to_one")
    print("validate: OK (one_to_one)")
except Exception as e:
    print("validate ловит проблему:", e)

print("\n=== 10) Пропуски ===")
na_df = pd.DataFrame({"a": [1, np.nan, 3], "b": [np.nan, 5, 6]})
print("isna():\n", na_df.isna())
print("notna():\n", na_df.notna())
print("fillna(0):\n", na_df.fillna(0))
print("ffill (протянуть вперёд):\n", na_df.ffill())
print("bfill (протянуть назад):\n", na_df.bfill())
print("dropna (удалить строки с NaN):\n", na_df.dropna())
print("interpolate():\n", na_df.interpolate())

print("\n=== 11) Дубликаты ===")
du = pd.DataFrame({"k": [1, 1, 2, 2, 2], "v": [10, 10, 20, 21, 20]})
print("duplicated по k,v:\n", du.duplicated(subset=["k", "v"]))
print("drop_duplicates(k,v):\n", du.drop_duplicates(subset=["k", "v"]))

print("\n=== 12) Сортировки/топы/сэмплы ===")
print("sort_values по price desc:\n", orders.sort_values("price", ascending=False).head())
print("nlargest(3) по price:\n", orders.nlargest(3, "price"))
print("sample(3, random_state=42):\n", orders.sample(3, random_state=42))

print("\n=== 13) Оконные/кумулятивные ===")
series = pd.Series([10, 20, 30, 40, 50])
print("rolling(3).mean:\n", series.rolling(3, min_periods=1).mean())
print("expanding().mean:\n", series.expanding().mean())
print("cum* (cumsum/cummax):\n", series.cumsum(), series.cummax())

print("\n=== 14) Ранги/позиции ===")
r = pd.Series([5, 1, 5, 3])
print("rank(method='dense', ascending=False):\n", r.rank(method="dense", ascending=False))
print("idxmax/idxmin:", r.idxmax(), r.idxmin())
g = pd.DataFrame({"grp": ["A", "A", "B", "B", "B"], "v": [5, 5, 1, 1, 2]})
g["pos_in_grp"] = g.groupby("grp").cumcount()
print(g)

print("\n=== 15) map/replace ===")
city_map = {"MSK": "Moscow", "SPB": "Petersburg", "EKB": "Ekaterinburg"}
print(users["city"].map(city_map))
print(users["city"].replace({"MSK": "MOW"}))

print("\n=== 16) explode / json_normalize ===")
nested = pd.DataFrame({"user_id": [1, 2], "tags": [["a", "b"], ["c"]]})
print("explode (развернуть списки в строки):\n", nested.explode("tags"))
from pandas import json_normalize
js = [{"order_id": 1, "user": {"id": 10, "city": "MSK"}}, {"order_id": 2, "user": {"id": 11, "city": "SPB"}}]
print("json_normalize (развернуть вложенный JSON):\n", json_normalize(js))

print("\n=== 17) Оптимизация памяти category ===")
print(users.dtypes)
users_opt = users.copy()
users_opt["city"] = users_opt["city"].astype("category")
users_opt["segment"] = users_opt["segment"].astype("category")
print(users_opt.dtypes)

print("\n=== 18) Индексы и MultiIndex ===")
mi = (
    orders
    .assign(day=orders["created_at"].dt.date)
    .groupby(["day", "user_id"], as_index=True)["price"]
    .sum()
)
print("Series с MultiIndex:\n", mi.head())
print("unstack (пользователи — колонки):\n", mi.unstack(fill_value=0).head())
print("stack — обратно в длинный формат:\n", mi.unstack(fill_value=0).stack().head())

print("\n=== 19) Экспорт ===")
out_df = agg_city
out_df.to_csv("out.csv", index=False)
out_df.to_parquet("out.parquet")
out_df.to_excel("out.xlsx", index=False)

import numpy as np
a = np.array([10, 20, 30])
print("Обычный массив:", a)
 
zeros = np.zeros(5)
ones = np.ones(3)
rng = np.arange(0, 10, 2)
linsp = np.linspace(0, 1, 5)
print("np.zeros:", zeros)
print("np.ones:", ones)
print("np.arange:", rng)
print("np.linspace:", linsp)

print("\nСреднее np.mean(a):", np.mean(a))
print("Стандартное отклонение np.std(a):", np.std(a))
print("Дисперсия np.var(a):", np.var(a))
print("Выборочное std np.std(a, ddof=1):", np.std(a, ddof=1))
print("Медиана np.median(a):", np.median(a))
print("Персентиль 95 np.percentile(a,95):", np.percentile(a, 95))
print("Сумма np.sum(a):", np.sum(a))
print("Минимум np.min(a):", np.min(a))
print("Максимум np.max(a):", np.max(a))
print("Ковариационная матрица np.cov:", np.cov([a, a]))

print("\nИндекс максимума np.argmax(a):", np.argmax(a))
print("Индекс минимума np.argmin(a):", np.argmin(a))
 
mask = a > 15
print("Маска a>15:", mask)
print("Элементы >15:", a[mask])
 
print("np.where(a>15,1,0):", np.where(a > 15, 1, 0))

print("\nКумулятивная сумма np.cumsum(a):", np.cumsum(a))
print("Кумулятивное произведение np.cumprod(a):", np.cumprod(a))

M = np.array([[1, 2, 3], [4, 5, 6]])
print("\nМатрица:\n", M)
print("Транспонирование M.T:\n", M.T)
print("Сумма по строкам axis=1:", np.sum(M, axis=1))
print("Сумма по столбцам axis=0:", np.sum(M, axis=0))

np.random.seed(42)
 
print("\nnp.random.randint(1,10,5):", np.random.randint(1, 10, 5))
print("np.random.rand(3):", np.random.rand(3))
print("np.random.randn(3):", np.random.randn(3))

print("\nНормальное распределение:", np.random.normal(loc=100, scale=15, size=5))
print("Равномерное распределение:", np.random.uniform(low=0, high=1, size=5))
print("Биномиальное (10 испытаний, p=0.3):", np.random.binomial(n=10, p=0.3, size=5))
print("Пуассон (λ=4):", np.random.poisson(lam=4, size=5))
print("Экспоненциальное (scale=30):", np.random.exponential(scale=30, size=5))

cities = ["MSK", "SPB", "NSK"]
print("\nnp.random.choice(cities,5):", np.random.choice(cities, size=5, replace=True))
arr = np.arange(1, 6)
np.random.shuffle(arr)
print("После shuffle:", arr)
print("Перестановка permutation:", np.random.permutation(5))

data = np.random.normal(0, 1, 1000)
print("\n95-й персентиль:", np.percentile(data, 95))
print("25-й и 75-й персентиль (IQR):", np.percentile(data, [25, 75]))