Pandas Tutorial

import pandas as pd

psg_players = pd.Series(['Navas', 'Mbappe', 'Neymar', 'Messi'], index=[1,7,10,30]) # Pandas series is important specify index because it is the way how we will manipulate data

psg_players

# What happen if we dont define the index. Pandas automatically assign a index: pd.Series(['Navas', 'Mbappe', 'Neymar', 'Messi'])

# Also we can assign a dictionary as Series: dictionary = {1:'Navas', 7:'Mbappe', 10:'Neymar', 30:"Messi"} pd.Series(dictionary)

# We define in psg_players variable an index (1,7,10,30) but what happen when call a value with a index that # we dont assign. Simple, it show us a error psg_players[0]

Execution error

KeyError: 0

# We call the value with index that we assign psg_players[7]

# We can assign a dictionary as DataFrame dictionary = { 'Players':['Navas', 'Mbappe', 'Neymar', 'Messi'], 'height' : ['183.0', '170.0', '170.0', '165.0'], 'goals': [2,200, 200, 200] }

pd.DataFrame(dictionary, index=[1,7,10,30]) # As well, we can assign an index. If we dont assign an index, pandas automatically # assign a index start with 0

Playersobject

heightobject

Navas

183.0

Mbappe

170.0

Neymar

170.0

Messi

165.0

This chart is empty

Chart was probably not set up properly in the notebook

df_book=pd.read_csv('bestsellers-with-categories.csv', sep=',', header=0) # By default command read_csv separate the file by commas but if your file dont separate by commas # you can use the attribute sep=";". As well, also csv file dont have a header, you can use the # attribute header to select header for column index

df_book

Nameobject

Publication Manual of the American Psychological Association, 6th Edition1.8%

StrengthsFinder 2.01.6%

349 others96.5%

Authorobject

Jeff Kinney2.2%

Suzanne Collins2%

246 others95.8%

10-Day Green Smoothie Cleanse

JJ Smith

11/22/63: A Novel

Stephen King

12 Rules for Life: An Antidote to Chaos

Jordan B. Peterson

1984 (Signet Classics)

George Orwell

5,000 Awesome Facts (About Everything!) (National Geographic Kids)

National Geographic Kids

A Dance with Dragons (A Song of Ice and Fire)

George R. R. Martin

A Game of Thrones / A Clash of Kings / A Storm of Swords / A Feast of Crows / A Dance with Dragons

George R. R. Martin

A Gentleman in Moscow: A Novel

Amor Towles

A Higher Loyalty: Truth, Lies, and Leadership

James Comey

A Man Called Ove: A Novel

Fredrik Backman

df_book.columns

# Whit loc we can filter a data for columns (labels) df_book[0:4]

Nameobject

Authorobject

10-Day Green Smoothie Cleanse

JJ Smith

11/22/63: A Novel

Stephen King

12 Rules for Life: An Antidote to Chaos

Jordan B. Peterson

1984 (Signet Classics)

George Orwell

# We can add specific columns df_book.loc[0:4, ['Name', 'Author']]

Nameobject

Authorobject

10-Day Green Smoothie Cleanse

JJ Smith

11/22/63: A Novel

Stephen King

12 Rules for Life: An Antidote to Chaos

Jordan B. Peterson

1984 (Signet Classics)

George Orwell

5,000 Awesome Facts (About Everything!) (National Geographic Kids)

National Geographic Kids

# We can modify the column value df_book.loc[:, ['Reviews']]*-1

Reviewsint64

-87841 - -37

-17350

-2052

-18979

-21424

-7665

-12643

-19735

-19699

-5983

-23848

# We can also make checks with loc df_book.loc[:,['Author']] == 'JJ Smith'

Authorbool

False99.8%

True0.2%

true

false

# Whit iloc we can filter a data for rows (index) df_book.iloc[:, 0:3]

Nameobject

Publication Manual of the American Psychological Association, 6th Edition1.8%

StrengthsFinder 2.01.6%

349 others96.5%

Authorobject

Jeff Kinney2.2%

Suzanne Collins2%

246 others95.8%

10-Day Green Smoothie Cleanse

JJ Smith

11/22/63: A Novel

Stephen King

12 Rules for Life: An Antidote to Chaos

Jordan B. Peterson

1984 (Signet Classics)

George Orwell

5,000 Awesome Facts (About Everything!) (National Geographic Kids)

National Geographic Kids

A Dance with Dragons (A Song of Ice and Fire)

George R. R. Martin

A Game of Thrones / A Clash of Kings / A Storm of Swords / A Feast of Crows / A Dance with Dragons

