Spaces:
Sleeping
Sleeping
File size: 3,015 Bytes
afefd94 6cf5d7d afefd94 6cf5d7d afefd94 6cf5d7d afefd94 bcf1995 6cf5d7d afefd94 |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 |
# sheet_tool.py
from google.oauth2.service_account import Credentials
import gspread
import pandas as pd
import matplotlib.pyplot as plt
import io
import base64
from typing import Tuple
# CONFIG - change these
CREDENTIALS_FILE = "credentials.json" # path to your service account JSON
SHEET_ID = "1nOekWGmPsjoHj9T-MFjlNGSFyKPRogVbEjFNRxGgxuM" # replace with your sheet id
WORKSHEET_INDEX = 0 # first sheet
SCOPES = [
"https://www.googleapis.com/auth/spreadsheets",
"https://www.googleapis.com/auth/drive",
]
def authorize_gs():
creds = Credentials.from_service_account_file(CREDENTIALS_FILE, scopes=SCOPES)
client = gspread.authorize(creds)
return client
def fetch_sheet_as_df(sheet_id: str = SHEET_ID, worksheet_index: int = WORKSHEET_INDEX) -> pd.DataFrame:
client = authorize_gs()
sh = client.open_by_key(sheet_id)
worksheet = sh.get_worksheet(worksheet_index)
data = worksheet.get_all_values()
if not data:
return pd.DataFrame()
df = pd.DataFrame(data[1:], columns=data[0])
# try to convert numeric columns where possible
for col in df.columns:
df[col] = pd.to_numeric(df[col], errors="ignore")
return df
def create_pivot(df: pd.DataFrame, index: list, columns: list, values: list, aggfunc: str = "sum") -> pd.DataFrame:
if df.empty:
return pd.DataFrame()
# Create pivot table
pivot = pd.pivot_table(df, index=index, columns=columns, values=values, aggfunc=aggfunc, fill_value=0)
# Flatten multi-index columns for readability
pivot = pivot.reset_index()
pivot.columns = [
" ".join(map(str, c)).strip() if isinstance(c, tuple) else str(c).strip()
for c in pivot.columns
]
return pivot
def summary_stats(df: pd.DataFrame, numeric_only: bool = True) -> pd.DataFrame:
if df.empty:
return pd.DataFrame()
return df.describe(include="all") if not numeric_only else df.describe()
def plot_dataframe(df: pd.DataFrame, kind: str = "bar", x: str = None, y: list = None, title: str = None, figsize=(8,5)) -> str:
"""
Creates a matplotlib plot and returns a base64 PNG data URI.
"""
if df.empty:
raise ValueError("DataFrame is empty")
plt.close('all')
fig, ax = plt.subplots(figsize=figsize)
if kind == "bar":
if x is None or y is None:
df.plot(kind="bar", ax=ax)
else:
df.plot(kind="bar", x=x, y=y, ax=ax)
elif kind == "line":
df.plot(kind="line", x=x, y=y, ax=ax)
elif kind == "pie":
df.set_index(x)[y].plot(kind="pie", ax=ax, autopct='%1.1f%%')
else:
df.plot(kind=kind, x=x, y=y, ax=ax)
if title:
ax.set_title(title)
ax.grid(True)
buf = io.BytesIO()
fig.tight_layout()
fig.savefig(buf, format="png")
buf.seek(0)
b64 = base64.b64encode(buf.read()).decode("utf-8")
return "data:image/png;base64," + b64
def df_to_csv_bytes(df: pd.DataFrame) -> bytes:
return df.to_csv(index=False).encode("utf-8")
|