Conditions Apply

Search

Python Programming Filter

How to Work with Excel Spreadsheets using Python

Excel is considered as one of the most popular and widely used spreadsheet applications developed by Microsoft. You can organize, analyze and store your data into tabular sheets with the help of Excel. From analysts and sales managers, to CEOs, professionals from every field use Excel for creating quick statistics and for data crunching.Spreadsheets are commonly used in the present world because of their intuitive nature and the ability to handle large datasets. Most importantly, they can work without any prior technical background.Finding different ways to work with Excel using code is essential since working with data and in Python has some serious advantages in comparison with Excel’s UI. Developers of Python have implemented ways to read, write and manipulate Excel documents.You can check the quality of your spreadsheet application by going over the checklist below:Is the spreadsheet able to represent static data?Is the spreadsheet able to mix data, calculations, and reports?Is the data in your spreadsheet complete and consistent in nature?Does the spreadsheet have an organized worksheet structure?This checklist will help you in verifying the qualitative nature of the spreadsheet application you’re going to work on.Practical Applications  In this article, we would be using openpyxl to work on data. With the help of this module, you can extract data from a database into an Excel spreadsheet or you can also convert an Excel spreadsheet into a programmatic format. There can be a lot of possible situations where you might feel the need to use a package like openpyxl. Let us discuss a few of them to get a comprehensive overview of it.Importing New Products Into a Database Consider yourself working in an online store company. When they want to add new products to the online store, they make an Excel spreadsheet with a few hundred rows along with the name of the product, description, price and a few more basic information and then they give it to you. Now, if you want to import this particular data, you need to iterate over each row of the spreadsheet and then add each of the products into the database of the online store.[Text Wrapping Break] Exporting Database Data Into a SpreadsheetConsider you have a Database table. In this particular table, you have collected information of all your users which includes their name, contact number, email address, and so forth. Now, the Marketing Team is willing to collectively contact all the users and promote a new product of the company. However, neither do they have access to the Database nor they have any idea about using SQL to extract the information. In this situation, openpyxl comes to play. You can use it effectively to iterate over each User record and transform the required information into an Excel spreadsheet.    Appending Information to an Existing SpreadsheetConsider the same online store example we discussed above. You have an Excel spreadsheet with a list of users and your job is to append to each row the total amount they have spent in your store.In order to perform this, you have to read the spreadsheet first and then iterate through each row and fetch the total amount spent from the Database. Finally, you need to write it back to the spreadsheet.Starting openpyxlYou can install the openpyxl package using pip. Open your terminal and write the following command: $ pip install openpyxlAfter you have installed the spreadsheet, you can make up your own simple spreadsheet: from openpyxl import Workbook workbook = Workbook() spreadsheet = workbook.active spreadsheet["A1"] = "Hello" spreadsheet["B1"] = "World!" workbook.save(filename="HelloWorld.xlsx")How to Read Excel Spreadsheets with openpyxl Let us start with the most important thing that you can do with a spreadsheet,i.e. read it. We will be using a Watch Sample Dataset which contains a list of 100 watches with information like product name, product ID, review and so forth.  A Simple Way to Read an Excel Spreadsheet Let us start with opening our sample spreadsheet:>>> from openpyxl import load_workbook >>> workbook = load_workbook(filename="sample.xlsx") >>> workbook.sheetnames ['Sheet 1'] >>> spreadsheet = workbook.active >>> spreadsheet <Worksheet "Sheet 1"> >>> spreadsheet.titleIn the example code above, we open the spreadsheet using load_workbook and then we check all the sheets that are available to work with using workbook.sheetnames. Then Sheet 1 is automatically selected using workbook.active since it is the first sheet available. This is the most common way of opening a spreadsheet.  Now, let us see the code to retrieve data from the spreadsheet: >>> spreadsheet["A1"] <Cell 'Sheet 1'.A1> >>> spreadsheet["A1"].value 'marketplace' >>> spreadsheet["F10"].value "G-Shock Men's Grey Sport Watch"You can retrieve the actual value and the cell value  both. To get the actual value, use .value and to get the cell, you can use .cell():>>> spreadsheet.cell(row=10, column=6) <Cell 'Sheet 1'.F10> >>> spreadsheet.cell(row=10, column=6).value "G-Shock Men's Grey Sport Watch"Importing Data from a Spreadsheet In this section, we will discuss how to iterate through the data, and about conversion into a more useful format using Python.Let us first start with iterating through the data. There are a number of iterating methods that depend solely on the user.You can slice the data with a combination of rows and columns:>>> spreadsheet["A1:C2"] ((<Cell 'Sheet 1'.A1>, <Cell 'Sheet 1'.B1>, <Cell 'Sheet 1'.C1>),  (<Cell 'Sheet 1'.A2>, <Cell 'Sheet 1'.B2>, <Cell 'Sheet 1'.C2>)) You can also iterate through the dataset by ranging between rows and columns: >>> # Get all cells from column A  >>> spreadsheet["A"] (<Cell 'Sheet 1'.A1>,  <Cell 'Sheet 1'.A2>,   ...   <Cell 'Sheet 1'.A99>,   <Cell 'Sheet 1'.A100>) >>> # Get all cells for a range of columns >>> spreadsheet["A:B"]  ((<Cell 'Sheet 1'.A1>,    <Cell 'Sheet 1'.A2>,    ...    <Cell 'Sheet 1'.A99>,    <Cell 'Sheet 1'.A100>),   (<Cell 'Sheet 1'.B1>,    <Cell 'Sheet 1'.B2>,    ...    <Cell 'Sheet 1'.B99>,    <Cell 'Sheet 1'.B100>))  >>> # Get all cells from row 5 >>> spreadsheet[5] (<Cell 'Sheet 1'.A5>,  <Cell 'Sheet 1'.B5>,  ...   <Cell 'Sheet 1'.N5>,  <Cell 'Sheet 1'.O5>) >>> # Get all cells for a range of rows >>> spreadsheet[5:6] ((<Cell 'Sheet 1'.A5>,   <Cell 'Sheet 1'.B5>,    ...    <Cell 'Sheet 1'.N5>,    <Cell 'Sheet 1'.O5>),   (<Cell 'Sheet 1'.A6>,    <Cell 'Sheet 1'.B6>,    ...    <Cell 'Sheet 1'.N6>,    <Cell 'Sheet 1'.O6>)) Python offers arguments by which you can set limits to the iteration with the help of Python generators like .iter_rows() and .iter_cols(): >>> for row in spreadsheet.iter_rows(min_row=1, ... max_row=2, ... min_col=1, ... max_col=3): ... print(row) (<Cell 'Sheet 1'.A1>, <Cell 'Sheet 1'.B1>, <Cell 'Sheet 1'.C1>) (<Cell 'Sheet 1'.A2>, <Cell 'Sheet 1'.B2>, <Cell 'Sheet 1'.C2>) >>> for column in spreadsheet.iter_cols(min_row=1,  ... max_row=2, ... min_col=1, ... max_col=3): ... print(column) (<Cell 'Sheet 1'.A1>, <Cell 'Sheet 1'.A2>) (<Cell 'Sheet 1'.B1>, <Cell 'Sheet 1'.B2>)  (<Cell 'Sheet 1'.C1>, <Cell 'Sheet 1'.C2>) You can also add Boolean values_only in the above example and set it to True to get the values of cell: >>> for value in spreadsheet.iter_rows(min_row=1,  ... max_row=2,  ... min_col=1,  ... max_col=3,  ... values_only=True): ... print(value) ('marketplace', 'customer_id', 'review_id') ('US', 3653882, 'R3O9SGZBVQBV76')Since we are now done with iterating the data, let us now manipulate data using Python’s primitive data structures. Consider a situation where you want to extract information of a product from the sample spreadsheet and then store it into the dictionary. The key to the dictionary would be the product ID.   Convert Data into Python classesTo convert data into Python data classes, let us first decide what we want to store and how to store it.  The two essential elements that can be extracted from the data are as follows:                                                     1. Products                                             2. Review                                                          • ID                                                         • ID                                                          • Title                                                     • Customers ID                                                          • Parent                                                 • Headline                                                          • Category                                            • Body                                                                                                                         • DateLet us implement the two elements: import datetime from dataclasses import dataclass @dataclass class Product: id: str parent: str title: str category: str @dataclass class Review: id: str customer_id: str stars: int headline: str body: str  date: datetime.datetime The next step is to create a mapping between columns and the required fields: >>> for value in spreadsheet.iter_rows(min_row=1, ... max_row=1, ... values_only=True): ... print(value) ('marketplace', 'customer_id', 'review_id', 'product_id', ...) >>> # Or an alternative >>> for cell in sheet[1]: ... print(cell.value) marketplace Customer_ID Review_ID Product_ID Product_Parent ...Finally, let us convert the data into new structures which will parse the data in spreadsheet into a list of products and review objects: from datetime import datetime  from openpyxl import load_workbook  from classes import Product,Review  from mapping import PRODUCT_ID,PRODUCT_PARENT,PRODUCT_TITLE, \ PRODUCT_CATEGORY,REVIEW_DATE,REVIEW_ID,REVIEW_CUSTOMER, \ REVIEW_STARS,REVIEW_HEADLINE,REVIEW_BODY # Using the read_only method since you're not gonna be editing the spreadsheet workbook = load_workbook(filename="watch_sample.xlsx",read_only=True)  spreadsheet = workbook.active products = [] reviews = [] # Using the values_only because you just want to return the cell value for row in spreadsheet .iter_rows(min_row=2, values_only=True):  product = Product(id=row[PRODUCT_ID],  parent=row[PRODUCT_PARENT],  title=row[PRODUCT_TITLE],  category=row[PRODUCT_CATEGORY])  products.append(product) # You need to parse the date from the spreadsheet into a datetime format spread_date = row[REVIEW_DATE]  parsed_date = datetime.strptime(spread_date,"%Y-%m-%d") review = Review(id=row[REVIEW_ID], Customer_ID=row[REVIEW_CUSTOMER], stars=row[REVIEW_STARS], headline=row[REVIEW_HEADLINE], body=row[REVIEW_BODY], date=parsed_date) reviews.append(review) print(products[0]) print(reviews[0])After you execute the code, you will get an output that looks like this:Product(id='A90FALZ1ZC',parent=937111370,...) Review(id='D3O9OGZVVQBV76',customer_id=3903882,...)Appending Data To understanding how to append data, let us hover back to the first sample spreadsheet. We will open the document and append some data to it: from openpyxl import load_workbook # Start by opening the spreadsheet and selecting the main sheet workbook = load_workbook(filename="hello_world.xlsx") spreadsheet = workbook.active # Write what you want into a specific cell spreadsheet["C1"]="Manipulating_Data ;)" # Save the spreadsheet workbook.save(filename="hello_world_append.xlsx"If you open your Excel file, you will notice the additional Manipulating_Data being added to an adjacent cell. Writing Excel Spreadsheets With openpyxl A spreadsheet is a file that helps to store data in specific rows and columns. We can calculate and store numerical data and also perform computation using formulas. So, let’s begin with some simple Spreadsheets and understand what each line means. Creating our first simple Spreadsheet 1 from openpyxl import Workbook  2    3 filename = "first_program.xlsx"  4    5 workbook = Workbook()  6 spreadsheet = workbook.active  7    8 sheet["A1"] = "first"  9 sheet["B1"] = "program!" 10   11 workbook.save(filename=filename)Line 5: In order to make a Spreadsheet, at first,  we have to create an Empty workbook to perform further operations. Lines 8 and 9 : We can add data to a specific cell as per our requirement. In this example, we can see that two values “first” and “program” have been added to specific cells in the sheet. Line 11: The line shows how to save data after all the operations we have done. Basic Spreadsheet Operations Before going to the difficult coding part, at first we have to build our building blocks like how to add and update values, how to manage rows and columns, adding filters, styles or formulas in a Spreadsheet. We have already explained the following code by which we can add values to a Spreadsheet: >>> spreadsheet["A1"] = "the_value_we_want_to_add"There is another way that we can add values to Spreadsheet: >>> cell = sheet["A1"] >>> cell <Cell 'Sheet'.A1> >>> cell.value 'hello' >>> cell.value = "hey" >>> cell.value 'hey'Line 1: In the first line at first we have declared the cell and updated its value. Line 5: We have printed the value of the cell as “first”  because  in the first program we have already assigned sheet["A1"]with “first” Line 8 : We have updated the value of the cell as "second"by simply assigning it to cell.value. Lines 9 : In this line, we have just printed the updated value of cell. Finally, you have to save all the operations you have performed into the spreadsheet once you call workbook.save().If  the cell didn’t exist while adding a value ,then openpyxl creates a cell:>>> # Before, our spreadsheet has only 1 row >>> print_rows() ('first', 'program!') >>> # Try adding a value to row 10 >>> spreadsheet["B10"] = "test" >>> print_rows() ('first', 'program!') (None, None)  (None, None)  (None, None)  (None, None)  (None, None)  (None, None)  (None, None)  (None, None)  (None, 'test') Managing Rows and Columns in Spreadsheet Insertion or deletion of rows (adding or removing elements of rows /columns) is one of the most basic operations in Spreadsheet. In openpyxl.We can perform these operations by simply calling these methods and passing its arguments. .insert_rows().delete_rows().insert_cols().delete_cols()We can pass 2 types of arguments to the methods :  idx amount Idx stands for index position and amount refers to the number of values we can store in the Spreadsheet. Using our basic knowledge based on the first  simple program, let’s see how we can use these methods inside the program: >>> print_rows() ('first', 'program!') >>> # Insert a column at the first position before column 1 ("A") >>> spreadsheet.insert_cols(idx=1) >>> print_rows() (None, 'first', 'program!') >>> # Insert 5 columns in  between column 2 ("B") and 3 ("C") >>> spreadsheet.insert_cols(idx=3,amount=5) >>> print_rows() (None, 'first', None, None, None, None, None, 'program!') >>> # Delete the created columns >>> spreadsheet.delete_cols(idx=3,amount=5) >>> v.delete_cols(idx=1) >>> print_rows() ('first', 'program!') >>> # Insert a new row in the beginning >>> spreadsheet.insert_rows(idx=1) >>> print_rows() (None, None) ('first', 'program!') >>> # Insert 3 new rows in the beginning  >>> spreadsheet.insert_rows(idx=1,amount=3) >>> print_rows() (None, None) (None, None)  (None, None)  (None, None)  ('first', 'program!') >>> # Delete the first 4 rows  >>> spreadsheet.delete_rows(idx=1,amount=4)  >>> print_rows()  ('first', 'program!') Managing SheetsWe have seen the following recurring piece of code in our previous examples .This is one of the ways of selecting the default sheet from the Spreadsheet: spreadsheet = workbook.activeHowever, if you want to open a spreadsheet with multiple sheets, you can write the following command: >>> # Let's say you have two sheets: "Products" and "Company Sales" >>> workbook.sheetnames ['Products', 'Company Sales'] >>> # You can select a sheet using its title >>> Products_Sheet = workbook["Products"] >>> Sales_sheet = workbook["Company Sales"]If we want to change the title of the Sheet, execute the following code: >>> workbook.sheetnames ['Products', 'Company Sales'] >>> Products_Sheet = workbook["Products"] >>> Products_Sheet.title = "New Products" >>> workbook.sheetnames ['New Products', 'Company Sales']We can CREATE / DELETE Sheets also with the help of two methods - .create_sheet() and  .remove(): >>> #To print the available sheet names >>> workbook.sheetnames  ['Products', 'Company Sales'] >>> #To create a new Sheet named "Operations" >>> Operations_Sheet = workbook.create_sheet("Operations") >>> #To print the updated available sheet names >>> workbook.sheetnames ['Products', 'Company Sales', 'Operations'] >>> # To define the position Where we want to create the Sheet(here “HR” sheet is created at the first position .Here index 0 represents the first position) >>> HR_Sheet = workbook.create_sheet("HR",0) >>> #To again  print the updated available sheet names >>> workbook.sheetnames ['HR', 'Products', 'Company Sales', 'Operations'] >>> # To remove them,we just have to send the sheet names as an argument which we want to delete to the method  .remove()  >>> workbook.remove(Operations_Sheet) >>> workbook.sheetnames ['HR', 'Products', 'Company Sales'] >>> #To delete hr_sheet >>> workbook.remove(hr_sheet) >>> workbook.sheetnames ['Products', 'Company Sales']Adding Filters to the Spreadsheet We can use openpyxl to add filters in our Spreadsheet but when we open our Spreadsheet, the data won’t be rearranged according to these sorts and filters. When you’re programmatically creating a spreadsheet and it is going to be sent and used by someone else, it is a good practice to add different filters and allow people to use it afterward. In the code below there is a simple example which shows how to add a simple filter to your spreadsheet: >>> # Check the used spreadsheet space using the attribute "dimensions" >>> spreadsheet.dimensions 'A1:O100' >>> spreadsheet.auto_filter.ref="A1:O100" >>> workbook.save(filename="watch_sample_with_filters.xlsx")Adding Formulas to the Spreadsheet Formulas are one of the most commonly used and powerful features of spreadsheets. By using formulas, you can solve various mathematical equations with the additional support of openpyxl which makes those calculations as simple as editing a specific cell’s value.The list of formulas supported by openpyxl are:>>> from openpyxl.utils import FORMULAE >>> FORMULAE frozenset({'ABS',            'AMORLINC',            'ACCRINT',             'ACOS',             'ACCRINTM',             'ACOSH',              ...,                   'AND',            'YEARFRAC',             'YIELDDISC',             'AMORDEGRC',             'YIELDMAT',             'YIELD',             'ZTEST'}) Let’s add some formulas to our spreadsheet. Let’s check the average star rating of  the 99 reviews within the spreadsheet: >>> # Star rating is in column "H"  >>> spreadsheet["P2"] = "=AVERAGE(H2:H100)" >>> workbook.save(filename = "first_example.xlsx")Now, if we open your spreadsheet and go to cell P2, you can see the value to be 4.18181818181818.  Similarly, we can use this methodology to include any formulas for our requirements in our spreadsheet. For example, if we want to count the number of helpful reviews: >>> # The helpful votes  counted in column "I"  >>> spreadsheet["P3"] = '=COUNTIF(I2:I100, ">0")' >>> workbook.save(filename = "first_example.xlsx") Adding Styles to the SpreadsheetIt is not so important and usually, we don’t use this in everyday code but for the sake of completeness, we will also understand this with the following example.Using openpyxl, we get multiple styling options such as including fonts, colors,  borders,and so on.Let’s have a look at an example:>>> # Import necessary style classes >>> from openpyxl.styles import Font,Color,Alignment,Border,Side,colors >>> # Create a few styles >>> Bold_Font = Font(bold=True) >>> Big_Red_Text = Font(color=colors.RED,size=20)  >>> Center_Aligned_Text = Alignment(horizontal="center")  >>> Double_Border_Side = Side(border_style="double")  >>> Square_Border = Border(top=double_border_side,  ... right=double_border_side,  ... bottom=double_border_side,  ... left=double_border_side)  >>> # Style some cells! >>> spreadsheet["A2"].font = Bold_Font >>> spreadsheet["A3"].font = Big_Red_Text >>> spreadsheet["A4"].alignment = Center_Aligned_Text >>> spreadsheet["A5"].border = Square_Border >>> workbook.save(filename="sample_styles.xlsx") If you want to apply multiple styles to one or several cells in our spreadsheets,you can use  NamedStyle class: >>> from openpyxl.styles import NamedStyle >>> # Let's create a style template for the header row >>> header = NamedStyle(name="header") >>> header.font = Font(bold=True) >>> header.border = Border(bottom=Side(border_style="thin")) >>> header.alignment = Alignment(horizontal="center",vertical="center") >>> # Now let's apply this to all first row (header) cells >>> header_row = sheet[1] >>> for cell in header_row: ... cell.style = header >>> workbook.save(filename="sample_styles.xlsx") Adding Charts to our SpreadsheetCharts are a good way to compute and understand large amounts of data quickly and easily. We have a lot of charts such as bar chart, pie chart, line chart, and so on. Let us start by creating a new workbook with some data:  1 from openpyxl import Workbook   2 from openpyxl.chart import BarChart,Reference   3    4 workbook = Workbook()   5 spreadsheet = workbook.active   6    7 # Let's create some sample sales data  8 rows = [   9    ["Product","Online","Store"],  10    [1,30,45],  11    [2,40,30],  12    [3,40,25],  13    [4,50,30],  14    [5,30,25],  15    [6,25,35],  16    [7,20,40],  17 ]  18   19 for row in rows: 20    spreadsheet .append(row)Now let us create a bar chart that will show the total number of sales per product: 22 chart = BarChart() 23 data = Reference(worksheet=sheet, 24                 min_row=1,  25                 max_row=8,  26                 min_col=2,  27                 max_col=3)  28   29 chart.add_data(data,titles_from_data=True) 30 spreadsheet .add_chart(chart, "E2") 31 32 workbook.save("chart.xlsx")You can also create a line chart by simply making some changes to the data:  1 import random   2 from openpyxl import Workbook   3 from openpyxl.chart import LineChart,Reference   4    5 workbook = Workbook()  6 sheet = workbook.active  7    8 # Let's create some sample sales data   9 rows= [ 10    ["", "January", "February", "March", "April",  11    "May", "June", "July", "August", "September",  12     "October", "November", "December"],  13    [1, ], 14    [2, ], 15    [3, ], 16 ]  17   18 for row in rows:  19    sheet.append(row) 20   21 for row in sheet.iter_rows(min_row=2, 22                           max_row=4, 23                           min_col=2, 24                           max_col=13): 25    for cell in row: 26        cell.value = random.randrange(5,100)There are numerous types of charts and various types of customizations you can apply to your spreadsheet to make it more attractive.Convert Python Classes to Excel SpreadsheetLet us now learn how to convert the Excel Spreadsheet data to Python classes.  Assume we have a database and we use some Object Relational mapping to map the database into Python classes and then export the objects into spreadsheets: from dataclasses import dataclass from typing import List @dataclass class Sale: id: str  quantity: int @dataclass  class Product:  id: str  name: str  sales:List[Sale] Now, to generate some random data, let’s assume that the above classes are stored in  db_classes.py file then:  1 import random   2    3 # Ignore these for now. You'll use them in a sec ;)   4 from openpyxl import Workbook   5 from openpyxl.chart import LineChart,Reference   6    7 from db_classes import Product,Sale   8    9 products_range = []  10   11 # Let's create 5 products 12 for idx in range(1,6): 13    sales = []  14   15    # Create 5 months of sales  16    for_in range(5): 17        sale_range = Sale(quantity=random.randrange(5,100)) 18        sales.append(sale) 19   20    product = Product(id=str(idx), 21                      name="Product %s" % idx, 22                      sales=sales) 23    products_range.append(product)By running this code, we will get 5 products in 5 months of sale with a random quantity of sales for each month. Now, we have  to convert this into a spreadsheet in which we need to iterate over the data: 25 workbook = Workbook()  26 spreadsheet = workbook.active  27 28 # Append column names first  29 spreadsheet.append(["Product ID","Product Name","Month 1",  30              "Month 2","Month 3","Month 4","Month 5"])  31   32 # Append the data  33 for product in products_range: 34    data = [product.id,product.name] 35    for sale in product.sales: 36        data.append(sale.quantity)  37    spreadsheet.append(data) This will create a spreadsheet with some data coming from your database. How to work with pandas to handle Spreadsheets?We have learned to work with Excel in Python because Excel is one of the most popular tools and finding a way to work with Excel is critical. Pandas is a great tool to work with Excel in Python. It has unique methods to read all kinds of data in an Excel file and we can export items back to Excel using it. To use it, at first we need to install pandas package: $ pip install pandas Then, let’s create a simple DataFrame:  1 import pandas as pd   2    3 data = {   4    "Product Name":["Product 1","Product 2"],   5    "Sales Month 1":[10, 20],   6    "Sales Month 2":[5, 35],   7 }   8 dataframe = pd.DataFrame(data)Now we have some data, and to convert it from a DataFrame into a worksheet we generally use .dataframe_to_rows(): 10 from openpyxl import Workbook 11 from openpyxl.utils.dataframe import  dataframe_to_rows  12   13 workbook = Workbook()  14 spreadsheet = workbook.active  15   16 for row in dataframe_to_rows(df, index=False,header=True):  17    spreadsheet .append(row) 18   19 workbook.save("pandas_spreadsheet.xlsx")We need to use  read_excel method to read data from pandas DataFrame object. excel_file =’movies.xls’  movies=pd.read_excel(excel_file) We can also use Excel file class to use multiple sheets from the same excel file: movies_sheets = [] for sheet in xlsx.sheet_names:     movies_sheets.append(xlsx.parse(sheet))     movies = pd.concat(movies_sheets))Indexes and columns allows you to access data from your DataFrame easily: >>> df.columns  Index(['marketplace', 'customer_id', 'review_id', 'product_id',        'product_parent', 'product_title', 'product_category', 'star_rating',         'helpful_votes', 'total_votes', 'vine', 'verified_purchase',         'review_headline', 'review_body', 'review_date'],        dtype='object') >>> # Get first 10 reviews' star rating  >>> df["star_rating"][:10] R3O9SGZBVQBV76    5 RKH8BNC3L5DLF     5  R2HLE8WKZSU3NL    2  R31U3UH5AZ42LL    5  R2SV659OUJ945Y    4  RA51CP8TR5A2L     5  RB2Q7DLDN6TH6     5  R2RHFJV0UYBK3Y    1  R2Z6JOQ94LFHEP    5  RX27XIIWY5JPB     4  Name: star_rating, dtype: int64 >>> # Grab review with id "R2EQL1V1L6E0C9", using the index >>> df.loc["R2EQL1V1L6E0C9"] marketplace               US customer_id         15305006  review_id     R2EQL1V1L6E0C9  product_id        B004LURNO6  product_parent     892860326  review_headline   Five Stars  review_body          Love it  review_date       2015-08-31  Name: R2EQL1V1L6E0C9, dtype: object Summary In this article we have covered: How to extract information from spreadsheets  How to create Spreadsheets in different ways How to customize a spreadsheet by adding filters, styles, or charts and so on How to use pandas to work with spreadsheets Now you are well aware of the different types of implementations you can perform with spreadsheets using Python. However, if you are willing to gather more information on this topic, you can always rely on the official documentation of openpyxl. To gain more knowledge about Python tips and tricks, check out our Python tutorial. To gain mastery over Python coding,join ourPython certification course.  
Rated 4.5/5 based on 22 customer reviews

