Python - Cost Management Analysis

Refer to the cost analysis data from Funtern's cloud team's MS Azure usage since February.

Use Python to do below:

• Import the data

• Calculate the FX for each day.

• Draw a line chart for Cost USD (K), Cost CAD (K) and FX

• For Cost USD (K), Cost CAD (K) and FX, calculate the average, min and max

1.Import the data

First of all, we need to import the data from excel to Python, then we can do the following steps. We use pandas here. “df = pd.read_excel(“”)” is the file’s path. Considering we need to draw a line chart later, I write the related code in this step.

We can get the imported data when we run this code.

2.Calculate the FX for each day

We create a new column named “FX” by using “df[“FX”]”. FX refers to the exchange rate, CAD to USD. So we need to divide column “Cost CAD (K)” by column “Cost USD (K)”. We should notice that “[]” is used in this code instead of “()”, since “Cost USD (K)”, “Cost CAD (K)” and “FX” are columns’ names.

3.Draw a line chart for Cost USD (K), Cost CAD (K) and FX

We choose “UsageDate” as horizontal axis, the other three columns as vertical axis. Considering there are some “NAN” in the database, we can clear these “NAN” first. Using “drop” function to remove line 3 to line 12.

Then we write code to crate line chart, “xaxis” refers to horizontal axis, “yaxis” refers to vertical axis. Here “plot” is used to draw line chart.

5.For Cost USD (K), Cost CAD (K) and FX, calculate the average, min and max

Write down “df.describe()” then we can get a statement about the database, including mean, min and max. “df.describe()” works for the columns that contain numbers, those includes words (such as column “UsageDate”) are not satisfied with “df.describe()”.

FUNTERN Associate: Jin Wang