Advance Pandas Trick and Techniques

Index of learning

Basic Pandas Reading Method
Advance Pandas Reading Methods
1. Manipulating Column & Index Locations and Names
2. Data Parsing options
3. Reading data from excel files
4. Reading data from some other popular formats
Apply multiple filter criteria to a pandas DataFrame
Changing the datatype of a Pandas Series
Filter rows of a pandas DataFrame by column value
Selecting multiple rows and columns from a pandas DataFrame
Sorting a pandas DataFrame or a Series
Using pandas Series data structure to select a subset of the data
Using string methods in pandas
Using the axis parameter in pandas
Applying a function to a pandas Series or DataFrame
Handling SettingWithCopyWarning
Handling missing values in pandas
Indexing in pandas dataframes
Merging and concatenating multiple data frames into one
Modifying a Pandas Dataframe inplace
Removing columns from a pandas DataFrame
Renaming columns in a pandas DataFrame
Using groupby method
Work with dates and times data
Choosing the colors for the plots
Controlling plot aesthetics
Plotting categorical data
Plotting with data aware grids

In [1]:

%%html
<style>
.output_wrapper, .output {
    height:auto !important;
    max-height:350px;  /* your desired max-height here */
}
.output_scroll {
    box-shadow:none !important;
    webkit-box-shadow:none !important;
}
</style>

In [2]:

from IPython.core.interactiveshell import InteractiveShell
InteractiveShell.ast_node_interactivity = "all"

1.Basic Pandas Reading Method ¶

Go to top

In [3]:

import pandas as pd
import matplotlib.pyplot as plt
import numpy as np
%matplotlib inline
import warnings
warnings.simplefilter(action='ignore', category=FutureWarning)
warnings.simplefilter(action='ignore', category=RuntimeWarning)
warnings.simplefilter(action='ignore', category=UserWarning)

In [4]:

df = pd.read_csv("../input/datasetsdifferent-format/IMDB.csv", encoding="ISO-8859-1")
df.head()

Out[4]:

	X	Title	Rating	TotalVotes	Genre1	Genre2	Genre3	MetaCritic	Budget	Runtime	CVotes10	CVotes09	CVotes08	CVotes07	CVotes06	CVotes05	CVotes04	CVotes03	CVotes02	CVotes01	CVotesMale	CVotesFemale	CVotesU18	CVotesU18M	CVotesU18F	CVotes1829	CVotes1829M	CVotes1829F	CVotes3044	CVotes3044M	CVotes3044F	CVotes45A	CVotes45AM	CVotes45AF	CVotes1000	CVotesUS	CVotesnUS	VotesM	VotesF	VotesU18	VotesU18M	VotesU18F	Votes1829	Votes1829M	Votes1829F	Votes3044	Votes3044M	Votes3044F	Votes45A	Votes45AM	Votes45AF	VotesIMDB	Votes1000	VotesUS	VotesnUS	Domestic	Foreign	Worldwide
0	1	12 Years a SlaveÂ (2013)	8.1	496092	Biography	Drama	History	96.0	20000000.0	134 min	75556	126223	161460	83070	27231	9603	4021	2420	1785	4739	313823	82012	1837	1363	457	200910	153669	45301	138762	112943	23895	29252	23072	5726	664	53328	224519.0	8.1	8.1	8.4	8.4	8.5	8.2	8.2	8.2	8.0	7.9	8.0	7.8	7.8	8.1	8.0	7.7	8.3	8.0	$56,671,993	131061209	$187,733,202
1	2	127 HoursÂ (2010)	7.6	297075	Adventure	Biography	Drama	82.0	18000000.0	94 min	28939	44110	98845	78451	28394	9403	3796	1930	1161	2059	212866	44600	745	567	170	133336	106007	26152	102120	86609	14304	14895	12400	2261	649	38478	169745.0	7.6	7.6	7.9	7.9	7.9	7.7	7.8	7.7	7.5	7.5	7.5	7.3	7.3	7.5	7.6	7.0	7.7	7.6	$18,335,230	42403567	$60,738,797
2	3	50/50Â (2011)	7.7	283935	Comedy	Drama	Romance	72.0	8000000.0	100 min	28304	47501	99524	71485	24252	7545	2381	1109	634	1202	188925	58348	506	348	153	132350	96269	34765	94745	75394	18163	12829	9912	2681	555	46947	147849.0	7.7	7.7	7.9	7.9	7.9	7.8	7.8	7.7	7.6	7.6	7.6	7.4	7.4	7.5	7.4	7.0	7.9	7.6	$35,014,192	4173591	$39,187,783
3	4	About TimeÂ (2013)	7.8	225412	Comedy	Drama	Fantasy	NaN	12000000.0	123 min	38556	43170	70850	45487	16542	5673	2210	1084	664	1182	126718	58098	654	325	321	92940	57778	34126	67477	50212	16222	13973	10690	3026	475	20450	111670.0	7.8	7.9	8.2	8.1	8.3	8.0	8.0	8.0	7.6	7.6	7.7	7.6	7.5	7.8	7.7	6.9	7.8	7.7	$15,322,921	71777528	$87,100,449
4	5	AmourÂ (2012)	7.9	76121	Drama	Romance	NaN	94.0	8900000.0	127 min	11093	15944	22942	14187	5945	2585	1188	710	534	995	49808	16719	121	95	24	28593	20107	8167	28691	21990	6269	7425	5803	1490	391	7959	46138.0	7.8	7.9	8.6	8.7	8.5	8.0	8.0	7.9	7.7	7.7	7.9	7.9	7.8	8.1	6.6	7.2	7.9	7.8	$6,739,492	13100000	$19,839,492

2.Advance Pandas Reading Methods ¶

Go to top

Advance Pandas Reading Methods

2.1 Manipulating Columns & Index Location and Names ¶

1. No Header and No Columns¶

There is no header* and no columns while reading csv file here and used encoding because file in ISO-8859-1 format

In [5]:

df = pd.read_csv('../input/datasetsdifferent-format/IMDB.csv', encoding = "ISO-8859-1", header=None)
df.head()

Out[5]:

	0	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24	25	26	27	28	29	30	31	32	33	34	35	36	37	38	39	40	41	42	43	44	45	46	47	48	49	50	51	52	53	54	55	56	57
0	X	Title	Rating	TotalVotes	Genre1	Genre2	Genre3	MetaCritic	Budget	Runtime	CVotes10	CVotes09	CVotes08	CVotes07	CVotes06	CVotes05	CVotes04	CVotes03	CVotes02	CVotes01	CVotesMale	CVotesFemale	CVotesU18	CVotesU18M	CVotesU18F	CVotes1829	CVotes1829M	CVotes1829F	CVotes3044	CVotes3044M	CVotes3044F	CVotes45A	CVotes45AM	CVotes45AF	CVotes1000	CVotesUS	CVotesnUS	VotesM	VotesF	VotesU18	VotesU18M	VotesU18F	Votes1829	Votes1829M	Votes1829F	Votes3044	Votes3044M	Votes3044F	Votes45A	Votes45AM	Votes45AF	VotesIMDB	Votes1000	VotesUS	VotesnUS	Domestic	Foreign	Worldwide
1	1	12 Years a SlaveÂ (2013)	8.1	496092	Biography	Drama	History	96	20000000	134 min	75556	126223	161460	83070	27231	9603	4021	2420	1785	4739	313823	82012	1837	1363	457	200910	153669	45301	138762	112943	23895	29252	23072	5726	664	53328	224519	8.1	8.1	8.4	8.4	8.5	8.2	8.2	8.2	8	7.9	8	7.8	7.8	8.1	8	7.7	8.3	8	$56,671,993	131061209	$187,733,202
2	2	127 HoursÂ (2010)	7.6	297075	Adventure	Biography	Drama	82	18000000	94 min	28939	44110	98845	78451	28394	9403	3796	1930	1161	2059	212866	44600	745	567	170	133336	106007	26152	102120	86609	14304	14895	12400	2261	649	38478	169745	7.6	7.6	7.9	7.9	7.9	7.7	7.8	7.7	7.5	7.5	7.5	7.3	7.3	7.5	7.6	7	7.7	7.6	$18,335,230	42403567	$60,738,797
3	3	50/50Â (2011)	7.7	283935	Comedy	Drama	Romance	72	8000000	100 min	28304	47501	99524	71485	24252	7545	2381	1109	634	1202	188925	58348	506	348	153	132350	96269	34765	94745	75394	18163	12829	9912	2681	555	46947	147849	7.7	7.7	7.9	7.9	7.9	7.8	7.8	7.7	7.6	7.6	7.6	7.4	7.4	7.5	7.4	7	7.9	7.6	$35,014,192	4173591	$39,187,783
4	4	About TimeÂ (2013)	7.8	225412	Comedy	Drama	Fantasy	NaN	12000000	123 min	38556	43170	70850	45487	16542	5673	2210	1084	664	1182	126718	58098	654	325	321	92940	57778	34126	67477	50212	16222	13973	10690	3026	475	20450	111670	7.8	7.9	8.2	8.1	8.3	8	8	8	7.6	7.6	7.7	7.6	7.5	7.8	7.7	6.9	7.8	7.7	$15,322,921	71777528	$87,100,449

2.Specify a different row as header¶

Read specific rows as header which is working as column name
In the result, row 2 become header of dataframe

In [6]:

df = pd.read_csv("../input/datasetsdifferent-format/IMDB.csv", encoding = "ISO-8859-1", header=2)
df.head()

Out[6]:

	2	127 HoursÂ (2010)	7.6	297075	Adventure	Biography	Drama	82	18000000	94 min	28939	44110	98845	78451	28394	9403	3796	1930	1161	2059	212866	44600	745	567	170	133336	106007	26152	102120	86609	14304	14895	12400	2261	649	38478	169745	7.6.1	7.6.2	7.9	7.9.1	7.9.2	7.7	7.8	7.7.1	7.5	7.5.1	7.5.2	7.3	7.3.1	7.5.3	7.6.3	7	7.7.2	7.6.4	$18,335,230	42403567	$60,738,797
0	3	50/50Â (2011)	7.7	283935	Comedy	Drama	Romance	72.0	8000000.0	100 min	28304	47501	99524	71485	24252	7545	2381	1109	634	1202	188925	58348	506	348	153	132350	96269	34765	94745	75394	18163	12829	9912	2681	555	46947	147849.0	7.7	7.7	7.9	7.9	7.9	7.8	7.8	7.7	7.6	7.6	7.6	7.4	7.4	7.5	7.4	7.0	7.9	7.6	$35,014,192	4173591	$39,187,783
1	4	About TimeÂ (2013)	7.8	225412	Comedy	Drama	Fantasy	NaN	12000000.0	123 min	38556	43170	70850	45487	16542	5673	2210	1084	664	1182	126718	58098	654	325	321	92940	57778	34126	67477	50212	16222	13973	10690	3026	475	20450	111670.0	7.8	7.9	8.2	8.1	8.3	8.0	8.0	8.0	7.6	7.6	7.7	7.6	7.5	7.8	7.7	6.9	7.8	7.7	$15,322,921	71777528	$87,100,449
2	5	AmourÂ (2012)	7.9	76121	Drama	Romance	NaN	94.0	8900000.0	127 min	11093	15944	22942	14187	5945	2585	1188	710	534	995	49808	16719	121	95	24	28593	20107	8167	28691	21990	6269	7425	5803	1490	391	7959	46138.0	7.8	7.9	8.6	8.7	8.5	8.0	8.0	7.9	7.7	7.7	7.9	7.9	7.8	8.1	6.6	7.2	7.9	7.8	$6,739,492	13100000	$19,839,492
3	6	ArgoÂ (2012)	7.7	486840	Action	Biography	Drama	86.0	44500000.0	120 min	43875	89490	171495	115165	37332	12630	4992	2910	2020	6941	334838	67910	971	795	162	178794	146371	30643	163795	136391	24948	36215	28817	6752	740	70110	229137.0	7.7	7.9	8.0	8.0	7.8	7.8	7.8	7.9	7.7	7.6	7.8	7.7	7.7	8.0	8.1	7.2	8.0	7.6	$136,025,503	96300000	$232,325,503
4	7	ArrivalÂ (2016)	8.0	370842	Drama	Mystery	Sci-Fi	81.0	47000000.0	116 min	55533	87850	109536	65440	26913	10556	5057	3083	2194	4734	237437	46272	1943	1544	376	126301	101741	23163	111985	95005	15227	24027	20118	3440	537	42062	163774.0	7.9	8.0	8.6	8.6	8.4	8.2	8.2	8.1	7.8	7.8	7.8	7.6	7.6	7.7	8.3	7.3	8.0	7.9	$100,546,139	102842047	$203,388,186

3.Specify a column as index¶

Read specify column as index for dataframe
In the result, title column become Index column of dataframe

In [7]:

df = pd.read_csv("../input/datasetsdifferent-format/IMDB.csv", encoding = "ISO-8859-1", index_col="Title")
df.head()

Out[7]:

	X	Rating	TotalVotes	Genre1	Genre2	Genre3	MetaCritic	Budget	Runtime	CVotes10	CVotes09	CVotes08	CVotes07	CVotes06	CVotes05	CVotes04	CVotes03	CVotes02	CVotes01	CVotesMale	CVotesFemale	CVotesU18	CVotesU18M	CVotesU18F	CVotes1829	CVotes1829M	CVotes1829F	CVotes3044	CVotes3044M	CVotes3044F	CVotes45A	CVotes45AM	CVotes45AF	CVotes1000	CVotesUS	CVotesnUS	VotesM	VotesF	VotesU18	VotesU18M	VotesU18F	Votes1829	Votes1829M	Votes1829F	Votes3044	Votes3044M	Votes3044F	Votes45A	Votes45AM	Votes45AF	VotesIMDB	Votes1000	VotesUS	VotesnUS	Domestic	Foreign	Worldwide
Title
12 Years a SlaveÂ (2013)	1	8.1	496092	Biography	Drama	History	96.0	20000000.0	134 min	75556	126223	161460	83070	27231	9603	4021	2420	1785	4739	313823	82012	1837	1363	457	200910	153669	45301	138762	112943	23895	29252	23072	5726	664	53328	224519.0	8.1	8.1	8.4	8.4	8.5	8.2	8.2	8.2	8.0	7.9	8.0	7.8	7.8	8.1	8.0	7.7	8.3	8.0	$56,671,993	131061209	$187,733,202
127 HoursÂ (2010)	2	7.6	297075	Adventure	Biography	Drama	82.0	18000000.0	94 min	28939	44110	98845	78451	28394	9403	3796	1930	1161	2059	212866	44600	745	567	170	133336	106007	26152	102120	86609	14304	14895	12400	2261	649	38478	169745.0	7.6	7.6	7.9	7.9	7.9	7.7	7.8	7.7	7.5	7.5	7.5	7.3	7.3	7.5	7.6	7.0	7.7	7.6	$18,335,230	42403567	$60,738,797
50/50Â (2011)	3	7.7	283935	Comedy	Drama	Romance	72.0	8000000.0	100 min	28304	47501	99524	71485	24252	7545	2381	1109	634	1202	188925	58348	506	348	153	132350	96269	34765	94745	75394	18163	12829	9912	2681	555	46947	147849.0	7.7	7.7	7.9	7.9	7.9	7.8	7.8	7.7	7.6	7.6	7.6	7.4	7.4	7.5	7.4	7.0	7.9	7.6	$35,014,192	4173591	$39,187,783
About TimeÂ (2013)	4	7.8	225412	Comedy	Drama	Fantasy	NaN	12000000.0	123 min	38556	43170	70850	45487	16542	5673	2210	1084	664	1182	126718	58098	654	325	321	92940	57778	34126	67477	50212	16222	13973	10690	3026	475	20450	111670.0	7.8	7.9	8.2	8.1	8.3	8.0	8.0	8.0	7.6	7.6	7.7	7.6	7.5	7.8	7.7	6.9	7.8	7.7	$15,322,921	71777528	$87,100,449
AmourÂ (2012)	5	7.9	76121	Drama	Romance	NaN	94.0	8900000.0	127 min	11093	15944	22942	14187	5945	2585	1188	710	534	995	49808	16719	121	95	24	28593	20107	8167	28691	21990	6269	7425	5803	1490	391	7959	46138.0	7.8	7.9	8.6	8.7	8.5	8.0	8.0	7.9	7.7	7.7	7.9	7.9	7.8	8.1	6.6	7.2	7.9	7.8	$6,739,492	13100000	$19,839,492

4.Choose only a subset of columns to be read¶

Subset specific columns from the dataframe while reading file
In the result, subset the Title, Genre1, Genre2, Budget columns

In [8]:

df = pd.read_csv("../input/datasetsdifferent-format/IMDB.csv", encoding = "ISO-8859-1", usecols=['Title','Genre1','Genre2','Budget'])
df.head()

Out[8]:

	Title	Genre1	Genre2	Budget
0	12 Years a SlaveÂ (2013)	Biography	Drama	20000000.0
1	127 HoursÂ (2010)	Adventure	Biography	18000000.0
2	50/50Â (2011)	Comedy	Drama	8000000.0
3	About TimeÂ (2013)	Comedy	Drama	12000000.0
4	AmourÂ (2012)	Drama	Romance	8900000.0

5.Handling Missing and NA data¶

*Missing Value format :* NaN: ”, ‘#N/A’, ‘#N/A N/A’, ‘#NA’, ‘-1.#IND’, ‘-1.#QNAN’, ‘-NaN’, ‘-nan’, ‘1.#IND’, ‘1.#QNAN’, ‘N/A’, ‘NA’, ‘NULL’, ‘NaN’, ‘nan’`.

Handling Missing value while reading data.
In the result, dataframe handle the result which contain nan kind missing value

In [9]:

df = pd.read_csv('../input/datasetsdifferent-format/IMDB.csv', encoding = "ISO-8859-1", na_values=['nan'])
display(df.head())
print(df.shape)

	X	Title	Rating	TotalVotes	Genre1	Genre2	Genre3	MetaCritic	Budget	Runtime	CVotes10	CVotes09	CVotes08	CVotes07	CVotes06	CVotes05	CVotes04	CVotes03	CVotes02	CVotes01	CVotesMale	CVotesFemale	CVotesU18	CVotesU18M	CVotesU18F	CVotes1829	CVotes1829M	CVotes1829F	CVotes3044	CVotes3044M	CVotes3044F	CVotes45A	CVotes45AM	CVotes45AF	CVotes1000	CVotesUS	CVotesnUS	VotesM	VotesF	VotesU18	VotesU18M	VotesU18F	Votes1829	Votes1829M	Votes1829F	Votes3044	Votes3044M	Votes3044F	Votes45A	Votes45AM	Votes45AF	VotesIMDB	Votes1000	VotesUS	VotesnUS	Domestic	Foreign	Worldwide
0	1	12 Years a SlaveÂ (2013)	8.1	496092	Biography	Drama	History	96.0	20000000.0	134 min	75556	126223	161460	83070	27231	9603	4021	2420	1785	4739	313823	82012	1837	1363	457	200910	153669	45301	138762	112943	23895	29252	23072	5726	664	53328	224519.0	8.1	8.1	8.4	8.4	8.5	8.2	8.2	8.2	8.0	7.9	8.0	7.8	7.8	8.1	8.0	7.7	8.3	8.0	$56,671,993	131061209	$187,733,202
1	2	127 HoursÂ (2010)	7.6	297075	Adventure	Biography	Drama	82.0	18000000.0	94 min	28939	44110	98845	78451	28394	9403	3796	1930	1161	2059	212866	44600	745	567	170	133336	106007	26152	102120	86609	14304	14895	12400	2261	649	38478	169745.0	7.6	7.6	7.9	7.9	7.9	7.7	7.8	7.7	7.5	7.5	7.5	7.3	7.3	7.5	7.6	7.0	7.7	7.6	$18,335,230	42403567	$60,738,797
2	3	50/50Â (2011)	7.7	283935	Comedy	Drama	Romance	72.0	8000000.0	100 min	28304	47501	99524	71485	24252	7545	2381	1109	634	1202	188925	58348	506	348	153	132350	96269	34765	94745	75394	18163	12829	9912	2681	555	46947	147849.0	7.7	7.7	7.9	7.9	7.9	7.8	7.8	7.7	7.6	7.6	7.6	7.4	7.4	7.5	7.4	7.0	7.9	7.6	$35,014,192	4173591	$39,187,783
3	4	About TimeÂ (2013)	7.8	225412	Comedy	Drama	Fantasy	NaN	12000000.0	123 min	38556	43170	70850	45487	16542	5673	2210	1084	664	1182	126718	58098	654	325	321	92940	57778	34126	67477	50212	16222	13973	10690	3026	475	20450	111670.0	7.8	7.9	8.2	8.1	8.3	8.0	8.0	8.0	7.6	7.6	7.7	7.6	7.5	7.8	7.7	6.9	7.8	7.7	$15,322,921	71777528	$87,100,449
4	5	AmourÂ (2012)	7.9	76121	Drama	Romance	NaN	94.0	8900000.0	127 min	11093	15944	22942	14187	5945	2585	1188	710	534	995	49808	16719	121	95	24	28593	20107	8167	28691	21990	6269	7425	5803	1490	391	7959	46138.0	7.8	7.9	8.6	8.7	8.5	8.0	8.0	7.9	7.7	7.7	7.9	7.9	7.8	8.1	6.6	7.2	7.9	7.8	$6,739,492	13100000	$19,839,492