How to Work with Excel Spreadsheets using Python

13937
How to Work with Excel Spreadsheets using Python

Excel is considered as one of the most popular and widely used spreadsheet applications developed by Microsoft. You can organize, analyze and store your data into tabular sheets with the help of Excel. From analysts and sales managers, to CEOs, professionals from every field use Excel for creating quick statistics and for data crunching.

Spreadsheets are commonly used in the present world because of their intuitive nature and the ability to handle large datasets. Most importantly, they can work without any prior technical background.

Finding different ways to work with Excel using code is essential since working with data and in Python has some serious advantages in comparison with Excel’s UI. Developers of Python have implemented ways to read, write and manipulate Excel documents.

You can check the quality of your spreadsheet application by going over the checklist below:

  • Is the spreadsheet able to represent static data?
  • Is the spreadsheet able to mix data, calculations, and reports?
  • Is the data in your spreadsheet complete and consistent in nature?
  • Does the spreadsheet have an organized worksheet structure?

This checklist will help you in verifying the qualitative nature of the spreadsheet application you’re going to work on.

Practical Applications  

In this article, we would be using openpyxl to work on data. With the help of this module, you can extract data from a database into an Excel spreadsheet or you can also convert an Excel spreadsheet into a programmatic format. There can be a lot of possible situations where you might feel the need to use a package like openpyxl. Let us discuss a few of them to get a comprehensive overview of it.

