Pandas Read Excel Unexpected Character in Cell Name
Read an Excel File Using Python
- Read an Excel File Using
pandasPackage in Python - Read an Excel File Using the
xlrdPackage in Python - Examples of Tasks Performed Over Excel Files in Python
Python programming language is well known for its employ in the field of data science. Data science more often than not involves dealing with data and analyzing it with the help of graphs and plots such as line plots, violin plots, histograms, and estrus maps, and mathematical computations such as mean, median, mode, probability, variance, etc. What makes Python fifty-fifty more suitable is the fact that it makes file reading and manipulation very seamless. Since data is generally represented in popular file formats such as xls, xlsx, csv, txt, etc., handling them with Python is a piece of block.
This article volition introduce how to read excel files using Python with the aid of some examples. For example, we will consider a sample excel file that you tin download from hither so that we all are on the aforementioned page. Just rename information technology to sample.xls for the following lawmaking snippets to piece of work, or change the file proper name in the following code snippets itself.
Read an Excel File Using pandas Packet in Python
In Python, we tin can use the pandas library to read an excel file. The pandas module is a robust, powerful, fast, and flexible open-source data assay and manipulation library written in Python. If you don't accept it installed on your car or virtual environment, use the following control.
- To install
pandas:pip install pandasorpip3 install pandas
Refer to the following code for reading an excel file using the pandas module.
import xlrd import pandas df = pandas.read_excel("sample.xls") print("Columns") print(df.columns) Output:
Columns Alphabetize(['Segment', 'Country', 'Product', 'Discount Band', 'Units Sold', 'Manufacturing Toll', 'Sale Toll', 'Gross Sales', 'Discounts', ' Sales', 'COGS', 'Profit', 'Date', 'Calendar month Number', 'Month Name', 'Year'], dtype='object') Read an Excel File Using the xlrd Parcel in Python
In Python, we can use the xlrd package to read excel files. The xlrd module is a Python parcel used for reading and formatting excel files. In instance y'all don't accept it installed on your auto or virtual environs, use the following command.
- To install
xlrd, apply the following command.
pip install xlrd Or,
pip3 install xlrd Refer to the following code for reading excel files using xlrd.
from xlrd import open_workbook wb = open_workbook('sample.xls') canvass = wb.sheet_by_index(0) sheet.cell_value(0, 0) columns = [] print("Columns") for i in range(sheet.ncols): columns.append(sheet.cell_value(0, i)) print(columns) Output:
Columns ['Segment', 'Land', 'Product', 'Discount Ring', 'Units Sold', 'Manufacturing Toll', 'Auction Price', 'Gross Sales', 'Discounts', ' Sales', 'COGS', 'Turn a profit', 'Date', 'Month Number', 'Month Name', 'Year'] Here'southward a cursory explanation of what the higher up lawmaking does. It first creates a file descriptor for the excel file with the aid of the open_workbook() part. And so it resets the file pointer to the (0,0) position or the meridian-left cell. Next, it iterates over the first row and stores all the cavalcade names in a variable. By and large, columns name are present in the first row; that is why the lawmaking considers that location. In case the column names are on some dissimilar row, one can change the 0 value in the statement sheet.cell_value(0, i) to whatever row number they wish to. Essentially, (0, i) represents y and x coordinates, where y is 0, and x is i, considering origin (0, 0) to be nowadays at the top-left corner of the file.
Examples of Tasks Performed Over Excel Files in Python
Let's look at some simple tasks that we can perform over excel files to understand these ii libraries better.
Press the First 3 Rows of an Excel File
Using the pandas parcel
import pandas df = pandas.read_excel("sample.xls") count = 3 for index, row in df.iterrows(): print(row, end = "\n\northward") if alphabetize == count - 1: pause Output:
Segment Government Land Canada Product Carretera Discount Band None Units Sold 1618.v Manufacturing Price iii Sale Price 20 Gross Sales 32370.0 Discounts 0.0 Sales 32370.0 COGS 16185.0 Profit 16185.0 Engagement 2014-01-01 00:00:00 Month Number 1 Month Proper name January Year 2014 Proper name: 0, dtype: object Segment Regime Country Germany Product Carretera Discount Band None Units Sold 1321.0 Manufacturing Cost 3 Auction Price 20 Gross Sales 26420.0 Discounts 0.0 Sales 26420.0 COGS 13210.0 Turn a profit 13210.0 Appointment 2014-01-01 00:00:00 Month Number i Month Name January Year 2014 Proper noun: one, dtype: object Segment Midmarket Country France Production Carretera Discount Band None Units Sold 2178.0 Manufacturing Cost 3 Auction Price 15 Gross Sales 32670.0 Discounts 0.0 Sales 32670.0 COGS 21780.0 Profit 10890.0 Engagement 2014-06-01 00:00:00 Month Number vi Calendar month Proper noun June Year 2014 Proper name: 2, dtype: object Using the xlrd package
from xlrd import open_workbook wb = open_workbook('sample.xls') sheet = wb.sheet_by_index(0) canvas.cell_value(0, 0) count = 3 for i in range(1, count + i): for j in range(sheet.ncols): print(canvass.cell_value(i, j), end = ", ") print() Output:
Regime, Canada, Carretera, None, 1618.5, 3.0, twenty.0, 32370.0, 0.0, 32370.0, 16185.0, 16185.0, 41640.0, one.0, Jan, 2014, Regime, Federal republic of germany, Carretera, None, 1321.0, three.0, 20.0, 26420.0, 0.0, 26420.0, 13210.0, 13210.0, 41640.0, 1.0, January, 2014, Midmarket, French republic, Carretera, None, 2178.0, 3.0, xv.0, 32670.0, 0.0, 32670.0, 21780.0, 10890.0, 41791.0, half-dozen.0, June, 2014, Press Values of a Specific Column
Using the pandas package
import pandas df = pandas.read_excel("sample.xls") column = df.columns[4] print(column) print("-" * len(column)) for index, row in df.iterrows(): impress(row[column]) Output:
Units Sold ---------- 1618.5 1321.0 2178.0 888.0 2470.0 1513.0 921.0 2518.0 1899.0 1545.0 2470.0 2665.5 958.0 2146.0 345.0 615.0 292.0 974.0 2518.0 1006.0 367.0 883.0 549.0 788.0 2472.0 1143.0 1725.0 912.0 2152.0 1817.0 1513.0 1493.0 1804.0 2161.0 1006.0 1545.0 2821.0 345.0 2001.0 2838.0 2178.0 888.0 ... Using the xlrd package
from xlrd import open_workbook wb = open_workbook('sample.xls') canvas = wb.sheet_by_index(0) canvas.cell_value(0, 0) column_index = 4 cavalcade = sheet.cell_value(0, column_index) print(column) impress("-" * len(column)) for row in range(one, sheet.nrows): print(sheet.cell_value(row, column_index)) Output:
Units Sold ---------- 1618.5 1321.0 2178.0 888.0 2470.0 1513.0 921.0 2518.0 1899.0 1545.0 2470.0 2665.v 958.0 2146.0 345.0 615.0 292.0 974.0 2518.0 1006.0 367.0 883.0 549.0 788.0 2472.0 1143.0 1725.0 912.0 2152.0 1817.0 1513.0 1493.0 1804.0 2161.0 1006.0 1545.0 2821.0 345.0 2001.0 2838.0 2178.0 888.0 ... Write for u.s.a.
DelftStack articles are written by software geeks like you lot. If you also would like to contribute to DelftStack by writing paid articles, yous tin cheque the write for usa folio.
Related Commodity - Python Excel
williamsonardesclarm.blogspot.com
Source: https://www.delftstack.com/howto/python/python-read-excel/
Belum ada Komentar untuk "Pandas Read Excel Unexpected Character in Cell Name"
Posting Komentar