(117, 58)

6.Choose whether to skip over blank rows or not¶

you choose whether to skip over blank rows while reading data
In the result, you can see that we have skipped blank rows.

In [10]:

df = pd.read_csv('../input/datasetsdifferent-format/IMDB.csv', encoding = "ISO-8859-1", skip_blank_lines=False)
df.head()

Out[10]:

	X	Title	Rating	TotalVotes	Genre1	Genre2	Genre3	MetaCritic	Budget	Runtime	CVotes10	CVotes09	CVotes08	CVotes07	CVotes06	CVotes05	CVotes04	CVotes03	CVotes02	CVotes01	CVotesMale	CVotesFemale	CVotesU18	CVotesU18M	CVotesU18F	CVotes1829	CVotes1829M	CVotes1829F	CVotes3044	CVotes3044M	CVotes3044F	CVotes45A	CVotes45AM	CVotes45AF	CVotes1000	CVotesUS	CVotesnUS	VotesM	VotesF	VotesU18	VotesU18M	VotesU18F	Votes1829	Votes1829M	Votes1829F	Votes3044	Votes3044M	Votes3044F	Votes45A	Votes45AM	Votes45AF	VotesIMDB	Votes1000	VotesUS	VotesnUS	Domestic	Foreign	Worldwide
0	1	12 Years a SlaveÂ (2013)	8.1	496092	Biography	Drama	History	96.0	20000000.0	134 min	75556	126223	161460	83070	27231	9603	4021	2420	1785	4739	313823	82012	1837	1363	457	200910	153669	45301	138762	112943	23895	29252	23072	5726	664	53328	224519.0	8.1	8.1	8.4	8.4	8.5	8.2	8.2	8.2	8.0	7.9	8.0	7.8	7.8	8.1	8.0	7.7	8.3	8.0	$56,671,993	131061209	$187,733,202
1	2	127 HoursÂ (2010)	7.6	297075	Adventure	Biography	Drama	82.0	18000000.0	94 min	28939	44110	98845	78451	28394	9403	3796	1930	1161	2059	212866	44600	745	567	170	133336	106007	26152	102120	86609	14304	14895	12400	2261	649	38478	169745.0	7.6	7.6	7.9	7.9	7.9	7.7	7.8	7.7	7.5	7.5	7.5	7.3	7.3	7.5	7.6	7.0	7.7	7.6	$18,335,230	42403567	$60,738,797
2	3	50/50Â (2011)	7.7	283935	Comedy	Drama	Romance	72.0	8000000.0	100 min	28304	47501	99524	71485	24252	7545	2381	1109	634	1202	188925	58348	506	348	153	132350	96269	34765	94745	75394	18163	12829	9912	2681	555	46947	147849.0	7.7	7.7	7.9	7.9	7.9	7.8	7.8	7.7	7.6	7.6	7.6	7.4	7.4	7.5	7.4	7.0	7.9	7.6	$35,014,192	4173591	$39,187,783
3	4	About TimeÂ (2013)	7.8	225412	Comedy	Drama	Fantasy	NaN	12000000.0	123 min	38556	43170	70850	45487	16542	5673	2210	1084	664	1182	126718	58098	654	325	321	92940	57778	34126	67477	50212	16222	13973	10690	3026	475	20450	111670.0	7.8	7.9	8.2	8.1	8.3	8.0	8.0	8.0	7.6	7.6	7.7	7.6	7.5	7.8	7.7	6.9	7.8	7.7	$15,322,921	71777528	$87,100,449
4	5	AmourÂ (2012)	7.9	76121	Drama	Romance	NaN	94.0	8900000.0	127 min	11093	15944	22942	14187	5945	2585	1188	710	534	995	49808	16719	121	95	24	28593	20107	8167	28691	21990	6269	7425	5803	1490	391	7959	46138.0	7.8	7.9	8.6	8.7	8.5	8.0	8.0	7.9	7.7	7.7	7.9	7.9	7.8	8.1	6.6	7.2	7.9	7.8	$6,739,492	13100000	$19,839,492

2.2 Data Parsing options ¶

1. Skip Rows¶

We can skip the rows by reading the dataset
In the result, you can see that row number 1,3,7 are skipped from the dataframe.

In [11]:

df = pd.read_csv('../input/datasetsdifferent-format/IMDB.csv', encoding = "ISO-8859-1", skiprows = [1,3,7])
df.head()

Out[11]:

	X	Title	Rating	TotalVotes	Genre1	Genre2	Genre3	MetaCritic	Budget	Runtime	CVotes10	CVotes09	CVotes08	CVotes07	CVotes06	CVotes05	CVotes04	CVotes03	CVotes02	CVotes01	CVotesMale	CVotesFemale	CVotesU18	CVotesU18M	CVotesU18F	CVotes1829	CVotes1829M	CVotes1829F	CVotes3044	CVotes3044M	CVotes3044F	CVotes45A	CVotes45AM	CVotes45AF	CVotes1000	CVotesUS	CVotesnUS	VotesM	VotesF	VotesU18	VotesU18M	VotesU18F	Votes1829	Votes1829M	Votes1829F	Votes3044	Votes3044M	Votes3044F	Votes45A	Votes45AM	Votes45AF	VotesIMDB	Votes1000	VotesUS	VotesnUS	Domestic	Foreign	Worldwide
0	2	127 HoursÂ (2010)	7.6	297075	Adventure	Biography	Drama	82.0	18000000.0	94 min	28939	44110	98845	78451	28394	9403	3796	1930	1161	2059	212866	44600	745	567	170	133336	106007	26152	102120	86609	14304	14895	12400	2261	649	38478	169745.0	7.6	7.6	7.9	7.9	7.9	7.7	7.8	7.7	7.5	7.5	7.5	7.3	7.3	7.5	7.6	7.0	7.7	7.6	$18,335,230	42403567	$60,738,797
1	4	About TimeÂ (2013)	7.8	225412	Comedy	Drama	Fantasy	NaN	12000000.0	123 min	38556	43170	70850	45487	16542	5673	2210	1084	664	1182	126718	58098	654	325	321	92940	57778	34126	67477	50212	16222	13973	10690	3026	475	20450	111670.0	7.8	7.9	8.2	8.1	8.3	8.0	8.0	8.0	7.6	7.6	7.7	7.6	7.5	7.8	7.7	6.9	7.8	7.7	$15,322,921	71777528	$87,100,449
2	5	AmourÂ (2012)	7.9	76121	Drama	Romance	NaN	94.0	8900000.0	127 min	11093	15944	22942	14187	5945	2585	1188	710	534	995	49808	16719	121	95	24	28593	20107	8167	28691	21990	6269	7425	5803	1490	391	7959	46138.0	7.8	7.9	8.6	8.7	8.5	8.0	8.0	7.9	7.7	7.7	7.9	7.9	7.8	8.1	6.6	7.2	7.9	7.8	$6,739,492	13100000	$19,839,492
3	6	ArgoÂ (2012)	7.7	486840	Action	Biography	Drama	86.0	44500000.0	120 min	43875	89490	171495	115165	37332	12630	4992	2910	2020	6941	334838	67910	971	795	162	178794	146371	30643	163795	136391	24948	36215	28817	6752	740	70110	229137.0	7.7	7.9	8.0	8.0	7.8	7.8	7.8	7.9	7.7	7.6	7.8	7.7	7.7	8.0	8.1	7.2	8.0	7.6	$136,025,503	96300000	$232,325,503
4	9	Before MidnightÂ (2013)	7.9	106553	Drama	Romance	NaN	94.0	3000000.0	109 min	16953	22109	31439	19251	8142	3412	1649	1033	826	1745	67076	23823	208	138	66	43312	30016	12857	37072	28401	8189	7479	5891	1470	447	12382	59116.0	7.9	7.8	8.1	8.3	7.4	8.1	8.2	7.9	7.8	7.8	7.6	7.3	7.4	7.2	8.5	7.0	8.0	7.9	$8,114,627	3061842	$11,176,469

We can skip the rows from the footer.
In the result,

In [12]:

df.tail(2)
print("After Skipping the Rows")
df = pd.read_csv('../input/datasetsdifferent-format/IMDB.csv', encoding = "ISO-8859-1", skipfooter=2, engine='python')
df.tail(2)

Out[12]:

	X	Title	Rating	TotalVotes	Genre1	Genre2	Genre3	MetaCritic	Budget	Runtime	CVotes10	CVotes09	CVotes08	CVotes07	CVotes06	CVotes05	CVotes04	CVotes03	CVotes02	CVotes01	CVotesMale	CVotesFemale	CVotesU18	CVotesU18M	CVotesU18F	CVotes1829	CVotes1829M	CVotes1829F	CVotes3044	CVotes3044M	CVotes3044F	CVotes45A	CVotes45AM	CVotes45AF	CVotes1000	CVotesUS	CVotesnUS	VotesM	VotesF	VotesU18	VotesU18M	VotesU18F	Votes1829	Votes1829M	Votes1829F	Votes3044	Votes3044M	Votes3044F	Votes45A	Votes45AM	Votes45AF	VotesIMDB	Votes1000	VotesUS	VotesnUS	Domestic	Foreign	Worldwide
112	117	X-Men: First ClassÂ (2011)	7.8	556713	Action	Adventure	Sci-Fi	65.0	160000000.0	132 min	64428	96219	200144	129352	41945	12861	4799	2349	1448	3182	382107	80444	2075	1612	443	223309	176821	44428	185909	157332	26094	30217	25051	4691	780	87542	257681.0	7.7	7.9	8.0	7.9	8.3	7.9	7.9	8.0	7.7	7.6	7.8	7.6	7.5	7.7	7.6	7.3	7.8	7.7	$146,408,305	207215819	$353,624,124
113	118	ZootopiaÂ (2016)	8.1	309474	Animation	Adventure	Comedy	78.0	150000000.0	108 min	53626	70912	102352	57261	16719	4539	1467	733	496	1386	176202	52345	2362	1641	706	119637	87499	30813	75474	61358	13034	12353	9959	2151	518	35975	122844.0	8.0	8.3	8.4	8.3	8.7	8.2	8.1	8.4	7.8	7.8	8.1	7.8	7.8	8.1	7.7	7.6	8.0	8.0	$341,268,248	682515947	$1,023,784,195

After Skipping the Rows

Out[12]:

	X	Title	Rating	TotalVotes	Genre1	Genre2	Genre3	MetaCritic	Budget	Runtime	CVotes10	CVotes09	CVotes08	CVotes07	CVotes06	CVotes05	CVotes04	CVotes03	CVotes02	CVotes01	CVotesMale	CVotesFemale	CVotesU18	CVotesU18M	CVotesU18F	CVotes1829	CVotes1829M	CVotes1829F	CVotes3044	CVotes3044M	CVotes3044F	CVotes45A	CVotes45AM	CVotes45AF	CVotes1000	CVotesUS	CVotesnUS	VotesM	VotesF	VotesU18	VotesU18M	VotesU18F	Votes1829	Votes1829M	Votes1829F	Votes3044	Votes3044M	Votes3044F	Votes45A	Votes45AM	Votes45AF	VotesIMDB	Votes1000	VotesUS	VotesnUS	Domestic	Foreign	Worldwide
113	115	Wreck-It RalphÂ (2012)	7.7	295125	Animation	Adventure	Comedy	72.0	165000000.0	NaN	41980	50262	96477	67934	24894	7748	2724	1190	703	1226	190983	50202	1663	1182	467	120962	90759	29003	90203	74767	14148	13706	11356	2112	614	44962	129487.0	7.7	7.8	7.9	7.9	8.0	7.9	7.9	7.9	7.6	7.6	7.7	7.4	7.4	7.5	7.4	7.2	7.9	7.6	$189,422,889	281800000	$471,222,889
114	116	X-Men: Days of Future PastÂ (2014)	8.0	560736	Action	Adventure	Sci-Fi	74.0	200000000.0	132 min	91765	127521	183578	104658	33027	10059	3710	1903	1225	3301	370835	71008	3038	2403	614	220178	179039	39094	158607	135392	20927	26834	22460	3884	710	67889	229049.0	8.0	8.1	8.4	8.4	8.6	8.1	8.1	8.2	7.8	7.8	8.0	7.7	7.7	7.9	7.5	7.4	8.1	7.9	$233,921,534	513941241	$747,862,775

3.Reading only a subset of the file or a certain number of rows¶

We are also Reading only a subset of the file or a certain number of rows while reading whole dataset file.
In the result, we can see the shape of the data before and after.

In [13]:

print("Before Shape:",df.shape)
print("After Selecting 100 Rows")
df = pd.read_csv('../input/datasetsdifferent-format/IMDB.csv', encoding = "ISO-8859-1", nrows=100)
print("After Shape:",df.shape)

Before Shape: (115, 58)
After Selecting 100 Rows
After Shape: (100, 58)

2.3.Reading data from excel files ¶

1.Basic Excel read¶

Basic Excel file reading with default sheet number

In [14]:

df = pd.read_excel('../input/datasetsdifferent-format/IMDB.xlsx')
df.head()

Out[14]:

	X	Title	Rating	TotalVotes	Genre1	Genre2	Genre3	MetaCritic	Budget	Runtime	CVotes10	CVotes09	CVotes08	CVotes07	CVotes06	CVotes05	CVotes04	CVotes03	CVotes02	CVotes01	CVotesMale	CVotesFemale	CVotesU18	CVotesU18M	CVotesU18F	CVotes1829	CVotes1829M	CVotes1829F	CVotes3044	CVotes3044M	CVotes3044F	CVotes45A	CVotes45AM	CVotes45AF	CVotes1000	CVotesUS	CVotesnUS	VotesM	VotesF	VotesU18	VotesU18M	VotesU18F	Votes1829	Votes1829M	Votes1829F	Votes3044	Votes3044M	Votes3044F	Votes45A	Votes45AM	Votes45AF	VotesIMDB	Votes1000	VotesUS	VotesnUS	Domestic	Foreign	Worldwide
0	1	12 Years a Slave (2013)	8.1	496092	Biography	Drama	History	96.0	20000000.0	134 min	75556	126223	161460	83070	27231	9603	4021	2420	1785	4739	313823	82012	1837	1363	457	200910	153669	45301	138762	112943	23895	29252	23072	5726	664	53328	224519.0	8.1	8.1	8.4	8.4	8.5	8.2	8.2	8.2	8.0	7.9	8.0	7.8	7.8	8.1	8.0	7.7	8.3	8.0	$56,671,993	131061209	$187,733,202
1	2	127 Hours (2010)	7.6	297075	Adventure	Biography	Drama	82.0	18000000.0	94 min	28939	44110	98845	78451	28394	9403	3796	1930	1161	2059	212866	44600	745	567	170	133336	106007	26152	102120	86609	14304	14895	12400	2261	649	38478	169745.0	7.6	7.6	7.9	7.9	7.9	7.7	7.8	7.7	7.5	7.5	7.5	7.3	7.3	7.5	7.6	7.0	7.7	7.6	$18,335,230	42403567	$60,738,797
2	3	50/50 (2011)	7.7	283935	Comedy	Drama	Romance	72.0	8000000.0	100 min	28304	47501	99524	71485	24252	7545	2381	1109	634	1202	188925	58348	506	348	153	132350	96269	34765	94745	75394	18163	12829	9912	2681	555	46947	147849.0	7.7	7.7	7.9	7.9	7.9	7.8	7.8	7.7	7.6	7.6	7.6	7.4	7.4	7.5	7.4	7.0	7.9	7.6	$35,014,192	4173591	$39,187,783
3	4	About Time (2013)	7.8	225412	Comedy	Drama	Fantasy	NaN	12000000.0	123 min	38556	43170	70850	45487	16542	5673	2210	1084	664	1182	126718	58098	654	325	321	92940	57778	34126	67477	50212	16222	13973	10690	3026	475	20450	111670.0	7.8	7.9	8.2	8.1	8.3	8.0	8.0	8.0	7.6	7.6	7.7	7.6	7.5	7.8	7.7	6.9	7.8	7.7	$15,322,921	71777528	$87,100,449
4	5	Amour (2012)	7.9	76121	Drama	Romance	NaN	94.0	8900000.0	127 min	11093	15944	22942	14187	5945	2585	1188	710	534	995	49808	16719	121	95	24	28593	20107	8167	28691	21990	6269	7425	5803	1490	391	7959	46138.0	7.8	7.9	8.6	8.7	8.5	8.0	8.0	7.9	7.7	7.7	7.9	7.9	7.8	8.1	6.6	7.2	7.9	7.8	$6,739,492	13100000	$19,839,492

Advanced read options¶

pandas.read_excel(io, sheetname=0, header=0, skiprows=None, skip_footer=0, index_col=None, names=None, parse_cols=None, parse_dates=False, date_parser=None, na_values=None, thousands=None, convert_float=True, has_index_names=None, converters=None, dtype=None, true_values=None, false_values=None, engine=None, squeeze=False, **kwds)

*Reference:* Pandas Doc

2.Which Sheet to read?¶

We can select which sheet which we have to read.

In [15]:

df = pd.read_excel('../input/datasetsdifferent-format/IMDB.xlsx', sheet_name=0)
df.head()

Out[15]:

	X	Title	Rating	TotalVotes	Genre1	Genre2	Genre3	MetaCritic	Budget	Runtime	CVotes10	CVotes09	CVotes08	CVotes07	CVotes06	CVotes05	CVotes04	CVotes03	CVotes02	CVotes01	CVotesMale	CVotesFemale	CVotesU18	CVotesU18M	CVotesU18F	CVotes1829	CVotes1829M	CVotes1829F	CVotes3044	CVotes3044M	CVotes3044F	CVotes45A	CVotes45AM	CVotes45AF	CVotes1000	CVotesUS	CVotesnUS	VotesM	VotesF	VotesU18	VotesU18M	VotesU18F	Votes1829	Votes1829M	Votes1829F	Votes3044	Votes3044M	Votes3044F	Votes45A	Votes45AM	Votes45AF	VotesIMDB	Votes1000	VotesUS	VotesnUS	Domestic	Foreign	Worldwide