Importing New Products Into a Database 

Consider yourself working in an online store company. When they want to add new products to the online store, they make an Excel spreadsheet with a few hundred rows along with the name of the product, description, price and a few more basic information and then they give it to you. 

Now, if you want to import this particular data, you need to iterate over each row of the spreadsheet and then add each of the products into the database of the online store.[Text Wrapping Break] 

Exporting Database Data Into a Spreadsheet

Consider you have a Database table. In this particular table, you have collected information of all your users which includes their name, contact number, email address, and so forth. Now, the Marketing Team is willing to collectively contact all the users and promote a new product of the company. However, neither do they have access to the Database nor they have any idea about using SQL to extract the information. 

In this situation, openpyxl comes to play. You can use it effectively to iterate over each User record and transform the required information into an Excel spreadsheet.    

Appending Information to an Existing Spreadsheet

Consider the same online store example we discussed above. You have an Excel spreadsheet with a list of users and your job is to append to each row the total amount they have spent in your store.

In order to perform this, you have to read the spreadsheet first and then iterate through each row and fetch the total amount spent from the Database. Finally, you need to write it back to the spreadsheet.

Starting openpyxl

You can install the openpyxl package using pip. Open your terminal and write the following command: 

$ pip install openpyxl

After you have installed the spreadsheet, you can make up your own simple spreadsheet: 

from openpyxl import Workbook

workbook = Workbook()
spreadsheet = workbook.active

spreadsheet["A1"] = "Hello"
spreadsheet["B1"] = "World!"

workbook.save(filename="HelloWorld.xlsx")

How to Read Excel Spreadsheets with openpyxl 

Let us start with the most important thing that you can do with a spreadsheet,i.e. read it. We will be using a Watch Sample Dataset which contains a list of 100 watches with information like product name, product ID, review and so forth.  

A Simple Way to Read an Excel Spreadsheet 

Let us start with opening our sample spreadsheet:

>>> from openpyxl import load_workbook
>>> workbook = load_workbook(filename="sample.xlsx")
>>> workbook.sheetnames
['Sheet 1']

>>> spreadsheet = workbook.active
>>> spreadsheet
<Worksheet "Sheet 1">

>>> spreadsheet.title

In the example code above, we open the spreadsheet using load_workbook and then we check all the sheets that are available to work with using workbook.sheetnames. Then Sheet 1 is automatically selected using workbook.active since it is the first sheet available. This is the most common way of opening a spreadsheet.  

Now, let us see the code to retrieve data from the spreadsheet: 

>>> spreadsheet["A1"]
<Cell 'Sheet 1'.A1>

>>> spreadsheet["A1"].value
'marketplace'

>>> spreadsheet["F10"].value
"G-Shock Men's Grey Sport Watch"

You can retrieve the actual value and the cell value  both. To get the actual value, use .value and to get the cell, you can use .cell():

>>> spreadsheet.cell(row=10, column=6)
<Cell 'Sheet 1'.F10>

>>> spreadsheet.cell(row=10, column=6).value
"G-Shock Men's Grey Sport Watch"

Importing Data from a Spreadsheet 

In this section, we will discuss how to iterate through the data, and about conversion into a more useful format using Python.

Let us first start with iterating through the data. There are a number of iterating methods that depend solely on the user.

You can slice the data with a combination of rows and columns:

>>> spreadsheet["A1:C2"]
((<Cell 'Sheet 1'.A1>, <Cell 'Sheet 1'.B1>, <Cell 'Sheet 1'.C1>), 
 (<Cell 'Sheet 1'.A2>, <Cell 'Sheet 1'.B2>, <Cell 'Sheet 1'.C2>)) 

You can also iterate through the dataset by ranging between rows and columns: 

>>> # Get all cells from column A 
>>> spreadsheet["A"]
(<Cell 'Sheet 1'.A1>, 
 <Cell 'Sheet 1'.A2>, 
 ... 
 <Cell 'Sheet 1'.A99>, 
 <Cell 'Sheet 1'.A100>) 

>>> # Get all cells for a range of columns 
>>> spreadsheet["A:B"] 
((<Cell 'Sheet 1'.A1>, 
  <Cell 'Sheet 1'.A2>, 
  ... 
  <Cell 'Sheet 1'.A99>, 
  <Cell 'Sheet 1'.A100>), 
 (<Cell 'Sheet 1'.B1>, 
  <Cell 'Sheet 1'.B2>, 
  ... 
  <Cell 'Sheet 1'.B99>, 
  <Cell 'Sheet 1'.B100>)) 

>>> # Get all cells from row 5 
>>> spreadsheet[5]
(<Cell 'Sheet 1'.A5>,
 <Cell 'Sheet 1'.B5>,
 ... 
 <Cell 'Sheet 1'.N5>,
 <Cell 'Sheet 1'.O5>)

>>> # Get all cells for a range of rows
>>> spreadsheet[5:6]
((<Cell 'Sheet 1'.A5>, 
  <Cell 'Sheet 1'.B5>, 
  ... 
  <Cell 'Sheet 1'.N5>, 
  <Cell 'Sheet 1'.O5>), 
 (<Cell 'Sheet 1'.A6>, 
  <Cell 'Sheet 1'.B6>, 
  ... 
  <Cell 'Sheet 1'.N6>, 
  <Cell 'Sheet 1'.O6>)) 

Python offers arguments by which you can set limits to the iteration with the help of Python generators like .iter_rows() and .iter_cols()

>>> for row in spreadsheet.iter_rows(min_row=1,
...                           max_row=2,
...                           min_col=1,
...                           max_col=3):
...    print(row)
(<Cell 'Sheet 1'.A1>, <Cell 'Sheet 1'.B1>, <Cell 'Sheet 1'.C1>) 
(<Cell 'Sheet 1'.A2>, <Cell 'Sheet 1'.B2>, <Cell 'Sheet 1'.C2>)

>>> for column in spreadsheet.iter_cols(min_row=1...                              max_row=2,
...                              min_col=1,
...                              max_col=3):
... print(column)
(<Cell 'Sheet 1'.A1>, <Cell 'Sheet 1'.A2>) 
(<Cell 'Sheet 1'.B1>, <Cell 'Sheet 1'.B2>) 
(<Cell 'Sheet 1'.C1>, <Cell 'Sheet 1'.C2>) 

You can also add Boolean values_only in the above example and set it to True to get the values of cell: 

>>> for value in spreadsheet.iter_rows(min_row=1...                          max_row=2...                          min_col=1...                          max_col=3...                          values_only=True):
...    print(value)
('marketplace', 'customer_id', 'review_id') 
('US', 3653882, 'R3O9SGZBVQBV76')

Since we are now done with iterating the data, let us now manipulate data using Python’s primitive data structures. 

Consider a situation where you want to extract information of a product from the sample spreadsheet and then store it into the dictionary. The key to the dictionary would be the product ID.   

Convert Data into Python classes

To convert data into Python data classes, let us first decide what we want to store and how to store it.  

The two essential elements that can be extracted from the data are as follows:

Convert Data into Python classes

                                                     1. Products                                             2. Review

                                                          • ID                                                         • ID
                                                          • Title                                                     • Customers ID
                                                          • Parent                                                 • Headline
                                                          • Category                                            • Body
                                                                                                                         • Date

Let us implement the two elements: 

import datetime
from dataclasses import dataclass

@dataclass
class Product:
    id: str
    parent: str
    title: str
    category: str
@dataclass
class Review:
    id: str
    customer_id: str
    stars: int
    headline: str
    body: str 
    date: datetime.datetime 

The next step is to create a mapping between columns and the required fields: 

>>> for value in spreadsheet.iter_rows(min_row=1,
...                             max_row=1,
...                             values_only=True):
...    print(value)
('marketplace', 'customer_id', 'review_id', 'product_id', ...)

>>> # Or an alternative
>>> for cell in sheet[1]:
...  print(cell.value)
marketplace
Customer_ID
Review_ID
Product_ID
Product_Parent
...

Finally, let us convert the data into new structures which will parse the data in spreadsheet into a list of products and review objects: 

from datetime import datetime 
from openpyxl import load_workbook 
from classes import Product,Review 
from mapping import PRODUCT_ID,PRODUCT_PARENT,PRODUCT_TITLE, \
    PRODUCT_CATEGORY,REVIEW_DATE,REVIEW_ID,REVIEW_CUSTOMER, \
    REVIEW_STARS,REVIEW_HEADLINE,REVIEW_BODY

# Using the read_only method since you're not gonna be editing the spreadsheet 
workbook = load_workbook(filename="watch_sample.xlsx",read_only=True) 
spreadsheet = workbook.active

products = []
reviews = []

# Using the values_only because you just want to return the cell value
for row in spreadsheet .iter_rows(min_row=2, values_only=True): 
    product = Product(id=row[PRODUCT_ID], 
                      parent=row[PRODUCT_PARENT], 
                      title=row[PRODUCT_TITLE], 
                      category=row[PRODUCT_CATEGORY]) 
    products.append(product)
