Which Type of File Used in Excel Would Be Best to Read and Write in Python Software?
Trending weblog posts
How to Piece of work with Excel Spreadsheets using Python
16200
- by Priyankur Sarkar
- 31st Oct, 2019
- Last updated on 23rd Feb, 2022
- 16 mins read
Excel is considered one of the near popular and widely used spreadsheet applications developed past 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. These features make excel one of the almost pop data sources in Python programs.
Spreadsheets are commonly used in the nowadays world because of their intuitive nature and the ability to handle large datasets. Nearly importantly, they can work without whatever prior technical background.
Finding dissimilar ways to piece of work with Excel using code is essential since working with data and in Python has some serious advantages in comparing with Excel's UI. Developers of Python have implemented ways to read, write and manipulate Excel documents. An instructor-led python programming grade is a expert fashion to get hands-on experience of using excel within Python.
You tin check the quality of your spreadsheet awarding by going over the checklist beneath:
- 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 commodity, we would exist using openpyxl to piece of work on data. With the help of this module, you can excerpt data from a database into an Excel spreadsheet or you can also convert an Excel spreadsheet into a programmatic format. There tin be a lot of possible situations where you might experience the demand to utilise a bundle similar openpyxl. Let the states 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 desire 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, clarification, price, and a few more bones information so they give it to y'all.
At present, if you want to import this detail data, y'all demand to iterate over each row of the spreadsheet and so add together each of the products into the database of the online shop.
Exporting Database Data Into a Spreadsheet
Consider yous take a Database tabular array. In this particular table, you accept collected information of all your users which includes their name, contact number, email accost, and so forth. Now, the Marketing Team is willing to collectively contact all the users and promote a new production of the company. Withal, neither do they take access to the Database nor do they have whatever thought most using SQL to excerpt the information.
In this state of affairs, openpyxl comes to play. You lot tin utilise it finer to iterate over each User record and transform the required information into an Excel spreadsheet.
Appending Information to an Existing Spreadsheet
Consider the aforementioned online store example we discussed to a higher place. 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 social club to perform this, you have to read the spreadsheet first then iterate through each row and fetch the total corporeality 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 control:
$ pip install openpyxl Afterwards you have installed the spreadsheet, you can make up your own simple spreadsheet:
from openpyxl import Workbook workbook = Workbook() spreadsheet = workbook.agile 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 yous can exercise with a spreadsheet,i.due east. read it. We will exist using a Watch Sample Dataset which contains a list of 100 watches with information like product name, product ID, review, and then forth.
A Simple Way to Read an Excel Spreadsheet
Let us beginning 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 i"> >>> spreadsheet.championship
In the instance code above, we open up the spreadsheet using load_workbook and so we check all the sheets that are bachelor to work with using workbook.sheetnames . And so Sail 1 is automatically selected using workbook.active since it is the first sheet available. This is the near common way of opening a spreadsheet.
Now, permit united states of america see the lawmaking to recall data from the spreadsheet:
>>> spreadsheet["A1"] <Cell 'Sail 1'.A1> >>> spreadsheet["A1"].value 'market' >>> spreadsheet["F10"].value "G-Shock Men's Grayness Sport Watch"
You can recollect the actual value and the cell value both. To become the actual value, use .value and to go the cell, you tin utilize .cell() :
>>> spreadsheet.cell(row= 10, column= vi) <Jail cell 'Sheet 1'.F10> >>> spreadsheet.cell(row= x, column= half dozen).value "G-Daze Men's Grey Sport Lookout man"
Importing Data from a Spreadsheet
In this department, we will discuss how to iterate through the data, and about conversion into a more useful format using Python.
Let united states of america first first with iterating through the data. There are a number of iterating methods that depend solely on the user.
You tin slice the data with a combination of rows and columns:
>>> spreadsheet["A1:C2"] ((<Cell 'Sheet one'.A1>, <Cell 'Sheet 1'.B1>, <Cell 'Sheet ane'.C1>), (<Cell 'Sail 1'.A2>, <Cell 'Sheet 1'.B2>, <Cell 'Sheet 1'.C2>))
You tin can as well iterate through the dataset by ranging betwixt rows and columns:
>>> # Become all cells from cavalcade A >>> spreadsheet["A"] (<Cell 'Sheet ane'.A1>, <Cell 'Sheet 1'.A2>, ... <Prison cell 'Sail i'.A99>, <Cell 'Sail i'.A100>) >>> # Get all cells for a range of columns >>> spreadsheet["A:B"] ((<Cell 'Canvas 1'.A1>, <Cell 'Sheet 1'.A2>, ... <Cell 'Canvass 1'.A99>, <Cell 'Sheet 1'.A100>), (<Jail cell 'Sheet i'.B1>, <Cell 'Sheet one'.B2>, ... <Prison cell 'Sheet 1'.B99>, <Cell 'Sheet 1'.B100>)) >>> # Get all cells from row v >>> spreadsheet[5] (<Cell 'Sheet 1'.A5>, <Cell 'Sheet ane'.B5>, ... <Cell 'Sheet 1'.N5>, <Cell 'Sail ane'.O5>) >>> # Go all cells for a range of rows >>> spreadsheet[five:half dozen] ((<Cell 'Canvas i'.A5>, <Prison cell 'Sheet ane'.B5>, ... <Cell 'Canvass 1'.N5>, <Jail cell 'Sheet 1'.O5>), (<Prison cell 'Sheet 1'.A6>, <Cell 'Sheet 1'.B6>, ... <Prison cell 'Sheet one'.N6>, <Cell 'Sail ane'.O6>))
Python offers arguments past which you tin 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= i, ... max_row= ii, ... min_col= one, ... max_col= iii): ... print(row) (<Jail cell 'Sheet one'.A1>, <Cell 'Sheet 1'.B1>, <Cell 'Canvas 1'.C1>) (<Cell 'Sheet 1'.A2>, <Prison cell 'Sail 1'.B2>, <Cell 'Sheet 1'.C2>) >>> for column in spreadsheet.iter_cols(min_row= one, ... max_row= 2, ... min_col= one, ... max_col= 3): ... impress(column) (<Cell 'Sheet ane'.A1>, <Prison cell 'Sheet 1'.A2>) (<Cell 'Canvass i'.B1>, <Cell 'Canvass one'.B2>) (<Cell 'Sail 1'.C1>, <Jail cell 'Canvas 1'.C2>)
You can also add Boolean values_only in the to a higher place 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= i, ... 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 the states now manipulate information using Python's primitive data structures.
Consider a situation where you lot want to extract information most a product from the sample spreadsheet and then shop it in the dictionary. The primal to the dictionary would be the product ID.
Convert Data into Python classes
To convert data into Python data classes, let united states commencement decide what we want to store and how to store it.
The two essential elements that can exist extracted from the information are equally follows:
i. Products2. Review
• ID • ID
• Title • Customers ID
• Parent • Headline
• Category • Torso
• Engagement
Let usa implement the two elements:
import datetime from dataclasses import dataclass @dataclass class Production: id: str parent: str title: str category: str @dataclass course Review: id: str customer_id: str stars: int headline: str body: str date: datetime.datetime
The adjacent footstep is to create a mapping between columns and the required fields:
>>> for value in spreadsheet.iter_rows(min_row= one, ... max_row= one, ... values_only= True): ... print(value) ('marketplace', 'customer_id', 'review_id', 'product_id', ...) >>> # Or an culling >>> for jail cell in sheet[one]: ... print(prison cell.value) marketplace Customer_ID Review_ID Product_ID Product_Parent ...
Finally, let us catechumen the data into new structures which will parse the data in spreadsheet into a listing 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 simply want to return the prison cell value for row in spreadsheet .iter_rows(min_row= ii, values_only= True): production = Product(id =row[PRODUCT_ID], parent=row[PRODUCT_PARENT], title=row[PRODUCT_TITLE], category=row[PRODUCT_CATEGORY]) products.append(production) # 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.suspend(review) impress(products[0]) print(reviews[0])
After you execute the code, you lot will get an output that looks like this:
Product(id = 'A90FALZ1ZC',parent= 937111370,...) Review(id = 'D3O9OGZVVQBV76',customer_id= 3903882,...)
Appending Data
To sympathise how to append data, permit us hover back to the first sample spreadsheet. Nosotros will open the document and append some data to information technology:
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 lot want into a specific cell spreadsheet["C1"]= "Manipulating_Data ;)" # Relieve the spreadsheet workbook.save(filename= "hello_world_append.xlsx"
If you open your Excel file, y'all volition notice the additional Manipulating_Data being added to an adjacent cell.
Writing Excel Spreadsheets With openpyxl
A spreadsheet is a file that helps to shop data in specific rows and columns. Nosotros can calculate and store numerical data and besides perform ciphering using formulas.
So, let's brainstorm with some simple Spreadsheets and empathize what each line ways.
Creating our starting time unproblematic Spreadsheet
one from openpyxl import Workbook two 3 filename = "first_program.xlsx" four v workbook = Workbook() six spreadsheet = workbook.active 7 viii sheet["A1"] = "first" 9 sheet["B1"] = "programme!" 10 11 workbook.save(filename=filename)
Line 5:In order to make a Spreadsheet, at get-go, we have to create an Empty workbook to perform farther operations.
Lines 8 and 9: Nosotros can add information to a specific cell as per our requirement. In this example, we can run across that two values "start" and "program" have been added to specific cells in the sheet.
Line 11: The line shows how to salvage data after all the operations we have done.
Basic Spreadsheet Operations
Earlier going to the difficult coding part, where a python advanced certification might be required to country a job, you demand to strengthen the building blocks like how to add and update values, how to manage rows and columns, adding filters, styles, or formulas in a Spreadsheet.
Nosotros take already explained the post-obit code past which we can add values to a Spreadsheet:
>>> spreadsheet["A1"] = "the_value_we_want_to_add"
There is another way that we tin can add values to the Spreadsheet:
>>> prison cell = canvass["A1"] >>> jail cell <Cell 'Sheet'.A1> >>> cell.value 'hi' >>> cell.value = "hey" >>> jail cell.value 'hey'
Line 1:In the first line at beginning we have declared the cell and updated its value.
Line 5:We take printed the value of the cell as "starting time" because in the beginning program we have already assigned sheet["A1"] with "beginning"
Line 8:We have updated the value of the prison cell as "second" past simply assigning it to cell.value .
Lines 9:In this line, we accept just printed the updated value of jail cell.
Finally, y'all take to save all the operations y'all have performed into the spreadsheet one time y'all call workbook.save() .
If the cell didn't be while adding a value, then openpyxl creates a cell:
>>> # Earlier, our spreadsheet has only i row >>> print_rows() ('offset', 'program!') >>> # Endeavor adding a value to row 10 >>> spreadsheet["B10"] = "exam" >>> print_rows() ('kickoff', 'program!') (None, None) (None, None) (None, None) (None, None) (None, None) (None, None) (None, None) (None, None) (None, 'exam')
Managing Rows and Columns in Spreadsheet
Insertion or deletion of rows (adding or removing elements of rows /columns) is 1 of the nigh basic operations in Spreadsheet. In openpyxl.We can perform these operations past simply calling these methods and passing its arguments.
- .insert_rows()
- .delete_rows()
- .insert_cols()
- .delete_cols()
We can pass two types of arguments to the methods :
- idx
- amount
Idx stands for index position and amount refers to the number of values we tin can store in the Spreadsheet.
Using our basic knowledge based on the first uncomplicated program, permit's see how nosotros tin can use these methods inside the programme:
>>> print_rows() ('first', 'program!') >>> # Insert a column at the first position before column one ("A") >>> spreadsheet.insert_cols(idx= 1) >>> print_rows() (None, 'first', 'programme!') >>> # Insert 5 columns in between column 2 ("B") and iii ("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() ('beginning', 'programme!') >>> # Insert a new row in the beginning >>> spreadsheet.insert_rows(idx= one) >>> print_rows() (None, None) ('first', 'program!') >>> # Insert 3 new rows in the offset >>> spreadsheet.insert_rows(idx= ane,amount= 3) >>> print_rows() (None, None) (None, None) (None, None) (None, None) ('start', 'plan!') >>> # Delete the beginning iv rows >>> spreadsheet.delete_rows(idx= ane,amount= 4) >>> print_rows() ('first', 'program!')
Managing Sheets
Nosotros have seen the following recurring piece of code in our previous examples. This is i of the means of selecting the default sheet from the Spreadsheet:
spreadsheet = workbook.active
All the same, if you lot want to open up a spreadsheet with multiple sheets, yous tin can write the post-obit command:
>>> # Let'southward say you accept ii 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["Visitor 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', 'Visitor Sales']
Nosotros can CREATE / DELETE Sheets also with the help of 2 methods - .create_sheet() and .remove() :
>>> #To print the available sheet names >>> workbook.sheetnames ['Products', 'Company Sales'] >>> #To create a new Canvas named "Operations" >>> Operations_Sheet = workbook.create_sheet("Operations") >>> #To print the updated bachelor canvas names >>> workbook.sheetnames ['Products', 'Company Sales', 'Operations'] >>> # To define the position Where we desire to create the Sheet(here "60 minutes" canvass is created at the starting time position .Hither index 0 represents the kickoff 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 take to send the sheet names as an argument which nosotros 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 tin can use openpyxl to add filters in our Spreadsheet just when we open our Spreadsheet, the data won't exist rearranged according to these sorts and filters.
When you lot're programmatically creating a spreadsheet and information technology is going to be sent and used by someone else, it is a good exercise to add different filters and permit people to apply it afterward.
In the code below at that place is a simple example that 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 normally 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 past openpyxl are:
>>> from openpyxl.utils import FORMULAE >>> FORMULAE frozenset({'ABS', 'AMORLINC', 'ACCRINT', 'ACOS', 'ACCRINTM', 'ACOSH', ..., 'AND', 'YEARFRAC', 'YIELDDISC', 'AMORDEGRC', 'YIELDMAT', 'YIELD', 'ZTEST'})
Allow'south add some formulas to our spreadsheet.
Allow's check the average star rating of the 99 reviews within the spreadsheet:
>>> # Star rating is in cavalcade "H" >>> spreadsheet["P2"] = "=Average(H2:H100)" >>> workbook.salve(filename = "first_example.xlsx")
Now, if we open your spreadsheet and go to cell P2, yous can see the value to be four.18181818181818.
Similarly, we can use this methodology to include whatever formulas for our requirements in our spreadsheet. For case, if nosotros desire to count the number of helpful reviews:
>>> # The helpful votes counted in cavalcade "I" >>> spreadsheet["P3"] = '=COUNTIF(I2:I100, ">0")' >>> workbook.salve(filename = "first_example.xlsx")
Calculation Styles to the Spreadsheet
It is non then important and usually, we don't utilise this in everyday code only for the sake of completeness, we will also understand this with the post-obit example.
Using openpyxl , nosotros get multiple styling options such as including fonts, colors, borders,and so on.
Let's have a look at an example:
>>> # Import necessary manner classes >>> from openpyxl.styles import Font,Color,Alignment,Border,Side,colors >>> # Create a few styles >>> Bold_Font = Font(bold= Truthful) >>> Big_Red_Text = Font(color=colors.Cherry,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 tin use NamedStyle class:
>>> from openpyxl.styles import NamedStyle >>> # Let's create a style template for the header row >>> header = NamedStyle(proper noun= "header") >>> header.font = Font(bold= True) >>> header.border = Edge(bottom=Side(border_style= "sparse")) >>> header.alignment = Alignment(horizontal= "center",vertical= "center") >>> # Now let's apply this to all first row (header) cells >>> header_row = sail[1] >>> for jail cell in header_row: ... jail cell.style = header >>> workbook.relieve(filename= "sample_styles.xlsx")
Adding Charts to our Spreadsheet
Charts are a good way to compute and empathise large amounts of data speedily and easily. We take a lot of charts such as bar chart, pie chart, line nautical chart, and and so on.
Let united states kickoff past creating a new workbook with some information:
1 from openpyxl import Workbook 2 from openpyxl.chart import BarChart,Reference iii 4 workbook = Workbook() 5 spreadsheet = workbook.active half-dozen 7 # Let's create some sample sales data eight rows = [ 9 ["Product","Online","Store"], 10 [1,30,45], 11 [2,40,30], 12 [3,40,25], 13 [four,50,30], 14 [5,thirty,25], 15 [6,25,35], sixteen [vii,xx,40], 17 ] 18 19 for row in rows: 20 spreadsheet .append(row)
Now let us create a bar chart that volition show the total number of sales per product:
22 chart = BarChart() 23 information = Reference(worksheet=sheet, 24 min_row= 1, 25 max_row= viii, 26 min_col= 2, 27 max_col= iii) 28 29 chart.add_data(data,titles_from_data= Truthful) 30 spreadsheet .add_chart(chart, "E2") 31 32 workbook.salvage("chart.xlsx")
You lot can also create a line chart by simply making some changes to the data:
i import random 2 from openpyxl import Workbook 3 from openpyxl.chart import LineChart,Reference 4 5 workbook = Workbook() 6 canvas = workbook.active vii 8 # Permit'south create some sample sales data 9 rows= [ x ["", "Jan", "February", "March", "Apr", 11"May", "June", "July", "Baronial", "September", 12 "Oct", "November", "December"], thirteen [1, ], fourteen [2, ], 15 [iii, ], 16 ] 17 xviii for row in rows: 19 sheet.suspend(row) twenty 21 for row in sheet.iter_rows(min_row= ii, 22 max_row= 4, 23 min_col= 2, 24 max_col= 13): 25for cell in row: 26 cell.value = random.randrange(5,100)
In that location are numerous types of charts and diverse types of customizations you can apply to your spreadsheet to make it more attractive.
Catechumen Python Classes to Excel Spreadsheet
Let us now learn how to catechumen the Excel Spreadsheet data to Python classes.
Presume we have a database and we use some Object Relational mapping to map the database into Python classes then consign the objects into spreadsheets:
from dataclasses import dataclass from typing import List @dataclass class Sale: id: str quantity: int @dataclass grade Product: id: str name: str sales:List[Sale]
Now, to generate some random data, let's presume that the to a higher place classes are stored in db_classes.py file so:
ane import random two 3 # Ignore these for now. You lot'll employ them in a sec ;) four from openpyxl import Workbook 5 from openpyxl.chart import LineChart,Reference 6 7 from db_classes import Product,Sale eight nine products_range = [] 10 11 # Permit's create five products 12 for idx in range(1,half-dozen): 13 sales = [] 14 fifteen# Create 5 months of sales 16for_in range(five): 17 sale_range = Sale(quantity=random.randrange(5,100)) eighteen sales.suspend(sale) nineteen xx product = Production(id= str(idx), 21 proper noun= "Product %due south" % idx, 22 sales=sales) 23 products_range.append(product)
Past running this code, we will get 5 products in 5 months of auction with a random quantity of sales for each calendar month.
Now, nosotros take to catechumen this into a spreadsheet in which we need to iterate over the data:
25 workbook = Workbook() 26 spreadsheet = workbook.active 27 28 # Suspend column names first 29 spreadsheet.append(["Product ID","Product Proper name","Calendar month 1", 30"Calendar month 2","Calendar month 3","Month iv","Month 5"]) 31 32 # Suspend the data 33 for product in products_range: 34 data = [production.id,product.name] 35for 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 nearly pop tools and finding a manner to piece of work with Excel is disquisitional. Pandas is a great tool to piece of work with Excel in Python. It has unique methods to read all kinds of data in an Excel file and we tin can export items back to Excel using it.
To use it, at start we need to install pandas package:
$ pip install pandas
Then, let's create a simple DataFrame:
1 import pandas every bit pd ii 3 data = { iv"Product Name":["Product ane","Product 2"], v"Sales Month one":[10, twenty], 6"Sales Month 2":[5, 35], vii } 8 dataframe = pd.DataFrame(data)
Now nosotros have some data, and to convert it from a DataFrame into a worksheet we generally employ . dataframe_to_rows() :
ten from openpyxl import Workbook 11 from openpyxl.utils.dataframe importdataframe_to_rows 12 13 workbook = Workbook() fourteen spreadsheet = workbook.agile 15 16 for row in dataframe_to_rows(df, index= False,header= True): 17 spreadsheet .suspend(row) 18 19 workbook.salvage("pandas_spreadsheet.xlsx")
Nosotros 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 utilize multiple sheets from the same excel file:
movies_sheets = [] for sheet in xlsx.sheet_names: movies_sheets.suspend(xlsx.parse(sail)) movies = pd.concat(movies_sheets))
Indexes and columns permit you lot to access data from your DataFramehands:
>>> 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 beginning 10 reviews' star rating >>> df["star_rating"][:10] R3O9SGZBVQBV76 5 RKH8BNC3L5DLF v R2HLE8WKZSU3NL 2 R31U3UH5AZ42LL 5 R2SV659OUJ945Y 4 RA51CP8TR5A2L 5 RB2Q7DLDN6TH6 v R2RHFJV0UYBK3Y 1 R2Z6JOQ94LFHEP five RX27XIIWY5JPB 4 Proper name: star_rating, dtype: int64 >>> # Catch review with id "R2EQL1V1L6E0C9", using the index >>> df.loc["R2EQL1V1L6E0C9"] market Usa 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 Proper noun: R2EQL1V1L6E0C9, dtype: object
Summary
In this article we have covered:
- How to extract information from spreadsheets
- How to create Spreadsheets in unlike means
- How to customize a spreadsheet by adding filters, styles, or charts and then on
- How to use pandas to work with spreadsheets
Now you lot are well aware of the unlike types of implementations you can perform with spreadsheets using Python. Nevertheless, if you lot are willing to gather more information on this topic, y'all can e'er rely on the official documentation of openpyxl. To gain more knowledge about Python tips and tricks, bank check out our Python tutorial. To gain mastery over Python coding, bring together KnowledgeHut python programming course .
Suggested Blogs
Source: https://www.knowledgehut.com/blog/programming/how-to-work-with-excel-using-python
0 Response to "Which Type of File Used in Excel Would Be Best to Read and Write in Python Software?"
Enregistrer un commentaire