0	1	12 Years a Slave (2013)	8.1	496092	Biography	Drama	History	96.0	20000000.0	134 min	75556	126223	161460	83070	27231	9603	4021	2420	1785	4739	313823	82012	1837	1363	457	200910	153669	45301	138762	112943	23895	29252	23072	5726	664	53328	224519.0	8.1	8.1	8.4	8.4	8.5	8.2	8.2	8.2	8.0	7.9	8.0	7.8	7.8	8.1	8.0	7.7	8.3	8.0	$56,671,993	131061209	$187,733,202
1	2	127 Hours (2010)	7.6	297075	Adventure	Biography	Drama	82.0	18000000.0	94 min	28939	44110	98845	78451	28394	9403	3796	1930	1161	2059	212866	44600	745	567	170	133336	106007	26152	102120	86609	14304	14895	12400	2261	649	38478	169745.0	7.6	7.6	7.9	7.9	7.9	7.7	7.8	7.7	7.5	7.5	7.5	7.3	7.3	7.5	7.6	7.0	7.7	7.6	$18,335,230	42403567	$60,738,797
2	3	50/50 (2011)	7.7	283935	Comedy	Drama	Romance	72.0	8000000.0	100 min	28304	47501	99524	71485	24252	7545	2381	1109	634	1202	188925	58348	506	348	153	132350	96269	34765	94745	75394	18163	12829	9912	2681	555	46947	147849.0	7.7	7.7	7.9	7.9	7.9	7.8	7.8	7.7	7.6	7.6	7.6	7.4	7.4	7.5	7.4	7.0	7.9	7.6	$35,014,192	4173591	$39,187,783
3	4	About Time (2013)	7.8	225412	Comedy	Drama	Fantasy	NaN	12000000.0	123 min	38556	43170	70850	45487	16542	5673	2210	1084	664	1182	126718	58098	654	325	321	92940	57778	34126	67477	50212	16222	13973	10690	3026	475	20450	111670.0	7.8	7.9	8.2	8.1	8.3	8.0	8.0	8.0	7.6	7.6	7.7	7.6	7.5	7.8	7.7	6.9	7.8	7.7	$15,322,921	71777528	$87,100,449
4	5	Amour (2012)	7.9	76121	Drama	Romance	NaN	94.0	8900000.0	127 min	11093	15944	22942	14187	5945	2585	1188	710	534	995	49808	16719	121	95	24	28593	20107	8167	28691	21990	6269	7425	5803	1490	391	7959	46138.0	7.8	7.9	8.6	8.7	8.5	8.0	8.0	7.9	7.7	7.7	7.9	7.9	7.8	8.1	6.6	7.2	7.9	7.8	$6,739,492	13100000	$19,839,492

3.Reading data from multiple sheets in an excel file¶

Find out the sheet list of the excel file

In [16]:

df_excel = pd.ExcelFile('../input/datasetsdifferent-format/IMDB.xlsx')
df_excel.sheet_names

Out[16]:

['movies', 'by genre']

In [17]:

df1 = df_excel.parse('movies')
df2 = df_excel.parse('by genre')
df1.head()
df2.head()

Out[17]:

	X	Title	Rating	TotalVotes	Genre1	Genre2	Genre3	MetaCritic	Budget	Runtime	CVotes10	CVotes09	CVotes08	CVotes07	CVotes06	CVotes05	CVotes04	CVotes03	CVotes02	CVotes01	CVotesMale	CVotesFemale	CVotesU18	CVotesU18M	CVotesU18F	CVotes1829	CVotes1829M	CVotes1829F	CVotes3044	CVotes3044M	CVotes3044F	CVotes45A	CVotes45AM	CVotes45AF	CVotes1000	CVotesUS	CVotesnUS	VotesM	VotesF	VotesU18	VotesU18M	VotesU18F	Votes1829	Votes1829M	Votes1829F	Votes3044	Votes3044M	Votes3044F	Votes45A	Votes45AM	Votes45AF	VotesIMDB	Votes1000	VotesUS	VotesnUS	Domestic	Foreign	Worldwide
0	1	12 Years a Slave (2013)	8.1	496092	Biography	Drama	History	96.0	20000000.0	134 min	75556	126223	161460	83070	27231	9603	4021	2420	1785	4739	313823	82012	1837	1363	457	200910	153669	45301	138762	112943	23895	29252	23072	5726	664	53328	224519.0	8.1	8.1	8.4	8.4	8.5	8.2	8.2	8.2	8.0	7.9	8.0	7.8	7.8	8.1	8.0	7.7	8.3	8.0	$56,671,993	131061209	$187,733,202
1	2	127 Hours (2010)	7.6	297075	Adventure	Biography	Drama	82.0	18000000.0	94 min	28939	44110	98845	78451	28394	9403	3796	1930	1161	2059	212866	44600	745	567	170	133336	106007	26152	102120	86609	14304	14895	12400	2261	649	38478	169745.0	7.6	7.6	7.9	7.9	7.9	7.7	7.8	7.7	7.5	7.5	7.5	7.3	7.3	7.5	7.6	7.0	7.7	7.6	$18,335,230	42403567	$60,738,797
2	3	50/50 (2011)	7.7	283935	Comedy	Drama	Romance	72.0	8000000.0	100 min	28304	47501	99524	71485	24252	7545	2381	1109	634	1202	188925	58348	506	348	153	132350	96269	34765	94745	75394	18163	12829	9912	2681	555	46947	147849.0	7.7	7.7	7.9	7.9	7.9	7.8	7.8	7.7	7.6	7.6	7.6	7.4	7.4	7.5	7.4	7.0	7.9	7.6	$35,014,192	4173591	$39,187,783
3	4	About Time (2013)	7.8	225412	Comedy	Drama	Fantasy	NaN	12000000.0	123 min	38556	43170	70850	45487	16542	5673	2210	1084	664	1182	126718	58098	654	325	321	92940	57778	34126	67477	50212	16222	13973	10690	3026	475	20450	111670.0	7.8	7.9	8.2	8.1	8.3	8.0	8.0	8.0	7.6	7.6	7.7	7.6	7.5	7.8	7.7	6.9	7.8	7.7	$15,322,921	71777528	$87,100,449
4	5	Amour (2012)	7.9	76121	Drama	Romance	NaN	94.0	8900000.0	127 min	11093	15944	22942	14187	5945	2585	1188	710	534	995	49808	16719	121	95	24	28593	20107	8167	28691	21990	6269	7425	5803	1490	391	7959	46138.0	7.8	7.9	8.6	8.7	8.5	8.0	8.0	7.9	7.7	7.7	7.9	7.9	7.8	8.1	6.6	7.2	7.9	7.8	$6,739,492	13100000	$19,839,492

Out[17]:

	X	Title	Rating	TotalVotes	Genre1	Genre2	Genre3	MetaCritic	Budget	Runtime	CVotes10	CVotes09	CVotes08	CVotes07	CVotes06	CVotes05	CVotes04	CVotes03	CVotes02	CVotes01	CVotesMale	CVotesFemale	CVotesU18	CVotesU18M	CVotesU18F	CVotes1829	CVotes1829M	CVotes1829F	CVotes3044	CVotes3044M	CVotes3044F	CVotes45A	CVotes45AM	CVotes45AF	CVotes1000	CVotesUS	CVotesnUS	VotesM	VotesF	VotesU18	VotesU18M	VotesU18F	Votes1829	Votes1829M	Votes1829F	Votes3044	Votes3044M	Votes3044F	Votes45A	Votes45AM	Votes45AF	VotesIMDB	Votes1000	VotesUS	VotesnUS	Domestic	Foreign	Worldwide
0	1	12 Years a Slave (2013)	8.1	496092	Biography	Drama	History	96.0	20000000.0	134 min	75556	126223	161460	83070	27231	9603	4021	2420	1785	4739	313823	82012	1837	1363	457	200910	153669	45301	138762	112943	23895	29252	23072	5726	664	53328	224519.0	8.1	8.1	8.4	8.4	8.5	8.2	8.2	8.2	8.0	7.9	8.0	7.8	7.8	8.1	8.0	7.7	8.3	8.0	$56,671,993	131061209	$187,733,202
1	2	127 Hours (2010)	7.6	297075	Adventure	Biography	Drama	82.0	18000000.0	94 min	28939	44110	98845	78451	28394	9403	3796	1930	1161	2059	212866	44600	745	567	170	133336	106007	26152	102120	86609	14304	14895	12400	2261	649	38478	169745.0	7.6	7.6	7.9	7.9	7.9	7.7	7.8	7.7	7.5	7.5	7.5	7.3	7.3	7.5	7.6	7.0	7.7	7.6	$18,335,230	42403567	$60,738,797
2	3	50/50 (2011)	7.7	283935	Comedy	Drama	Romance	72.0	8000000.0	100 min	28304	47501	99524	71485	24252	7545	2381	1109	634	1202	188925	58348	506	348	153	132350	96269	34765	94745	75394	18163	12829	9912	2681	555	46947	147849.0	7.7	7.7	7.9	7.9	7.9	7.8	7.8	7.7	7.6	7.6	7.6	7.4	7.4	7.5	7.4	7.0	7.9	7.6	$35,014,192	4173591	$39,187,783
3	4	About Time (2013)	7.8	225412	Comedy	Drama	Fantasy	NaN	12000000.0	123 min	38556	43170	70850	45487	16542	5673	2210	1084	664	1182	126718	58098	654	325	321	92940	57778	34126	67477	50212	16222	13973	10690	3026	475	20450	111670.0	7.8	7.9	8.2	8.1	8.3	8.0	8.0	8.0	7.6	7.6	7.7	7.6	7.5	7.8	7.7	6.9	7.8	7.7	$15,322,921	71777528	$87,100,449
4	5	Amour (2012)	7.9	76121	Drama	Romance	NaN	94.0	8900000.0	127 min	11093	15944	22942	14187	5945	2585	1188	710	534	995	49808	16719	121	95	24	28593	20107	8167	28691	21990	6269	7425	5803	1490	391	7959	46138.0	7.8	7.9	8.6	8.7	8.5	8.0	8.0	7.9	7.7	7.7	7.9	7.9	7.8	8.1	6.6	7.2	7.9	7.8	$6,739,492	13100000	$19,839,492

4.Choose Header or column labels¶

we can also select header or columns labels from the read_excel() function

In [18]:

df = pd.read_excel('../input/datasetsdifferent-format/IMDB.xlsx', sheet_name=1, header=3)
df.head()

Out[18]:

	3	50/50 (2011)	7.7	283935	Comedy	Drama	Romance	72	8000000	100 min	28304	47501	99524	71485	24252	7545	2381	1109	634	1202	188925	58348	506	348	153	132350	96269	34765	94745	75394	18163	12829	9912	2681	555	46947	147849	7.7.1	7.7.2	7.9	7.9.1	7.9.2	7.8	7.8.1	7.7.3	7.6	7.6.1	7.6.2	7.4	7.4.1	7.5	7.4.2	7	7.9.3	7.6.3	$35,014,192	4173591	$39,187,783
0	4	About Time (2013)	7.8	225412	Comedy	Drama	Fantasy	NaN	12000000.0	123 min	38556	43170	70850	45487	16542	5673	2210	1084	664	1182	126718	58098	654	325	321	92940	57778	34126	67477	50212	16222	13973	10690	3026	475	20450	111670.0	7.8	7.9	8.2	8.1	8.3	8.0	8.0	8.0	7.6	7.6	7.7	7.6	7.5	7.8	7.7	6.9	7.8	7.7	$15,322,921	71777528	$87,100,449
1	5	Amour (2012)	7.9	76121	Drama	Romance	NaN	94.0	8900000.0	127 min	11093	15944	22942	14187	5945	2585	1188	710	534	995	49808	16719	121	95	24	28593	20107	8167	28691	21990	6269	7425	5803	1490	391	7959	46138.0	7.8	7.9	8.6	8.7	8.5	8.0	8.0	7.9	7.7	7.7	7.9	7.9	7.8	8.1	6.6	7.2	7.9	7.8	$6,739,492	13100000	$19,839,492
2	6	Argo (2012)	7.7	486840	Action	Biography	Drama	86.0	44500000.0	120 min	43875	89490	171495	115165	37332	12630	4992	2910	2020	6941	334838	67910	971	795	162	178794	146371	30643	163795	136391	24948	36215	28817	6752	740	70110	229137.0	7.7	7.9	8.0	8.0	7.8	7.8	7.8	7.9	7.7	7.6	7.8	7.7	7.7	8.0	8.1	7.2	8.0	7.6	$136,025,503	96300000	$232,325,503
3	7	Arrival (2016)	8.0	370842	Drama	Mystery	Sci-Fi	81.0	47000000.0	116 min	55533	87850	109536	65440	26913	10556	5057	3083	2194	4734	237437	46272	1943	1544	376	126301	101741	23163	111985	95005	15227	24027	20118	3440	537	42062	163774.0	7.9	8.0	8.6	8.6	8.4	8.2	8.2	8.1	7.8	7.8	7.8	7.6	7.6	7.7	8.3	7.3	8.0	7.9	$100,546,139	102842047	$203,388,186
4	9	Before Midnight (2013)	7.9	106553	Drama	Romance	NaN	94.0	3000000.0	109 min	16953	22109	31439	19251	8142	3412	1649	1033	826	1745	67076	23823	208	138	66	43312	30016	12857	37072	28401	8189	7479	5891	1470	447	12382	59116.0	7.9	7.8	8.1	8.3	7.4	8.1	8.2	7.9	7.8	7.8	7.6	7.3	7.4	7.2	8.5	7.0	8.0	7.9	$8,114,627	3061842	$11,176,469

5.No header¶

We can set header = None for not seeing header

In [19]:

df = pd.read_excel('../input/datasetsdifferent-format/IMDB.xlsx', sheet_name=1, header=None)
df.head()

Out[19]:

	0	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24	25	26	27	28	29	30	31	32	33	34	35	36	37	38	39	40	41	42	43	44	45	46	47	48	49	50	51	52	53	54	55	56	57
0	X	Title	Rating	TotalVotes	Genre1	Genre2	Genre3	MetaCritic	Budget	Runtime	CVotes10	CVotes09	CVotes08	CVotes07	CVotes06	CVotes05	CVotes04	CVotes03	CVotes02	CVotes01	CVotesMale	CVotesFemale	CVotesU18	CVotesU18M	CVotesU18F	CVotes1829	CVotes1829M	CVotes1829F	CVotes3044	CVotes3044M	CVotes3044F	CVotes45A	CVotes45AM	CVotes45AF	CVotes1000	CVotesUS	CVotesnUS	VotesM	VotesF	VotesU18	VotesU18M	VotesU18F	Votes1829	Votes1829M	Votes1829F	Votes3044	Votes3044M	Votes3044F	Votes45A	Votes45AM	Votes45AF	VotesIMDB	Votes1000	VotesUS	VotesnUS	Domestic	Foreign	Worldwide
1	1	12 Years a Slave (2013)	8.1	496092	Biography	Drama	History	96	20000000	134 min	75556	126223	161460	83070	27231	9603	4021	2420	1785	4739	313823	82012	1837	1363	457	200910	153669	45301	138762	112943	23895	29252	23072	5726	664	53328	224519	8.1	8.1	8.4	8.4	8.5	8.2	8.2	8.2	8	7.9	8	7.8	7.8	8.1	8	7.7	8.3	8	$56,671,993	131061209	$187,733,202
2	2	127 Hours (2010)	7.6	297075	Adventure	Biography	Drama	82	18000000	94 min	28939	44110	98845	78451	28394	9403	3796	1930	1161	2059	212866	44600	745	567	170	133336	106007	26152	102120	86609	14304	14895	12400	2261	649	38478	169745	7.6	7.6	7.9	7.9	7.9	7.7	7.8	7.7	7.5	7.5	7.5	7.3	7.3	7.5	7.6	7	7.7	7.6	$18,335,230	42403567	$60,738,797
3	3	50/50 (2011)	7.7	283935	Comedy	Drama	Romance	72	8000000	100 min	28304	47501	99524	71485	24252	7545	2381	1109	634	1202	188925	58348	506	348	153	132350	96269	34765	94745	75394	18163	12829	9912	2681	555	46947	147849	7.7	7.7	7.9	7.9	7.9	7.8	7.8	7.7	7.6	7.6	7.6	7.4	7.4	7.5	7.4	7	7.9	7.6	$35,014,192	4173591	$39,187,783
4	4	About Time (2013)	7.8	225412	Comedy	Drama	Fantasy	NaN	12000000	123 min	38556	43170	70850	45487	16542	5673	2210	1084	664	1182	126718	58098	654	325	321	92940	57778	34126	67477	50212	16222	13973	10690	3026	475	20450	111670	7.8	7.9	8.2	8.1	8.3	8	8	8	7.6	7.6	7.7	7.6	7.5	7.8	7.7	6.9	7.8	7.7	$15,322,921	71777528	$87,100,449

6.Skip Rows at the beginning of the file¶

Skip the rows

In [20]:

df = pd.read_excel('../input/datasetsdifferent-format/IMDB.xlsx', sheet_name=1, skiprows=7)
df.head(10)

Out[20]:

	7	Arrival (2016)	8	370842	Drama	Mystery	Sci-Fi	81	47000000	116 min	55533	87850	109536	65440	26913	10556	5057	3083	2194	4734	237437	46272	1943	1544	376	126301	101741	23163	111985	95005	15227	24027	20118	3440	537	42062	163774	7.9	8.1	8.6	8.6.1	8.4	8.2	8.2.1	8.1	7.8	7.8.1	7.8.2	7.6	7.6.1	7.7	8.3	7.3	8.2	7.9.1	$100,546,139	102842047	$203,388,186
