Using Excel with pandas

it's hard to find an organization that doesn't work with it in some way. From analysts, to sales VPs, to CEOs, various professionals use Excel for both quick stats and serious data crunching.
With Excel being so pervasive, data professionals must be familiar with it. You'll also want a tool that can easily read and write Excel files — pandas is perfect for this.
Pandas has excellent methods for reading all kinds of data from Excel files. You can also export your results from pandas back to Excel, if that's preferred by your intended audience. Pandas is great for other routine data analysis tasks, such as:

• quick Exploratory Data Analysis (EDA)
• drawing attractive plots
• feeding data into machine learning tools like scikit-learn
• building machine learning models on your data
• taking cleaned and processed data to any number of data tools

Pandas is better at automating data processing tasks than Excel, including processing Excel files.

 

System prerequisites

We will use Python 3 and Jupyter Notebook to demonstrate the code in this tutorial.
In addition to Python and Jupyter Notebook, you will need the following Python modules:

• matplotlib - data visualization
• NumPy - numerical data functionality
• OpenPyXL - read/write Excel 2010 xlsx/xlsm files
• pandas - data import, clean-up, exploration, and analysis
• xlrd - read Excel data
• xlwt - write to Excel
• XlsxWriter - write to Excel (xlsx) files
  

The data set

In this tutorial, we will use a multi-sheet Excel file we created from Kaggle's IMDB Scores data

Read data from the Excel file

We need to first import the data from the Excel file into pandas. To do that, we start by importing the pandas module.

"We need to first import the data from the Excel file into pandas. To do that, we start by importing the pandas module."

import pandas as pd

excel method to read in data from the Excel file. The easiest way to call this method is to pass the file name. If no sheet name is specified then it will read the first sheet

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

method read the data from the Excel file into a pandas DataFrame object. Pandas defaults to storing data in DataFrames. We then stored this DataFrame into a variable called movies.
Pandas has a built-in DataFrame.head() method that we can use to easily display the first few rows of our DataFrame. If no argument is passed, it will display first five rows. If a number is passed, it will display the equal number of rows from the top.


movies.head()

Output

cast Total	Facebook likes - Movie	Facenumber in posters	User Votes	Reviews by Users	Reviews by Crtiics	IMDB Score
0	Intolerance: Love's Struggle Throughout the Ages	1916	Drama|History|War	NaN	USA	Not Rated	123	1.33	385907.0	NaN	...	436	22	9.0	481	691	1	10718	88	69.0	8.0
1	Over the Hill to the Poorhouse	1920	Crime|Drama	NaN	USA	NaN	110	1.33	100000.0	3000000.0	...	2	2	0.0	4	0	1	5	1	1.0	4.8
2	The Big Parade	1925	Drama|Romance|War	NaN	USA	Not Rated	151	1.33	245000.0	NaN	...	81	12	6.0	108	226	0	4849	45	48.0	8.3
3	Metropolis	1927	Drama|Sci-Fi	German	Germany	Not Rated	145	1.33	6000000.0	26435.0	...	136	23	18.0	203	12000	1	111841	413	260.0	8.3
4	Pandora's Box	1929	Crime|Drama|Romance	German	Germany	Not Rated	110	1.33	NaN	9950.0	...	426	20	3.0	455	926	1	7431	84	71.0	8.0