# You need to parse the date from the spreadsheet into a datetime format
spread_date = row[REVIEW_DATE] 
parsed_date = datetime.strptime(spread_date,"%Y-%m-%d")

review = Review(id=row[REVIEW_ID],
              Customer_ID=row[REVIEW_CUSTOMER],
              stars=row[REVIEW_STARS],
              headline=row[REVIEW_HEADLINE],
              body=row[REVIEW_BODY],
              date=parsed_date)
reviews.append(review)

print(products[0])
print(reviews[0])

After you execute the code, you will get an output that looks like this:

Product(id='A90FALZ1ZC',parent=937111370,...)
Review(id='D3O9OGZVVQBV76',customer_id=3903882,...)

Appending Data 

To understanding how to append data, let us hover back to the first sample spreadsheet. We will open the document and append some data to it: 

from openpyxl import load_workbook

# Start by opening the spreadsheet and selecting the main sheet 
workbook = load_workbook(filename="hello_world.xlsx")
spreadsheet = workbook.active

# Write what you want into a specific cell
spreadsheet["C1"]="Manipulating_Data ;)"

# Save the spreadsheet
workbook.save(filename="hello_world_append.xlsx"

If you open your Excel file, you will notice the additional Manipulating_Data being added to an adjacent cell. 

Writing Excel Spreadsheets With openpyxl 

A spreadsheet is a file that helps to store data in specific rows and columns. We can calculate and store numerical data and also perform computation using formulas. 

So, let’s begin with some simple Spreadsheets and understand what each line means. 

Creating our first simple Spreadsheet

 1 from openpyxl import Workbook
 2  
 3 filename = "first_program.xlsx"
 4  
 5 workbook = Workbook()
 6 spreadsheet = workbook.active
 7  
 8 sheet["A1"] = "first"
 9 sheet["B1"] = "program!"
10  
11 workbook.save(filename=filename)

Line 5: In order to make a Spreadsheet, at first,  we have to create an Empty workbook to perform further operations. 

Lines 8 and 9 : We can add data to a specific cell as per our requirement. In this example, we can see that two values “first” and “program” have been added to specific cells in the sheet. 

Line 11: The line shows how to save data after all the operations we have done. 

Basic Spreadsheet Operations 

Before going to the difficult coding part, at first we have to build our building blocks like how to add and update values, how to manage rows and columns, adding filters, styles or formulas in a Spreadsheet. 

We have already explained the following code by which we can add values to a Spreadsheet: 

>>> spreadsheet["A1"] = "the_value_we_want_to_add"

There is another way that we can add values to Spreadsheet: 

>>> cell = sheet["A1"]
>>> cell
<Cell 'Sheet'.A1>

>>> cell.value
'hello'

>>> cell.value = "hey"
>>> cell.value
'hey'

Line 1: In the first line at first we have declared the cell and updated its value. 

Line 5: We have printed the value of the cell as “first”  because  in the first program we have already assigned sheet["A1"]with “first” 

Line 8 : We have updated the value of the cell as "second"by simply assigning it to cell.value

Lines 9 : In this line, we have just printed the updated value of cell. 

Finally, you have to save all the operations you have performed into the spreadsheet once you call workbook.save().

If  the cell didn’t exist while adding a value ,then openpyxl creates a cell:

>>> # Before, our spreadsheet has only 1 row
>>> print_rows()
('first', 'program!')

>>> # Try adding a value to row 10
>>> spreadsheet["B10"] = "test"
>>> print_rows()
('first', 'program!') 
(None, None) 
(None, None) 
(None, None) 
(None, None) 
(None, None) 
(None, None) 
(None, None) 
(None, None) 
(None, 'test') 

Managing Rows and Columns in Spreadsheet 

Insertion or deletion of rows (adding or removing elements of rows /columns) is one of the most basic operations in Spreadsheet. In openpyxl.We can perform these operations by simply calling these methods and passing its arguments. 

  • .insert_rows()
  • .delete_rows()
  • .insert_cols()
  • .delete_cols()

We can pass 2 types of arguments to the methods :  

  1. idx 
  2. amount 

Idx stands for index position and amount refers to the number of values we can store in the Spreadsheet. 

Using our basic knowledge based on the first  simple program, let’s see how we can use these methods inside the program: 

>>> print_rows()
('first', 'program!')

>>> # Insert a column at the first position before column 1 ("A")
>>> spreadsheet.insert_cols(idx=1)
>>> print_rows()
(None, 'first', 'program!')

>>> # Insert 5 columns in  between column 2 ("B") and 3 ("C")
>>> spreadsheet.insert_cols(idx=3,amount=5)
>>> print_rows()
(None, 'first', None, None, None, None, None, 'program!')

>>> # Delete the created columns
>>> spreadsheet.delete_cols(idx=3,amount=5)
>>> v.delete_cols(idx=1)
>>> print_rows()
('first', 'program!')

>>> # Insert a new row in the beginning
>>> spreadsheet.insert_rows(idx=1)
>>> print_rows()
(None, None)
('first', 'program!')

>>> # Insert 3 new rows in the beginning 
>>> spreadsheet.insert_rows(idx=1,amount=3)
>>> print_rows()
(None, None) 
(None, None) 
(None, None) 
(None, None) 
('first', 'program!')

>>> # Delete the first 4 rows 
>>> spreadsheet.delete_rows(idx=1,amount=4>>> print_rows() 
('first', 'program!') 

Managing Sheets

We have seen the following recurring piece of code in our previous examples .This is one of the ways of selecting the default sheet from the Spreadsheet: 

spreadsheet = workbook.active

However, if you want to open a spreadsheet with multiple sheets, you can write the following command: 

>>> # Let's say you have two sheets: "Products" and "Company Sales"
>>> workbook.sheetnames
['Products', 'Company Sales']

>>> # You can select a sheet using its title
>>> Products_Sheet = workbook["Products"]
>>> Sales_sheet = workbook["Company Sales"]

If we want to change the title of the Sheet, execute the following code: 

>>> workbook.sheetnames
['Products', 'Company Sales']

>>> Products_Sheet = workbook["Products"]
>>> Products_Sheet.title = "New Products"

>>> workbook.sheetnames
['New Products', 'Company Sales']

We can CREATE / DELETE Sheets also with the help of two methods - .create_sheet() and  .remove()

>>> #To print the available sheet names
>>> workbook.sheetnames 
['Products', 'Company Sales']

>>> #To create a new Sheet named "Operations"
>>> Operations_Sheet = workbook.create_sheet("Operations")

>>> #To print the updated available sheet names
>>> workbook.sheetnames
['Products', 'Company Sales', 'Operations']

>>> # To define the position Where we want to create the Sheet(here “HR” sheet is created at the first position .Here index 0 represents the first position)
>>> HR_Sheet = workbook.create_sheet("HR",0)

>>> #To again  print the updated available sheet names
>>> workbook.sheetnames
['HR', 'Products', 'Company Sales', 'Operations']

>>> # To remove them,we just have to send the sheet names as an argument which we want to delete to the method  .remove() 
>>> workbook.remove(Operations_Sheet)
>>> workbook.sheetnames
['HR', 'Products', 'Company Sales']

>>> #To delete hr_sheet
>>> workbook.remove(hr_sheet)
>>> workbook.sheetnames
['Products', 'Company Sales']

Adding Filters to the Spreadsheet 

We can use openpyxl to add filters in our Spreadsheet but when we open our Spreadsheet, the data won’t be rearranged according to these sorts and filters. 

When you’re programmatically creating a spreadsheet and it is going to be sent and used by someone else, it is a good practice to add different filters and allow people to use it afterward. 

In the code below there is a simple example which shows how to add a simple filter to your spreadsheet: 

>>> # Check the used spreadsheet space using the attribute "dimensions"
>>> spreadsheet.dimensions
'A1:O100'

>>> spreadsheet.auto_filter.ref="A1:O100"
>>> workbook.save(filename="watch_sample_with_filters.xlsx")

Adding Formulas to the Spreadsheet 

Formulas are one of the most commonly used and powerful features of spreadsheets. By using formulas, you can solve various mathematical equations with the additional support of openpyxl which makes those calculations as simple as editing a specific cell’s value.

The list of formulas supported by openpyxl are:

>>> from openpyxl.utils import FORMULAE
>>> FORMULAE
frozenset({'ABS',
           'AMORLINC',
           'ACCRINT', 
           'ACOS', 
           'ACCRINTM', 
           'ACOSH', 
            ...,       
           'AND', 
           'YEARFRAC', 
           'YIELDDISC', 
           'AMORDEGRC', 
           'YIELDMAT', 
           'YIELD', 
           'ZTEST'}) 

Let’s add some formulas to our spreadsheet. 

Let’s check the average star rating of  the 99 reviews within the spreadsheet: 

>>> # Star rating is in column "H" 
>>> spreadsheet["P2"] = "=AVERAGE(H2:H100)"
>>> workbook.save(filename = "first_example.xlsx")

Now, if we open your spreadsheet and go to cell P2, you can see the value to be 4.18181818181818.  

Similarly, we can use this methodology to include any formulas for our requirements in our spreadsheet. For example, if we want to count the number of helpful reviews: 

>>> # The helpful votes  counted in column "I" 
>>> spreadsheet["P3"] = '=COUNTIF(I2:I100, ">0")' 
>>> workbook.save(filename = "first_example.xlsx") 

Adding Styles to the Spreadsheet

It is not so important and usually, we don’t use this in everyday code but for the sake of completeness, we will also understand this with the following example.

Using openpyxl, we get multiple styling options such as including fonts, colors,  borders,and so on.

Let’s have a look at an example:

>>> # Import necessary style classes
>>> from openpyxl.styles import Font,Color,Alignment,Border,Side,colors

>>> # Create a few styles
>>> Bold_Font = Font(bold=True)
>>> Big_Red_Text = Font(color=colors.RED,size=20>>> Center_Aligned_Text = Alignment(horizontal="center">>> Double_Border_Side = Side(border_style="double">>> Square_Border = Border(top=double_border_side, 
...                       right=double_border_side, 
...                       bottom=double_border_side, 
...                       left=double_border_side) 

>>> # Style some cells!
>>> spreadsheet["A2"].font = Bold_Font
>>> spreadsheet["A3"].font = Big_Red_Text
>>> spreadsheet["A4"].alignment = Center_Aligned_Text
>>> spreadsheet["A5"].border = Square_Border
>>> workbook.save(filename="sample_styles.xlsx") 

If you want to apply multiple styles to one or several cells in our spreadsheets,you can use  NamedStyle class: 

>>> from openpyxl.styles import NamedStyle

>>> # Let's create a style template for the header row
>>> header = NamedStyle(name="header")
>>> header.font = Font(bold=True)
>>> header.border = Border(bottom=Side(border_style="thin"))
>>> header.alignment = Alignment(horizontal="center",vertical="center")

>>> # Now let's apply this to all first row (header) cells
>>> header_row = sheet[1]
>>> for cell in header_row:
... cell.style = header

>>> workbook.save(filename="sample_styles.xlsx") 

Adding Charts to our Spreadsheet

Charts are a good way to compute and understand large amounts of data quickly and easily. We have a lot of charts such as bar chart, pie chart, line chart, and so on. 

Let us start by creating a new workbook with some data: 

 1 from openpyxl import Workbook 
 2 from openpyxl.chart import BarChart,Reference 
 3  
 4 workbook = Workbook() 
 5 spreadsheet = workbook.active 
 6  
 7 # Let's create some sample sales data 
 8 rows = [ 
 9    ["Product","Online","Store"], 
10    [1,30,45], 
11    [2,40,30], 
12    [3,40,25], 
13    [4,50,30], 
14    [5,30,25], 
15    [6,25,35], 
16    [7,20,40], 
17 ] 
18  
19 for row in rows:
20    spreadsheet .append(row)

Now let us create a bar chart that will show the total number of sales per product: 

22 chart = BarChart()
23 data = Reference(worksheet=sheet,
24                 min_row=1, 
25                 max_row=8, 
26                 min_col=2, 
27                 max_col=3) 
28  
29 chart.add_data(data,titles_from_data=True)
30 spreadsheet .add_chart(chart, "E2")
31
32 workbook.save("chart.xlsx")

You can also create a line chart by simply making some changes to the data: 

 1 import random 
 2 from openpyxl import Workbook 
 3 from openpyxl.chart import LineChart,Reference 
 4  
 5 workbook = Workbook()
 6 sheet = workbook.active
 7  
 8 # Let's create some sample sales data 
 9 rows= [
10    ["", "January", "February", "March", "April", 
11    "May", "June", "July", "August", "September", 
12     "October", "November", "December"], 
13    [1, ],
14    [2, ],
15    [3, ],
16 ] 
17  
18 for row in rows: 
19    sheet.append(row)
20  
21 for row in sheet.iter_rows(min_row=2,
22                           max_row=4,
23                           min_col=2,
24                           max_col=13):
25    for cell in row:
26        cell.value = random.randrange(5,100)

There are numerous types of charts and various types of customizations you can apply to your spreadsheet to make it more attractive.

Convert Python Classes to Excel Spreadsheet

Let us now learn how to convert the Excel Spreadsheet data to Python classes.  

Assume we have a database and we use some Object Relational mapping to map the database into Python classes and then export the objects into spreadsheets: 

from dataclasses import dataclass
from typing import List

@dataclass
class Sale:
    id: str 
    quantity: int

@dataclass 
class Product: 
    id: str 
    name: str 
    sales:List[Sale] 

Now, to generate some random data, let’s assume that the above classes are stored in  db_classes.py file then: 

 1 import random 
 2  
 3 # Ignore these for now. You'll use them in a sec ;) 
 4 from openpyxl import Workbook 
 5 from openpyxl.chart import LineChart,Reference 
 6  
 7 from db_classes import Product,Sale 
 8  
 9 products_range = [] 
10  
11 # Let's create 5 products
12 for idx in range(1,6):
13    sales = [] 
14  
15    # Create 5 months of sales 
16    for_in range(5):
17        sale_range = Sale(quantity=random.randrange(5,100))
18        sales.append(sale)
19  
20    product = Product(id=str(idx),
21                      name="Product %s" % idx,
22                      sales=sales)
23    products_range.append(product)

By running this code, we will get 5 products in 5 months of sale with a random quantity of sales for each month. 

Now, we have  to convert this into a spreadsheet in which we need to iterate over the data: 

25 workbook = Workbook() 
26 spreadsheet = workbook.active 
27
28 # Append column names first 
29 spreadsheet.append(["Product ID","Product Name","Month 1", 
30              "Month 2","Month 3","Month 4","Month 5"]) 
31  
32 # Append the data 
33 for product in products_range:
34    data = [product.id,product.name]
35    for sale in product.sales:
36        data.append(sale.quantity) 
37    spreadsheet.append(data) 

This will create a spreadsheet with some data coming from your database. 

How to work with pandas to handle Spreadsheets?

How to work with pandas to handle Spreadsheets

We have learned to work with Excel in Python because Excel is one of the most popular tools and finding a way to work with Excel is critical. Pandas is a great tool to work with Excel in Python. It has unique methods to read all kinds of data in an Excel file and we can export items back to Excel using it. 

To use it, at first we need to install pandas package: 

$ pip install pandas 

Then, let’s create a simple DataFrame: 

 1 import pandas as pd 
 2  
 3 data = { 
 4    "Product Name":["Product 1","Product 2"], 
 5    "Sales Month 1":[10, 20], 
 6    "Sales Month 2":[5, 35], 
 7 } 
 8 dataframe = pd.DataFrame(data)

Now we have some data, and to convert it from a DataFrame into a worksheet we generally use .dataframe_to_rows()

10 from openpyxl import Workbook
11 from openpyxl.utils.dataframe import  dataframe_to_rows 
12  
13 workbook = Workbook() 
14 spreadsheet = workbook.active 
15  
16 for row in dataframe_to_rows(df, index=False,header=True): 
17    spreadsheet .append(row)
18  
19 workbook.save("pandas_spreadsheet.xlsx")

We need to use  read_excel method to read data from pandas DataFrame object. 

excel_file =’movies.xls’ 
movies=pd.read_excel(excel_file) 

We can also use Excel file class to use multiple sheets from the same excel file: 

movies_sheets = []
for sheet in xlsx.sheet_names:
    movies_sheets.append(xlsx.parse(sheet))
    movies = pd.concat(movies_sheets))

Indexes and columns allows you to access data from your DataFrame easily: 

>>> df.columns 
Index(['marketplace', 'customer_id', 'review_id', 'product_id', 
       'product_parent', 'product_title', 'product_category', 'star_rating', 
       'helpful_votes', 'total_votes', 'vine', 'verified_purchase', 
       'review_headline', 'review_body', 'review_date'], 
      dtype='object') 
>>> # Get first 10 reviews' star rating 
>>> df["star_rating"][:10]
R3O9SGZBVQBV76    5 
RKH8BNC3L5DLF     5 
R2HLE8WKZSU3NL    2 
R31U3UH5AZ42LL    5 
R2SV659OUJ945Y    4 
RA51CP8TR5A2L     5 
RB2Q7DLDN6TH6     5 
R2RHFJV0UYBK3Y    1 
R2Z6JOQ94LFHEP    5 
RX27XIIWY5JPB     4 
Name: star_rating, dtype: int64

>>> # Grab review with id "R2EQL1V1L6E0C9", using the index
>>> df.loc["R2EQL1V1L6E0C9"]
marketplace               US 
customer_id         15305006 
review_id     R2EQL1V1L6E0C9 
product_id        B004LURNO6 
product_parent     892860326 
review_headline   Five Stars 
review_body          Love it 
review_date       2015-08-31 
Name: R2EQL1V1L6E0C9, dtype: object 

Summary 

In this article we have covered: 

  • How to extract information from spreadsheets  
  • How to create Spreadsheets in different ways 
  • How to customize a spreadsheet by adding filters, styles, or charts and so on 
  • How to use pandas to work with spreadsheets 

Now you are well aware of the different types of implementations you can perform with spreadsheets using Python. However, if you are willing to gather more information on this topic, you can always rely on the official documentation of openpyxl. To gain more knowledge about Python tips and tricks, check out our Python tutorial. To gain mastery over Python coding,join ourPython certification course

 

Priyankur

Priyankur Sarkar

Data Science Enthusiast

Priyankur Sarkar loves to play with data and get insightful results out of it, then turn those data insights and results in business growth. He is an electronics engineer with a versatile experience as an individual contributor and leading teams, and has actively worked towards building Machine Learning capabilities for organizations.

Join the Discussion

Your email address will not be published. Required fields are marked *

Suggested Blogs

How to Round Numbers in Python

While you are dealing with data, sometimes you may come across a biased dataset. In statistics, bias is whereby the expected value of the results differs from the true underlying quantitative parameter being estimated. Working with such data can be dangerous and can lead you to incorrect conclusions. To learn more about various other concepts of Python, go through our Python Tutorials or enroll to our Python Certification course online.There are many types of biases such as selection bias, reporting bias, sampling bias and so on. Similarly, rounding bias is related to numeric data. In this article we will see:Why is it important to know the ways to round numbersHow to use various strategies to round numbersHow data is affected by rounding itHow to use NumPy arrays and Pandas DataFrames to round numbersLet us first learn about Python’s built-in rounding process.About Python’s Built-in round() FunctionPython Programming offers a built-in round() function which rounds off a number to the given number of digits and makes rounding of numbers easier. The function round() accepts two numeric arguments, n and n digits and then returns the number n after rounding it to ndigits. If the number of digits are not provided for round off, the function rounds off the number n to the nearest integer.Suppose, you want to round off a number, say 4.5. It will be rounded to the nearest whole number which is 5. However, the number 4.74 will be rounded to one decimal place to give 4.7.It is important to quickly and readily round numbers while you are working with floats which have many decimal places. The inbuilt Python function round() makes it simple and easy.Syntaxround(number, number of digits)The parameters in the round() function are:number - number to be roundednumber of digits (Optional) - number of digits up to which the given number is to be rounded.The second parameter is optional. In case, if it is missing then round() function returns:For an integer, 12, it rounds off to 12For a decimal number, if the last digit after the decimal point is >=5 it will round off to the next whole number, and if =5 print(round(5.476, 2))     # when the (ndigit+1)th digit is  1 print(round("x", 2)) TypeError: type str doesn't define __round__ methodAnother example,print(round(1.5)) print(round(2)) print(round(2.5))The output will be:2 2 2The function round() rounds 1.5 up to 2, and 2.5 down to 2. This is not a bug, the round() function behaves this way. In this article you will learn a few other ways to round a number. Let us look at the variety of methods to round a number.Diverse Methods for RoundingThere are many ways to round a number with its own advantages and disadvantages. Here we will learn some of the techniques to rounding a number.TruncationTruncation, as the name means to shorten things. It is one of the simplest methods to round a number which involves truncating a number to a given number of digits. In this method, each digit after a given position is replaced with 0. Let us look into some examples.ValueTruncated ToResult19.345Tens place1019.345Ones place1919.345Tenths place19.319.345Hundredths place19.34The truncate() function can be used for positive as well as negative numbers:>>> truncate(19.5) 19.0 >>> truncate(-2.852, 1) -2.8 >>> truncate(2.825, 2) 2.82The truncate() function can also be used to truncate digits towards the left of the decimal point by passing a negative number.>>> truncate(235.7, -1) 230.0 >>> truncate(-1936.37, -3) -1000.0When a positive number is truncated, we are basically rounding it down. Similarly, when we truncate a negative number, the number is rounded up. Let us look at the various rounding methods.Rounding UpThere is another strategy called “rounding up” where a number is rounded up to a specified number of digits. For example:ValueRound Up ToResult12.345Tens place2018.345Ones place1918.345Tenths place18.418.345Hundredths place18.35The term ceiling is used in mathematics to explain the nearest integer which is greater than or equal to a particular given number. In Python, for “rounding up” we use two functions namely,ceil() function, andmath() functionA non-integer number lies between two consecutive integers. For example, considering a number 5.2, this will lie between 4 and 5. Here, ceiling is the higher endpoint of the interval, whereas floor is the lower one. Therefore, ceiling of 5.2 is 5, and floor of 5.2 is 4. However, the ceiling of 5 is 5.In Python, the function to implement the ceiling function is the math.ceil() function. It always returns the closest integer which is greater than or equal to its input.>>> import math >>> math.ceil(5.2) 6 >>> math.ceil(5) 5 >>> math.ceil(-0.5) 0If you notice you will see that the ceiling of -0.5 is 0, and not -1.Let us look into a short code to implement the “rounding up” strategy using round_up() function:def round_up(n, decimals=0):     multiplier = 10 ** decimals     return math.ceil(n * multiplier) / multiplierLet’s look at how round_up() function works with various inputs:>>> round_up(3.1) 4.0 >>> round_up(3.23, 1) 3.3 >>> round_up(3.543, 2) 3.55You can pass negative values  to decimals, just like we did in truncation.>>> round_up(32.45, -1) 40.0 >>> round_up(3352, -2) 3400You can follow the diagram below to understand round up and round down. Round up to the right and down to the left.Rounding up always rounds a number to the right on the number line, and rounding down always rounds a number to the left on the number line.Rounding DownSimilar to rounding up we have another strategy called rounding down whereValueRounded Down ToResult19.345Tens place1019.345Ones place1919.345Tenths place19.319.345Hundredths place19.34In Python, rounding down can be implemented using a similar algorithm as we truncate or round up. Firstly you will have to shift the decimal point and then round an integer. Lastly shift the decimal point back.math.ceil() is used to round up to the ceiling of the number once the decimal point is shifted. For “rounding down” we first need to round the floor of the number once the decimal point is shifted.>>> math.floor(1.2) 1 >>> math.floor(-0.5) -1Here’s the definition of round_down():def round_down(n, decimals=0):     multiplier = 10 ** decimals return math.floor(n * multiplier) / multiplierThis is quite similar to round_up() function. Here we are using math.floor() instead of math.ceil().>>> round_down(1.5) 1 >>> round_down(1.48, 1) 1.4 >>> round_down(-0.5) -1Rounding a number up or down has extreme effects in a large dataset. After rounding up or down, you can actually remove a lot of precision as well as alter computations.Rounding Half UpThe “rounding half up” strategy rounds every number to the nearest number with the specified precision, and breaks ties by rounding up. Here are some examples:ValueRound Half Up ToResult19.825Tens place1019.825Ones place2019.825Tenths place19.819.825Hundredths place19.83In Python, rounding half up strategy can be implemented by shifting the decimal point to the right by the desired number of places. In this case you will have to determine whether the digit after the shifted decimal point is less than or greater than equal to 5.You can add 0.5 to the value which is shifted and then round it down with the math.floor() function.def round_half_up(n, decimals=0):     multiplier = 10 ** decimals return math.floor(n*multiplier + 0.5) / multiplierIf you notice you might see that round_half_up() looks similar to round_down. The only difference is to add 0.5 after shifting the decimal point so that the result of rounding down matches with the expected value.>>> round_half_up(19.23, 1) 19.2 >>> round_half_up(19.28, 1) 19.3 >>> round_half_up(19.25, 1) 19.3Rounding Half DownIn this method of rounding, it rounds to the nearest number similarly like “rounding half up” method, the difference is that it breaks ties by rounding to the lesser of the two numbers. Here are some examples:ValueRound Half Down ToResult16.825Tens place1716.825Ones place1716.825Tenths place16.816.825Hundredths place16.82In Python, “rounding half down” strategy can be implemented by replacing math.floor() in the round_half_up() function with math.ceil() and then by subtracting 0.5 instead of adding:def round_half_down(n, decimals=0):     multiplier = 10 ** decimals return math.ceil(n*multiplier - 0.5) / multiplierLet us look into some test cases.>>> round_half_down(1.5) 1.0 >>> round_half_down(-1.5) -2.0 >>> round_half_down(2.25, 1) 2.2In general there are no bias for both round_half_up() and round_half_down(). However, rounding of data with more number of ties results in bias. Let us consider an example to understand better.>>> data = [-2.15, 1.45, 4.35, -12.75]Let us compute the mean of these numbers:>>> statistics.mean(data) -2.275Now let us compute the mean on the data after rounding to one decimal place with round_half_up() and round_half_down():>>> rhu_data = [round_half_up(n, 1) for n in data] >>> statistics.mean(rhu_data) -2.2249999999999996 >>> rhd_data = [round_half_down(n, 1) for n in data] >>> statistics.mean(rhd_data) -2.325The round_half_up() function results in a round towards positive infinity bias, and round_half_down() results in a round towards negative infinity bias.Rounding Half Away From ZeroIf you have noticed carefully while going through round_half_up() and round_half_down(), neither of the two is symmetric around zero:>>> round_half_up(1.5) 2.0 >>> round_half_up(-1.5) -1.0 >>> round_half_down(1.5) 1.0 >>> round_half_down(-1.5) -2.0In order to introduce symmetry, you can always round a tie away from zero. The table mentioned below illustrates it clearly:ValueRound Half Away From Zero ToResult16.25Tens place2016.25Ones place1616.25Tenths place16.3-16.25Tens place-20-16.25Ones place-16-16.25Tenths place-16.3The implementation of “rounding half away from zero” strategy on a number n is very simple. All you need to do is start as usual by shifting the decimal point to the right a given number of places and then notice the digit d immediately to the right of the decimal place in this new number. Here, there are four cases to consider:If n is positive and d >= 5, round upIf n is positive and d < 5, round downIf n is negative and d >= 5, round downIf n is negative and d < 5, round upAfter rounding as per the rules mentioned above, you can shift the decimal place back to the left.There is a question which might come to your mind - How do you handle situations where the number of positive and negative ties are drastically different? The answer to this question brings us full circle to the function that deceived us at the beginning of this article: Python’s built-in  round() function.Rounding Half To EvenThere is a way to mitigate rounding bias while you are rounding values in a dataset. You can simply round ties to the nearest even number at the desired precision. Let us look at some examples:ValueRound Half To Even ToResult16.255Tens place2016.255Ones place1616.255Tenths place16.216.255Hundredths place16.26To prove that round() really does round to even, let us try on a few different values:>>> round(4.5) 4 >>> round(3.5) 4 >>> round(1.75, 1) 1.8 >>> round(1.65, 1) 1.6The Decimal ClassThe  decimal module in Python is one of those features of the language which you might not be aware of if you have just started learning Python. Decimal “is based on a floating-point model which was designed with people in mind, and necessarily has a paramount guiding principle – computers must provide an arithmetic that works in the same way as the arithmetic that people learn at school.” – except from the decimal arithmetic specification. Some of the benefits of the decimal module are mentioned below -Exact decimal representation: 0.1 is actually 0.1, and 0.1 + 0.1 + 0.1 - 0.3 returns 0, as expected.Preservation of significant digits: When you add 1.50 and 2.30, the result is 3.80 with the trailing zero maintained to indicate significance.User-alterable precision: The default precision of the decimal module is twenty-eight digits, but this value can be altered by the user to match the problem at hand.Let us see how rounding works in the decimal module.>>> import decimal >>> decimal.getcontext() Context(     prec=28,     rounding=ROUND_HALF_EVEN,     Emin=-999999,     Emax=999999,     capitals=1,     clamp=0,     flags=[],     traps=[         InvalidOperation,         DivisionByZero,         Overflow     ] )The function decimal.getcontext() returns a context object which represents the default context of the decimal module. It also includes the default precision and the default rounding strategy.In the above example, you will see that the default rounding strategy for the decimal module is ROUND_HALF_EVEN. It allows to align with the built-in round() functionLet us create a new Decimal instance by passing a string containing the desired value and declare a number using the decimal module’s Decimal class.>>> from decimal import Decimal >>> Decimal("0.1") Decimal('0.1')You may create a Decimal instance from a floating-point number but in that case, a floating-point representation error will be introduced. For example, this is what happens when you create a Decimal instance from the floating-point number 0.1>>> Decimal(0.1) Decimal('0.1000000000000000055511151231257827021181583404541015625')You may create Decimal instances from strings containing the decimal numbers you need in order to maintain exact precision.Rounding a Decimal using the .quantize() method:>>> Decimal("1.85").quantize(Decimal("1.0")) Decimal('1.8')The Decimal("1.0") argument in .quantize() allows to determine the number of decimal places in order to round the number. As 1.0 has one decimal place, the number 1.85 rounds to a single decimal place. Rounding half to even is the default strategy, hence the result is 1.8.Decimal class:>>> Decimal("2.775").quantize(Decimal("1.00")) Decimal('2.78')Decimal module provides another benefit. After performing arithmetic the rounding is taken care of automatically and also the significant digits are preserved.>>> decimal.getcontext().prec = 2 >>> Decimal("2.23") + Decimal("1.12") Decimal('3.4')To change the default rounding strategy, you can set the decimal.getcontect().rounding property to any one of several  flags. The following table summarizes these flags and which rounding strategy they implement:FlagRounding Strategydecimal.ROUND_CEILINGRounding updecimal.ROUND_FLOORRounding downdecimal.ROUND_DOWNTruncationdecimal.ROUND_UPRounding away from zerodecimal.ROUND_HALF_UPRounding half away from zerodecimal.ROUND_HALF_DOWNRounding half towards zerodecimal.ROUND_HALF_EVENRounding half to evendecimal.ROUND_05UPRounding up and rounding towards zeroRounding NumPy ArraysIn Data Science and scientific computation, most of the times we store data as a  NumPy array. One of the most powerful features of NumPy is the use of  vectorization and broadcasting to apply operations to an entire array at once instead of one element at a time.Let’s generate some data by creating a 3×4 NumPy array of pseudo-random numbers:>>> import numpy as np >>> np.random.seed(444) >>> data = np.random.randn(3, 4) >>> data array([[ 0.35743992,  0.3775384 ,  1.38233789,  1.17554883],        [-0.9392757 , -1.14315015, -0.54243951, -0.54870808], [ 0.20851975, 0.21268956, 1.26802054, -0.80730293]])Here, first we seed the np.random module to reproduce the output easily. Then a 3×4 NumPy array of floating-point numbers is created with np.random.randn().Do not forget to install pip3 before executing the code mentioned above. If you are using  Anaconda you are good to go.To round all of the values in the data array, pass data as the argument to the  np.around() function. The desired number of decimal places is set with the decimals keyword argument. In this case, round half to even strategy is used similar to Python’s built-in round() function.To round the data in your array to integers, NumPy offers several options which are mentioned below:numpy.ceil()numpy.floor()numpy.trunc()numpy.rint()The np.ceil() function rounds every value in the array to the nearest integer greater than or equal to the original value:>>> np.ceil(data) array([[ 1.,  1.,  2.,  2.],        [-0., -1., -0., -0.], [ 1., 1., 2., -0.]])Look at the code carefully, we have a new number! Negative zero! Let us now take a look at Pandas library, widely used in Data Science with Python.Rounding Pandas Series and DataFramePandas has been a game-changer for data analytics and data science. The two main data structures in Pandas are Dataframe and Series. Dataframe works like an Excel spreadsheet whereas you can consider Series to be columns in a spreadsheet. Series.round() and DataFrame.round() methods. Let us look at an example.Do not forget to install pip3 before executing the code mentioned above. If you are using  Anaconda you are good to go.>>> import pandas as pd >>> # Re-seed np.random if you closed your REPL since the last example >>> np.random.seed(444) >>> series = pd.Series(np.random.randn(4)) >>> series 0    0.357440 1    0.377538 2    1.382338 3    1.175549 dtype: float64 >>> series.round(2) 0    0.36 1    0.38 2    1.38 3    1.18 dtype: float64 >>> df = pd.DataFrame(np.random.randn(3, 3), columns=["A", "B", "C"]) >>> df           A         B         C 0 -0.939276 -1.143150 -0.542440 1 -0.548708  0.208520  0.212690 2  1.268021 -0.807303 -3.303072 >>> df.round(3)        A      B      C 0 -0.939 -1.143 -0.542 1 -0.549  0.209  0.213 2  1.268 -0.807 -3.303 The DataFrame.round() method can also accept a dictionary or a Series, to specify a different precision for each column. For instance, the following examples show how to round the first column of df to one decimal place, the second to two, and the third to three decimal places: >>> # Specify column-by-column precision with a dictionary >>> df.round({"A": 1, "B": 2, "C": 3})      A     B      C 0 -0.9 -1.14 -0.542 1 -0.5  0.21  0.213 2  1.3 -0.81 -3.303 >>> # Specify column-by-column precision with a Series >>> decimals = pd.Series([1, 2, 3], index=["A", "B", "C"]) >>> df.round(decimals)      A     B      C 0 -0.9 -1.14 -0.542 1 -0.5  0.21  0.213 2  1.3 -0.81 -3.303 If you need more rounding flexibility, you can apply NumPy's floor(), ceil(), and print() functions to Pandas Series and DataFrame objects: >>> np.floor(df)      A    B    C 0 -1.0 -2.0 -1.0 1 -1.0  0.0  0.0 2  1.0 -1.0 -4.0 >>> np.ceil(df)      A    B    C 0 -0.0 -1.0 -0.0 1 -0.0  1.0  1.0 2  2.0 -0.0 -3.0 >>> np.rint(df)      A    B    C 0 -1.0 -1.0 -1.0 1 -1.0  0.0  0.0 2  1.0 -1.0 -3.0 The modified round_half_up() function from the previous section will also work here: >>> round_half_up(df, decimals=2)       A     B     C 0 -0.94 -1.14 -0.54 1 -0.55  0.21  0.21 2 1.27 -0.81 -3.30Best Practices and ApplicationsNow that you have come across most of the rounding techniques, let us learn some of the best practices to make sure we round numbers in the correct way.Generate More Data and Round LaterSuppose you are dealing with a large set of data, storage can be a problem at times. For example, in an industrial oven you would want to measure the temperature every ten seconds accurate to eight decimal places, using a temperature sensor. These readings will help to avoid large fluctuations which may lead to failure of any heating element or components. We can write a Python script to compare the readings and check for large fluctuations.There will be a large number of readings as they are being recorded each and everyday. You may consider to maintain three decimal places of precision. But again, removing too much precision may result in a change in the calculation. However, if you have enough space, you can easily store the entire data at full precision. With less storage, it is always better to store at least two or three decimal places of precision which are required for calculation.In the end, once you are done computing the daily average of the temperature, you may calculate it to the maximum precision available and finally round the result.Currency Exchange and RegulationsWhenever we purchase an item from a particular place, the tax amount paid against the amount of the item depends largely on geographical factors. An item which costs you $2 may cost you less (say $1.8)  if you buy the same item from a different state. It is due to regulations set forth by the local government.In another case, when the minimum unit of currency at the accounting level in a country is smaller than the lowest unit of physical currency, Swedish rounding is done. You can find a list of such rounding methods used by various countries if you look up on the internet.If you want to design any such software for calculating currencies, keep in mind to check the local laws and regulations applicable in your present location.Reduce errorAs you are rounding numbers in a large datasets used in complex computations, your primary concern should be to limit the growth of the error due to rounding.SummaryIn this article we have seen a few methods to round numbers, out of those “rounding half to even” strategy minimizes rounding bias the best. We are lucky to have Python, NumPy, and Pandas already have built-in rounding functions to use this strategy. Here, we have learned about -Several rounding strategies, and how to implement in pure Python.Every rounding strategy inherently introduces a rounding bias, and the “rounding half to even” strategy mitigates this bias well, most of the time.You can round NumPy arrays and Pandas Series and DataFrame objects.If you enjoyed reading this article and found it to be interesting, leave a comment. To learn more about rounding numbers and other features of Python, join our Python certification course.
Rated 5.0/5 based on 43 customer reviews
12995
How to Round Numbers in Python

While you are dealing with data, sometimes you may... Read More

Swift Vs Python

Programming Languages: Their popularity Every passing year witnesses changes in the preferences of programming languages. Some of them get knocked off the perch, while others continue growing. In recent years, two programming languages stand out from the rest and are rapidly growing in popularity. Those two are Swift and Python. In this article, we will talk about the attributes of Swift and Python, their pros and cons and how they are similar to each other. Read along to know more. What they are  Swift and Python. One is a general-purpose, multi-paradigm, object-oriented, functional, imperative and block-structured language while the latter is a widely-used general-purpose, high-level programming language.  Python was originally designed by Guido van Rossum in 1991 and further developed by Python Software Foundation. It was developed to stress code readability along with its syntax enables programmers to code less to express their concepts. It helps coders to speed up the workflow and integrate systems more efficiently. In a survey by Stack Overflow in 2017, Python was the fastest-growing programming language. This resulted in numerous companies prominently using Python as their programming language, the list including Quora, Netflix, Dropbox, Reddit, Facebook, Spotify, Instagram, etc.  In terms of Python’s usability shown above, Data analysis goes first, followed by web development, machine learning, and DevOps. However, Python is less used for educational purposes, prototyping, and Quality Assurance Services. Now talking about Swift, it was designed and released in 2014 after conducting fresh research on programming languages and by using a modern approach to safety, software design patterns by Apple Inc. It is a completely new programming language for the iOS application, macOS application, watchOS application, tvOS application. Needless to say, it quickly grew to be one of the top 5 programming languages and became the most used programming language among the Apple developer community within a short span of than 5 years, also effectively replacing the previously used Objective C. Let us share an important piece of information with you. According to a survey done on the most popular programming languages, Python takes the first spot with overwhelming popularity with a share of 25.36%, whereas Swift is climbing up the ladder at the 9th spot with 2.69% share. The table is mentioned below:Advantages and disadvantages of using Python  Advantages In this section, we will focus on the criteria that make Python a truly developer-friendly language. As we learnt that Python has its uses in numerous lines of work, we will find out how it ticks the checkboxes of the required criteria. Simplicity and readability One of the prime benefits of using Python is that it is simple to code and read. Of course, it is not a repetitive language to follow but is very similar to English and hence is easy to follow. Moreover, Python is a good choice for beginners in programming Multi-paradigm It is a programming language that is object-oriented as well as procedural. Its procedural paradigm allows reuse code and object-oriented methodology allows varied inheritances and summarising data and functions as one Open-source As Python is open source, you can download and modify its source code. This versatile feature led to the formation of a strong community that keeps growing stronger Integration with other languages Being an extensible and embeddable language, programmers can easily integrate Python to Java applications, C, and C++ Portability and compatibility Python is compatible with various platforms. If required, users do not require to change the code before moving the project. to be moved to another platform Vast collection of libraries Being in the game for a long time, Python boasts having a strong community with a vast range of libraries and frameworks for different purposes. providing programmers with a wide spectrum of opportunities. Additionally, libraries like Pandas, Plotly, NumPy, Pipenv, and others and are included as well. Django, Flask, CherryPy, and PyTorch are.among the most famous frameworks. Disadvantages With the pros come the cons. There is also the other side of a coin that needs attention. In spite of having a long history in the programming world, Python still has several weak sides. Not ideal for Mobile development Python is not a good solution for mobile developers. However, a come-around solution with a few challenges is Kivy - a cross-platform Python framework for developing mobile apps Design restrictions There are specific design limitations in Python. Being dynamically typed language using duck typing, Python automatically identifies a type of a variable and can cause runtime errors Although it is not frequent, it does make errors at times.  Memory consumption Python consumes high memory and is definitely not a good option to run intensive memory tasks. Swift pros and cons Being a relatively new programming language, Swift was launched at the WWDC conference in 2014. According to Apple, the primary features of Swift is that it is fast, modern and interactive. Swift's creator Chris Lattner his creation was a result of ideas inspired by different languages such as C#, Ruby, and especially by Python. That's why we can easily find a couple of similarities between Swift and Python. Nevertheless, let’s see what the pros and cons of Swift are. Advantages Easy to read and maintain The Swift program codes are based on English as it acquired syntaxes from other programming languages, thus making the language more expressive Scalable More features to Swift, so it is a scalable programming language. Swift has already replaced Objective C and Swift is what Apple is relying on Concise Swift does not include long lines of code and that favours the developers who want a concise syntax, thus increasing the development and testing rate of the program Safety and improved performance Almost 40% better than the Objective-C, Swift is handier to tackle the bugs that lead to safer programming when speed and performance is concerned Cross-device support This language can handle a wide range of Apple platforms such as iOS, iOS X, macOS, tvOS, and watchOS Automatic Memory Management This feature prevents memory leaks and helps in optimising the application’s performance that is done by using Automatic Reference Counting. Disadvantages Compatibility issues The updated versions Swift is observed to be a bit unstable with the newer versions of Apple leading to a few issues. Switching to a newer version of Swift is the fix but that is costly Speed Issues This is relevant to the earlier versions of the Swift programming language Less in number: The number of Swift developers is limited as Swift is a new programming language Delay in uploading apps Developers will be facing delays over their apps written in Swift to be uploaded to the App Store only after iOS 8 and Xcode 6 are released. The estimated time for release is reported to be September-October, 2014. Common attributes of Swift and Python Swift and Python are predominantly contrasting languages. Despite that, the do possess some common traits. Let’s see what they are. Both Swift and Python have a distinct syntax and are very similar to the English language. Missing semicolons while coding in either Swift or Python will not result in errors. Both languages have a REPL environment that aids in detecting errors in code and debugging Both are multi-paradigm programming languages They have additional tools to facilitate learning. What makes Swift and Python different from each other? From the previous discussions in this article, it is crystal clear that Swift and Python are fundamentally different from each other. Apple’s Swift is ideal for developing software for the Apple ecosystem while Python can be utilised for use cases but is mainly applied in back-end development. Moreover, as Apple claims, Swift is 8.4x faster than Python in terms of performance. Choosing between Swift and Python depends on the intent of the programmer. If the purpose is developing mobile applications that need to work flawlessly in the Apple platforms, then Swift is the ultimate choice. However, if the intentions are to develop artificial intelligence, design a prototype or build the backend, then Python is the one. In the end, what matters is the intent So now we see that in fact choosing Python or Swift for coding mostly depends on your purpose. If you are fond of developing mobile applications that will work seamlessly on Apple operating systems, you should definitely choose Swift. Python is good in case you want to develop your own artificial intelligence, build the backend or create a prototype. There is no hiding the fact that both Swift and Python are good at what they do. While Python has been a game-changer for years, Swift has been rapidly rising up the ranks. Comparing the two directly is a bit unjust as each one of the two has their own uses. The best person to select the right programming language is you! So be the judge of your decision. Good luck! 
Rated 4.5/5 based on 19 customer reviews
9987
Swift Vs Python

Programming Languages: Their popularity Every pas... Read More

Top 10 Python IDEs and Code Editors

Over the years, Python language has evolved enormously with the contribution of developers. Python is one of the most popular programming languages. It was designed primarily for server-side web development, software development, evaluation, scripting and artificial intelligence. For this feature Python encloses certain code editors and IDEs that are used for software development say, Python itself. If you are new to programming, learning Python is highly recommended as it is fast, efficient and easy to learn. Python interpreters are available on various operating systems such as Windows, Linux, Mac OS. This article provides a look into code editors and IDEs along with their features, pros and cons and talks about which are the best suited for writing Python codes. But first let us see what are code editors and IDEs. What is a Code Editor? A code editor is built for editing and modifying source code. A standalone text editor is used for writing and editing computer programs. Excellent ones can execute code as well as control a debugger as well as interact with source control systems. Compared to an IDE, a good dedicated code editor is usually smaller and quicker, but is less functional. Typically they are optimized for programming languages. One major feature of a text editor is that they are designed to modify various files and work with whatever language or framework you choose. What is IDE? IDE (Integrated Development Environment) understands the code significantly better than a text editor. It is a program exclusively built for software development. It is designed with a set of tools that all work together:  Text editor  Compiler Build automation Debugging Libraries, and many more to speed up the work.  These tools integrate: An editor designed to frame codes with text formatting, auto-completionetc., build, execution, debugging tools, file management and source and version control. It reduces manual efforts and combines all the equipment in a typical framework. IDE comes with heavy files. Hence, the downloads and installation is quite tedious. IDE requires expertise along with a lot of patience.  How does an IDE and Code editor differ from each other? An IDE is distinctive from code editors in the following ways: Integrated build process:The user does not have to write his own scripts to build apps in an IDE.  File management: IDE has an integrated file management system and deployment tool. It provides support to other framework as well. On the other hand, a Text editor is a simple editor where source code can be edited and it has no other formatting or compiling options. Development Environment: An IDE is mainly used for development purposes as it provides comparatively better features than a text editor. It allows you to write, compile and debug the entire script.  Syntax Highlighting:The editor displays the text message and puts the source code in different colours to improve its readability. Even error messages are displayed in different colours so that the user understands where he has written the wrong code.  Auto completion:It identifies and inserts a common code for the user instantly. This feature acts as an assistance for the programmer. The code suggestion automatically gets displayed.  Debugger: This tool helps the programmer to test and debug the source code of the main program.  Although IDEs have far better features than a Text editor one major significance of Text editor is that it allows modifying all types of files rather than specifying any definite language or types. Features For a good software development, we need code editors and IDEs which help the developer to automate the process of editing, compiling, testing, debugging and much more. Some of the features of these editors are listed below: Good user interface: They allow users to interact and run programs easily. Incredibly fast: Although these IDEs need to import heavy libraries, compile and debug, they offer fast compilation and run time.  Syntax stylizing: Codes are colorized automatically and syntax is highlighted.    Debugging tool: Itruns the code, set breakpoints, examine the variables. Provides good language syntax: IDEs usually work on a specific language but the others are designed for multi-language support. Code editors are designed with multi-language support.  Good source and version control environment: IDEs come with source control feature to keep a track of changes made in source code and other text files during the development of any software. Intelligent code completion:This feature speeds up the coding process by automatically suggesting for incomplete codes. It reduces typos and other common mistakes. Why do we need a good coding environment? For a good software development one seeks a better coding environment. Although features vary from app to app, a definite set of features is required for one. There are many other things involved such as source code control, extension tools, language support etc. Listed below are the core features which make a good coding environment : Retrieve files: All the codes written in an IDE get saved. Also, the programmer can retrieve his code file at the same state where the work is left off. Run within the environment: It should be able to compile and run within the environment where the codes are written. No external file shall be needed to be downloaded for the execution of the programs.  Good Debugging Tool: An IDE or editor should be able to diagnose and  troubleshoot the programmer’s works and highlight the lines with errors if any. A pop-up window should display the error message. This way the programmer can keep a track of his errands and diagnose them.   Automatic formatting tool: Indentation is done automatically as soon as the programmer moves onto the next line. It keeps the code clean and readable. Quick highlighting: keywords, variables and symbols are highlighted. This feature keeps the code clean and easy to understand. Also, pops up the variables making them easy to spot. This makes it a whole lot easier to pick out portions of code than simply looking at a wall of undifferentiated text. Some of the IDEs and code editors There are various Python IDEs and text editors. Some of the IDEs and text editors along with their features and pros and cons are mentioned below: IDLEKey Features: It is an open source IDE entirely written in Python. It is mainly supported by WINDOWS, LINUX, MAC OS etc.. IDLE is a decent IDE for learning because it is lightweight and quite simple to use. IDLE is installed by default as soon as installation of Python is complete. This makes it easier to get started in Python. IDLE features include the Python shell window(interactive interpreter), auto-completion, syntax highlighting, smart indentation, and a basic integrated debugger. It is however not suitable for the completion of larger projects and best suitable for educational purposes only.  Pros A cross-platform where a developer can search within any window, search through multiple files and replace within the windows editor  Supports syntax highlighting, auto code completion, smart indentation and editable configurations Includes Python shell with highlighter Powerful Integrated Debugger with continuous breakpoints, global view, and local spaces Improves the performance  Call stack visibility Increases the flexibility for developers Cons Used for programming just for beginners Limited to handle normal usage issues. Supports basic design  Large software development cannot be handled  Sublime text Key Features: It is a source code editor, supported on all platforms. It is a very popular cross-platform  and a better text editor. It possesses a built-in support for Python for code editing and packages to extend the syntax and editing features. All Sublime Text packages are written in Python and also a Python API. Installation of the packages often requires you to execute scripts directly in Sublime Text. it is designed to support huge programming and markup languages. Additional functions can be applied by the user with the help of plugins.  Pros More reliable for developers and is cross-platform Supports GOTO anything to access files  Generates wide index of each method, class, and function. AllowsUser interface toolkit Easy navigation to words or symbols Multiple selections to change things at one time Offers command palette to sort, edit and modify the syntax and maintain the indentation.  Offers powerful API and package ecosystem Great performance Highly customizable Allows split editing and instant project switch  Better compatibility with language grammar Custom selection on specific projects Cons Not free Installation of extensions is quite tricky Does not support for direct executing or debugging code from within the editor Less active GIT plugin AtomKey Features: It is an open source code editor developed by Github. It is supported on all platforms. It has features similar to that of Python. It has a framework based on atom shells which help to achieve cross platform functionality. With a sleek interface, file system browser, and marketplace for extensions, it offers a framework for creating desktop applications using JavaScript, HTML, CSS . Extensions can be installed when Atom is running.It enables support for third party packages. Its major feature is that although it is a code editor,it can also be used as an IDE. It is also used for educational purposes. Atom is being improvised day by day, striving to make the user experience rewarding and not remain confined to beginners use only.  Pros Cross-platform  Smooth editing Improves performance of its users Offers built-in package manager and file system browser Faster scripting  Offers smart auto-completion  Smart and flexible Supports multiple pane features Easy navigation across an application Simple to use Allows user interface customization Full support from GitHub Quick access to data and information Cons For beginners only Tedious for sorting configurations and plugins Clumsy tabs reduce performance  Slow loading Runs on JavaScript process  Built on Electron, does not run as a native application VimKey Features: Categorized as a stable open source code editor, VI and VIM are modal editors. As it is supported on almost every platform such as: Windows, LINUX, MAC OS, IOS, Android, UNIX, AmigaOS, MorphOS etc. it is highly configurable. Because of its modal mode of operation, it differs from most other text editors. It possesses three basic modes: insert mode, normal or command mode and command line mode. It is easily customized by the addition of extensions and configuration which makes it easily adaptable for Python development.  Pros Free and easily accessible Customizable and persistent  Has a multi-level undo tree  Extensions are added manually Configuration file is modified Multi-buffers support simultaneous file editing Automated indentation  Good user interface Recognition and conversion of file formats Exclusive libraries including wide range of languages Comes with own scripting language with powerful integration, search and replace functionality Extensive system of plugins Allows debugging and refactoring  Provides two different modes to work: normal and editing mode Strings in VIM can be saved and reused  Cons Used as a text editor only No different color for the pop-up option Not good for beginners PyDev Key Features: It is also categorized as an open source IDE mainly written with JAVA.Since it is an eclipse plugin, the Java IDE is transformed into Python IDE. Its integration with Django gives a Python framework. It also has keyword auto-completion, good debugging tool, syntax highlighting and indentation. Pros Free open source Robust IDE feature set Auto-completion of codes and analysis Smart indentation Interactive console shortcuts Integrated with Django configuration  Platform independent Cons: User interface is not great  Visual studioKey Features: It is categorized as an IDE, is a full-featured IDE developed by Microsoft. It is compatible with Windows and Mac OS only and comes with free as well as paid versions. It has its own marketplace for extensions. PTVS(Python Tools for Visual Studio) offers various features as in coding for Python development, IntelliSense, debugging, refactoring etc. Pros Easy and less tedious installation for development purposes Cons Spacious files  Not supported on Linux Visual studio code Key Features: VS code is a code editor and is way more different from VS. It is a free open source code editor developed by Microsoft can be run on platforms such as Windows, Linux and Mac OS X.  It has a full-featured editor that is highly configurable with Python compatibility for software development. Python tools can be added to enable coding in Python.VS code is integrated with Git which promotes it to perform operations like push, commit directly from the editor itself. It also has electron framework for Node JS applications running on the Blink browser engine. It is enclosed with smart code completion with function definition, imported modules and variable types. Apart from these, VS code also comes with syntax highlighting, a debugging console and proprietary IntelliSense code auto completion. After installing Python, VS code recognizes Python files and libraries immediately.  Pros Free and available on every platform  Small, light-weight but highly extensible Huge compatibility Has a powerful code management system Enables debugging from the editor Multi-language support  Extensive libraries Smart user interface and an acceptable layout Cons Slow search engine Tedious launch time Not a native app just like Atom WingKey Features: Wing is also one of the powerful IDEs today and comes with a lot of good features. It is an open source IDE used commercially. It also is constituted with a strong framework and has a strong debugger and smart editor for Python development making it fast, accurate and fun to perform. It comes with a 30 day trial version. It supports text driven development with unit test, PyTest and Django testing framework.  Pros Open source Find and go-to definition Customizable and extensible Auto-code completion Quick Troubleshoot  Source browser shows all the variables used in the script Powerful debugger  Good refactoring  Cons Not capable of supporting dark themes Wing interface is quite intimidating Commercial version is expensive Python-specific IDEs and Editors Anaconda - Jupyter NotebooksKey Features: It is also an open source IDE with a server-client structure, used to create and edit the codes of a Python. Once it is saved, you can share live code equations, visualizations and text. It has anaconda distribution i.e., libraries are preinstalled so downloading the anaconda itself does the task. It supports Python and R language which are installed by default at installation.  This IDE is again used for data science learning. Quite easy to use, it is not just used as an editor but also as an educational tool or presentation. It supports numerical simulation, machine  learning visualization and statistical modelling. Pros Free Open source  Good user interface Server-client structure Educational tool- Data science, Machine learning  Supports numerical simulation  Enables to create, write, edit and insert images Combines code, text and images Integrated libraries - Matplotlib, NumPy, Pandas Multi-language support Auto code completion Cons Sometimes slow loading is experienced Google Colaboratory Key Features: It is the simplest web IDE used for Python. It gives a free GPU access. Instead of downloading heavy files and tedious launch time, one can directly update the files from Colab to the drive. All you need to do is log in to your google account and open Colab. There is no need for extra setup. Unlike other IDEs no files are required to download. Google provides free computation resources with Colaboratory. It is designed for creating machine learning models. For compilation and execution, all you need to do is to update Python package and get started.   Pros Available to all Code can be run without any interruption Highly user interactive No heavy file downloads Integrated libraries Multi-language support Updated in google drive Update the Python package for execution  Runs on cloud Comments can be added in cells Can import Jupiter or IPython notebooks Cons  All colaboratory files are to be stored in google drive Install all specific libraries No access to unsaved files once the session is over Pycharm Key Features: Developed by Jet Brains and one of the widely used full-featured Python IDE, this is a cross-platform IDE for Python programming and  is well-integrated with Python console and IPython Notebook. It is supported by Windows, Linux, Mac OS and other platforms as well. It has massive productivity and saves ample amount of time. It comes with smart code navigation, code editor, good debugging tool, quick refactoring etc. and supports Python web development frameworks such as Angular JS, JavaScript, CSS, HTML  and live editing functions. The paid version offers advanced features such as full database management and a multitude Framework than the community version such as Django, Flask, Google App, Engine, Pyramid and web2py. Pros Great supportive community Brilliant performance. Amazing editing tools Robust debugging tool Smart code navigation Quick and safe refactoring  Built in developer tools Error detection and fix up suggestions Customizable interface Available in free and paid version Cons Slow loading  Installation is quite difficult and may hang up in between SpyderKey Features: It is an open source IDE supported on all platforms. Ranked as one of the best Python compilers, it supports syntax highlighting, auto completion of codes just like Pycharm. It offers an advanced level of editing, debugging, quick diagnose, troubleshoot and many data exploration features. To get started with Spyder, one needs to install anaconda distribution which is basically used in data science and machine learning. Just like Pycharm it has IntelliSense auto-completion of code. Spyder is built on a structured and powerful framework which makes it one of the best IDE used so far. It is most commonly used for scientific development. Pros Free open source IDE Quick troubleshoot Active framework Smart editing and debugging Syntax is automatically highlighted Auto completion of codes Good for data science and machine learning Structured framework Integrates common Python data science libraries like SciPy, NumPy, and Matplotlib Finds and eliminates bottlenecks Explores and edits variables directly from GUI  Performs well in multi-language editor and auto completion mode Cons Spyder is not capable to configure a specific warning Too many plugins degrades its performance ThonnyKey Features: Thonny is another IDE best suited for beginners for Python development and provides a good virtual environment. It is supported on all platforms. It gives a simple debugger with F5, F6 and F7 keys for debugging. Also, Thonny supports highlighting errors, good representation of function calls, auto code completion and smart indentation. It even allows the developers to configure their code and shell commands. by default,  in Thonny Python is pre-installed as it downloads with its own version of Python.  Pros Simple Graphical user interface.  Free open source IDE Best for beginners Simple debugger with F5, F6, F7 Keys Tackles issues with Python interpreters Highlights syntax error Auto-completion of code Good representation of function calls User can change reference mode easily Step through expression evaluation Reply and resolve to comments Cons Interface is not that good for developers Confined to text editing No template support Slow plugin creation Too basic IDE for software development Which Python IDE is right for you? Requirements vary from programmer to programmer. It is one’s own choice to pick the right tool that is best suited for the task at hand. Beginners need to use a simple tool with few customizations whereas experts require tools with advanced features to bring new updates. Few suggestions are listed below:- Beginners should start with IDLE and Thonny as they do not have complex features and are pretty easy to learn. For data science learners Jupyter Notebooks and Google Colaboratory is preferred. Generally, large scale enterprises prefer the paid versions of IDEs like PyCharm, Atom, Sublime Text etc. in order to get extensive service support from the company. Also, they provide easy finance options and manpower. On the other hand, middle and small scale enterprises tend to look for open source tools which provides them with excellent features. Some of such IDEs are Spyder, Pydev, IDLE and Visual Studio. Conclusion Today, Python stands out as one of the most popular programming languages worldwide. IDE being a program dedicated to software development has made it easier for developers to build, execute, and debug their codes. Code editors can only be used for editing codes whereas an IDE is a feature rich editor which has inbuilt text editor, compiler, debugging tool and libraries. Different IDEs and code editors are detailed in this article along with their merits and demerits. Some are suitable for beginners because of their lightweight nature and simplicity like IDLE, Thonny whereas experts require advance featured ones for building software.  For learning purposes say data science, machine learning Jupyter and Google Colaboratory are strongly recommended. Again there are large scale enterprises who prefer PyCharm, Atom, Sublime Text for software development. On the other hand, small scale enterprises prefer Spyder, Pydev, IDLE and Visual Studio. Hence,the type of IDE or code editor that should be used completely depends upon the requirement of the programmer . To gain more knowledge about Python tips and tricks, check our Python tutorial and get a good hold over coding in Python by joining the Python certification course. 
Rated 4.5/5 based on 19 customer reviews
10088
Top 10 Python IDEs and Code Editors

Over the years, Python language has evolved enormo... Read More