0	9	Before Midnight (2013)	7.9	106553	Drama	Romance	NaN	94.0	3000000.0	109 min	16953	22109	31439	19251	8142	3412	1649	1033	826	1745	67076	23823	208	138	66	43312	30016	12857	37072	28401	8189	7479	5891	1470	447	12382	59116.0	7.9	7.8	8.1	8.3	7.4	8.1	8.2	7.9	7.8	7.8	7.6	7.3	7.4	7.2	8.5	7.0	8.0	7.9	$8,114,627	3061842	$11,176,469
1	10	Big Hero 6 (2014)	7.8	315485	Animation	Action	Adventure	74.0	165000000.0	NaN	50311	61304	103726	65681	22389	6830	2251	1036	539	1439	187383	58731	2446	1571	855	128237	91744	35122	84098	68040	14796	13974	11304	2400	525	36702	131818.0	7.7	8.2	8.2	8.0	8.7	7.9	7.8	8.3	7.7	7.6	8.0	7.6	7.6	7.9	7.9	7.2	7.9	7.7	$222,527,828	435290784	$657,818,612
2	11	Birdman or (The Unexpected Virtue of Ignorance...	7.8	448725	Comedy	Drama	NaN	88.0	18000000.0	119 min	60209	94476	121637	80828	38373	19161	10116	6750	5378	11807	292808	63310	1891	1538	334	178850	142244	34666	129547	108049	19457	26016	21166	4329	656	52288	203731.0	7.8	7.5	8.5	8.6	7.9	8.0	8.1	7.7	7.6	7.6	7.3	7.2	7.3	7.0	7.8	7.1	7.9	7.7	$42,340,598	60874496	$103,215,094
3	12	Black Swan (2010)	8.0	587893	Drama	Thriller	NaN	79.0	13000000.0	108 min	93798	136615	174500	97826	40319	16993	9084	6065	3981	8726	356707	143077	1112	583	516	244970	159567	82856	204465	156163	45352	35111	27022	7459	802	86552	306578.0	8.0	8.0	8.5	8.6	8.4	8.1	8.1	8.1	7.9	7.9	8.0	7.5	7.5	7.4	7.9	7.6	8.0	8.0	$106,954,678	222443368	$329,398,046
4	13	Boyhood (2014)	7.9	290327	Drama	NaN	NaN	100.0	4000000.0	165 min	49673	62055	76838	52238	23789	10431	4906	3071	2248	5086	183807	51558	1393	995	381	123006	92639	29076	81594	65261	15118	17881	13995	3567	559	36433	134679.0	8.0	7.7	8.1	8.1	8.0	8.1	8.1	7.8	7.8	7.8	7.6	7.7	7.7	7.7	8.2	7.2	8.0	7.9	$25,352,281	19143000	$44,495,281
5	14	Bridge of Spies (2015)	7.6	223756	Drama	History	Thriller	81.0	40000000.0	142 min	15757	32840	83322	63800	19183	5178	1657	735	419	878	152707	23978	846	732	104	76784	64810	11177	70780	61525	8196	18494	15504	2667	545	24273	105678.0	7.6	7.6	7.9	7.9	7.8	7.7	7.7	7.6	7.5	7.5	7.5	7.7	7.6	7.9	7.7	7.4	7.7	7.5	$72,313,754	93164594	$165,478,348
6	15	Captain America: Civil War (2016)	7.9	431555	Action	Adventure	Sci-Fi	75.0	250000000.0	147 min	81893	90156	117188	79377	32782	12322	5095	2994	1989	7786	264239	43818	3572	2865	683	148991	124124	23355	105069	91345	12135	19151	16351	2459	593	48777	153638.0	7.8	7.9	8.3	8.3	8.6	8.0	8.0	8.0	7.7	7.7	7.8	7.6	7.6	7.9	7.7	7.5	8.1	7.7	$408,084,349	745220146	$1,153,304,495
7	16	Captain America: The Winter Soldier (2014)	7.8	552706	Action	Adventure	Sci-Fi	70.0	170000000.0	136 min	84943	103896	169440	120197	44124	14639	5571	2735	1932	5248	360615	66751	3765	2900	844	208526	170111	36456	150264	129500	18637	28922	24313	4103	720	72120	213180.0	7.7	7.9	8.3	8.2	8.5	7.8	7.8	7.9	7.6	7.6	7.7	7.7	7.6	7.8	7.6	7.5	8.1	7.5	$259,766,572	454497695	$714,264,267
8	17	Captain Fantastic (2016)	7.9	115194	Comedy	Drama	NaN	72.0	5000000.0	NaN	16165	24762	39686	22429	7134	2255	982	542	419	832	71760	19138	447	329	112	40918	30740	9707	36357	29410	6414	8123	6521	1433	351	10694	56956.0	7.9	8.1	8.2	8.2	8.2	8.0	8.0	8.2	7.8	7.7	7.9	7.7	7.6	7.9	8.5	6.8	7.8	7.8	$5,879,736	n/a	$5,879,736
9	18	Captain Phillips (2013)	7.8	350818	Biography	Drama	Thriller	83.0	55000000.0	134 min	37461	70216	133266	76657	21791	6099	2051	1062	707	1517	247889	41602	995	838	147	131052	110723	19092	114418	98191	14686	24670	20178	4053	633	43042	165981.0	7.8	7.9	8.2	8.2	8.1	8.0	8.0	7.9	7.7	7.7	7.8	7.7	7.7	8.0	8.0	7.4	7.8	7.8	$107,100,855	111690956	$218,791,811

7.Skip rows from the end of the file¶

In [21]:

df = pd.read_excel('../input/datasetsdifferent-format/IMDB.xlsx', sheet_name=1, ski_footer=10)
df.tail(10)

Out[21]:

	X	Title	Rating	TotalVotes	Genre1	Genre2	Genre3	MetaCritic	Budget	Runtime	CVotes10	CVotes09	CVotes08	CVotes07	CVotes06	CVotes05	CVotes04	CVotes03	CVotes02	CVotes01	CVotesMale	CVotesFemale	CVotesU18	CVotesU18M	CVotesU18F	CVotes1829	CVotes1829M	CVotes1829F	CVotes3044	CVotes3044M	CVotes3044F	CVotes45A	CVotes45AM	CVotes45AF	CVotes1000	CVotesUS	CVotesnUS	VotesM	VotesF	VotesU18	VotesU18M	VotesU18F	Votes1829	Votes1829M	Votes1829F	Votes3044	Votes3044M	Votes3044F	Votes45A	Votes45AM	Votes45AF	VotesIMDB	Votes1000	VotesUS	VotesnUS	Domestic	Foreign	Worldwide
107	109	True Grit (2010)	7.6	257670	Adventure	Drama	Western	80.0	38000000.0	110 min	21094	40901	91825	67175	23055	7191	2678	1305	779	1672	197105	27125	381	340	35	89394	76864	11720	104201	91807	11163	25641	21885	3369	747	53749	137672.0	7.7	7.6	7.8	7.8	7.5	7.7	7.7	7.6	7.6	7.6	7.5	7.7	7.7	7.7	7.8	7.3	7.9	7.6	$171,243,005	81033922	$252,276,927
108	110	Tucker and Dale vs Evil (2010)	7.6	138624	Comedy	Horror	NaN	65.0	5000000.0	NaN	16572	19818	44460	35863	13456	4588	1684	855	479	848	106144	15113	219	198	20	52889	45169	7232	56379	49634	6156	8861	7645	1072	540	26213	73918.0	7.5	7.7	7.7	7.7	8.2	7.6	7.6	7.6	7.5	7.5	7.7	7.5	7.4	7.7	7.5	7.1	7.7	7.5	$223,838	4525678	$4,749,516
109	111	Tyrannosaur (2011)	7.6	26016	Drama	NaN	NaN	65.0	1000000.0	NaN	2060	4083	9078	6754	2468	755	310	146	111	251	19827	3649	6	6	7314	5920	1338	12497	10628	1724	3311	2784	480	4	2231	18173	NaN	7.6	7.6	6.0	6.0	7.6	7.6	7.6	7.5	7.5	7.6	7.5	7.5	7.4	5.8	6.5	7.4	7.6	NaN	$22,321	n/a	$22,321
110	112	Warrior (2011)	8.2	361049	Action	Drama	Sport	71.0	25000000.0	140 min	74983	96953	106673	52972	16668	5727	2353	1205	1050	2479	270734	31075	673	583	84	153824	136536	16000	117636	105144	11019	15201	12960	1990	586	45342	176397.0	8.2	8.2	8.5	8.5	8.7	8.4	8.4	8.4	8.0	8.0	8.0	7.7	7.7	7.5	7.8	7.1	8.2	8.1	$13,657,115	9400000	$23,057,115
111	113	What We Do in the Shadows (2014)	7.6	87975	Comedy	Horror	NaN	76.0	1600000.0	NaN	10485	14507	28608	20735	7696	2802	1200	721	450	781	57028	15840	268	209	56	32406	23869	8125	31707	25592	5689	6013	4740	1138	389	12341	45062.0	7.5	7.8	7.9	7.9	8.0	7.7	7.7	7.9	7.5	7.4	7.8	7.3	7.3	7.7	7.7	6.8	7.7	7.5	$3,469,224	2794000	$6,263,224
112	114	Whiplash (2014)	8.5	492285	Drama	Music	NaN	88.0	3300000.0	107 min	110404	161864	132656	56007	16577	6031	2937	1859	1263	2723	308900	71066	2878	2200	660	205839	161853	41944	123712	102839	19018	23345	19072	3812	590	49868	213952.0	8.5	8.4	9.0	9.1	8.9	8.6	8.7	8.5	8.3	8.3	8.2	8.1	8.1	8.2	8.7	8.0	8.6	8.4	$13,092,000	35890041	$48,982,041
113	115	Wreck-It Ralph (2012)	7.7	295125	Animation	Adventure	Comedy	72.0	165000000.0	NaN	41980	50262	96477	67934	24894	7748	2724	1190	703	1226	190983	50202	1663	1182	467	120962	90759	29003	90203	74767	14148	13706	11356	2112	614	44962	129487.0	7.7	7.8	7.9	7.9	8.0	7.9	7.9	7.9	7.6	7.6	7.7	7.4	7.4	7.5	7.4	7.2	7.9	7.6	$189,422,889	281800000	$471,222,889
114	116	X-Men: Days of Future Past (2014)	8.0	560736	Action	Adventure	Sci-Fi	74.0	200000000.0	132 min	91765	127521	183578	104658	33027	10059	3710	1903	1225	3301	370835	71008	3038	2403	614	220178	179039	39094	158607	135392	20927	26834	22460	3884	710	67889	229049.0	8.0	8.1	8.4	8.4	8.6	8.1	8.1	8.2	7.8	7.8	8.0	7.7	7.7	7.9	7.5	7.4	8.1	7.9	$233,921,534	513941241	$747,862,775
115	117	X-Men: First Class (2011)	7.8	556713	Action	Adventure	Sci-Fi	65.0	160000000.0	132 min	64428	96219	200144	129352	41945	12861	4799	2349	1448	3182	382107	80444	2075	1612	443	223309	176821	44428	185909	157332	26094	30217	25051	4691	780	87542	257681.0	7.7	7.9	8.0	7.9	8.3	7.9	7.9	8.0	7.7	7.6	7.8	7.6	7.5	7.7	7.6	7.3	7.8	7.7	$146,408,305	207215819	$353,624,124
116	118	Zootopia (2016)	8.1	309474	Animation	Adventure	Comedy	78.0	150000000.0	108 min	53626	70912	102352	57261	16719	4539	1467	733	496	1386	176202	52345	2362	1641	706	119637	87499	30813	75474	61358	13034	12353	9959	2151	518	35975	122844.0	8.0	8.3	8.4	8.3	8.7	8.2	8.1	8.4	7.8	7.8	8.1	7.8	7.8	8.1	7.7	7.6	8.0	8.0	$341,268,248	682515947	$1,023,784,195

8.Choose Columns¶

we can choose column from the excel file

In [22]:

df = pd.read_excel('../input/datasetsdifferent-format/IMDB.xlsx', sheet_name= 0, usecols=2)
df.head()

Out[22]:

	X	Title	Rating
0	1	12 Years a Slave (2013)	8.1
1	2	127 Hours (2010)	7.6
2	3	50/50 (2011)	7.7
3	4	About Time (2013)	7.8
4	5	Amour (2012)	7.9

9.Column Names¶

In [23]:

df = pd.read_excel('../input/datasetsdifferent-format/IMDB.xlsx', sheet_name=0, usecols = 2, names=['X','Title', 'Rating'], )
df.head()

Out[23]:

	X	Title	Rating
0	1	12 Years a Slave (2013)	8.1
1	2	127 Hours (2010)	7.6
2	3	50/50 (2011)	7.7
3	4	About Time (2013)	7.8
4	5	Amour (2012)	7.9

10.Set an Index while reading data¶

In [24]:

df = pd.read_excel('../input/datasetsdifferent-format/IMDB.xlsx', sheet_name=0, index_col='Title')
df.head()

Out[24]:

	X	Rating	TotalVotes	Genre1	Genre2	Genre3	MetaCritic	Budget	Runtime	CVotes10	CVotes09	CVotes08	CVotes07	CVotes06	CVotes05	CVotes04	CVotes03	CVotes02	CVotes01	CVotesMale	CVotesFemale	CVotesU18	CVotesU18M	CVotesU18F	CVotes1829	CVotes1829M	CVotes1829F	CVotes3044	CVotes3044M	CVotes3044F	CVotes45A	CVotes45AM	CVotes45AF	CVotes1000	CVotesUS	CVotesnUS	VotesM	VotesF	VotesU18	VotesU18M	VotesU18F	Votes1829	Votes1829M	Votes1829F	Votes3044	Votes3044M	Votes3044F	Votes45A	Votes45AM	Votes45AF	VotesIMDB	Votes1000	VotesUS	VotesnUS	Domestic	Foreign	Worldwide
Title
12 Years a Slave (2013)	1	8.1	496092	Biography	Drama	History	96.0	20000000.0	134 min	75556	126223	161460	83070	27231	9603	4021	2420	1785	4739	313823	82012	1837	1363	457	200910	153669	45301	138762	112943	23895	29252	23072	5726	664	53328	224519.0	8.1	8.1	8.4	8.4	8.5	8.2	8.2	8.2	8.0	7.9	8.0	7.8	7.8	8.1	8.0	7.7	8.3	8.0	$56,671,993	131061209	$187,733,202
127 Hours (2010)	2	7.6	297075	Adventure	Biography	Drama	82.0	18000000.0	94 min	28939	44110	98845	78451	28394	9403	3796	1930	1161	2059	212866	44600	745	567	170	133336	106007	26152	102120	86609	14304	14895	12400	2261	649	38478	169745.0	7.6	7.6	7.9	7.9	7.9	7.7	7.8	7.7	7.5	7.5	7.5	7.3	7.3	7.5	7.6	7.0	7.7	7.6	$18,335,230	42403567	$60,738,797
50/50 (2011)	3	7.7	283935	Comedy	Drama	Romance	72.0	8000000.0	100 min	28304	47501	99524	71485	24252	7545	2381	1109	634	1202	188925	58348	506	348	153	132350	96269	34765	94745	75394	18163	12829	9912	2681	555	46947	147849.0	7.7	7.7	7.9	7.9	7.9	7.8	7.8	7.7	7.6	7.6	7.6	7.4	7.4	7.5	7.4	7.0	7.9	7.6	$35,014,192	4173591	$39,187,783
About Time (2013)	4	7.8	225412	Comedy	Drama	Fantasy	NaN	12000000.0	123 min	38556	43170	70850	45487	16542	5673	2210	1084	664	1182	126718	58098	654	325	321	92940	57778	34126	67477	50212	16222	13973	10690	3026	475	20450	111670.0	7.8	7.9	8.2	8.1	8.3	8.0	8.0	8.0	7.6	7.6	7.7	7.6	7.5	7.8	7.7	6.9	7.8	7.7	$15,322,921	71777528	$87,100,449
Amour (2012)	5	7.9	76121	Drama	Romance	NaN	94.0	8900000.0	127 min	11093	15944	22942	14187	5945	2585	1188	710	534	995	49808	16719	121	95	24	28593	20107	8167	28691	21990	6269	7425	5803	1490	391	7959	46138.0	7.8	7.9	8.6	8.7	8.5	8.0	8.0	7.9	7.7	7.7	7.9	7.9	7.8	8.1	6.6	7.2	7.9	7.8	$6,739,492	13100000	$19,839,492

11.Handle missing data while reading¶

In [25]:

df = pd.read_excel('../input/datasetsdifferent-format/IMDB.xlsx', sheet_name= 0, na_values=['nan']) ## as per missing value
df.head()

Out[25]:

	X	Title	Rating	TotalVotes	Genre1	Genre2	Genre3	MetaCritic	Budget	Runtime	CVotes10	CVotes09	CVotes08	CVotes07	CVotes06	CVotes05	CVotes04	CVotes03	CVotes02	CVotes01	CVotesMale	CVotesFemale	CVotesU18	CVotesU18M	CVotesU18F	CVotes1829	CVotes1829M	CVotes1829F	CVotes3044	CVotes3044M	CVotes3044F	CVotes45A	CVotes45AM	CVotes45AF	CVotes1000	CVotesUS	CVotesnUS	VotesM	VotesF	VotesU18	VotesU18M	VotesU18F	Votes1829	Votes1829M	Votes1829F	Votes3044	Votes3044M	Votes3044F	Votes45A	Votes45AM	Votes45AF	VotesIMDB	Votes1000	VotesUS	VotesnUS	Domestic	Foreign	Worldwide
0	1	12 Years a Slave (2013)	8.1	496092	Biography	Drama	History	96.0	20000000.0	134 min	75556	126223	161460	83070	27231	9603	4021	2420	1785	4739	313823	82012	1837	1363	457	200910	153669	45301	138762	112943	23895	29252	23072	5726	664	53328	224519.0	8.1	8.1	8.4	8.4	8.5	8.2	8.2	8.2	8.0	7.9	8.0	7.8	7.8	8.1	8.0	7.7	8.3	8.0	$56,671,993	131061209	$187,733,202
1	2	127 Hours (2010)	7.6	297075	Adventure	Biography	Drama	82.0	18000000.0	94 min	28939	44110	98845	78451	28394	9403	3796	1930	1161	2059	212866	44600	745	567	170	133336	106007	26152	102120	86609	14304	14895	12400	2261	649	38478	169745.0	7.6	7.6	7.9	7.9	7.9	7.7	7.8	7.7	7.5	7.5	7.5	7.3	7.3	7.5	7.6	7.0	7.7	7.6	$18,335,230	42403567	$60,738,797
2	3	50/50 (2011)	7.7	283935	Comedy	Drama	Romance	72.0	8000000.0	100 min	28304	47501	99524	71485	24252	7545	2381	1109	634	1202	188925	58348	506	348	153	132350	96269	34765	94745	75394	18163	12829	9912	2681	555	46947	147849.0	7.7	7.7	7.9	7.9	7.9	7.8	7.8	7.7	7.6	7.6	7.6	7.4	7.4	7.5	7.4	7.0	7.9	7.6	$35,014,192	4173591	$39,187,783
3	4	About Time (2013)	7.8	225412	Comedy	Drama	Fantasy	NaN	12000000.0	123 min	38556	43170	70850	45487	16542	5673	2210	1084	664	1182	126718	58098	654	325	321	92940	57778	34126	67477	50212	16222	13973	10690	3026	475	20450	111670.0	7.8	7.9	8.2	8.1	8.3	8.0	8.0	8.0	7.6	7.6	7.7	7.6	7.5	7.8	7.7	6.9	7.8	7.7	$15,322,921	71777528	$87,100,449
4	5	Amour (2012)	7.9	76121	Drama	Romance	NaN	94.0	8900000.0	127 min	11093	15944	22942	14187	5945	2585	1188	710	534	995	49808	16719	121	95	24	28593	20107	8167	28691	21990	6269	7425	5803	1490	391	7959	46138.0	7.8	7.9	8.6	8.7	8.5	8.0	8.0	7.9	7.7	7.7	7.9	7.9	7.8	8.1	6.6	7.2	7.9	7.8	$6,739,492	13100000	$19,839,492

2.4.Reading data from some other popular formats ¶

1.Reading JSON data into Pandas¶

In [26]:

movies_json = pd.read_json('../input/datasetsdifferent-format/IMDB.json')
movies_json.head()

Out[26]:

	Budget	CVotes01	CVotes02	CVotes03	CVotes04	CVotes05	CVotes06	CVotes07	CVotes08	CVotes09	CVotes10	CVotes1000	CVotes1829	CVotes1829F	CVotes1829M	CVotes3044	CVotes3044F	CVotes3044M	CVotes45A	CVotes45AF	CVotes45AM	CVotesFemale	CVotesMale	CVotesU18	CVotesU18F	CVotesU18M	CVotesUS	CVotesnUS	Domestic	Foreign	Genre1	Genre2	Genre3	MetaCritic	Rating	Runtime	Title	TotalVotes	Votes1000	Votes1829	Votes1829F	Votes1829M	Votes3044	Votes3044F	Votes3044M	Votes45A	Votes45AF	Votes45AM	VotesF	VotesIMDB	VotesM	VotesU18	VotesU18F	VotesU18M	VotesUS	VotesnUS	Worldwide	X
0	20000000	4739	1785	2420	4021	9603	27231	83070	161460	126223	75556	664	200910	45301	153669	138762	23895	112943	29252	5726	23072	82012	313823	1837	457	1363	53328	224519	$56,671,993	131061209	Biography	Drama	History	96	8.1	134 min	12 Years a Slave�(2013)	496092	7.7	8.2	8.2	8.2	8.0	8.0	7.9	7.8	8.1	7.8	8.1	8.0	8.1	8.4	8.5	8.4	8.3	8	$187,733,202	1
1	18000000	2059	1161	1930	3796	9403	28394	78451	98845	44110	28939	649	133336	26152	106007	102120	14304	86609	14895	2261	12400	44600	212866	745	170	567	38478	169745	$18,335,230	42403567	Adventure	Biography	Drama	82	7.6	94 min	127 Hours�(2010)	297075	7.0	7.7	7.7	7.8	7.5	7.5	7.5	7.3	7.5	7.3	7.6	7.6	7.6	7.9	7.9	7.9	7.7	7.6	$60,738,797	2
2	8000000	1202	634	1109	2381	7545	24252	71485	99524	47501	28304	555	132350	34765	96269	94745	18163	75394	12829	2681	9912	58348	188925	506	153	348	46947	147849	$35,014,192	4173591	Comedy	Drama	Romance	72	7.7	100 min	50/50�(2011)	283935	7.0	7.8	7.7	7.8	7.6	7.6	7.6	7.4	7.5	7.4	7.7	7.4	7.7	7.9	7.9	7.9	7.9	7.6	$39,187,783	3
3	12000000	1182	664	1084	2210	5673	16542	45487	70850	43170	38556	475	92940	34126	57778	67477	16222	50212	13973	3026	10690	58098	126718	654	321	325	20450	111670	$15,322,921	71777528	Comedy	Drama	Fantasy	NA	7.8	123 min	About Time�(2013)	225412	6.9	8.0	8.0	8.0	7.6	7.7	7.6	7.6	7.8	7.5	7.9	7.7	7.8	8.2	8.3	8.1	7.8	7.7	$87,100,449	4
4	8900000	995	534	710	1188	2585	5945	14187	22942	15944	11093	391	28593	8167	20107	28691	6269	21990	7425	1490	5803	16719	49808	121	24	95	7959	46138	$6,739,492	13100000	Drama	Romance		94	7.9	127 min	Amour�(2012)	76121	7.2	8.0	7.9	8.0	7.7	7.9	7.7	7.9	8.1	7.8	7.9	6.6	7.8	8.6	8.5	8.7	7.9	7.8	$19,839,492	5

2.Reading HTML data¶

In [27]:

df = pd.read_html('../input/datasetsdifferent-format/IMDB.html')
# df

3.Read pickle file¶

In [28]:

df = pd.read_pickle('../input/datasetsdifferent-format/IMDB.p')
df.head()

Out[28]:

	X	Title	Rating	TotalVotes	Genre1	Genre2	Genre3	MetaCritic	Budget	Runtime	CVotes10	CVotes09	CVotes08	CVotes07	CVotes06	CVotes05	CVotes04	CVotes03	CVotes02	CVotes01	CVotesMale	CVotesFemale	CVotesU18	CVotesU18M	CVotesU18F	CVotes1829	CVotes1829M	CVotes1829F	CVotes3044	CVotes3044M	CVotes3044F	CVotes45A	CVotes45AM	CVotes45AF	CVotes1000	CVotesUS	CVotesnUS	VotesM	VotesF	VotesU18	VotesU18M	VotesU18F	Votes1829	Votes1829M	Votes1829F	Votes3044	Votes3044M	Votes3044F	Votes45A	Votes45AM	Votes45AF	VotesIMDB	Votes1000	VotesUS	VotesnUS	Domestic	Foreign	Worldwide
0	1	12 Years a SlaveÂ (2013)	8.1	496092	Biography	Drama	History	96.0	20000000.0	134 min	75556	126223	161460	83070	27231	9603	4021	2420	1785	4739	313823	82012	1837	1363	457	200910	153669	45301	138762	112943	23895	29252	23072	5726	664	53328	224519.0	8.1	8.1	8.4	8.4	8.5	8.2	8.2	8.2	8.0	7.9	8.0	7.8	7.8	8.1	8.0	7.7	8.3	8.0	$56,671,993	131061209	$187,733,202
1	2	127 HoursÂ (2010)	7.6	297075	Adventure	Biography	Drama	82.0	18000000.0	94 min	28939	44110	98845	78451	28394	9403	3796	1930	1161	2059	212866	44600	745	567	170	133336	106007	26152	102120	86609	14304	14895	12400	2261	649	38478	169745.0	7.6	7.6	7.9	7.9	7.9	7.7	7.8	7.7	7.5	7.5	7.5	7.3	7.3	7.5	7.6	7.0	7.7	7.6	$18,335,230	42403567	$60,738,797
2	3	50/50Â (2011)	7.7	283935	Comedy	Drama	Romance	72.0	8000000.0	100 min	28304	47501	99524	71485	24252	7545	2381	1109	634	1202	188925	58348	506	348	153	132350	96269	34765	94745	75394	18163	12829	9912	2681	555	46947	147849.0	7.7	7.7	7.9	7.9	7.9	7.8	7.8	7.7	7.6	7.6	7.6	7.4	7.4	7.5	7.4	7.0	7.9	7.6	$35,014,192	4173591	$39,187,783
3	4	About TimeÂ (2013)	7.8	225412	Comedy	Drama	Fantasy	NaN	12000000.0	123 min	38556	43170	70850	45487	16542	5673	2210	1084	664	1182	126718	58098	654	325	321	92940	57778	34126	67477	50212	16222	13973	10690	3026	475	20450	111670.0	7.8	7.9	8.2	8.1	8.3	8.0	8.0	8.0	7.6	7.6	7.7	7.6	7.5	7.8	7.7	6.9	7.8	7.7	$15,322,921	71777528	$87,100,449
4	5	AmourÂ (2012)	7.9	76121	Drama	Romance	NaN	94.0	8900000.0	127 min	11093	15944	22942	14187	5945	2585	1188	710	534	995	49808	16719	121	95	24	28593	20107	8167	28691	21990	6269	7425	5803	1490	391	7959	46138.0	7.8	7.9	8.6	8.7	8.5	8.0	8.0	7.9	7.7	7.7	7.9	7.9	7.8	8.1	6.6	7.2	7.9	7.8	$6,739,492	13100000	$19,839,492

4.Read SQL file¶

In [29]:

import sqlite3

In [30]:

conn = sqlite3.connect("../input/datasetsdifferent-format/IMDB.sqlite")
df = pd.read_sql_query("SELECT * FROM IMDB;", conn)
df.head()

Out[30]:

	X	Title	Rating	TotalVotes	Genre1	Genre2	Genre3	MetaCritic	Budget	Runtime	CVotes10	CVotes09	CVotes08	CVotes07	CVotes06	CVotes05	CVotes04	CVotes03	CVotes02	CVotes01	CVotesMale	CVotesFemale	CVotesU18	CVotesU18M	CVotesU18F	CVotes1829	CVotes1829M	CVotes1829F	CVotes3044	CVotes3044M	CVotes3044F	CVotes45A	CVotes45AM	CVotes45AF	CVotes1000	CVotesUS	CVotesnUS	VotesM	VotesF	VotesU18	VotesU18M	VotesU18F	Votes1829	Votes1829M	Votes1829F	Votes3044	Votes3044M	Votes3044F	Votes45A	Votes45AM	Votes45AF	VotesIMDB	Votes1000	VotesUS	VotesnUS	Domestic	Foreign	Worldwide
0	1	12 Years a Slave (2013)	8.1	496092	Biography	Drama	History	96	20000000	134 min	75556	126223	161460	83070	27231	9603	4021	2420	1785	4739	313823	82012	1837	1363	457	200910	153669	45301	138762	112943	23895	29252	23072	5726	664	53328	224519	8.1	8.1	8.4	8.4	8.5	8.2	8.2	8.2	8.0	7.9	8.0	7.8	7.8	8.1	8.0	7.7	8.3	8	$56,671,993	131061209	$187,733,202
1	2	127 Hours (2010)	7.6	297075	Adventure	Biography	Drama	82	18000000	94 min	28939	44110	98845	78451	28394	9403	3796	1930	1161	2059	212866	44600	745	567	170	133336	106007	26152	102120	86609	14304	14895	12400	2261	649	38478	169745	7.6	7.6	7.9	7.9	7.9	7.7	7.8	7.7	7.5	7.5	7.5	7.3	7.3	7.5	7.6	7.0	7.7	7.6	$18,335,230	42403567	$60,738,797
2	3	50/50 (2011)	7.7	283935	Comedy	Drama	Romance	72	8000000	100 min	28304	47501	99524	71485	24252	7545	2381	1109	634	1202	188925	58348	506	348	153	132350	96269	34765	94745	75394	18163	12829	9912	2681	555	46947	147849	7.7	7.7	7.9	7.9	7.9	7.8	7.8	7.7	7.6	7.6	7.6	7.4	7.4	7.5	7.4	7.0	7.9	7.6	$35,014,192	4173591	$39,187,783
3	4	About Time (2013)	7.8	225412	Comedy	Drama	Fantasy	NA	12000000	123 min	38556	43170	70850	45487	16542	5673	2210	1084	664	1182	126718	58098	654	325	321	92940	57778	34126	67477	50212	16222	13973	10690	3026	475	20450	111670	7.8	7.9	8.2	8.1	8.3	8.0	8.0	8.0	7.6	7.6	7.7	7.6	7.5	7.8	7.7	6.9	7.8	7.7	$15,322,921	71777528	$87,100,449
4	5	Amour (2012)	7.9	76121	Drama	Romance		94	8900000	127 min	11093	15944	22942	14187	5945	2585	1188	710	534	995	49808	16719	121	95	24	28593	20107	8167	28691	21990	6269	7425	5803	1490	391	7959	46138	7.8	7.9	8.6	8.7	8.5	8.0	8.0	7.9	7.7	7.7	7.9	7.9	7.8	8.1	6.6	7.2	7.9	7.8	$6,739,492	13100000	$19,839,492

5.Read data from clipboard¶

In [31]:

# df = pd.read_clipboard()
# # df.head()

3.Apply multiple filter criteria to a pandas DataFrame ¶

Go to top

In this section, you will learn¶

Filter using & AND Operator.
Filter using | OR Operator.
Filtering using isin method
Using *isin method* with multiple conditions

1.Read in the dataset¶

In [32]:

data_zillow = pd.read_table('../input/datasetsdifferent-format/data-zillow.csv', sep=',')
data_zillow.head()

Out[32]:

	Date	RegionID	RegionName	State	Metro	County	SizeRank	Zhvi
0	2017-05-31	6181	New York	NY	New York	Queens	0	672400
1	2017-05-31	12447	Los Angeles	CA	Los Angeles-Long Beach-Anaheim	Los Angeles	1	629900
2	2017-05-31	17426	Chicago	IL	Chicago	Cook	2	222700
3	2017-05-31	13271	Philadelphia	PA	Philadelphia	Philadelphia	3	137300
4	2017-05-31	40326	Phoenix	AZ	Phoenix	Maricopa	4	211300

2. FIlter Based on Multiple Condition¶

In [33]:

data_zillow[(data_zillow['Zhvi'] > 1000000) & (data_zillow['State'] == 'NY')].head()

Out[33]:

	Date	RegionID	RegionName	State	Metro	County	SizeRank	Zhvi
1132	2017-05-31	18375	Great Neck	NY	New York	Nassau	1132	1235800
2405	2017-05-31	54333	Scarsdale	NY	New York	Westchester	2405	1468100
2619	2017-05-31	47495	Rye	NY	New York	Westchester	2619	1736400
3032	2017-05-31	25725	Manhasset	NY	New York	Nassau	3032	1483400
3064	2017-05-31	18955	Larchmont	NY	New York	Westchester	3064	1052200

In [34]:

data_zillow[((data_zillow['State'] == 'CA') | (data_zillow['State'] == 'NY'))].head()

Out[34]:

	Date	RegionID	RegionName	State	Metro	County	SizeRank	Zhvi
0	2017-05-31	6181	New York	NY	New York	Queens	0	672400
1	2017-05-31	12447	Los Angeles	CA	Los Angeles-Long Beach-Anaheim	Los Angeles	1	629900
6	2017-05-31	54296	San Diego	CA	San Diego	San Diego	6	572100
8	2017-05-31	33839	San Jose	CA	San Jose	Santa Clara	8	877400
10	2017-05-31	20330	San Francisco	CA	San Francisco	San Francisco	10	1194300

In [35]:

zillow_filter = data_zillow['Metro'].isin(['New York','San Diego'])
data_zillow[zillow_filter].head()

Out[35]:

	Date	RegionID	RegionName	State	Metro	County	SizeRank	Zhvi
0	2017-05-31	6181	New York	NY	New York	Queens	0	672400
6	2017-05-31	54296	San Diego	CA	San Diego	San Diego	6	572100
63	2017-05-31	12970	Newark	NJ	New York	Essex	63	232800
72	2017-05-31	25320	Jersey City	NJ	New York	Hudson	72	380000
85	2017-05-31	51405	Chula Vista	CA	San Diego	San Diego	85	486900

In [36]:

zillow_filter1 = data_zillow.isin({'State': ['CA'], 'Metro': ['San Francisco']})
data_zillow[zillow_filter1].head()

Out[36]:

	Date	RegionID	RegionName	State	Metro	County	SizeRank	Zhvi
0	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN
1	NaN	NaN	NaN	CA	NaN	NaN	NaN	NaN
2	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN
3	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN
4	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN

4.Changing the datatype of a Pandas Series ¶

Go to Top

In this section you will learn¶

Changes Data int to float
Changing datatype while reading data
Converting string to datetime

1.Read Dataset¶

In [37]:

data_zillow = pd.read_table('../input/datasetsdifferent-format/data-zillow.csv', sep=',')
data_zillow.head()

Out[37]:

	Date	RegionID	RegionName	State	Metro	County	SizeRank	Zhvi
0	2017-05-31	6181	New York	NY	New York	Queens	0	672400
1	2017-05-31	12447	Los Angeles	CA	Los Angeles-Long Beach-Anaheim	Los Angeles	1	629900
2	2017-05-31	17426	Chicago	IL	Chicago	Cook	2	222700
3	2017-05-31	13271	Philadelphia	PA	Philadelphia	Philadelphia	3	137300
4	2017-05-31	40326	Phoenix	AZ	Phoenix	Maricopa	4	211300

2. Changes Data int to float¶

In [38]:

data_zillow.dtypes

Out[38]:

Date          object
RegionID       int64
RegionName    object
State         object
Metro         object
County        object
SizeRank       int64
Zhvi           int64
dtype: object

In [39]:

data_zillow['Zhvi'] = data_zillow.Zhvi.astype(float)

In [40]:

data_zillow.dtypes

Out[40]:

Date           object
RegionID        int64
RegionName     object
State          object
Metro          object
County         object
SizeRank        int64
Zhvi          float64
dtype: object

3.Changing datatype while reading data¶

By using dtype parameter in reading function we can change data types of any column as per below example

In [41]:

data_zillow1 = pd.read_csv('../input/datasetsdifferent-format/data-zillow.csv', sep=',', dtype={'Zhvi':float})
data_zillow1.dtypes

Out[41]:

Date           object
RegionID        int64
RegionName     object
State          object
Metro          object
County         object
SizeRank        int64
Zhvi          float64
dtype: object

4.Converting string to datetime¶

we can also change date data type by using pd.to_datetime()

In [42]:

pd.to_datetime(data_zillow1.Date,infer_datetime_format=True).head()

Out[42]:

0   2017-05-31
1   2017-05-31
2   2017-05-31
3   2017-05-31
4   2017-05-31
Name: Date, dtype: datetime64[ns]

5.Filter rows of a pandas DataFrame by column value ¶

Go to top

In this section, you will learn¶

Filtering Method by using filter()
Filtering Method by Regular expression in filter() function
Filter data using boolean indexing
An alternative way to filter

1. Read Dataset¶

In [43]:

data = pd.read_table('../input/datasetsdifferent-format/data-zillow.csv', sep=',')
data.head()

Out[43]:

	Date	RegionID	RegionName	State	Metro	County	SizeRank	Zhvi
0	2017-05-31	6181	New York	NY	New York	Queens	0	672400
1	2017-05-31	12447	Los Angeles	CA	Los Angeles-Long Beach-Anaheim	Los Angeles	1	629900
2	2017-05-31	17426	Chicago	IL	Chicago	Cook	2	222700
3	2017-05-31	13271	Philadelphia	PA	Philadelphia	Philadelphia	3	137300
4	2017-05-31	40326	Phoenix	AZ	Phoenix	Maricopa	4	211300

2.Filter columns by Different Ways¶

Filtering Method by using filter()
Filtering Method by Regular expression in filter() function
Filter data using boolean indexing
An alternative way to filter

In [44]:

filtered_data = data.filter(items=['State', 'Metro'])
filtered_data.head(6)

Out[44]:

	State	Metro
0	NY	New York
1	CA	Los Angeles-Long Beach-Anaheim
2	IL	Chicago
3	PA	Philadelphia
4	AZ	Phoenix
5	NV	Las Vegas

3.Filter columns by regular expression using filter()¶

In [45]:

filtered_data = data.filter(regex='Region', axis=1)
filtered_data.head()

Out[45]:

	RegionID	RegionName
0	6181	New York
1	12447	Los Angeles
2	17426	Chicago
3	13271	Philadelphia
4	40326	Phoenix

4.Filter data using boolean indexing¶

In [46]:

price_filter_series = data['Zhvi'] > 500000
price_filter_series.head()

Out[46]:

0     True
1     True
2    False
3    False
4    False
Name: Zhvi, dtype: bool

In [47]:

data[price_filter_series].head()

Out[47]:

	Date	RegionID	RegionName	State	Metro	County	SizeRank	Zhvi
0	2017-05-31	6181	New York	NY	New York	Queens	0	672400
1	2017-05-31	12447	Los Angeles	CA	Los Angeles-Long Beach-Anaheim	Los Angeles	1	629900
6	2017-05-31	54296	San Diego	CA	San Diego	San Diego	6	572100
8	2017-05-31	33839	San Jose	CA	San Jose	Santa Clara	8	877400
10	2017-05-31	20330	San Francisco	CA	San Francisco	San Francisco	10	1194300

5.An alternative way to filter¶

In [48]:

data[data.Zhvi >= 1000000].head()

Out[48]:

	Date	RegionID	RegionName	State	Metro	County	SizeRank	Zhvi
10	2017-05-31	20330	San Francisco	CA	San Francisco	San Francisco	10	1194300
181	2017-05-31	54626	Sunnyvale	CA	San Jose	Santa Clara	181	1509300
234	2017-05-31	13713	Santa Clara	CA	San Jose	Santa Clara	234	1071500
238	2017-05-31	16992	Berkeley	CA	San Francisco	Alameda	238	1102000
308	2017-05-31	13699	San Mateo	CA	San Francisco	San Mateo	308	1198300

6.Selecting multiple rows and columns from a pandas DataFrame ¶

Go to top

In this Section you can learn:¶

Select single row, single column
Select single row, multiple columns
Select single row, all columns
Select multiple rows, single column
Select multiple rows and multiple contiguous columns
Select multiple rows and multiple non-contiguous columns
Select multiple rows and all columns
Select non-contiguous rows
Selecting rows based on a specific column's value
Selecting all rows for a specific column based on a value of another column

1.Read dataset¶

In [49]:

data_zillow = pd.read_table('../input/datasetsdifferent-format/data-zillow.csv', sep=',')
data_zillow.head()

Out[49]:

	Date	RegionID	RegionName	State	Metro	County	SizeRank	Zhvi
0	2017-05-31	6181	New York	NY	New York	Queens	0	672400
1	2017-05-31	12447	Los Angeles	CA	Los Angeles-Long Beach-Anaheim	Los Angeles	1	629900
2	2017-05-31	17426	Chicago	IL	Chicago	Cook	2	222700
3	2017-05-31	13271	Philadelphia	PA	Philadelphia	Philadelphia	3	137300
4	2017-05-31	40326	Phoenix	AZ	Phoenix	Maricopa	4	211300

2.Select single row, single column¶

In [50]:

data_zillow.loc[7, 'Metro']

Out[50]:

'Dallas-Fort Worth'

In [51]:

data_zillow.iloc[7,4]

Out[51]:

'Dallas-Fort Worth'

3.Select single row, multiple columns¶

In [52]:

data_zillow.loc[7, ['Metro', 'County']]

Out[52]:

Metro     Dallas-Fort Worth
County               Dallas
Name: 7, dtype: object

In [53]:

data_zillow.iloc[7, [4,5]]

Out[53]:

Metro     Dallas-Fort Worth
County               Dallas
Name: 7, dtype: object

4.Select single row, all columns¶

In [54]:

data_zillow.loc[11, :]

Out[54]:

Date          2017-05-31
RegionID           10221
RegionName        Austin
State                 TX
Metro             Austin
County            Travis
SizeRank              11
Zhvi              321600
Name: 11, dtype: object

5.Select multiple rows, single column¶

In [55]:

data_zillow.loc[101:105, 'Metro']

Out[55]:

101                     Winston-Salem
102    Los Angeles-Long Beach-Anaheim
103                          Richmond
104             Miami-Fort Lauderdale
105                           Ventura
Name: Metro, dtype: object

6.Select multiple rows and multiple contiguous columns¶

In loc we pass the column label to fetch data.
In iloc we pass the number to fetch data.

In [56]:

data_zillow.loc[201:204, "State":"County"]

Out[56]:

	State	Metro	County
201	OH	Canton	Stark
202	LA	New Orleans	Jefferson
203	CA	Santa Maria-Santa Barbara	Santa Barbara
204	CA	Los Angeles-Long Beach-Anaheim	Los Angeles

In [57]:

data_zillow.iloc[201:205, 3:6]

Out[57]:

	State	Metro	County
201	OH	Canton	Stark
202	LA	New Orleans	Jefferson
203	CA	Santa Maria-Santa Barbara	Santa Barbara
204	CA	Los Angeles-Long Beach-Anaheim	Los Angeles

7.Select multiple rows and multiple non-contiguous columns¶

In [58]:

data_zillow.loc[201:205, ['RegionName', 'State']]

Out[58]:

	RegionName	State
201	Canton	OH
202	Metairie	LA
203	Santa Maria	CA
204	Inglewood	CA
205	Orange	CA

8.Select multiple rows and all columns¶

In [59]:

data_zillow.loc[201:205, :]

Out[59]:

	Date	RegionID	RegionName	State	Metro	County	SizeRank	Zhvi
201	2017-05-31	51260	Canton	OH	Canton	Stark	201	94400
202	2017-05-31	5914	Metairie	LA	New Orleans	Jefferson	202	232700
203	2017-05-31	47570	Santa Maria	CA	Santa Maria-Santa Barbara	Santa Barbara	203	354600
204	2017-05-31	45888	Inglewood	CA	Los Angeles-Long Beach-Anaheim	Los Angeles	204	470600
205	2017-05-31	33252	Orange	CA	Los Angeles-Long Beach-Anaheim	Orange	205	652000

9.Select non-contiguous rows¶

In [60]:

data_zillow.loc[[0,5,10], :]

Out[60]:

	Date	RegionID	RegionName	State	Metro	County	SizeRank	Zhvi
0	2017-05-31	6181	New York	NY	New York	Queens	0	672400
5	2017-05-31	18959	Las Vegas	NV	Las Vegas	Clark	5	216500
10	2017-05-31	20330	San Francisco	CA	San Francisco	San Francisco	10	1194300

10.Selecting rows based on a specific column's value¶

In [61]:

data_zillow.loc[data_zillow.County=="Queens"]

Out[61]:

	Date	RegionID	RegionName	State	Metro	County	SizeRank	Zhvi
0	2017-05-31	6181	New York	NY	New York	Queens	0	672400

11.Selecting all rows for a specific column based on a value of another column¶

In [62]:

data_zillow.loc[data_zillow.Metro=="New York", "County"].head()

Out[62]:

0           Queens
63           Essex
72          Hudson
138    Westchester
176        Passaic
Name: County, dtype: object

7.Sorting a pandas DataFrame or a Series ¶

Go to top

In this section you can learn:¶

Simple sort
Changing the sort order
Sort by more than one column
Sort by multiple columns and mixed ascending order
Sort a Series

1.Read dataset¶

In [63]:

data_zillow = pd.read_table('../input/datasetsdifferent-format/data-zillow.csv', sep=',')
data_zillow.head()

Out[63]:

	Date	RegionID	RegionName	State	Metro	County	SizeRank	Zhvi
0	2017-05-31	6181	New York	NY	New York	Queens	0	672400
1	2017-05-31	12447	Los Angeles	CA	Los Angeles-Long Beach-Anaheim	Los Angeles	1	629900
2	2017-05-31	17426	Chicago	IL	Chicago	Cook	2	222700
3	2017-05-31	13271	Philadelphia	PA	Philadelphia	Philadelphia	3	137300
4	2017-05-31	40326	Phoenix	AZ	Phoenix	Maricopa	4	211300

2.Simple sort¶

Sort the value by using column name

In [64]:

data_zillow.sort_values('Metro').head()

Out[64]:

	Date	RegionID	RegionName	State	Metro	County	SizeRank	Zhvi
9851	2017-05-31	48458	Westport	WA	Aberdeen	Grays Harbor	9851	144600
4996	2017-05-31	36873	Elma	WA	Aberdeen	Grays Harbor	4996	175200
5090	2017-05-31	35514	Hoquiam	WA	Aberdeen	Grays Harbor	5090	95700
9401	2017-05-31	33215	Ocean Shores	WA	Aberdeen	Grays Harbor	9401	152400
9149	2017-05-31	18370	Grayland	WA	Aberdeen	Grays Harbor	9149	143900

3.Changing the sort order¶

Sorting the value basis on the descending order

In [65]:

sorted = data_zillow.sort_values('Metro', ascending=False)
sorted.head()

Out[65]:

	Date	RegionID	RegionName	State	Metro	County	SizeRank	Zhvi
8064	2017-05-31	19538	Nashport	OH	Zanesville	Muskingum	8064	153800
10271	2017-05-31	15262	Hopewell	OH	Zanesville	Muskingum	10271	138700
10373	2017-05-31	49730	Norwich	OH	Zanesville	Muskingum	10373	145100
5423	2017-05-31	53527	New Concord	OH	Zanesville	Muskingum	5423	138300
7595	2017-05-31	17815	Dresden	OH	Zanesville	Muskingum	7595	118400

4.Sort by more than one column¶

In [66]:

sorted = data_zillow.sort_values(by=['Metro','County'])
sorted.head()

Out[66]:

	Date	RegionID	RegionName	State	Metro	County	SizeRank	Zhvi
2073	2017-05-31	30116	Aberdeen	WA	Aberdeen	Grays Harbor	2073	127800
4568	2017-05-31	56078	Montesano	WA	Aberdeen	Grays Harbor	4568	182000
4996	2017-05-31	36873	Elma	WA	Aberdeen	Grays Harbor	4996	175200
5090	2017-05-31	35514	Hoquiam	WA	Aberdeen	Grays Harbor	5090	95700
7108	2017-05-31	6275	Oakville	WA	Aberdeen	Grays Harbor	7108	186900

5.Sort by multiple columns and mixed ascending order¶

In [67]:

sorted = data_zillow.sort_values(by=['Metro','County', 'Zhvi'], 
                            ascending=[True, True, False])
sorted.head()

Out[67]:

	Date	RegionID	RegionName	State	Metro	County	SizeRank	Zhvi
7108	2017-05-31	6275	Oakville	WA	Aberdeen	Grays Harbor	7108	186900
4568	2017-05-31	56078	Montesano	WA	Aberdeen	Grays Harbor	4568	182000
4996	2017-05-31	36873	Elma	WA	Aberdeen	Grays Harbor	4996	175200
8420	2017-05-31	19269	McCleary	WA	Aberdeen	Grays Harbor	8420	170700
9401	2017-05-31	33215	Ocean Shores	WA	Aberdeen	Grays Harbor	9401	152400

6.Sort a Series¶

1.Let's create a Series object

In [68]:

regions = data_zillow.RegionID
type(regions)

Out[68]:

pandas.core.series.Series

Let's sort the series¶

1.Original Series

In [69]:

regions.head()

Out[69]:

0     6181
1    12447
2    17426
3    13271
4    40326
Name: RegionID, dtype: int64

2.Sorted

In [70]:

regions.sort_values().head()

Out[70]:

3043    3301
4159    3304
4986    3305
1762    3310
3116    3312
Name: RegionID, dtype: int64

8.Using pandas Series data structure to select a subset of the data ¶

Go to top

In this Section, you will learn below topics¶

Select data
- Select a Series with bracket notation
DataFrame vs Series
- Multi Column Selection - Series or DataFrame
- Select using dot notation
Creating a new series by selection

1.Read Dataset¶

In [71]:

data = pd.read_table('../input/datasetsdifferent-format/data-zillow.csv', sep=',')

In [72]:

data.head()

Out[72]:

	Date	RegionID	RegionName	State	Metro	County	SizeRank	Zhvi
0	2017-05-31	6181	New York	NY	New York	Queens	0	672400
1	2017-05-31	12447	Los Angeles	CA	Los Angeles-Long Beach-Anaheim	Los Angeles	1	629900
2	2017-05-31	17426	Chicago	IL	Chicago	Cook	2	222700
3	2017-05-31	13271	Philadelphia	PA	Philadelphia	Philadelphia	3	137300
4	2017-05-31	40326	Phoenix	AZ	Phoenix	Maricopa	4	211300

2.Select data¶

Select a Series with bracket notation

In [73]:

regions = data['RegionName']
type(regions)

Out[73]:

pandas.core.series.Series

In [74]:

regions.head()

Out[74]:

0        New York
1     Los Angeles
2         Chicago
3    Philadelphia
4         Phoenix
Name: RegionName, dtype: object

3.DataFrame vs Series¶

Multi Column Selection - Series or DataFrame

In [75]:

region_n_state = data[['RegionName', 'State']]
region_n_state.head()

Out[75]:

	RegionName	State
0	New York	NY
1	Los Angeles	CA
2	Chicago	IL
3	Philadelphia	PA
4	Phoenix	AZ

In [76]:

type(region_n_state)

Out[76]:

pandas.core.frame.DataFrame

Select using dot notation

In [77]:

data.State.head()

Out[77]:

0    NY
1    CA
2    IL
3    PA
4    AZ
Name: State, dtype: object

4.Creating a new series by selection¶

In [78]:

data['Address'] = data.County + ', ' + data.Metro + ', ' + data.State

In [79]:

data.Address.head()

Out[79]:

0                               Queens, New York, NY
1    Los Angeles, Los Angeles-Long Beach-Anaheim, CA
2                                  Cook, Chicago, IL
3                     Philadelphia, Philadelphia, PA
4                              Maricopa, Phoenix, AZ
Name: Address, dtype: object

9.Using string methods in pandas ¶

Go To Top

In this section, you will learn¶

Check for a substring
Make values of a series or column uppercase
Make values lowercase
Get the length of each value in a column
Remove all whitespace from the beginning
Replace parts of a column's values

1. Read dataset¶

In [80]:

data = pd.read_table('../input/datasetsdifferent-format/data-zillow.csv', sep=',')
data.head()

Out[80]:

	Date	RegionID	RegionName	State	Metro	County	SizeRank	Zhvi
0	2017-05-31	6181	New York	NY	New York	Queens	0	672400
1	2017-05-31	12447	Los Angeles	CA	Los Angeles-Long Beach-Anaheim	Los Angeles	1	629900
2	2017-05-31	17426	Chicago	IL	Chicago	Cook	2	222700
3	2017-05-31	13271	Philadelphia	PA	Philadelphia	Philadelphia	3	137300
4	2017-05-31	40326	Phoenix	AZ	Phoenix	Maricopa	4	211300

2.Check for a substring¶

In [81]:

data.RegionName.str.contains('New').head()

Out[81]:

0     True
1    False
2    False
3    False
4    False
Name: RegionName, dtype: bool

3.Make values of a series or column uppercase¶

In [82]:

data.RegionName.str.upper().head()

Out[82]:

0        NEW YORK
1     LOS ANGELES
2         CHICAGO
3    PHILADELPHIA
4         PHOENIX
Name: RegionName, dtype: object

4.Make values lowercase¶

In [83]:

data.RegionName.str.lower().head()

Out[83]:

0        new york
1     los angeles
2         chicago
3    philadelphia
4         phoenix
Name: RegionName, dtype: object

5.Get the length of each value in a column¶

In [84]:

data.County.str.len().head()

Out[84]:

0     6
1    11
2     4
3    12
4     8
Name: County, dtype: int64

6.Remove all whitespace from the beginning¶

In [85]:

data.RegionName.str.lstrip().head()

Out[85]:

0        New York
1     Los Angeles
2         Chicago
3    Philadelphia
4         Phoenix
Name: RegionName, dtype: object

7.Replace parts of a column's values¶

In [86]:

data.RegionName.str.replace(' ', '').head()

Out[86]:

0         NewYork
1      LosAngeles
2         Chicago
3    Philadelphia
4         Phoenix
Name: RegionName, dtype: object

10.Using the axis parameter in pandas ¶

Go To TOP

In this section, you can learn¶

Usage of axis parameter
axis usage examples
- axis = 0
- axis = 1
- use labels instead of 0 and 1

1.Read Dataset¶

In [87]:

data = pd.read_table('../input/datasetsdifferent-format/data-zillow.csv', sep=',')
data.head()

Out[87]:

	Date	RegionID	RegionName	State	Metro	County	SizeRank	Zhvi
0	2017-05-31	6181	New York	NY	New York	Queens	0	672400
1	2017-05-31	12447	Los Angeles	CA	Los Angeles-Long Beach-Anaheim	Los Angeles	1	629900
2	2017-05-31	17426	Chicago	IL	Chicago	Cook	2	222700
3	2017-05-31	13271	Philadelphia	PA	Philadelphia	Philadelphia	3	137300
4	2017-05-31	40326	Phoenix	AZ	Phoenix	Maricopa	4	211300

2.Usage of axis parameter¶

In [88]:

data.head()

Out[88]:

	Date	RegionID	RegionName	State	Metro	County	SizeRank	Zhvi
0	2017-05-31	6181	New York	NY	New York	Queens	0	672400
1	2017-05-31	12447	Los Angeles	CA	Los Angeles-Long Beach-Anaheim	Los Angeles	1	629900
2	2017-05-31	17426	Chicago	IL	Chicago	Cook	2	222700
3	2017-05-31	13271	Philadelphia	PA	Philadelphia	Philadelphia	3	137300
4	2017-05-31	40326	Phoenix	AZ	Phoenix	Maricopa	4	211300

In [89]:

data.axes

Out[89]:

[RangeIndex(start=0, stop=10830, step=1),
 Index(['Date', 'RegionID', 'RegionName', 'State', 'Metro', 'County',
        'SizeRank', 'Zhvi'],
       dtype='object')]

1.axis = 0¶

In [90]:

data.mean(axis=0)

Out[90]:

RegionID     84344.818837
SizeRank      5414.500000
Zhvi        250307.590028
dtype: float64

2.axis = 1¶

In [91]:

data.mean(axis=1).head()

Out[91]:

0    226193.666667
1    214116.000000
2     80042.666667
3     50191.333333
4     83876.666667
dtype: float64

3.use labels instead of 0 and 1¶

In [92]:

data.mean(axis='rows')

Out[92]:

RegionID     84344.818837
SizeRank      5414.500000
Zhvi        250307.590028
dtype: float64

In [93]:

data.mean(axis='columns').head()

Out[93]:

0    226193.666667
1    214116.000000
2     80042.666667
3     50191.333333
4     83876.666667
dtype: float64

In [94]:

data.drop(0, axis=0).head()

Out[94]:

	Date	RegionID	RegionName	State	Metro	County	SizeRank	Zhvi
1	2017-05-31	12447	Los Angeles	CA	Los Angeles-Long Beach-Anaheim	Los Angeles	1	629900
2	2017-05-31	17426	Chicago	IL	Chicago	Cook	2	222700
3	2017-05-31	13271	Philadelphia	PA	Philadelphia	Philadelphia	3	137300
4	2017-05-31	40326	Phoenix	AZ	Phoenix	Maricopa	4	211300
5	2017-05-31	18959	Las Vegas	NV	Las Vegas	Clark	5	216500

In [95]:

data.drop('Date', axis=1).head()

Out[95]:

	RegionID	RegionName	State	Metro	County	SizeRank	Zhvi
0	6181	New York	NY	New York	Queens	0	672400
1	12447	Los Angeles	CA	Los Angeles-Long Beach-Anaheim	Los Angeles	1	629900
2	17426	Chicago	IL	Chicago	Cook	2	222700
3	13271	Philadelphia	PA	Philadelphia	Philadelphia	3	137300
4	40326	Phoenix	AZ	Phoenix	Maricopa	4	211300

In [96]:

data.drop('Date', axis=1).head()

Out[96]:

	RegionID	RegionName	State	Metro	County	SizeRank	Zhvi
0	6181	New York	NY	New York	Queens	0	672400
1	12447	Los Angeles	CA	Los Angeles-Long Beach-Anaheim	Los Angeles	1	629900
2	17426	Chicago	IL	Chicago	Cook	2	222700
3	13271	Philadelphia	PA	Philadelphia	Philadelphia	3	137300
4	40326	Phoenix	AZ	Phoenix	Maricopa	4	211300

11.Applying a function to a pandas Series or DataFrame ¶

Go To TOP

In this section, you can learn¶

Apply functions using apply()
Apply functions using applymap()
Applying our own functions

1.Read dataset¶

In [97]:

data = pd.read_csv('../input/datasetsdifferent-format/data-titanic.csv')
data.head()

Out[97]:

	PassengerId	Survived	Pclass	Name	Sex	Age	SibSp	Ticket	Fare	Cabin	Embarked
0	1	0	3	Braund, Mr. Owen Harris	male	22.0	1	A/5 21171	7.2500	NaN	S
1	2	1	1	Cumings, Mrs. John Bradley (Florence Briggs Th...	female	38.0	1	PC 17599	71.2833	C85	C
2	3	1	3	Heikkinen, Miss. Laina	female	26.0	0	STON/O2. 3101282	7.9250	NaN	S
3	4	1	1	Futrelle, Mrs. Jacques Heath (Lily May Peel)	female	35.0	1	113803	53.1000	C123	S
4	5	0	3	Allen, Mr. William Henry	male	35.0	0	373450	8.0500	NaN	S

2.Apply functions using apply()¶

In [98]:

func_lower = lambda x: x.lower()
data.Name.apply(func_lower).head()

Out[98]:

0                              braund, mr. owen harris
1    cumings, mrs. john bradley (florence briggs th...
2                               heikkinen, miss. laina
3         futrelle, mrs. jacques heath (lily may peel)
4                             allen, mr. william henry
Name: Name, dtype: object

3.Apply functions using applymap()¶

In [99]:

data[['Age', 'Pclass']].applymap(np.square).head()

Out[99]:

	Age	Pclass
0	484.0	9
1	1444.0	1
2	676.0	9
3	1225.0	1
4	1225.0	9

3.Applying our own functions¶

In [100]:

def my_func(i):
    return i + 20

In [101]:

data[['Age', 'Pclass']].applymap(my_func).head()

Out[101]:

	Age	Pclass
0	42.0	23
1	58.0	21
2	46.0	23
3	55.0	21
4	55.0	23

12.Handling SettingWithCopyWarning ¶

Go To TOP

In this section, you can learn¶

A SettingWithCopyWarning scenario
Handling the SettingWithCopyWarning

1.A SettingWithCopyWarning scenario¶

In [102]:

data[data.Age.isnull()].Age = data.Age.mean()

/opt/conda/lib/python3.6/site-packages/pandas/core/generic.py:4405: SettingWithCopyWarning: 
A value is trying to be set on a copy of a slice from a DataFrame.
Try using .loc[row_indexer,col_indexer] = value instead

See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy
  self[name] = value

2.Handling the SettingWithCopyWarning¶

In [103]:

data[data.Age.isnull()].Age.head()

Out[103]:

5    NaN
17   NaN
19   NaN
26   NaN
28   NaN
Name: Age, dtype: float64

In [104]:

data.loc[data.Age.isnull(), 'Age'] = data.Age.mean

In [105]:

data[data.Age.isnull()]

Out[105]:

	PassengerId	Survived	Pclass	Name	Sex	Age	SibSp	Parch	Ticket	Fare	Cabin	Embarked

13.Handling missing values in pandas ¶

Go To TOP

In this section, you can learn¶

Missing Records
1. Find out total records in the dataset
2. Number of valid records per column
Dropping missing records
1. Drop all records that have one or more missing values
2. Drop only those rows that have all records missing
Fill in missing data
1. Fill in missing data with zeros
2. Fill in missing data with a mean of the values from other rows

In [106]:

data = pd.read_csv("../input/datasetsdifferent-format/data-titanic.csv")

1. Missing Records¶

Find out total records in the dataset

In [107]:

data.shape

Out[107]:

(891, 12)

Number of valid records per column

In [108]:

data.count()

Out[108]:

PassengerId    891
Survived       891
Pclass         891
Name           891
Sex            891
Age            714
SibSp          891
Parch          891
Ticket         891
Fare           891
Cabin          204
Embarked       889
dtype: int64

2. Dropping missing records¶

Drop all records that have one or more missing values

In [109]:

data_missing_dropped = data.dropna()
data_missing_dropped.shape

Out[109]:

(183, 12)

Drop only those rows that have all records missing

In [110]:

data_all_missing_dropped = data.dropna(how="all")
data_all_missing_dropped.shape

Out[110]:

(891, 12)

3. Fill in missing data¶

Fill in missing data with zeros

In [111]:

data_filled_zeros =  data.fillna(0)
data_filled_zeros.count()

Out[111]:

PassengerId    891
Survived       891
Pclass         891
Name           891
Sex            891
Age            891
SibSp          891
Parch          891
Ticket         891
Fare           891
Cabin          891
Embarked       891
dtype: int64

Fill in missing data with a mean of the values from other rows

In [112]:

data_filled_in_mean = data.copy()
data_filled_in_mean.Age.fillna(data.Age.mean(), inplace=True)
data_filled_in_mean.count()

Out[112]:

PassengerId    891
Survived       891
Pclass         891
Name           891
Sex            891
Age            891
SibSp          891
Parch          891
Ticket         891
Fare           891
Cabin          204
Embarked       889
dtype: int64

14.Indexing in pandas dataframes ¶

Go To TOP

In this section, you can learn¶

Default Index
Set an Index post reading of data
Set an Index while reading data
Selection using Index
Reset Index

In [113]:

data = pd.read_csv('../input/datasetsdifferent-format/data-titanic.csv')

1.Default Index¶

In [114]:

data.head()

Out[114]:

	PassengerId	Survived	Pclass	Name	Sex	Age	SibSp	Ticket	Fare	Cabin	Embarked
0	1	0	3	Braund, Mr. Owen Harris	male	22.0	1	A/5 21171	7.2500	NaN	S
1	2	1	1	Cumings, Mrs. John Bradley (Florence Briggs Th...	female	38.0	1	PC 17599	71.2833	C85	C
2	3	1	3	Heikkinen, Miss. Laina	female	26.0	0	STON/O2. 3101282	7.9250	NaN	S
3	4	1	1	Futrelle, Mrs. Jacques Heath (Lily May Peel)	female	35.0	1	113803	53.1000	C123	S
4	5	0	3	Allen, Mr. William Henry	male	35.0	0	373450	8.0500	NaN	S

2. Set an Index post reading of data¶

In [115]:

data.set_index('Name').head()

Out[115]:

	PassengerId	Survived	Pclass	Sex	Age	SibSp	Parch	Ticket	Fare	Cabin	Embarked
Name
Braund, Mr. Owen Harris	1	0	3	male	22.0	1	0	A/5 21171	7.2500	NaN	S
Cumings, Mrs. John Bradley (Florence Briggs Thayer)	2	1	1	female	38.0	1	0	PC 17599	71.2833	C85	C
Heikkinen, Miss. Laina	3	1	3	female	26.0	0	0	STON/O2. 3101282	7.9250	NaN	S
Futrelle, Mrs. Jacques Heath (Lily May Peel)	4	1	1	female	35.0	1	0	113803	53.1000	C123	S
Allen, Mr. William Henry	5	0	3	male	35.0	0	0	373450	8.0500	NaN	S

3. Set an Index while reading data¶

In [116]:

data = pd.read_csv('../input/datasetsdifferent-format/data-titanic.csv', index_col=3)
data.head()

Out[116]:

	PassengerId	Survived	Pclass	Sex	Age	SibSp	Parch	Ticket	Fare	Cabin	Embarked
Name
Braund, Mr. Owen Harris	1	0	3	male	22.0	1	0	A/5 21171	7.2500	NaN	S
Cumings, Mrs. John Bradley (Florence Briggs Thayer)	2	1	1	female	38.0	1	0	PC 17599	71.2833	C85	C
Heikkinen, Miss. Laina	3	1	3	female	26.0	0	0	STON/O2. 3101282	7.9250	NaN	S
Futrelle, Mrs. Jacques Heath (Lily May Peel)	4	1	1	female	35.0	1	0	113803	53.1000	C123	S
Allen, Mr. William Henry	5	0	3	male	35.0	0	0	373450	8.0500	NaN	S

4. Selection using Index¶

In [117]:

data.loc['Braund, Mr. Owen Harris',:]

Out[117]:

PassengerId            1
Survived               0
Pclass                 3
Sex                 male
Age                   22
SibSp                  1
Parch                  0
Ticket         A/5 21171
Fare                7.25
Cabin                NaN
Embarked               S
Name: Braund, Mr. Owen Harris, dtype: object

5. Reset Index¶

In [118]:

data.reset_index(inplace=True)

In [119]:

data.head()

Out[119]:

	Name	PassengerId	Survived	Pclass	Sex	Age	SibSp	Ticket	Fare	Cabin	Embarked
0	Braund, Mr. Owen Harris	1	0	3	male	22.0	1	A/5 21171	7.2500	NaN	S
1	Cumings, Mrs. John Bradley (Florence Briggs Th...	2	1	1	female	38.0	1	PC 17599	71.2833	C85	C
2	Heikkinen, Miss. Laina	3	1	3	female	26.0	0	STON/O2. 3101282	7.9250	NaN	S
3	Futrelle, Mrs. Jacques Heath (Lily May Peel)	4	1	1	female	35.0	1	113803	53.1000	C123	S
4	Allen, Mr. William Henry	5	0	3	male	35.0	0	373450	8.0500	NaN	S

15.Merging and concatenating multiple data frames into one ¶

Go To TOP

In this section, you can learn¶

Concatenate Dataset DataFrames
Concatenate using append()
Concatenate on columns
Merging DataFrames
Left outer merge
Right outer merge
Full outer merge

1. Concatenate Dataset DataFrames¶

In [120]:

dataset1 = pd.DataFrame({'Age': ['32', '26', '29'],
                         'Sex': ['F', 'M', 'F'],
                         'State': ['CA', 'NY', 'OH']},
                         index=['Jane', 'John', 'Cathy'])
    
dataset2 = pd.DataFrame({'Age': ['34', '23', '24', '21'],
                         'Sex': ['M', 'F', 'F', 'F'],
                         'State': ['AZ', 'OR', 'CA', 'WA']},
                         index=['Dave', 'Kris', 'Xi', 'Jo'])

In [121]:

pd.concat([dataset1, dataset2])

Out[121]:

	Age	Sex	State
Jane	32	F	CA
John	26	M	NY
Cathy	29	F	OH
Dave	34	M	AZ
Kris	23	F	OR
Xi	24	F	CA
Jo	21	F	WA

2. Concatenate using append()¶

In [122]:

dataset1.append(dataset2)

Out[122]:

	Age	Sex	State
Jane	32	F	CA
John	26	M	NY
Cathy	29	F	OH
Dave	34	M	AZ
Kris	23	F	OR
Xi	24	F	CA
Jo	21	F	WA

3. Concatenate on columns¶

In [123]:

dataset1 = pd.DataFrame({'Age': ['32', '26', '29'],
                         'Sex': ['F', 'M', 'F'],
                         'State': ['CA', 'NY', 'OH']},
                         index=['Jane', 'John', 'Cathy'])

dataset2 = pd.DataFrame({'City': ['SF', 'NY', 'Columbus'],
                         'Work Status': ['No', 'Yes', 'Yes']},
                         index=['Jane', 'John', 'Cathy'])


pd.concat([dataset1, dataset2], axis=1)

Out[123]:

	Age	Sex	State	City	Work Status
Jane	32	F	CA	SF	No
John	26	M	NY	NY	Yes
Cathy	29	F	OH	Columbus	Yes

4. Merging DataFrames¶

In [124]:

dataset1 = pd.DataFrame({'Name': ['Jane', 'John', 'Cathy', 'Sarah'],
                         'Age': ['32', '26', '29', '23'],
                         'Sex': ['F', 'M', 'F', 'F'],
                         'State': ['CA', 'NY', 'OH', 'TX']})

dataset2 = pd.DataFrame({'Name': ['Jane', 'John', 'Cathy', 'Rob'],
                        'City': ['SF', 'NY', 'Columbus', 'Austin'],
                         'Work Status': ['No', 'Yes', 'Yes', 'Yes']})

pd.merge(dataset1, dataset2, on='Name', how='inner')

Out[124]:

	Name	Age	Sex	State	City	Work Status
0	Jane	32	F	CA	SF	No
1	John	26	M	NY	NY	Yes
2	Cathy	29	F	OH	Columbus	Yes

5. Left outer merge¶

In [125]:

pd.merge(dataset1, dataset2, on='Name', how='left')

Out[125]:

	Name	Age	Sex	State	City	Work Status
0	Jane	32	F	CA	SF	No
1	John	26	M	NY	NY	Yes
2	Cathy	29	F	OH	Columbus	Yes
3	Sarah	23	F	TX	NaN	NaN

6. Right outer merge¶

In [126]:

pd.merge(dataset1, dataset2, on='Name', how='right')

Out[126]:

	Name	Age	Sex	State	City	Work Status
0	Jane	32	F	CA	SF	No
1	John	26	M	NY	NY	Yes
2	Cathy	29	F	OH	Columbus	Yes
3	Rob	NaN	NaN	NaN	Austin	Yes

7. Full outer merge¶

In [127]:

pd.merge(dataset1, dataset2, on='Name', how='outer')

Out[127]:

	Name	Age	Sex	State	City	Work Status
0	Jane	32	F	CA	SF	No
1	John	26	M	NY	NY	Yes
2	Cathy	29	F	OH	Columbus	Yes
3	Sarah	23	F	TX	NaN	NaN
4	Rob	NaN	NaN	NaN	Austin	Yes

16.Modifying a Pandas Dataframe inplace ¶

Go To TOP

In this section, you can learn¶

Modify without inplace
Modify inplace
inplace not required for very method

In [128]:

top_movies = pd.read_table('../input/datasetsdifferent-format/data-movies-top-grossing.csv', sep=',')

In [129]:

top_movies.head()

Out[129]:

	Rank	Title	Worldwide gross	Year
0	1	Avatar	$2,787,965,087	2009
1	2	Titanic	$2,186,772,302	1997
2	3	Star Wars: The Force Awakens	$2,068,223,624	2015
3	4	Jurassic World	$1,671,713,208	2015
4	5	The Avengers	$1,518,812,988	2012

1.Modify without inplace¶

In [130]:

top_movies.set_index('Rank').head()

Out[130]:

	Title	Worldwide gross	Year
Rank
1	Avatar	$2,787,965,087	2009
2	Titanic	$2,186,772,302	1997
3	Star Wars: The Force Awakens	$2,068,223,624	2015
4	Jurassic World	$1,671,713,208	2015
5	The Avengers	$1,518,812,988	2012

In [131]:

top_movies.head()

Out[131]:

	Rank	Title	Worldwide gross	Year
0	1	Avatar	$2,787,965,087	2009
1	2	Titanic	$2,186,772,302	1997
2	3	Star Wars: The Force Awakens	$2,068,223,624	2015
3	4	Jurassic World	$1,671,713,208	2015
4	5	The Avengers	$1,518,812,988	2012

2.Modify inplace¶

In [132]:

top_movies.set_index('Rank', inplace=True)

In [133]:

top_movies.head()

Out[133]:

	Title	Worldwide gross	Year
Rank
1	Avatar	$2,787,965,087	2009
2	Titanic	$2,186,772,302	1997
3	Star Wars: The Force Awakens	$2,068,223,624	2015
4	Jurassic World	$1,671,713,208	2015
5	The Avengers	$1,518,812,988	2012

3.inplace not required for very method¶

In [134]:

top_movies.rename(columns = {'Year': 'Release Year'}).head()

Out[134]:

	Title	Worldwide gross	Release Year
Rank
1	Avatar	$2,787,965,087	2009
2	Titanic	$2,186,772,302	1997
3	Star Wars: The Force Awakens	$2,068,223,624	2015
4	Jurassic World	$1,671,713,208	2015
5	The Avengers	$1,518,812,988	2012

17.Removing columns from a pandas DataFrame ¶

Go To TOP

In this section, you can learn¶

Remove one column
Remove more than one column
Remove row(s)

1.Remove one column¶

In [135]:

data = pd.read_csv('../input/datasetsdifferent-format/data-titanic.csv', index_col=3)
data.head()

Out[135]:

	PassengerId	Survived	Pclass	Sex	Age	SibSp	Parch	Ticket	Fare	Cabin	Embarked
Name
Braund, Mr. Owen Harris	1	0	3	male	22.0	1	0	A/5 21171	7.2500	NaN	S
Cumings, Mrs. John Bradley (Florence Briggs Thayer)	2	1	1	female	38.0	1	0	PC 17599	71.2833	C85	C
Heikkinen, Miss. Laina	3	1	3	female	26.0	0	0	STON/O2. 3101282	7.9250	NaN	S
Futrelle, Mrs. Jacques Heath (Lily May Peel)	4	1	1	female	35.0	1	0	113803	53.1000	C123	S
Allen, Mr. William Henry	5	0	3	male	35.0	0	0	373450	8.0500	NaN	S

In [136]:

data.drop('Ticket', axis=1, inplace=True)

In [137]:

data.head()

Out[137]:

	PassengerId	Survived	Pclass	Sex	Age	SibSp	Parch	Fare	Cabin	Embarked
Name
Braund, Mr. Owen Harris	1	0	3	male	22.0	1	0	7.2500	NaN	S
Cumings, Mrs. John Bradley (Florence Briggs Thayer)	2	1	1	female	38.0	1	0	71.2833	C85	C
Heikkinen, Miss. Laina	3	1	3	female	26.0	0	0	7.9250	NaN	S
Futrelle, Mrs. Jacques Heath (Lily May Peel)	4	1	1	female	35.0	1	0	53.1000	C123	S
Allen, Mr. William Henry	5	0	3	male	35.0	0	0	8.0500	NaN	S

2.Remove more than one column¶

In [138]:

data.drop(['Parch', 'Fare'], axis=1, inplace=True)
data.head()

Out[138]:

	PassengerId	Survived	Pclass	Sex	Age	SibSp	Cabin	Embarked
Name
Braund, Mr. Owen Harris	1	0	3	male	22.0	1	NaN	S
Cumings, Mrs. John Bradley (Florence Briggs Thayer)	2	1	1	female	38.0	1	C85	C
Heikkinen, Miss. Laina	3	1	3	female	26.0	0	NaN	S
Futrelle, Mrs. Jacques Heath (Lily May Peel)	4	1	1	female	35.0	1	C123	S
Allen, Mr. William Henry	5	0	3	male	35.0	0	NaN	S

3.Remove row(s)¶

In [139]:

data.drop(['Braund, Mr. Owen Harris', 'Heikkinen, Miss. Laina'], inplace=True)

In [140]:

data.head()

Out[140]:

	PassengerId	Survived	Pclass	Sex	Age	SibSp	Cabin	Embarked
Name
Cumings, Mrs. John Bradley (Florence Briggs Thayer)	2	1	1	female	38.0	1	C85	C
Futrelle, Mrs. Jacques Heath (Lily May Peel)	4	1	1	female	35.0	1	C123	S
Allen, Mr. William Henry	5	0	3	male	35.0	0	NaN	S
Moran, Mr. James	6	0	3	male	NaN	0	NaN	Q
McCarthy, Mr. Timothy J	7	0	1	male	54.0	0	E46	S

18.Renaming columns in a pandas DataFrame ¶

Go To TOP

In this section, you can learn¶

Rename columns while reading the data
Rename columns using rename method
1. Read in the dataset again
2. Rename
Rename all columns

1.Rename columns while reading the data¶

In [141]:

list_columns = ['Date', 'Region ID', 'Region Name', 'State',
             'City', 'County', 'Size Rank','Price']
data = pd.read_csv('../input/datasetsdifferent-format/data-zillow1.csv', names = list_columns)
data.head()

Out[141]:

	Date	Region ID	Region Name	State	City	County	Size Rank	Price
0	Date	RegionID	RegionName	State	Metro	County	SizeRank	Price
1	2017-05-31	6181	New York	NY	New York	Queens	0	672400
2	2017-05-31	12447	Los Angeles	CA	Los Angeles-Long Beach-Anaheim	Los Angeles	1	629900
3	2017-05-31	17426	Chicago	IL	Chicago	Cook	2	222700
4	2017-05-31	13271	Philadelphia	PA	Philadelphia	Philadelphia	3	137300

2.Rename columns using rename method¶

Read in the dataset again

In [142]:

data = pd.read_csv('../input/datasetsdifferent-format/data-zillow1.csv')
data.head()

Out[142]:

	Date	RegionID	RegionName	State	Metro	County	SizeRank	Price
0	2017-05-31	6181	New York	NY	New York	Queens	0	672400
1	2017-05-31	12447	Los Angeles	CA	Los Angeles-Long Beach-Anaheim	Los Angeles	1	629900
2	2017-05-31	17426	Chicago	IL	Chicago	Cook	2	222700
3	2017-05-31	13271	Philadelphia	PA	Philadelphia	Philadelphia	3	137300
4	2017-05-31	40326	Phoenix	AZ	Phoenix	Maricopa	4	211300

Rename

In [143]:

data.columns

Out[143]:

Index(['Date', 'RegionID', 'RegionName', 'State', 'Metro', 'County',
       'SizeRank', 'Price'],
      dtype='object')

In [144]:

data.rename(columns={'RegionName':'Region', 'Metro':'City'}, inplace=True)

In [145]:

data.columns

Out[145]:

Index(['Date', 'RegionID', 'Region', 'State', 'City', 'County', 'SizeRank',
       'Price'],
      dtype='object')

3.Rename all columns¶

In [146]:

data.columns = ['Date', 'Region ID', 'Region Name', 'State',
             'City', 'County', 'Size Rank','Price']

19.Using groupby method ¶

Go To TOP

In this section, you can learn¶

Get Mean price for every State
Split the data into groups
Apply a function on each group and combine the results
Get Descriptive statistics by Groups(States)
Group by data on State and Region
Get the number of records per State
Group by Columns
Iterate over Groups

In [147]:

data = pd.read_csv('../input/datasetsdifferent-format/data-zillow1.csv')
data.head()

Out[147]:

	Date	RegionID	RegionName	State	Metro	County	SizeRank	Price
0	2017-05-31	6181	New York	NY	New York	Queens	0	672400
1	2017-05-31	12447	Los Angeles	CA	Los Angeles-Long Beach-Anaheim	Los Angeles	1	629900
2	2017-05-31	17426	Chicago	IL	Chicago	Cook	2	222700
3	2017-05-31	13271	Philadelphia	PA	Philadelphia	Philadelphia	3	137300
4	2017-05-31	40326	Phoenix	AZ	Phoenix	Maricopa	4	211300

1.Get Mean price for every State¶

In [148]:

grouped_data = data[['State', 'Price']].groupby('State').mean()
grouped_data.head()

Out[148]:

	Price
State
AK	237783.333333
AL	137645.637584
AR	136331.707317
AZ	232353.921569
CA	617425.392297

2.Split the data into groups¶

In [149]:

grouped_data = data[['State', 'Price']].groupby('State')
grouped_data.head(2)

Out[149]:

	State	Price
0	NY	672400
1	CA	629900
2	IL	222700
3	PA	137300
4	AZ	211300
5	NV	216500
6	CA	572100
7	TX	164700
9	FL	152300
11	TX	321600
12	MI	41500
13	OH	128300
14	TN	81100
15	NC	183800
17	MA	554600
18	WA	670300
19	MD	121100
20	CO	383200
21	DC	555900
22	TN	228500
23	WI	107900
24	AZ	164800
25	OR	417900
26	OK	132700
27	NE	152100
28	NM	189600
33	MO	121600
34	VA	259500
35	CO	251200
36	GA	208100
...	...	...
137	IN	108800
138	NY	439700
145	MS	52800
148	KS	262600
174	OR	232900
183	NM	140300
185	CT	164100
189	DE	223200
194	SC	129900
199	UT	202100
206	CT	161200
229	MT	204800
255	NH	215500
275	ID	155100
290	ND	217800
302	IA	131300
368	WY	205000
390	NH	251600
417	MS	92500
453	AR	111500
454	RI	223000
519	DE	135500
547	HI	633700
550	ND	263500
561	MT	160500
572	WY	178900
842	AK	221000
1007	WV	99400
3545	WV	96200
7646	ME	73900

96 rows × 2 columns

In [150]:

3.Apply a function on each group and combine the results¶

In [150]:

grouped_data.mean().head()

Out[150]:

	Price
State
AK	237783.333333
AL	137645.637584
AR	136331.707317
AZ	232353.921569
CA	617425.392297

4.Get Descriptive statistics by Groups(States)¶

In [151]:

grouped_data.describe().head()

Out[151]:

	Price
	count	mean	std	min	25%	50%	75%	max
State
AK	12.0	237783.333333	41433.711205	175800.0	211700.0	222850.0	254950.0	323100.0
AL	149.0	137645.637584	72538.539135	44700.0	103900.0	126400.0	155800.0	598900.0
AR	82.0	136331.707317	42370.537394	65300.0	108175.0	128750.0	155050.0	268800.0
AZ	102.0	232353.921569	173068.589203	81500.0	148875.0	211950.0	258425.0	1611700.0
CA	701.0	617425.392297	604628.412673	74400.0	277000.0	453500.0	720200.0	6343800.0

5.Group by data on State and Region¶

In [152]:

grouped_data = data[['State',
                     'RegionName', 
                     'Price']].groupby(['State','RegionName']).mean()
grouped_data.head()

Out[152]:

		Price
State	RegionName
AK	Anchor Point	175800.0
	Anchorage	293900.0
	Fairbanks	221000.0
	Juneau	323100.0
	Kenai	206500.0

6.Get the number of records per State¶

In [153]:

grouped_data = data.groupby(['State']).size()
grouped_data.head()

Out[153]:

State
AK     12
AL    149
AR     82
AZ    102
CA    701
dtype: int64

7.Group by Columns¶

In [154]:

grouped_data = data.groupby(data.dtypes, axis=1)
# list(grouped_data)

8.Iterate over Groups¶

In [155]:

# for state, grouped_data in data.groupby('State'):
#     print(state, '\n', grouped_data)

20.Work with dates and times data ¶

Go To TOP

In this section, you can learn¶

let's first convert our date column to datetime
Let's set index to the date column
Filter and select time series Data
Get properties of date-time series data

1.let's first convert our date column to datetime¶

In [156]:

dataset = pd.DataFrame({'DOB': ['1976-06-01', '1980-09-23', '1984-03-30', '1991-12-31', '1994-10-2', '1973-11-11'],
                        'Sex': ['F', 'M', 'F', 'M', 'M', 'F'],
                        'State': ['CA', 'NY', 'OH', 'OR', 'TX', 'CA'],
                        'Name': ['Jane', 'John', 'Cathy', 'Jo', 'Sam', 'Tai']})
dataset

Out[156]:

	DOB	Sex	State	Name
0	1976-06-01	F	CA	Jane
1	1980-09-23	M	NY	John
2	1984-03-30	F	OH	Cathy
3	1991-12-31	M	OR	Jo
4	1994-10-2	M	TX	Sam
5	1973-11-11	F	CA	Tai

In [157]:

dataset.dtypes

Out[157]:

DOB      object
Sex      object
State    object
Name     object
dtype: object

In [158]:

dataset.DOB = pd.to_datetime(dataset.DOB)

In [159]:

dataset.dtypes

Out[159]:

DOB      datetime64[ns]
Sex              object
State            object
Name             object
dtype: object

2.Let's set index to the date column¶

In [160]:

dataset.set_index('DOB', inplace=True)

In [161]:

dataset

Out[161]:

	Sex	State	Name
DOB
1976-06-01	F	CA	Jane
1980-09-23	M	NY	John
1984-03-30	F	OH	Cathy
1991-12-31	M	OR	Jo
1994-10-02	M	TX	Sam
1973-11-11	F	CA	Tai

3.Filter and select time series Data¶

In [162]:

dataset['1980']

Out[162]:

	Sex	State	Name
DOB
1980-09-23	M	NY	John

In [163]:

dataset['1980':]

Out[163]:

	Sex	State	Name
DOB
1980-09-23	M	NY	John
1984-03-30	F	OH	Cathy
1991-12-31	M	OR	Jo
1994-10-02	M	TX	Sam

In [164]:

dataset[:'1980']

Out[164]:

	Sex	State	Name
DOB
1976-06-01	F	CA	Jane
1980-09-23	M	NY	John
1973-11-11	F	CA	Tai

In [165]:

display(dataset['1980':'1984'])
dataset.reset_index(inplace=True)

	Sex	State	Name
DOB
1980-09-23	M	NY	John
1984-03-30	F	OH	Cathy

4.Get properties of date-time series data¶

In [166]:

dataset.DOB.dt.dayofyear

Out[166]:

0    153
1    267
2     90
3    365
4    275
5    315
Name: DOB, dtype: int64

In [167]:

dataset.DOB.dt.weekday_name

Out[167]:

0    Tuesday
1    Tuesday
2     Friday
3    Tuesday
4     Sunday
5     Sunday
Name: DOB, dtype: object

21.Choosing the colors for the plots ¶

Go To TOP

In this section, you can learn¶

Color Palettes
Look at how these colors look on a plot
Change the color palette
Impact on the plot
seaborn palettes
matplotlib colormaps as color palettes
Let's set the palette to a matplotlib colormap
Impact on the plot
Building custom color palettes
Let's see how the plot has changed

In [168]:

import pandas as pd
from matplotlib import pyplot as plt
%matplotlib inline
import seaborn as sns

In [169]:

df = pd.read_csv('../input/datasetsdifferent-format/data-alcohol.csv')
df.head()

Out[169]:

	country	beer_servings	spirit_servings	wine_servings	total_litres_of_pure_alcohol
0	Afghanistan	0	0	0	0.0
1	Albania	89	132	54	4.9
2	Algeria	25	0	14	0.7
3	Andorra	245	138	312	12.4
4	Angola	217	57	45	5.9

1. Color Palettes¶

In [170]:

sns.palplot(sns.color_palette())

2.Look at how these colors look on a plot¶

In [171]:

plt.figure(figsize = (15,8))
sns.set()
sns.boxplot(data=df);

3. Change the color palette¶

In [172]:

sns.set_palette("bright")

4. Impact on the plot¶

In [173]:

plt.figure(figsize = (15,8))
sns.boxplot(data=df);

5. seaborn palettes¶

In [174]:

sns.palplot(sns.color_palette("deep", 7))
sns.palplot(sns.color_palette("muted", 7))
sns.palplot(sns.color_palette("pastel", 7))

sns.palplot(sns.color_palette("bright", 7))
sns.palplot(sns.color_palette("dark", 7))
sns.palplot(sns.color_palette("colorblind", 7))

6. matplotlib colormaps as color palettes¶

In [175]:

sns.palplot(sns.color_palette("RdBu", 7))
sns.palplot(sns.color_palette("Blues_d", 7))

7. Let's set the palette to a matplotlib colormap¶

In [176]:

sns.set_palette("Blues_d")

8. Impact on the plot¶

In [177]:

plt.figure(figsize = (15,8))
sns.boxplot(data=df);

9. Building custom color palettes¶

In [178]:

my_palette = ['#4B0082', '#0000FF', '#00FF00', '#FFFF00', '#FF7F00', '#FF0000']
sns.set_palette(my_palette)
sns.palplot(sns.color_palette())

10. Let's see how the plot has changed¶

In [179]:

plt.figure(figsize = (15,8))
sns.boxplot(data=df);

22.Controlling plot aesthetics ¶

Go To TOP

In this section, you can learn¶

First plot with seaborn
Changing the plot style with set_style
1. Set plot background to a white grid
2. Set the plot background to dark
3. Set the background to white
4. Adding 'ticks
Customizing the styles
1. Style parameters
Plotting Context Presets
1. Plotting Context Preset - paper
2. Plotting Preset - talk
3. Plotting Preset - poster

1. First plot with seaborn¶

In [180]:

import pandas as pd
from matplotlib import pyplot as plt
%matplotlib inline
import seaborn as sns
df = pd.read_csv('../input/datasetsdifferent-format/data-alcohol.csv')
df.head()

Out[180]:

	country	beer_servings	spirit_servings	wine_servings	total_litres_of_pure_alcohol
0	Afghanistan	0	0	0	0.0
1	Albania	89	132	54	4.9
2	Algeria	25	0	14	0.7
3	Andorra	245	138	312	12.4
4	Angola	217	57	45	5.9

In [181]:

sns.distplot(df.beer_servings)

Out[181]:

<matplotlib.axes._subplots.AxesSubplot at 0x7fb885df9dd8>

2. Changing the plot style with set_style¶

1. Set plot background to a white grid¶

In [182]:

sns.set()
sns.set_style("whitegrid")
sns.lmplot(x='beer_servings', y='wine_servings', data=df);

2. Set the plot background to dark¶

In [183]:

sns.set()
sns.set_style("dark")
sns.lmplot(x='beer_servings', y='wine_servings', data=df, fit_reg=False);

3.Set the background to white¶

In [184]:

# sns.set()
sns.set_style("white")
plt.figure(figsize=(15,8))
sns.swarmplot(x='country', y='wine_servings', data=df);

4.Adding 'ticks¶

In [185]:

plt.figure(figsize=(15,8))
sns.set_style("ticks")
sns.boxplot(data=df);

3.Customizing the styles¶

1.Style parameters¶

In [186]:

sns.axes_style()

Out[186]:

{'axes.facecolor': 'white',
 'axes.edgecolor': '.15',
 'axes.grid': False,
 'axes.axisbelow': True,
 'axes.labelcolor': '.15',
 'figure.facecolor': 'white',
 'grid.color': '.8',
 'grid.linestyle': '-',
 'text.color': '.15',
 'xtick.color': '.15',
 'ytick.color': '.15',
 'xtick.direction': 'out',
 'ytick.direction': 'out',
 'lines.solid_capstyle': 'round',
 'patch.edgecolor': 'w',
 'image.cmap': 'rocket',
 'font.family': ['sans-serif'],
 'font.sans-serif': ['Arial',
  'DejaVu Sans',
  'Liberation Sans',
  'Bitstream Vera Sans',
  'sans-serif'],
 'patch.force_edgecolor': True,
 'xtick.bottom': True,
 'xtick.top': False,
 'ytick.left': True,
 'ytick.right': False,
 'axes.spines.left': True,
 'axes.spines.bottom': True,
 'axes.spines.right': True,
 'axes.spines.top': True}

In [187]:

plt.figure(figsize=(15,8))
sns.set_style("ticks", {"axes.facecolor": ".1"})
sns.boxplot(data=df);

4.Plotting Context Presets¶

1.Plotting Context Preset - paper¶

In [188]:

sns.set()
sns.set_context("paper")
plt.figure(figsize=(15, 8))
sns.lmplot(x='beer_servings', y='wine_servings', data=df);

<Figure size 1080x576 with 0 Axes>

2.Plotting Preset - talk¶

In [189]:

sns.set()
sns.set_context("talk")
plt.figure(figsize=(8, 6))
sns.lmplot(x='beer_servings', y='wine_servings', data=df);

<Figure size 576x432 with 0 Axes>

3.Plotting Preset - poster¶

In [190]:

sns.set()
sns.set_context("poster")
plt.figure(figsize=(8, 6))
sns.lmplot(x='beer_servings', y='wine_servings', data=df);

<Figure size 576x432 with 0 Axes>

23.Plotting categorical data ¶

Go To TOP

In this section, you can learn¶

Scatterplots
Swarmplot
Boxplot
Violinplot
Barplot
Countplot
Wide form plots

1.Scatterplots¶

In [191]:

import pandas as pd
from matplotlib import pyplot as plt
%matplotlib inline
import seaborn as sns

In [192]:

df = pd.read_csv('../input/datasetsdifferent-format/data_simpsons_episodes.csv')
df.head()

Out[192]:

	id	title	original_air_date	production_code	season	number_in_season	number_in_series	us_viewers_in_millions	views	imdb_rating	imdb_votes	image_url	video_url
0	10	Homer's Night Out	1990-03-25	7G10	1	10	10	30.3	50816.0	7.4	1511.0	http://static-media.fxx.com/img/FX_Networks_-_...	http://www.simpsonsworld.com/video/275197507879
1	12	Krusty Gets Busted	1990-04-29	7G12	1	12	12	30.4	62561.0	8.3	1716.0	http://static-media.fxx.com/img/FX_Networks_-_...	http://www.simpsonsworld.com/video/288019523914
2	14	Bart Gets an "F"	1990-10-11	7F03	2	1	14	33.6	59575.0	8.2	1638.0	http://static-media.fxx.com/img/FX_Networks_-_...	http://www.simpsonsworld.com/video/260539459671
3	17	Two Cars in Every Garage and Three Eyes on Eve...	1990-11-01	7F01	2	4	17	26.1	64959.0	8.1	1457.0	http://static-media.fxx.com/img/FX_Networks_-_...	http://www.simpsonsworld.com/video/260537411822
4	19	Dead Putting Society	1990-11-15	7F08	2	6	19	25.4	50691.0	8.0	1366.0	http://static-media.fxx.com/img/FX_Networks_-_...	http://www.simpsonsworld.com/video/260539459670

In [193]:

plt.figure(figsize=(20,8))
sns.stripplot(x="season", y="us_viewers_in_millions", data=df);

2.Swarmplot¶

In [194]:

plt.figure(figsize=(20,8))
sns.swarmplot(x="season", y="us_viewers_in_millions", data=df);

3.Boxplot¶

In [195]:

plt.figure(figsize=(20,8))
sns.boxplot(x="season", y="us_viewers_in_millions", data=df);
# sns.boxenplot(x="season", y="us_viewers_in_millions", data=df);

4.Violinplot¶

In [196]:

plt.figure(figsize=(20,8))
sns.violinplot(x="season", y="us_viewers_in_millions", data=df);

5.Barplot¶

In [197]:

plt.figure(figsize=(20,8))
sns.barplot(x="season", y="us_viewers_in_millions", data=df);

6.Count Plot¶

In [198]:

plt.figure(figsize=(20,8))
sns.countplot(x="season", data=df);

7.Wide form plot¶

In [199]:

df = pd.read_csv('../input/datasetsdifferent-format/data-alcohol.csv')
df.head()

Out[199]:

	country	beer_servings	spirit_servings	wine_servings	total_litres_of_pure_alcohol
0	Afghanistan	0	0	0	0.0
1	Albania	89	132	54	4.9
2	Algeria	25	0	14	0.7
3	Andorra	245	138	312	12.4
4	Angola	217	57	45	5.9

In [200]:

plt.figure(figsize=(20,8))
sns.boxplot(data=df, orient="h");

24.Plotting with data aware grids ¶

Go To TOP

In this section, you can learn¶

Plotting with FacetGrid()
Plotting with PairGrid()
1. MLB Players Height, Weight, Age and Positions dataset
Plot it with PairGrid()
Plotting with PairPlot()

1. Plotting with FacetGrid()¶

In [201]:

df = pd.read_csv('../input/datasetsdifferent-format/data-titanic.csv')
df.head()

Out[201]:

	PassengerId	Survived	Pclass	Name	Sex	Age	SibSp	Ticket	Fare	Cabin	Embarked
0	1	0	3	Braund, Mr. Owen Harris	male	22.0	1	A/5 21171	7.2500	NaN	S
1	2	1	1	Cumings, Mrs. John Bradley (Florence Briggs Th...	female	38.0	1	PC 17599	71.2833	C85	C
2	3	1	3	Heikkinen, Miss. Laina	female	26.0	0	STON/O2. 3101282	7.9250	NaN	S
3	4	1	1	Futrelle, Mrs. Jacques Heath (Lily May Peel)	female	35.0	1	113803	53.1000	C123	S
4	5	0	3	Allen, Mr. William Henry	male	35.0	0	373450	8.0500	NaN	S

In [202]:

g = sns.FacetGrid(df, col="Sex", hue='Survived')
g.map(plt.hist, "Age");
g.add_legend();

2.Plotting with PairGrid()¶

MLB Players Height, Weight, Age and Positions dataset¶

In [203]:

mlb = pd.read_csv('../input/datasetsdifferent-format/data-mlb-players.csv')
mlb.head()

Out[203]:

	Position	Height	Weight	Age
0	Catcher	74	180.0	22.99
1	Catcher	74	215.0	34.69
2	Catcher	72	210.0	30.78
3	First_Baseman	72	210.0	35.43
4	First_Baseman	73	188.0	35.71

3.Plot it with PairGrid()¶

In [204]:

g = sns.PairGrid(mlb, vars=["Height", "Weight"], hue="Position")
g.map(plt.scatter);
g.add_legend();

4.Plot it with PairGrid()¶

In [205]:

sns.pairplot(mlb, hue="Position", size=2.5);

/opt/conda/lib/python3.6/site-packages/seaborn/axisgrid.py:2065: UserWarning: The `size` parameter has been renamed to `height`; pleaes update your code.
  warnings.warn(msg, UserWarning)

	Date	RegionID	RegionName	State	Metro	County	SizeRank	Zhvi
0	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN
1	NaN	NaN	NaN	CA	NaN	NaN	NaN	NaN
2	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN
3	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN
4	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN

	Date	RegionID	RegionName	State	Metro	County	SizeRank	Zhvi
0	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN
1	NaN	NaN	NaN	CA	NaN	NaN	NaN	NaN
2	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN
3	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN
4	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN

Advance Pandas Trick and Techniques

Index of learning

1.Basic Pandas Reading Method ¶

2.Advance Pandas Reading Methods ¶

2.1 Manipulating Columns & Index Location and Names ¶

1. No Header and No Columns¶

2.Specify a different row as header¶

3.Specify a column as index¶

4.Choose only a subset of columns to be read¶

5.Handling Missing and NA data¶

6.Choose whether to skip over blank rows or not¶

2.2 Data Parsing options ¶

1. Skip Rows¶

2.Skip rows from footer or from end of the file¶

3.Reading only a subset of the file or a certain number of rows¶

2.3.Reading data from excel files ¶

1.Basic Excel read¶

Advanced read options¶

2.Which Sheet to read?¶

3.Reading data from multiple sheets in an excel file¶

4.Choose Header or column labels¶

5.No header¶

6.Skip Rows at the beginning of the file¶

7.Skip rows from the end of the file¶

8.Choose Columns¶

9.Column Names¶

10.Set an Index while reading data¶

11.Handle missing data while reading¶

2.4.Reading data from some other popular formats ¶

1.Reading JSON data into Pandas¶

2.Reading HTML data¶

3.Read pickle file¶

4.Read SQL file¶

5.Read data from clipboard¶

3.Apply multiple filter criteria to a pandas DataFrame¶

In this section, you will learn¶

1.Read in the dataset¶

2. FIlter Based on Multiple Condition¶

4.Changing the datatype of a Pandas Series ¶

In this section you will learn¶

1.Read Dataset¶

2. Changes Data int to float¶

3.Changing datatype while reading data¶

4.Converting string to datetime¶

5.Filter rows of a pandas DataFrame by column value ¶

In this section, you will learn¶

1. Read Dataset¶

2.Filter columns by Different Ways¶

3.Filter columns by regular expression using filter()¶

4.Filter data using boolean indexing¶

5.An alternative way to filter¶

6.Selecting multiple rows and columns from a pandas DataFrame ¶

In this Section you can learn:¶

1.Read dataset¶

2.Select single row, single column¶

3.Select single row, multiple columns¶

4.Select single row, all columns¶

5.Select multiple rows, single column¶

6.Select multiple rows and multiple contiguous columns¶

7.Select multiple rows and multiple non-contiguous columns¶

8.Select multiple rows and all columns¶

9.Select non-contiguous rows¶

10.Selecting rows based on a specific column's value¶

11.Selecting all rows for a specific column based on a value of another column¶

7.Sorting a pandas DataFrame or a Series ¶

In this section you can learn:¶

1.Read dataset¶

2.Simple sort¶

3.Changing the sort order¶

4.Sort by more than one column¶

5.Sort by multiple columns and mixed ascending order¶

6.Sort a Series¶

8.Using pandas Series data structure to select a subset of the data ¶

In this Section, you will learn below topics¶

1.Read Dataset¶

2.Select data¶

3.DataFrame vs Series¶

4.Creating a new series by selection¶

9.Using string methods in pandas ¶

In this section, you will learn¶

3.Apply multiple filter criteria to a pandas DataFrame ¶

10.Using the axis parameter in pandas ¶

11.Applying a function to a pandas Series or DataFrame ¶

12.Handling SettingWithCopyWarning ¶

13.Handling missing values in pandas ¶

14.Indexing in pandas dataframes ¶

15.Merging and concatenating multiple data frames into one ¶

16.Modifying a Pandas Dataframe inplace ¶

	Date	RegionID	RegionName	State	Metro	County	SizeRank	Zhvi
0	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN
1	NaN	NaN	NaN	CA	NaN	NaN	NaN	NaN
2	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN
3	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN
4	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN