Library: Pandas¶
Installation¶
Pandas is an open-source library built on top of numpy providing high-performance, easy-to-use data structures and data analysis tools for the Python programming language.
Pandas supports the integration with many file formats or data sources out of the box (csv, excel, sql, json, parquet,…).
In [1]:
#!conda install pandas
#!pip install pandas
import numpy as np
import pandas as pd
In [2]:
labels = ['a','b','c']
my_list = [10,20,30]
arr = np.array([10,20,30])
d = {'a':10,'b':20,'c':30}
In [3]:
#from python list to Series
ser = pd.Series(data=my_list)
ser = pd.Series(data=my_list,index=labels)
ser = pd.Series(my_list,labels)
ser
#from Series to python list
pd.Series.to_list(ser)
Out[3]:
In [4]:
#from numpy array to Series
ser = pd.Series(arr)
ser = pd.Series(arr,labels)
ser
#from Series to numpy array
pd.Series(ser).array
Out[4]:
In [5]:
#from python dictionary to Series
ser = pd.Series(d)
ser
#from Series to dictionary
pd.Series.to_dict(ser)
Out[5]:
In [6]:
ser = pd.Series([10,20,30],['a','b','c'])
ser
Out[6]:
Indexing and selection¶
Pandas makes use of these index names or numbers by allowing for fast look ups of information.
In [7]:
ser1 = pd.Series([1,2,3,4],index = ['Enero', 'Febrero','Marzo', 'Abril'])
ser1
ser2 = pd.Series([1,2,5,4],index = ['Enero', 'Febrero','Junio', 'Abril'])
ser2
ser1['Febrero']
ser1.loc['Febrero'] #equivalent, based on label
ser1.iloc[1] #equivalent, based on position
Out[7]:
Operations¶
Based on their associated values.
In [8]:
ser1 + ser2 #NaN when no matching
Out[8]:
In [9]:
ser1 - ser2 #NaN when no matching
Out[9]:
In [10]:
ser1 / ser2 #NaN when no matching
Out[10]:
In [11]:
ser1 * ser2 #NaN when no matching
Out[11]:
Pandas Data Frames¶
Two-dimensional, size-mutable, potentially heterogeneous tabular data.
Data structure also contains labeled axes (rows and columns). Arithmetic operations align on both row and column labels. Can be thought of as a dict-like container for Series objects.
Data Input and Output¶
Creating Data Frames¶
In [12]:
#passing a dictionaty with columns of different types
df = pd.DataFrame({'A': (1,2,3,4,5,6),
'B': pd.Timestamp('20130102'),
'C': pd.Series(1, index=list(range(6)), dtype='float32'),
'D': np.array([3] * 6, dtype='int32'),
'E': pd.Categorical(["test", "train", "test", "train", "test", "train"]),
'F': 'foo'})
df
Out[12]:
Exploring the DataFrame¶
In [13]:
df.head(3) #first rows
Out[13]:
In [14]:
df.tail(3) #last rows
Out[14]:
In [15]:
df.info() #number of rows, columns, and types
In [16]:
df.describe() #basic description of quantitative data
Out[16]:
In [17]:
df.dtypes #data type of columns
Out[17]:
In [18]:
df.columns #column index names
Out[18]:
In [19]:
del df['F'] #deletes column
df
Out[19]:
In [20]:
df.sort_values(by='A', ascending=False) ##sorting dataframe, inplace=False by default
Out[20]:
Selection and Indexing¶
In [21]:
from numpy.random import randn
np.random.seed(123)
df = pd.DataFrame(randn(5,4),index='A B C D E'.split(),columns='W X Y Z'.split())
df
Out[21]:
In [22]:
#by row name
df.loc[['B']] #based on label, supports more than one
Out[22]:
In [23]:
#by row position
df.iloc[[1,2]] #based on position, supports more than one
Out[23]:
In [24]:
#by column name
df[['W']]
Out[24]:
In [25]:
#list of column names
df[['W','Z']]
Out[25]:
In [26]:
#by cell, [row,column]
df.loc['B','Y']
Out[26]:
In [27]:
#by group of cell, [rows,columns]
df.loc[['A','B'],['W','Y']]
Out[27]:
Conditional selection¶
In [28]:
#one condition
df[df>0] #NaN = false
Out[28]:
In [29]:
#condition in a column
df[df['W']>0]
Out[29]:
In [30]:
#condition in a column, select other columns
df[df['W']>0][['Y','X']]
Out[30]:
In [31]:
#two conditions in different columns
df[(df['W']>0) & (df['Y'] > 1)] #being & = AND
Out[31]:
In [32]:
#one condition or the other in different columns
df[(df['W']>0) | (df['Y'] > 1)] #being | = OR
Out[32]:
Modify index¶
In [33]:
# Reset to default 0,1...n index
df.reset_index()
df
Out[33]:
In [34]:
#new index
nind = 'blue green purple red yellow'.split()
df['Colours'] = nind
df.set_index('Colours')
#df.set_index('Colours',inplace=True) #to make it permanent
df
Out[34]:
Creating new column¶
In [35]:
df['newColumn'] = df['W'] + df['Y']
df
Out[35]:
Droping column¶
In [36]:
df.drop('newColumn',axis=1, inplace=True) #inplace=True makes it permanent
df.drop('Colours',axis=1, inplace=True) #inplace=True makes it permanent
df
Out[36]:
In [37]:
df.drop('E',axis=0) #inplace=True makes it permanent
df
Out[37]:
Null values¶
In [38]:
df = pd.DataFrame({'A':[1,2,np.nan],
'B':[5,np.nan,np.nan],
'C':[1,2,3]})
df
Out[38]:
In [39]:
df.isnull() #finding nulls
df.isna() #finding na
Out[39]:
In [40]:
df.dropna() #drops very row with NaN
Out[40]:
In [41]:
df.dropna(axis=1) #drops every column with NaN
Out[41]:
In [42]:
df.dropna(thresh=2) #drops every row with specific number of NaN
Out[42]:
In [43]:
df.fillna(value='FILL VALUE') #replaces NaN with a defined value
Out[43]:
In [44]:
df['A'].fillna(value=df['A'].mean()) #replaces NaN with the mean of the column
Out[44]:
Multi-Index Hierarchy¶
In [45]:
#Creates a data frame with multi-index
outside = ['bar', 'bar', 'baz', 'baz', 'foo', 'foo', 'qux', 'qux'] #index generated
inside = ['one', 'two', 'one', 'two', 'one', 'two', 'one', 'two'] #index generated
hier_index = list(zip(outside, inside)) #indexs are zip together
hier_index = pd.MultiIndex.from_tuples(hier_index, names=['first', 'second']) #hierarchy of index applied
df = pd.DataFrame(np.random.randn(8,3), index=hier_index, columns= ['A', 'B', 'C']) #random data generated
df
Out[45]:
In [46]:
#change index names
df.index.names
df.index.names = ['first','second']
df
Out[46]:
In [47]:
df.loc['foo'] #outer index selected
df.xs('foo') #equivalent
Out[47]:
In [48]:
df.loc['foo'].loc['one'] #outer and inner index selected by name
df.xs(['foo','one']) #equivalent
Out[48]:
In [49]:
df.xs('two',level='second') #cross-section function allows indexing directly inner index
Out[49]:
Groupby¶
The groupby method allows to group rows of data together and call aggregate functions.
In [50]:
# Create dataframe
data = {'Company':['GOOG','GOOG','MSFT','MSFT','FB','FB'],
'Person':['Sam','Charlie','Amy','Vanessa','Carl','Sarah'],
'Sales':[200,120,340,124,243,350]}
df = pd.DataFrame(data)
df
Out[50]:
In [51]:
df.groupby("Company") #group using criteria
df.groupby("Company").mean() #select way of aggregation(ex. mean)
Out[51]:
In [52]:
df.groupby("Company").sum() #sum of the group
Out[52]:
In [53]:
df.groupby("Company").max() #maximum of the group
Out[53]:
In [54]:
df.groupby("Company").std() #standard deviation of the group
Out[54]:
In [55]:
df.groupby("Company").count() #count cases of the group
Out[55]:
In [56]:
df.groupby("Company").describe().transpose() #describe + transpose
Out[56]:
Merging, Joining, and Concatenating¶
In [57]:
df1 = pd.DataFrame({'A': ['A0', 'A1', 'A2', 'A3'],
'B': ['B0', 'B1', 'B2', 'B3'],
'C': ['C0', 'C1', 'C2', 'C3'],
'D': ['D0', 'D1', 'D2', 'D3']},
index=[0, 1, 2, 3])
df1
Out[57]:
In [58]:
df2 = pd.DataFrame({'A': ['A4', 'A5', 'A6', 'A7'],
'B': ['B4', 'B5', 'B6', 'B7'],
'C': ['C4', 'C5', 'C6', 'C7'],
'D': ['D4', 'D5', 'D6', 'D7']},
index=[4, 5, 6, 7])
df2
Out[58]:
Concatenate¶
Dimensions should match along the axis you are concatenating on, gets all the rows together.
In [59]:
pd.concat([df1,df2], axis=0, join='outer', ignore_index=False, keys=None,
levels=None, names=None, verify_integrity=False, copy=True)
Out[59]:
In [60]:
result = df1.append(df2) #equivalent
result
Out[60]:
Merge¶
Allows you to merge DataFrames together using a similar logic as merging SQL Tables together.
In [61]:
#creates left table
left = pd.DataFrame({'key': ['K0', 'K1', 'K2', 'K3'],
'A': ['A0', 'A1', 'A2', 'A3'],
'B': ['B0', 'B1', 'B2', 'B3']})
left
#creates right table
right = pd.DataFrame({'key': ['K0', 'K1', 'K2', 'K3'],
'C': ['C0', 'C1', 'C2', 'C3'],
'D': ['D0', 'D1', 'D2', 'D3']})
right
#performs INNER merge (only coincidences in key between dataframes are kept)
pd.merge(left, right, how='inner', on='key', left_on=None, right_on=None,
left_index=False, right_index=False, sort=True,
suffixes=('_x', '_y'), copy=True, indicator=False,
validate=None)
Out[61]:
In [62]:
#creates left table
left = pd.DataFrame({'key1': ['K0', 'K0', 'K1', 'K2'],
'key2': ['K0', 'K1', 'K0', 'K1'],
'A': ['A0', 'A1', 'A2', 'A3'],
'B': ['B0', 'B1', 'B2', 'B3']})
#creates left table
right = pd.DataFrame({'key1': ['K0', 'K1', 'K1', 'K2'],
'key2': ['K0', 'K0', 'K0', 'K0'],
'C': ['C0', 'C1', 'C2', 'C3'],
'D': ['D0', 'D1', 'D2', 'D3']})
#performs inner merge (only coincidences between dataframes in both keys are kept)
pd.merge(left, right, on=['key1', 'key2'])
Out[62]:
In [63]:
#performs OUTER merge (all cases are kept)
pd.merge(left, right, how='outer', on=['key1', 'key2'])
Out[63]:
In [64]:
#performs RIGHT merge (all cases of right dataframe are kept)
pd.merge(left, right, how='right', on=['key1', 'key2'])
Out[64]:
In [65]:
#performs LEFT merge (all cases of left dataframe are kept)
pd.merge(left, right, how='left', on=['key1', 'key2'])
Out[65]:
Join¶
Combining the columns of two potentially differently-indexed DataFrames into a single result DataFrame.
In [66]:
left = pd.DataFrame({'A': ['A0', 'A1', 'A2'],
'B': ['B0', 'B1', 'B2']},
index=['K0', 'K1', 'K2'])
right = pd.DataFrame({'C': ['C0', 'C2', 'C3'],
'D': ['D0', 'D2', 'D3']},
index=['K0', 'K2', 'K3'])
left.join(right) #inner join
Out[66]:
In [67]:
left.join(right, how='outer') #outer join
Out[67]:
Operations¶
In [68]:
#creates data frame
df = pd.DataFrame({'col1':[1,2,3,4],'col2':[444,555,666,444],'col3':['abc','def','ghi','xyz']})
df
Out[68]:
In [69]:
#Select from DataFrame using criteria from multiple columns
newdf = df[(df['col1']>2) & (df['col2']==444)]
newdf
Out[69]:
In [70]:
#list of unique values
df['col2'].unique()
Out[70]:
In [71]:
#number of unique values
df['col2'].nunique()
Out[71]:
In [72]:
#amount of each value
df['col2'].value_counts()
Out[72]:
In [73]:
#Applying created functions
def times2(x):
return x*2
df['col1'].apply(times2)
Out[73]:
In [74]:
#Applying standard functions
df['col3'].apply(len) #lenght of the values
df['col1'].sum() #sum of the values
Out[74]:
Pivot table¶
In [75]:
#creates data frame from dictionary
data = {'A':['foo','foo','foo','bar','bar','bar'],
'B':['one','one','two','two','one','one'],
'C':['x','y','x','y','x','y'],
'D':[1,3,2,5,4,1]}
df = pd.DataFrame(data)
df
Out[75]:
In [76]:
#creates pivot table
df.pivot_table(values='D',index=['A', 'B'],columns=['C'])
Out[76]:
Reshaping by stacking¶
In [77]:
df
Out[77]:
In [78]:
df2 = df[:4]
df2
Out[78]:
In [79]:
#Stacking
stacked = df2.stack()
dfstacked = pd.DataFrame(stacked, columns =['cases'])
dfstacked
Out[79]:
In [80]:
#Unstacking
dfstacked.unstack()
Out[80]:
Reshaping by Melt¶
In [81]:
cheese = pd.DataFrame({'first': ['John', 'Mary'],
'last': ['Doe', 'Bo'],
'height': [5.5, 6.0],
'weight': [130, 150]})
cheese
Out[81]:
In [82]:
cheese.melt(id_vars=['first', 'last'], var_name='quantity')
Out[82]:
Data visualization¶
In [83]:
import matplotlib as plt
import seaborn as sns
# loading databases
tips = sns.load_dataset('tips')
tips.head()
Out[83]:
In [84]:
%matplotlib inline
Styles¶
In [85]:
#set matplotlib style
plt.style.use('default')
tips['total_bill'].hist()
Out[85]:
In [86]:
plt.style.use('ggplot')
tips['total_bill'].hist()
Out[86]:
In [87]:
plt.style.use('bmh')
tips['total_bill'].hist()
Out[87]:
In [88]:
plt.style.use('fivethirtyeight')
tips['total_bill'].hist()
Out[88]:
In [89]:
plt.style.use('classic')
tips['total_bill'].hist()
Out[89]:
In [90]:
plt.style.use('grayscale')
tips['total_bill'].hist()
Out[90]:
In [91]:
plt.style.use('seaborn')
tips['total_bill'].hist()
Out[91]:
Graphic types¶
In [92]:
df1 = tips[0:20][['total_bill', 'tip']]
df1.head()
df2 = tips[['total_bill', 'tip', 'day']]
df2.head()
df3 = tips[0:20][['total_bill', 'tip', 'size']]
df3.head()
Out[92]:
In [93]:
df1.plot.area() #can only use quantitative information, qualitative make error
Out[93]:
In [94]:
df1.plot.barh(stacked=True) #can only work with quantitative data, skips qualitative
Out[94]:
In [95]:
df1.plot.density() #distribution of quantitative data, skips qualitative
Out[95]:
In [96]:
df2['total_bill'].plot.hist(bins = 10) #distribution of quantitative data, skips qualitative
Out[96]:
In [97]:
df2.plot.line(figsize=(12,3),lw=1) #works with quantitative data, skips qualitative
Out[97]:
In [98]:
df3.plot.scatter(x = 'total_bill', y = 'tip', c = 'size', s=df3['size']*100, cmap = 'rainbow') #can only use quantitative information, qualitative make error
Out[98]:
In [99]:
df1.plot.bar(stacked = True) #can only work with quantitative data, skips qualitative
Out[99]:
In [100]:
df1.plot.box() #categories are the columns, can be used "by = " for groupby
Out[100]:
In [101]:
df1.plot.hexbin('total_bill', 'tip',gridsize=25,cmap='Blues') #distribution of quantitative data, skips qualitative
Out[101]:
In [102]:
df1.plot.kde()
Out[102]: