- Trending Categories
- Data Structure
- Networking
- RDBMS
- Operating System
- Java
- MS Excel
- iOS
- HTML
- CSS
- Android
- Python
- C Programming
- C++
- C#
- MongoDB
- MySQL
- Javascript
- PHP
- Physics
- Chemistry
- Biology
- Mathematics
- English
- Economics
- Psychology
- Social Studies
- Fashion Studies
- Legal Studies

- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who

# 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.