In [1]:
# import
import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
%matplotlib inline
In [2]:
# creating Series (array)
s1 = pd.Series([[1,3,5,np.nan,6,8]])
s1
Out[2]:
In [3]:
# creating DataFrames
dates = pd.date_range('20130101', periods=6)
dates
Out[3]:
In [4]:
df = pd.DataFrame(np.random.randn(6,4), index=dates, columns=list('ABCD'))
df
Out[4]:
In [5]:
# Creating a DataFrame by passing a dict of objects that can be converted to series-like.
df2 = pd.DataFrame({ 'A' : 1.,
'B' : pd.Timestamp('20130102'),
'C' : pd.Series(1,index=list(range(4)),dtype='float32'),
'D' : np.array([3] * 4,dtype='int32'),
'E' : pd.Categorical(["test","train","test","train"]),
'F' : 'foo' })
df2
Out[5]:
In [6]:
# checking Data Types
df2.dtypes
Out[6]:
In [7]:
# Use df2.<TAB> for column name completion as well as attributes which can work on dataframe
In [8]:
# for first 5 records
df.head()
Out[8]:
In [9]:
# for last 3
df.tail(3)
Out[9]:
In [10]:
# checking df index
df.index
Out[10]:
In [11]:
# column names
df.columns
Out[11]:
In [12]:
# df values
df.values
Out[12]:
In [13]:
# information abt df
df.info()
In [14]:
# describing stastistic summay
df.describe()
Out[14]:
In [15]:
df.T
Out[15]:
In [16]:
# Sorting by index
df.sort_index(axis=1, ascending=False)
Out[16]:
In [17]:
# sorting by values
df.sort_values(by="B")
Out[17]:
In [18]:
df.sort_values(by=["B", "A"])
Out[18]:
In [19]:
# selecting a column A
df['A']
Out[19]:
In [20]:
# slicing rows
df[0:3]
Out[20]:
In [21]:
df['2013-01-01':'2013-01-03']
Out[21]:
In [22]:
# cross selection using a lable
df.loc[dates[0]]
Out[22]:
In [23]:
df.loc[:, ['A', 'B']] # [row, column]
Out[23]:
In [24]:
# Showing label slicing, both endpoints are included
df.loc['20130102':'20130104',['A','B']]
Out[24]:
In [25]:
df.loc['20130102',['A','B']]
Out[25]:
In [26]:
# For getting a scalar value
df.loc['20130102',['A']]
Out[26]:
In [27]:
df.loc['20130102','B']
Out[27]:
In [28]:
# for faster access
#df.at['20130102', 'A']
In [29]:
# Select via the position of the passed integers
df.iloc[3]
Out[29]:
In [30]:
# By integer slices, acting similar to numpy/python
df.iloc[3:5, 2:4] # row - 3 n 4 , col = 2, 3
Out[30]:
In [31]:
# By lists of integer position locations, similar to the numpy/python style
df.iloc[[1,3,4], 2:4]
Out[31]:
In [32]:
# For slicing rows explicitly
df.iloc[1:3,:]
Out[32]:
In [33]:
# For slicing columns explicitly
df.iloc[:,1:3]
Out[33]:
In [34]:
# For getting a value explicitly
df.iloc[1,1]
Out[34]:
In [35]:
df.loc['2013-01-02', 'B']
Out[35]:
In [36]:
# For getting fast access to a scalar (equiv to the prior method)
df.iat[1,1]
Out[36]:
In [37]:
df.B > 0
Out[37]:
In [38]:
df[df.B > 0]
Out[38]:
In [39]:
df[df>0]
Out[39]:
In [40]:
# Using the isin() method for filtering:
df3 = df.copy()
df3['E'] = ['one', 'one','two','three','four','three']
df3
Out[40]:
In [41]:
df3[df3['E'].isin(['two','four'])]
Out[41]:
In [42]:
s1 = pd.Series([1,2,3,4,5,6], index=pd.date_range('20130102', periods=6))
s1
Out[42]:
In [43]:
df['F'] = s1
df
Out[43]:
In [44]:
# Setting values by label
df.at[dates[0],'A'] = 0
df
Out[44]:
In [45]:
# Setting values by position
df.iat[0,1] = 0
df
Out[45]:
In [46]:
# Setting by assigning with a numpy array
df.loc[:,'D'] = np.array([5] * len(df))
df
Out[46]:
In [47]:
df2 = df.copy()
df2
Out[47]:
In [48]:
# to replace all the positive value from its negative
df2[df2 > 0] = -df2
df2
Out[48]:
In [49]:
df2[df2 < 0] = -df2
df2
Out[49]:
In [50]:
# Reindexing allows you to change/add/delete the index on a specified axis. This returns a copy of the data.
df1 = df.reindex(index=dates[0:4], columns=list(df.columns) + ['E'])
df1
Out[50]:
In [51]:
df1.loc[dates[1],'E'] = 1
df1
Out[51]:
In [52]:
# to drop any rows that have missing data.
df1.dropna(how='any') # if any columns have NULL or NaN
Out[52]:
In [53]:
df1.dropna()
Out[53]:
In [54]:
df1.dropna(how='all') # if ALL columns have NULL or NaN
Out[54]:
In [55]:
df1.fillna(3)
Out[55]:
In [56]:
df1.fillna(value=4)
Out[56]:
In [57]:
df1.fillna({'F':3, 'E':2.9}) # Fill F column with 3 and E column wih 2.9
Out[57]:
In [58]:
pd.isnull(df1)
Out[58]:
In [59]:
df.info()
In [60]:
df.describe()
Out[60]:
In [61]:
df.count()
Out[61]:
In [62]:
df.mean() # column wise
Out[62]:
In [63]:
df.mean(1) # row wise
Out[63]:
In [64]:
df.std()
Out[64]:
In [65]:
df.std(1)
Out[65]:
In [66]:
s = pd.Series([1,3,5,np.nan,6,8], index=dates)
s
Out[66]:
In [67]:
s = s.shift(2) # shifting the content by 2 index
s
Out[67]:
In [68]:
df.sub(s, axis='index')
Out[68]:
In [69]:
df.apply(np.cumsum)
Out[69]:
In [70]:
df.apply(lambda x: x.max() - x.min())
Out[70]:
In [71]:
s = pd.Series(np.random.randint(0, 7, size=10))
s
Out[71]:
In [72]:
s.value_counts() # checking unique value counts
Out[72]:
In [73]:
s = pd.Series(['A', 'B', 'C', 'Aaba', 'Baca', np.nan, 'CABA', 'dog', 'cat'])
s
Out[73]:
In [74]:
s.str.lower()
Out[74]:
In [75]:
df = pd.DataFrame(np.random.randn(10, 4))
df
Out[75]:
In [76]:
pieces = [df[:3], df[3:7], df[7:]]
pieces
Out[76]:
In [77]:
pd.concat(pieces) # concat rowwise
Out[77]:
In [78]:
pd.concat(pieces, axis=1) # concat column wise
Out[78]:
In [79]:
left = pd.DataFrame({'key': ['foo', 'foo'], 'lval': [1, 2]})
right = pd.DataFrame({'key': ['foo', 'foo'], 'rval': [4, 5]})
left
Out[79]:
In [80]:
right
Out[80]:
In [81]:
pd.merge(left, right, on='key')
Out[81]:
In [82]:
left = pd.DataFrame({'key': ['foo', 'bar'], 'lval': [1, 2]})
right = pd.DataFrame({'key': ['foo', 'bar'], 'rval': [4, 5]})
left
Out[82]:
In [83]:
pd.merge(left, right, on='key')
Out[83]:
In [84]:
df = pd.DataFrame(np.random.randn(8, 4), columns=['A','B','C','D'])
df
Out[84]:
In [85]:
s = df.iloc[3]
s
Out[85]:
In [86]:
df.append(s, ignore_index=True)
df
Out[86]:
In [87]:
df = pd.DataFrame({'A' : ['foo', 'bar', 'foo', 'bar',
'foo', 'bar', 'foo', 'foo'],
'B' : ['one', 'one', 'two', 'three',
'two', 'two', 'one', 'three'],
'C' : np.random.randn(8),
'D' : np.random.randn(8)})
df
Out[87]:
In [88]:
df.groupby('A').sum()
Out[88]:
In [89]:
df.groupby(['A','B']).sum()
Out[89]:
In [90]:
tuples = list(zip(*[['bar', 'bar', 'baz', 'baz',
'foo', 'foo', 'qux', 'qux'],
['one', 'two', 'one', 'two',
'one', 'two', 'one', 'two']]))
index = pd.MultiIndex.from_tuples(tuples, names=['first', 'second'])
index
Out[90]:
In [91]:
df = pd.DataFrame(np.random.randn(8, 2), index=index, columns=['A', 'B'])
df
Out[91]:
The stack() method “compresses” a level in the DataFrame’s columns
In [92]:
df.stack()
Out[92]:
With a “stacked” DataFrame or Series (having a MultiIndex as the index), the inverse operation of stack() is unstack(), which by default unstacks the last level:
In [93]:
df.unstack() # this will unstack the inner index (last level)
Out[93]:
In [94]:
df.unstack('first') # unstacking by lable
Out[94]:
In [95]:
df.unstack('second')
Out[95]:
In [96]:
df.unstack(0) # unstacking by index
Out[96]:
In [97]:
df = pd.DataFrame({'A' : ['one', 'one', 'two', 'three'] * 3,
'B' : ['A', 'B', 'C'] * 4,
'C' : ['foo', 'foo', 'foo', 'bar', 'bar', 'bar'] * 2,
'D' : np.random.randn(12),
'E' : np.random.randn(12)})
df
Out[97]:
In [98]:
pd.pivot_table(df, values='D', index=['A', 'B'], columns=['C'])
Out[98]:
In [99]:
rng = pd.date_range('1/1/2012', periods=100, freq='S')
rng[:5]
Out[99]:
In [100]:
ts = pd.Series(np.random.randint(0, 500, len(rng)), index=rng)
ts[:8]
Out[100]:
In [101]:
ts.sum()
Out[101]:
In [102]:
ts.resample('5Min').sum()
Out[102]:
In [103]:
ts.resample('5Min')
Out[103]:
Time zone representation
In [104]:
rng = pd.date_range('3/6/2012 00:00', periods=5, freq='D')
rng
Out[104]:
In [105]:
ts = pd.Series(np.random.randn(len(rng)), rng)
ts
Out[105]:
In [106]:
ts_utc = ts.tz_localize('UTC')
ts_utc
Out[106]:
Convert to another time zone
In [107]:
ts_utc.tz_convert('Asia/Calcutta')
Out[107]:
Converting between time span representations
In [108]:
rng = pd.date_range('1/1/2012', periods=5, freq='M')
ts = pd.Series(np.random.randn(len(rng)), index=rng)
ts
Out[108]:
In [109]:
ps = ts.to_period()
ps
Out[109]:
In [110]:
ps.to_timestamp()
Out[110]:
Converting between period and timestamp enables some convenient arithmetic functions to be used. In the following example, we convert a quarterly frequency with year ending in November to 9am of the end of the month following the quarter end:
In [111]:
prng = pd.period_range('1990Q1', '2000Q4', freq='Q-NOV')
ts = pd.Series(np.random.randn(len(prng)), prng)
ts.head()
Out[111]:
In [112]:
ts.index = (prng.asfreq('M', 'e') + 1).asfreq('H', 's') + 9
ts.head()
Out[112]:
In [113]:
df = pd.DataFrame({"id":[1,2,3,4,5,6], "raw_grade":['a', 'b', 'b', 'a', 'a', 'e']})
df
Out[113]:
In [114]:
df.dtypes
Out[114]:
In [115]:
df["grade"] = df["raw_grade"].astype("category")
df["grade"]
Out[115]:
Rename the categories to more meaningful names (assigning to Series.cat.categories is inplace!)
In [116]:
df["grade"].cat.categories = ["very good", "good", "very bad"]
df
Out[116]:
In [117]:
# Reorder the categories and simultaneously add the
# missing categories (methods under Series .cat return a new Series per default).
df["grade"] = df["grade"].cat.set_categories(["very bad", "bad", "medium", "good", "very good"])
df
Out[117]:
In [118]:
# Sorting is per order in the categories, not lexical order.
df.sort_values(by="grade")
Out[118]:
In [119]:
# Grouping by a categorical column shows also empty categories.
df.groupby("grade").size()
Out[119]:
In [120]:
ts = pd.Series(np.random.randn(1000), index=pd.date_range('1/1/2000', periods=1000))
ts = ts.cumsum()
ts.plot()
Out[120]:
In [121]:
# On DataFrame, plot() is a convenience to plot all of the columns with labels:
df = pd.DataFrame(np.random.randn(1000, 4), index=ts.index,
columns=['A', 'B', 'C', 'D'])
df = df.cumsum()
df.plot(grid=True)
Out[121]:
In [122]:
df[:4]
Out[122]:
to_csv and read_csv
In [123]:
# writing it to csv
df.to_csv("dataset/df_as_csv.csv")
In [124]:
# reading it
df2 = pd.read_csv("dataset/df_as_csv.csv")
df2.head()
Out[124]:
for hdfs, to_hdf and read_hdf
In [125]:
df.to_hdf('foo.h5','df')
In [126]:
pd.read_hdf('foo.h5','df').head()
Out[126]:
For Excel, to_excel and read_excel
In [127]:
df.to_excel('foo.xlsx', sheet_name='Sheet1')
In [129]:
pd.read_excel('foo.xlsx', 'Sheet1', index_col=None, na_values=['NA']).head()
Out[129]:
In [ ]: