Code Interpreter Python, M and R Code for Excel BI Power Query Challenge 85

gistfile1.txt

PYTHON CODE
***********

import pandas as pd

# Load the dataset
df = pd.read_csv('/mnt/data/PQ Challenge 85.csv')

# Fill missing company names
df['Company'].fillna(method='ffill', inplace=True)

# Calculate total revenue and cost for each year
df['Total Revenue'] = df[['2019 Revenue', '2020 Rev', '2021 Rev', '2022 Rev']].sum(axis=1)
df['Total Cost'] = df[['2019 Cost', '2020 Cost', '2021 Cost', '2022 Cost']].sum(axis=1)

# Calculate total profit
df['Total Profit'] = df['Total Revenue'] - df['Total Cost']

# Group by company and calculate total revenue, cost, and profit for each company
df_grouped = df.groupby('Company').agg({'Total Revenue': 'sum', 'Total Cost': 'sum', 'Total Profit': 'sum'}).reset_index()

# Add a row for the grand total
grand_total = df_grouped.sum(numeric_only=True)
grand_total['Company'] = 'Grand Total'
df_grouped = df_grouped.append(grand_total, ignore_index=True)

# Display the transformed dataframe
df_grouped
import pandas as pd

# Load the dataset
df = pd.read_csv('/mnt/data/PQ Challenge 85.csv')

# Fill missing company names
df['Company'].fillna(method='ffill', inplace=True)

# Calculate total revenue and cost for each year
df['Total Revenue'] = df[['2019 Revenue', '2020 Rev', '2021 Rev', '2022 Rev']].sum(axis=1)
df['Total Cost'] = df[['2019 Cost', '2020 Cost', '2021 Cost', '2022 Cost']].sum(axis=1)

# Calculate total profit
df['Total Profit'] = df['Total Revenue'] - df['Total Cost']

# Group by company and calculate total revenue, cost, and profit for each company
df_grouped = df.groupby('Company').agg({'Total Revenue': 'sum', 'Total Cost': 'sum', 'Total Profit': 'sum'}).reset_index()

# Add a row for the grand total
grand_total = df_grouped.sum(numeric_only=True)
grand_total['Company'] = 'Grand Total'
df_grouped = df_grouped.append(grand_total, ignore_index=True)

# Display the transformed dataframe
df_grouped


M CODE
*******

let
    Source = Csv.Document(File.Contents("path_to_your_file.csv"),[Delimiter=",", Columns=10, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Filled Down" = Table.FillDown(#"Promoted Headers",{"Company"}),
    #"Added Total Revenue" = Table.AddColumn(#"Filled Down", "Total Revenue", each [#"2019 Revenue"] + [#"2020 Rev"] + [#"2021 Rev"] + [#"2022 Rev"]),
    #"Added Total Cost" = Table.AddColumn(#"Added Total Revenue", "Total Cost", each [#"2019 Cost"] + [#"2020 Cost"] + [#"2021 Cost"] + [#"2022 Cost"]),
    #"Added Total Profit" = Table.AddColumn(#"Added Total Cost", "Total Profit", each [#"Total Revenue"] - [#"Total Cost"]),
    #"Grouped Rows" = Table.Group(#"Added Total Profit", {"Company"}, {{"Total Revenue", each List.Sum([#"Total Revenue"]), type number}, {"Total Cost", each List.Sum([#"Total Cost"]), type number}, {"Total Profit", each List.Sum([#"Total Profit"]), type number}}),
    #"Added Grand Total" = Table.Combine({#"Grouped Rows", Table.FromRecords({[Company = "Grand Total", #"Total Revenue" = List.Sum(#"Grouped Rows"[#"Total Revenue"]), #"Total Cost" = List.Sum(#"Grouped Rows"[#"Total Cost"]), #"Total Profit" = List.Sum(#"Grouped Rows"[#"Total Profit"])]})})
in
    #"Added Grand Total"


R CODE
*******

# Load necessary libraries
library(dplyr)
library(readr)
library(tidyr)

# Load the dataset
df <- read_csv("path_to_your_file.csv")

# Fill missing company names
df$Company <- tidyr::fill(df$Company, .direction = "down")

# Calculate total revenue and cost for each year
df <- df %>%
    mutate(`Total Revenue` = `2019 Revenue` + `2020 Rev` + `2021 Rev` + `2022 Rev`,
           `Total Cost` = `2019 Cost` + `2020 Cost` + `2021 Cost` + `2022 Cost`)

# Calculate total profit
df <- df %>%
    mutate(`Total Profit` = `Total Revenue` - `Total Cost`)

# Group by company and calculate total revenue, cost, and profit for each company
df_grouped <- df %>%
    group_by(Company) %>%
    summarise(`Total Revenue` = sum(`Total Revenue`),
              `Total Cost` = sum(`Total Cost`),
              `Total Profit` = sum(`Total Profit`))

# Add a row for the grand total
grand_total <- summarise_all(df_grouped, sum)
grand_total$Company <- "Grand Total"
df_grouped <- bind_rows(df_grouped, grand_total)

# Print the transformed dataframe
print(df_grouped)

Both R and M code generated by Code Interpreter had multiple errors that prevented it from executing fully and producing the desired result