- Trending Categories
- Data Structure
- Operating System
- MS Excel
- C Programming
- 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 percent in the given Excel sheet using Pandas
Profit and loss percent is an important financial metric that helps in analyzing the profitability of a business. It is calculated by finding the difference between the total revenue and total cost, and then dividing that value by the total cost. In this technical blog, we will learn how to find the profit and loss percent in a given Excel sheet using Pandas. We will be using the same Excel sheet that we used in our previous blog post on finding profit and loss.
Import the Pandas library and read the Excel sheet using the read_excel() function.
Apply the following calculations to each row to determine total income and total cost −
Total Cost = Total Units Bought * Cost of Each Unit
Total Revenue = Total Sold Units * Selling Price of Each Unit
Use the following formula to determine the profit or loss for each row −
Profit/Loss = Total Revenue - Total Cost
Calculate the profit and loss percent for each row using the following formula −
Profit/Loss Percent = (Profit/Loss / Total Cost) * 100
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
import pandas as pd # Reading the Excel sheet df = pd.read_excel('sales.xlsx') # Calculating the Total Cost and Total Revenue df['Total Cost'] = df['Units Purchased'] * df['Unit Cost'] df['Total Revenue'] = df['Units Sold'] * df['Unit Price'] # Calculating the Profit/Loss df['Profit/Loss'] = df['Total Revenue'] - df['Total Cost'] # Calculating the Profit/Loss Percent df['Profit/Loss Percent'] = (df['Profit/Loss'] / df['Total Cost']) * 100 # Displaying the final DataFrame print(df)
Units Purchased Unit Cost Units Sold Unit Price Item Name Total Cost Total Revenue Profit/Loss Profit/Loss Percent 50 5.00 40 9.00 Apples 250.00 360.0 110.00 44.000000 100 3.50 80 7.00 Oranges 350.00 560.0 210.00 60.000000 25 12.00 20 15.00 Pineapples 300.00 300.0 0.00 0.000000 75 1.75 60 3.50 Bananas 131.25 210.0 78.75 60.000000 200 0.50 180 1.25 Carrots 100.00 225.0 125.00 125.000000 450 2.00 120 4.50 Potatoes 900.00 540.0 -360.00 -40.000000 40 8.00 30 12.00 Avocados 320.00 360.0 40.00 12.500000 80 1.50 70 3.00 Tomatoes 120.00 210.0 90.00 75.000000 300 20.00 25 25.00 Mangoes 6000.00 625.0 -5375.00 -860.000000 60 4.00 45 8.00 Grapes 240.00 360.0 120.00 100.000000
The Excel file "sales.xlsx" is read for data using the pandas read_excel() function.
By conducting arithmetic operations on two of the columns that are already present in lines 6 and 7, two additional columns are added to the DataFrame which are named “Total Cost” and “Total Income”.
The profit or loss is calculated by dividing the total cost by the total revenue. Following that, the benefit/misfortune is figured by partitioning it by the whole expense, then increasing the outcome by 100 to get the benefit/misfortune rate.
The terminal outputs the resulting DataFrame.
If you require to calculate profit and loss data and display it in a tabular format, the aforementioned code snippet can be useful, i.e. Businesses trying to track expenditures and evaluate overall performance can find it useful.
With the assistance of the tabular format, businesses can easily compare and assess their profit and loss numbers over a certain time, enabling them to confidently decide on their financial plan.
The code snippet is a versatile tool for financial analysis since it may be altered to satisfy the particular needs of a business and therefore this code may also be used by individuals to monitor their personal finances, including their assets and outgoings.
Pandas offers a dependable and effective method for figuring out an Excel sheet's profit and loss percentage. Businesses and individuals may quickly get useful insights into their financial performance by using Pandas to read in and change the data. Financial analysis requires the capacity to determine profit and loss percentages, and using Pandas helps expedite and improve this process. As a result, Pandas is a useful tool for anybody handling financial research and data.
Kickstart Your Career
Get certified by completing the courseGet Started