import boto3
import datetime
import pandas as pd
import numpy as np

now = datetime.datetime.utcnow()
start = (now - datetime.timedelta(days=40)).strftime("%Y-%m-%d")
end = now.strftime("%Y-%m-%d")

cd = boto3.client("ce",
    aws_access_key_id="XXX", aws_secret_access_key="XXX",)

results = []

token = None
while True:
    if token:
        kwargs = {"NextPageToken": token}
    else:
        kwargs = {}
    data = cd.get_cost_and_usage(
        TimePeriod={"Start": start, "End": end},
        Granularity="DAILY",
        Metrics=["UnblendedCost"],
        GroupBy=[
            {"Type": "DIMENSION", "Key": "LINKED_ACCOUNT"},
            {"Type": "DIMENSION", "Key": "SERVICE"},
        ],
        **kwargs
    )
    results += data["ResultsByTime"]
    token = data.get("NextPageToken")
    if not token:
        break


tp = list()
ai = list()
sn = list()
am = list()
for result_by_time in results:
    for group in result_by_time["Groups"]:
        TimePeriod = result_by_time["TimePeriod"]["Start"]
        account_id = group["Keys"][0]
        service_name = group["Keys"][1]
        amount = group["Metrics"]["UnblendedCost"]["Amount"]
        tp.append(TimePeriod)
        ai.append(account_id)
        sn.append(service_name)
        am.append(amount)

df = pd.DataFrame([tp, ai, sn, am]).T
df.columns = ["date", "account_no", "service_name", "amount"]
df.to_csv("myreport.csv")



## EDA

import janitor
from sklearn.feature_selection import VarianceThreshold

df = pd.read_csv("myreport.csv")

ndf = (
    df.groupby(["date", "account_no", "service_name"])["amount"].sum().unstack()
)

ndf1 = ndf.astype(np.float)
ndf1 = ndf1.fillna(0)

qconstant_filter = VarianceThreshold(threshold=1.01)
ndf2 = qconstant_filter.fit_transform(ndf1)

mycols = ndf1.columns[qconstant_filter.get_support()]
report = pd.DataFrame(ndf2, columns=mycols)

ndf1 = ndf1.reset_index()
report["date"] = ndf1["date"]

df = report.rename_axis(index=None, columns=None)
df = df.set_index("date")
df.plot(kind="bar")

df = df[(df > 1).any(axis=1)]

df = df.clean_names(
    strip_underscores=True, case_type="lower", remove_special=True
).limit_column_characters(18)