George R. R. Martin

A Gentleman in Moscow: A Novel

Amor Towles

A Higher Loyalty: Truth, Lies, and Leadership

James Comey

A Man Called Ove: A Novel

Fredrik Backman

df_book.iloc[:2,2:]

User Ratingfloat64

Reviewsint64

4.7

17350

4.6

2052

# Whit attribute drop df_book.drop('Genre', axis=1, inplace=True) # axis=1 is Colums and axis=0 is rows # inplace=True means that we want to delete te column in our DataFrame by defect this attribute is False # because only delete de colum in this output.

# Other form that we can delete a column is with method "del" from python. In this case we dont nedd # to specify implace because "del" does delete the column from our DataFrame. del df_book['Price']

# We nedd to specify a row df_book.drop(0, axis=0) # If We need to change in out DataFrame we can specify inplace=True

# We can specify a range df_book.drop([0,1,2,3], axis=0)

# We can use method "range" from paython df_book.drop(range(0,7),axis=0)

df_book['New_Column'] = 0 # We can assing values in new column with iqual df_book

Nameobject

Publication Manual of the American Psychological Association, 6th Edition1.8%

StrengthsFinder 2.01.6%

349 others96.5%

Authorobject

Jeff Kinney2.2%

Suzanne Collins2%

246 others95.8%

10-Day Green Smoothie Cleanse

JJ Smith

11/22/63: A Novel

Stephen King

12 Rules for Life: An Antidote to Chaos

Jordan B. Peterson

1984 (Signet Classics)

George Orwell

5,000 Awesome Facts (About Everything!) (National Geographic Kids)

National Geographic Kids

A Dance with Dragons (A Song of Ice and Fire)

George R. R. Martin

A Game of Thrones / A Clash of Kings / A Storm of Swords / A Feast of Crows / A Dance with Dragons

George R. R. Martin

A Gentleman in Moscow: A Novel

Amor Towles

A Higher Loyalty: Truth, Lies, and Leadership

James Comey

A Man Called Ove: A Novel

Fredrik Backman

import numpy as np dictionary = { 'col1' : [1,2,3,np.nan], 'col2' : [4, np.nan, 6,7], 'col3' : ['a', 'b', 'c', None] } # We created a new DataFrame df = pd.DataFrame(dictionary) df

col1float64

col2float64

nan

# With attribute "isnull" we can verify if a value is "nan" or "None" df.isnull()

col1bool

col2bool

false

true

false

true

false

# Whith "fillna" we can changes values from "nan" or "None" df.fillna('Missing')

col1object

col2object

1.0

4.0

2.0

Missing

3.0

6.0

Missing

7.0

# With "fillna" we can dicide with which value to fill the fields df.fillna(df['col1'].mean())

col1float64

col2float64

# With "dropna" attribute we delete rows that it have a nan or None values df.dropna()

col1float64

col2float64

df_book['Year'] > 2016

# We can assign a variable and filter all our DataFrame greater_than_2016 = df_book['Year'] > 2016

# Only show books greater than 2016 df_book[greater_than_2016]

Nameobject

Giraffes Can't Dance2%

Goodnight Moon2%

107 others96%

Authorobject

Dav Pilkey4.7%

Giles Andreae2%

96 others93.3%

12 Rules for Life: An Antidote to Chaos

Jordan B. Peterson

1984 (Signet Classics)

George Orwell

5,000 Awesome Facts (About Everything!) (National Geographic Kids)

National Geographic Kids

A Gentleman in Moscow: A Novel

Amor Towles

A Higher Loyalty: Truth, Lies, and Leadership

James Comey

A Man Called Ove: A Novel

Fredrik Backman

A Wrinkle in Time (Time Quintet)

Madeleine L'Engle

Astrophysics for People in a Hurry

Neil deGrasse Tyson

Becoming

Michelle Obama

Becoming

Michelle Obama

# We can add other filter with & genre_fiction = df_book['Genre'] == 'Fiction' df_book[genre_fiction & greater_than_2016]

Nameobject

Giraffes Can't Dance4.6%

Goodnight Moon4.6%

44 others90.8%

Authorobject

Dav Pilkey10.8%

Giles Andreae4.6%

37 others84.6%

1984 (Signet Classics)

George Orwell

A Gentleman in Moscow: A Novel

Amor Towles

A Man Called Ove: A Novel

Fredrik Backman

A Wrinkle in Time (Time Quintet)

Madeleine L'Engle

Brown Bear, Brown Bear, What Do You See?

