azure_cost_analysis_v2.py

import pandas as pd
import os

folder_path = "/os/folder/path"
summaryDF = pd.DataFrame()
data = pd.concat(
    [
        pd.read_excel(os.path.join(folder_path, f), sheet_name="Data")
        for f in os.listdir(folder_path)
    ],
    ignore_index=True,
)
df = data.query('ServiceName=="Bandwidth" or ServiceName=="Virtual Network"')
df['CleanCost'] = df['Cost'].apply(lambda x: x.replace('$', '').replace('<', ''))
df[["ServiceTier","Meter","CleanCost"]].to_csv('summary_2.csv', index=False)