Find the profit and loss in the given Excel sheet using Pandas

Pandas is a popular data manipulation and analysis library in Python that is widely used by data scientists and analysts. It provides several functions for working with data in Excel sheets. One of the most common tasks in analyzing financial data is finding the profit and loss in a given Excel sheet.

Setup

To handle excel files in Python using Python, you need to install the openpyxl dependency. To do this, open your terminal and type the command −

pip install openpyxl

After successful installation you can proceed with experimenting with Excel files and spreadsheets.

To download the Excel spreadsheet used in the exercises below, please check out this link

Algorithm

To read data from an Excel file into a Pandas DataFrame use the method read_excel() which is built into Pandas. We must deduct the whole cost from the total revenue in order to calculate the profit and loss. The following stages may be used to sum up the algorithm for utilizing Pandas to calculate profit and loss −

• With the read_excel() method, the Excel sheet is read into a Pandas DataFrame.

• The DataFrame should be updated with a new column for profit and loss.

• Subtract the total cost from the total income to determine the profit and loss for each row.

• Add up the profit and loss column in the DataFrame to determine the overall profit and loss.

Example 1

The following code reads an Excel sheet called 'sales.xlsx' and creates a DataFrame. It then adds a new column for the profit and loss and calculates the profit and loss for each row.

import pandas as pd

# read excel sheet into pandas dataframe
df = pd.read_excel('sales.xlsx')

# calculate total cost
df['Total Cost'] = df['Units Purchased'] * df['Unit Cost']

# calculate total revenue
df['Total Revenue'] = df['Units Sold'] * df['Unit Price']

# calculate profit/loss
df['Profit/Loss'] = df['Total Revenue'] - df['Total Cost']

# print the resulting dataframe
print(df)

# save the resulting dataframe to a new excel sheet 
df.to_excel('sales_results.xlsx', index=False)

Output

Units Purchased	Unit Cost	Units Sold	Unit Price	Item Name	Total Cost	Total Revenue	Profit/Loss
    50	             5.00	       40             9.00	   Apples	    250.00	    360.0	     110.00
    100	             3.50	       80	      7.00	  Oranges	    350.00	    560.0	     210.00
    25	            12.00	       20	     15.00     Pineapples	    300.00	    300.0	       0.00
    75	             1.75	       60	      3.50	  Bananas	    131.25	    210.0	      78.75
    200	             0.50	      180	      1.25	  Carrots	    100.00	    225.0	     125.00
    450	             2.00	      120	      4.50	 Potatoes	    900.00	    540.0	    -360.00
    40	             8.00	       30	     12.00	 Avocados	    320.00	    360.0	      40.00
    80	             1.50	       70	      3.00	 Tomatoes	    120.00	    210.0	      90.00
    300	            20.00	       25	     25.00	  Mangoes	   6000.00	    625.0	   -5375.00
    60	             4.00	       45	      8.00	   Grapes	    240.00	    360.0	     120.00

In this example, we first import the Pandas library and then read the Excel sheet using the read_excel() function. We then create new columns in the dataframe to calculate the total cost, total revenue, and profit/loss for each product. Finally, we print the resulting dataframe that includes the new columns with calculated values and save it to a new excel sheet for further processing.

Example 2: Computing Profit and Loss with Filters

import pandas as pd

# read excel sheet into pandas dataframe
df = pd.read_excel('sales_results.xlsx')

# filter the dataframe to include only the products with profit
df_profit = df[df['Total Revenue'] > df['Total Cost']]

# calculate the total profit
total_profit = df_profit['Total Revenue'].sum() - df_profit['Total Cost'].sum()

# filter the dataframe to include only the products with loss
df_loss = df[df['Total Revenue'] < df['Total Cost']]

# calculate the total loss
total_loss = df_loss['Total Cost'].sum() - df_loss['Total Revenue'].sum()

# print the total profit and loss
print(f"Total Profit: {total_profit}")
print(f"Total Loss: {total_loss}")

Output

Total Profit: 773.75
Total Loss: 5735.0

First import the Pandas library and then read the Excel sheet saved as a result from the previous example using the read_excel() function. We then filter the dataframe to include only the products that have a profit and calculate the total profit. Similarly, we filter the dataframe to include only the products that have a loss and calculate the total loss. Finally, we print the total profit and loss using the print() function.

Applications of Computing Profit and Loss Using Pandas

• Financial data analysis − Businesses may use Pandas to examine their financial information and determine the profit and loss for various goods and services.

• Investment analysis − Using Pandas, investors can examine a company's financial information to ascertain if it is lucrative or not.

• Business forecasting − Businesses could employ Pandas to project future revenues and losses by examining historical data.

Conclusion

For analyzing and calculating profit and loss from an Excel sheet, Pandas is a potent Python package that may be employed. Pandas is a vital tool for any data analyst or financial expert because of its simple interface and potent features. Developers may utilize Pandas to analyze their financial data and acquire insights into the success of their businesses by following the examples given in this article.