Bill Martin Jr.

Brown Bear, Brown Bear, What Do You See?

Bill Martin Jr.

Crazy Rich Asians (Crazy Rich Asians Trilogy)

Kevin Kwan

Dear Zoo: A Lift-the-Flap Book

Rod Campbell

Dear Zoo: A Lift-the-Flap Book

Rod Campbell

Dog Man and Cat Kid: From the Creator of Captain Underpants (Dog Man #4)

Dav Pilkey

# with the "info" attribute brings us which columns I have, which indexes it has, how many of those values # are not null, the type of data that each column handles, how many records and columns we have # in the DataFrame. df_book.info()

<class 'pandas.core.frame.DataFrame'>
RangeIndex: 550 entries, 0 to 549
Data columns (total 7 columns):
 #   Column       Non-Null Count  Dtype  
---  ------       --------------  -----  
 0   Name         550 non-null    object 
 1   Author       550 non-null    object 
 2   User Rating  550 non-null    float64
 3   Reviews      550 non-null    int64  
 4   Price        550 non-null    int64  
 5   Year         550 non-null    int64  
 6   Genre        550 non-null    object 
dtypes: float64(1), int64(3), object(3)
memory usage: 30.2+ KB

# With the attribute "describe" we get statistical data only from the columns that contain number format. df_book.describe()

User Ratingfloat64

Reviewsfloat64

count

550

mean

4.618363636

11953.28182

std

0.226980365

11731.13202

min

3.3

25%

4.5

4058

50%

4.7

8580

75%

4.8

17253.25

max

4.9

87841

# We "tail" attribute brings us the last rows of DataFrame df_book.tail(2)

Nameobject

Authorobject

548

You Are a Badass: How to Stop Doubting Your Greatness and Start Living an Awesome Life

Jen Sincero

549

You Are a Badass: How to Stop Doubting Your Greatness and Start Living an Awesome Life

Jen Sincero

# We "head" attribute brings us the first rows of DataFrame df_book.head(3)

Nameobject

Authorobject

10-Day Green Smoothie Cleanse

JJ Smith

11/22/63: A Novel

Stephen King

12 Rules for Life: An Antidote to Chaos

Jordan B. Peterson

# "memory_usage" attribute show How much memory am I using in my DataFrame. This helps me to better manage # the models I am going to create. df_book.memory_usage(deep=True)

# "value_counts" attribute returns the count of unique values. df_book['Author'].value_counts()

# We created a duplicate value df_book = df_book.append(df_book.iloc[0])

# Whit "drop_duplicate" attribute delete duplicates in all DataFrame df_book.drop_duplicates()

Nameobject

Publication Manual of the American Psychological Association, 6th Edition1.8%

StrengthsFinder 2.01.6%

349 others96.5%

Authorobject

Jeff Kinney2.2%

Suzanne Collins2%

246 others95.8%

10-Day Green Smoothie Cleanse

JJ Smith

11/22/63: A Novel

Stephen King

12 Rules for Life: An Antidote to Chaos

Jordan B. Peterson

1984 (Signet Classics)

George Orwell

5,000 Awesome Facts (About Everything!) (National Geographic Kids)

National Geographic Kids

A Dance with Dragons (A Song of Ice and Fire)

George R. R. Martin

A Game of Thrones / A Clash of Kings / A Storm of Swords / A Feast of Crows / A Dance with Dragons

George R. R. Martin

A Gentleman in Moscow: A Novel

Amor Towles

A Higher Loyalty: Truth, Lies, and Leadership

James Comey

A Man Called Ove: A Novel

Fredrik Backman

# Whit "sort_values" attribute sort a column df_book.sort_values('Year', ascending=False)

Nameobject

Publication Manual of the American Psychological Association, 6th Edition1.8%

StrengthsFinder 2.01.6%

349 others96.6%

Authorobject

Jeff Kinney2.2%

Gary Chapman2%

246 others95.8%

179

If Animals Kissed Good Night

Ann Whitford Paul

294

School Zone - Big Preschool Workbook - Ages 4 and Up, Colors, Shapes, Numbers 1-10, Alphabet, Pre-Writing, Pre-Reading…

School Zone

263

P is for Potty! (Sesame Street) (Lift-the-Flap)

Naomi Kleinberg

489

The Wonderful Things You Will Be

Emily Winfield Martin

267

Player's Handbook (Dungeons & Dragons)

Wizards RPG Team

130

Girl, Wash Your Face: Stop Believing the Lies About Who You Are So You Can Become Who You Were Meant to Be

Rachel Hollis

Brown Bear, Brown Bear, What Do You See?

Bill Martin Jr.

127

Giraffes Can't Dance

Giles Andreae

482

The Very Hungry Caterpillar

Eric Carle

Can't Hurt Me: Master Your Mind and Defy the Odds

David Goggins

df_book.groupby('Author').count()

Nameint64

1 - 12

User Ratingint64

1 - 12

Abraham Verghese

Adam Gasiewski

Adam Mansbach

Adir Levy

Admiral William H. McRaven

Adult Coloring Book Designs

Alan Moore

Alex Michaelides

Alice Schertle

Allie Brosh

df_book.groupby('Author').mean()

User Ratingfloat64

3.9 - 4.9

Reviewsfloat64

128.5 - 87841.0

Abraham Verghese

4.6

4866

Adam Gasiewski

4.4

3113

Adam Mansbach

4.8

9568

Adir Levy

4.8

8170

Admiral William H. McRaven

4.7

10199

Adult Coloring Book Designs

4.5

2313

Alan Moore

4.8

3829

Alex Michaelides

4.5

27536

Alice Schertle

4.9

1884

Allie Brosh

4.7

4896

# Whit "reset_index" our DataFrame assign again index with numbers df_book.groupby('Author').mean().reset_index()

Authorobject

Abraham Verghese0.4%

Adam Gasiewski0.4%

246 others99.2%

User Ratingfloat64

3.9 - 4.9

Abraham Verghese

4.6

Adam Gasiewski

4.4

Adam Mansbach

4.8

Adir Levy

4.8

Admiral William H. McRaven

4.7

Adult Coloring Book Designs

4.5

Alan Moore

4.8

Alex Michaelides

4.5

Alice Schertle

4.9

Allie Brosh

4.7

df_book.groupby('Author').sum().loc['William Davis']

# Whit "agg" attribute we can define a new DataFrame with certain aggregation functions. df_book.groupby('Author').agg('min', 'max')

User Ratingfloat64

3.3 - 4.9

Reviewsint64

37 - 87841

Abraham Verghese

4.6

4866

Adam Gasiewski

4.4

3113

Adam Mansbach

4.8

9568

Adir Levy

4.8

8170

Admiral William H. McRaven

4.7

10199

Adult Coloring Book Designs

4.5

2313

Alan Moore

4.8

3829

Alex Michaelides

4.5

27536

Alice Schertle

4.9

1884

Allie Brosh

4.7

4896

df_book.groupby('Author').agg({'Reviews': ['min', 'max'], 'User Rating': ['sum']})

Reviews minint64

37 - 87841

Reviews maxint64

220 - 87841

Abraham Verghese

4866

Adam Gasiewski

3113

Adam Mansbach

9568

Adir Levy

8170

Admiral William H. McRaven

10199

Adult Coloring Book Designs

2313

Alan Moore

3829

Alex Michaelides

27536

Alice Schertle

1884

Allie Brosh

4896

df_book.groupby(['Author', 'Year']).count()

Nameint64

1 - 6

User Ratingint64

1 - 6

('Abraham Verghese', 2010)

('Abraham Verghese', 2011)

('Adam Gasiewski', 2017)

('Adam Mansbach', 2011)

('Adir Levy', 2019)

('Admiral William H. McRaven', 2017)

('Adult Coloring Book Designs', 2016)

('Alan Moore', 2009)

('Alex Michaelides', 2019)

('Alice Schertle', 2014)

# We created ours DataFrame df1 = pd.DataFrame({ 'A':['A0', 'A1', 'A2','A3'], 'B':['B0', 'B1', 'B2','B3'], 'C':['C0', 'C1', 'C2','C3'], 'D':['D0', 'D1', 'D2','D3'] }) df2 = pd.DataFrame({ 'A':['A4', 'A5', 'A6','A7'], 'B':['B4', 'B5', 'B6','B7'], 'C':['C4', 'C5', 'C6','C7'], 'D':['D4', 'D5', 'D6','D7'] })

pd.concat([df1,df2], ignore_index=True) # for default is concatenated by rows "axis=0" # Whit "ignore_index" our new DataFrame dont take previou index

Aobject

Bobject

# We concatenated with columns pd.concat([df1,df2], axis=1, ignore_index=True)

0object

1object

izq = pd.DataFrame({ 'key' : ['k0', 'k1', 'k2','k3'], 'A' : ['A0', 'A1', 'A2','A3'], 'B': ['B0', 'B1', 'B2','B3'] }) der = pd.DataFrame({ 'key' : ['k0', 'k1', 'k2','k3'], 'C' : ['C0', 'C1', 'C2','C3'], 'D': ['D0', 'D1', 'D2','D3'] })

# With merge we make join with common data. With merge we make join with common data. We need a common # column so that the merge can be done, otherwise an error will occur. izq.merge(der, on='key')

keyobject

Aobject

izq = pd.DataFrame({ 'key_1' : ['k0', 'k1', 'k2','k3'], 'A' : ['A0', 'A1', 'A2','A3'], 'B': ['B0', 'B1', 'B2','B3'] }) der = pd.DataFrame({ 'key_2' : ['k0', 'k1', 'k2','k3'], 'C' : ['C0', 'C1', 'C2','C3'], 'D': ['D0', 'D1', 'D2','D3'] })

# If we have two keys with different names we can use "left_on" and "right_on" izq.merge(der, left_on='key_1', right_on='key_2')

key_1object

Aobject

# With the "how" we specify how to combine our Dataframe. how='left' andf how='right' izq.merge(der, left_on='key_1', right_on='key_2', how='left')

key_1object

Aobject

# We created data izq = pd.DataFrame({ 'A': ['A0','A1','A2'], 'B':['B0','B1','B2']}, index=['k0','k1','k2']) der =pd.DataFrame({ 'C': ['C0','C1','C2'], 'D':['D0','D1','D2']}, index=['k0','k2','k3'])

# Whit "how" we play with our data (lef, right, others) izq.join(der, how='inner')

Aobject

Bobject

df_book.pivot_table(index='Author',columns='Genre',values='User Rating')

Fictionfloat64

3.9 - 4.9

Non Fictionfloat64

4.0 - 4.9

Abraham Verghese

4.6

nan

Adam Gasiewski

nan

4.4

Adam Mansbach

4.8

nan

Adir Levy

4.8

nan

Admiral William H. McRaven

nan

4.7

Adult Coloring Book Designs

nan

4.5

Alan Moore

4.8

nan

Alex Michaelides

4.5

nan

Alice Schertle

4.9

nan

Allie Brosh

nan

4.7

# We can add function wiht "aggfunc" df_book.pivot_table(index='Genre',columns='Year', values='User Rating',aggfunc='sum')

2009float64

2010float64

Fiction

110.2

92.3

Non Fiction

119

135.6

def two_times(value): return value * 2

# With apply we can call functions directly df_book['User Rating'].apply(two_times)

df_book['Rating 2'] = df_book['User Rating'].apply(two_times) df_book

Nameobject

Publication Manual of the American Psychological Association, 6th Edition1.8%

StrengthsFinder 2.01.6%

349 others96.6%

Authorobject

Jeff Kinney2.2%

Suzanne Collins2%

246 others95.8%

10-Day Green Smoothie Cleanse

JJ Smith

11/22/63: A Novel

Stephen King

12 Rules for Life: An Antidote to Chaos

Jordan B. Peterson

1984 (Signet Classics)

George Orwell

5,000 Awesome Facts (About Everything!) (National Geographic Kids)

National Geographic Kids

A Dance with Dragons (A Song of Ice and Fire)

George R. R. Martin

A Game of Thrones / A Clash of Kings / A Storm of Swords / A Feast of Crows / A Dance with Dragons

George R. R. Martin

A Gentleman in Moscow: A Novel

Amor Towles

A Higher Loyalty: Truth, Lies, and Leadership

James Comey

A Man Called Ove: A Novel

Fredrik Backman

# We can too use lambda functions df_book['User Rating2'] =df_book['User Rating'].apply(lambda x: x* 3) df_book

Nameobject

Publication Manual of the American Psychological Association, 6th Edition1.8%

StrengthsFinder 2.01.6%

349 others96.6%

Authorobject

Jeff Kinney2.2%

Suzanne Collins2%

246 others95.8%

10-Day Green Smoothie Cleanse

JJ Smith

11/22/63: A Novel

Stephen King

12 Rules for Life: An Antidote to Chaos

Jordan B. Peterson

1984 (Signet Classics)

George Orwell

5,000 Awesome Facts (About Everything!) (National Geographic Kids)

National Geographic Kids

A Dance with Dragons (A Song of Ice and Fire)

George R. R. Martin

A Game of Thrones / A Clash of Kings / A Storm of Swords / A Feast of Crows / A Dance with Dragons

George R. R. Martin

A Gentleman in Moscow: A Novel

Amor Towles

A Higher Loyalty: Truth, Lies, and Leadership

James Comey

A Man Called Ove: A Novel

Fredrik Backman