Pandas¶
Jérémie Decock (www.jdhp.org)
Official documentation: http://pandas.pydata.org/pandas-docs/stable/
Import directives¶
In [1]:
%matplotlib inline
#%matplotlib notebook
from IPython.display import display
import matplotlib
matplotlib.rcParams['figure.figsize'] = (9, 9)
import matplotlib.pyplot as plt
import matplotlib.dates as mdates
import datetime
import pandas as pd
import numpy as np
In [2]:
pd.__version__
Out[2]:
Make data¶
Series (1D data)¶
With automatic indices¶
In [3]:
data_list = [1, 3, np.nan, 7]
series = pd.Series(data_list)
series
Out[3]:
In [4]:
data_array = np.array(data_list)
series = pd.Series(data_array)
series
Out[4]:
With defined indices¶
In [5]:
indices = pd.Series([1, 3, 5, 7])
series = pd.Series([10, 30, 50, 70], index=indices)
series
Out[5]:
In [6]:
indices = pd.Series(['A', 'B', 'C', 'D'])
series = pd.Series([10, 30, 50, 70], index=indices)
series
Out[6]:
In [7]:
data_dict = {'A': 10, 'B': 30, 'C': 50, 'D': 70}
series = pd.Series(data_dict)
series
Out[7]:
Get information about a series¶
In [8]:
series.index
Out[8]:
In [9]:
series.values
Out[9]:
In [10]:
series.shape
Out[10]:
In [11]:
series.dtypes
Out[11]:
In [12]:
series.describe()
Out[12]:
In [13]:
type(series.describe())
Out[13]:
In [14]:
series.memory_usage()
Out[14]:
Date ranges¶
In [15]:
dates = pd.date_range('20130101', periods=6)
dates
Out[15]:
In [16]:
dates = pd.date_range(start='2013-01-01', end='2013-01-08')
dates
Out[16]:
In [17]:
dates = pd.date_range('2013-01-01', periods=4, freq='M')
dates
Out[17]:
In [18]:
num_days = 7
data = np.random.random(num_days)
index = pd.date_range('2017-01-01', periods=num_days)
series = pd.Series(data, index)
series
Out[18]:
Frames (2D data)¶
With automatic indices and columns¶
In [19]:
data_list = [[1, 2, 3], [4, 5, 6]]
df = pd.DataFrame(data_array)
df
Out[19]:
In [20]:
data_array = np.array([[1, 2, 3], [4, 5, 6]])
df = pd.DataFrame(data_array)
df
Out[20]:
With defined indices and columns¶
Using lists:
In [21]:
data = [[1, 2, 3], [4, 5, 6]]
index = [10, 20]
columns = ['A', 'B', 'C']
df = pd.DataFrame(data, index, columns)
df
Out[21]:
Using numpy arrays:
In [22]:
data = np.array([[1, 2, 3], [4, 5, 6]])
index = np.array([10, 20])
columns = np.array(['A', 'B', 'C'])
df = pd.DataFrame(data, index=index, columns=columns)
df
Out[22]:
Using Series:
In [23]:
data = np.array([[1, 2, 3], [4, 5, 6]])
index = pd.Series([10, 20])
columns = pd.Series(['A', 'B', 'C'])
df = pd.DataFrame(data, index=index, columns=columns)
df
Out[23]:
With columns from dict¶
Dictionary keys define columns label.
In [24]:
data_dict = {'A': 'foo',
'B': [10, 20, 30],
'C': 3}
df = pd.DataFrame(data_dict)
df
Out[24]:
To define index as well:
In [25]:
data_dict = {'A': 'foo',
'B': [10, 20, 30],
'C': 3}
df = pd.DataFrame(data_dict, index=[10, 20, 30])
df
Out[25]:
Get information about a dataframe¶
In [26]:
df.index
Out[26]:
In [27]:
df.columns
Out[27]:
In [28]:
df.values
Out[28]:
In [29]:
df.shape
Out[29]:
In [30]:
df.dtypes
Out[30]:
In [31]:
df.info()
In [32]:
df.describe()
Out[32]:
In [33]:
type(df.describe())
Out[33]:
In [34]:
df.memory_usage()
Out[34]:
More details about dtype¶
DataFrame's columns can have different types. But what about rows ?
What append when a DataFrame with columns having different type is transposed ?
In [35]:
data_dict = {'A': 'foo',
'B': [10, 20, 30],
'C': 3}
df = pd.DataFrame(data_dict)
df
Out[35]:
In [36]:
df.dtypes
Out[36]:
In [37]:
df2 = df.T
df2
Out[37]:
In [38]:
df2.dtypes
Out[38]:
Panels (3D data)¶
Panels are deprecated.
Pandas now focuses on 1D (Series) and 2D (DataFrame) data structures.
The recommended alternative to work with 3-dimensional data is the xarray python library.
An other workaround: one can simply use a MultiIndex DataFrame for easily working with higher dimensional data.
See http://pandas.pydata.org/pandas-docs/stable/dsintro.html#deprecate-panel.
Panel4D and PanelND (ND data)¶
Panel4D and PanelND are deprecated.
Pandas now focuses on 1D (Series) and 2D (DataFrame) data structures.
The recommended alternative to work with n-dimensional data is the xarray python library.
An other workaround: one can simply use a MultiIndex DataFrame for easily working with higher dimensional data.
See http://pandas.pydata.org/pandas-docs/stable/dsintro.html#panel4d-and-panelnd-deprecated.
Export/import data (write/read files)¶
Reader functions are accessibles from the top level pd object.
Writer functions are accessibles from data objects (i.e. Series, DataFrame or Panel objects).
In [39]:
data_array = np.array([[1, 2, 3], [4, 5, 6]])
df = pd.DataFrame(data_array, index=[10, 20], columns=[100, 200, 300])
df
Out[39]:
HDF5 files¶
See python_pandas_hdf5_en.ipynb...
CSV files¶
Write CSV files¶
Simplest version:
In [40]:
df.to_csv(path_or_buf="python_pandas_io_test.csv")
In [41]:
!cat python_pandas_io_test.csv
Setting more options:
In [42]:
# FYI, many other options are available
df.to_csv(path_or_buf="python_pandas_io_test.csv",
sep=',',
columns=None,
header=True,
index=True,
index_label=None,
compression=None, # allowed values are 'gzip', 'bz2' or 'xz'
date_format=None)
In [43]:
!cat python_pandas_io_test.csv
Read CSV files¶
Simplest version:
In [44]:
df = pd.read_csv("python_pandas_io_test.csv")
df
Out[44]:
Setting more options:
In [45]:
df = pd.read_csv("python_pandas_io_test.csv",
sep=',',
delimiter=None,
header='infer',
names=None,
index_col=0,
usecols=None,
squeeze=False,
prefix=None,
mangle_dupe_cols=True,
dtype=None,
engine=None,
converters=None,
true_values=None,
false_values=None,
skipinitialspace=False,
skiprows=None,
nrows=None,
na_values=None,
keep_default_na=True,
na_filter=True,
verbose=False,
skip_blank_lines=True,
parse_dates=False,
infer_datetime_format=False,
keep_date_col=False,
date_parser=None,
dayfirst=False,
iterator=False,
chunksize=None,
compression='infer',
thousands=None,
decimal=b'.',
lineterminator=None,
quotechar='"',
quoting=0,
escapechar=None,
comment=None,
encoding=None,
dialect=None,
#tupleize_cols=False,
error_bad_lines=True,
warn_bad_lines=True,
skipfooter=0,
#skip_footer=0,
doublequote=True,
delim_whitespace=False,
#as_recarray=False,
#compact_ints=False,
#use_unsigned=False,
low_memory=True,
#buffer_lines=None,
memory_map=False,
float_precision=None)
df
Out[45]:
In [46]:
!rm python_pandas_io_test.csv
JSON files¶
In [47]:
import io
Write JSON files¶
Simplest version¶
In [48]:
df.to_json(path_or_buf="python_pandas_io_test.json")
In [49]:
!cat python_pandas_io_test.json
Setting orient="split"¶
In [50]:
df.to_json(path_or_buf="python_pandas_io_test_split.json",
orient="split")
In [51]:
!cat python_pandas_io_test_split.json
Setting orient="records"¶
In [52]:
df.to_json(path_or_buf="python_pandas_io_test_records.json",
orient="records")
In [53]:
!cat python_pandas_io_test_records.json
Setting orient="index" (the default option for Series)¶
In [54]:
df.to_json(path_or_buf="python_pandas_io_test_index.json",
orient="index")
In [55]:
!cat python_pandas_io_test_index.json
Setting orient="columns" (the default option for DataFrame) (for DataFrame only)¶
In [56]:
df.to_json(path_or_buf="python_pandas_io_test_columns.json",
orient="columns")
In [57]:
!cat python_pandas_io_test_columns.json
Setting orient="values" (for DataFrame only)¶
In [58]:
df.to_json(path_or_buf="python_pandas_io_test_values.json",
orient="values")
In [59]:
!cat python_pandas_io_test_values.json
Setting more options¶
In [60]:
# FYI, many other options are available
df.to_json(path_or_buf="python_pandas_io_test.json",
orient='columns', # For DataFrame: 'split','records','index','columns' or 'values'
date_format=None, # None, 'epoch' or 'iso'
double_precision=10,
force_ascii=True,
date_unit='ms')
In [61]:
!cat python_pandas_io_test.json
Read JSON files¶
Using orient="split"¶
Dict like data {index -> [index], columns -> [columns], data -> [values]}
In [62]:
!cat python_pandas_io_test_split.json
In [63]:
df = pd.read_json("python_pandas_io_test_split.json",
orient="split")
df
Out[63]:
Using orient="records"¶
List like [{column -> value}, ... , {column -> value}]
In [64]:
!cat python_pandas_io_test_records.json
In [65]:
df = pd.read_json("python_pandas_io_test_records.json",
orient="records")
df
Out[65]:
Using orient="index"¶
Dict like {index -> {column -> value}}
In [66]:
!cat python_pandas_io_test_index.json
In [67]:
df = pd.read_json("python_pandas_io_test_index.json",
orient="index")
df
Out[67]:
Using orient="columns"¶
Dict like {column -> {index -> value}}
In [68]:
!cat python_pandas_io_test_columns.json
In [69]:
df = pd.read_json("python_pandas_io_test_columns.json",
orient="columns")
df
Out[69]:
Using orient="values" (for DataFrame only)¶
Just the values array
In [70]:
!cat python_pandas_io_test_values.json
In [71]:
df = pd.read_json("python_pandas_io_test_values.json",
orient="values")
df
Out[71]:
Setting more options¶
In [72]:
df = pd.read_json("python_pandas_io_test.json",
orient=None,
typ='frame',
dtype=True,
convert_axes=True,
convert_dates=True,
keep_default_dates=True,
numpy=False,
precise_float=False,
date_unit=None,
encoding=None,
lines=False)
df
Out[72]:
In [73]:
!rm python_pandas_io_test*.json
YAML¶
In [74]:
!echo "- {A: 1, B: 2}" > python_pandas_io_test.yaml
!echo "- {A: 3}" >> python_pandas_io_test.yaml
!echo "- {B: 4}" >> python_pandas_io_test.yaml
In [75]:
!cat python_pandas_io_test.yaml
In [76]:
try:
import yaml
with open('python_pandas_io_test.yaml', 'r') as f:
df = pd.io.json.json_normalize(yaml.load(f))
print(df)
except:
pass
In [77]:
!rm python_pandas_io_test.yaml
Other file formats¶
Many other file formats can be used to import or export data with JSON.
See the following link for more information: http://pandas.pydata.org/pandas-docs/stable/io.html
Select columns¶
In [78]:
data_array = np.array([np.arange(1, 10, 1), np.arange(10, 100, 10), np.arange(100, 1000, 100)]).T
df = pd.DataFrame(data_array,
index=np.arange(1, 10, 1),
columns=['A', 'B', 'C'])
df
Out[78]:
Select a single column¶
The following instructions return a Series.
Label based selection¶
In [79]:
df.B
Out[79]:
In [80]:
df["B"]
Out[80]:
In [81]:
df.loc[:,"B"]
Out[81]:
Index based selection¶
In [82]:
df.iloc[:,1]
Out[82]:
Select multiple columns¶
Label based selection¶
In [83]:
df[['A','B']]
Out[83]:
In [84]:
df.loc[:,['A','B']]
Out[84]:
Index based selection¶
In [85]:
df.iloc[:,0:2]
Out[85]:
Select rows¶
In [86]:
data_array = np.array([np.arange(1, 10, 1), np.arange(10, 100, 10), np.arange(100, 1000, 100)]).T
df = pd.DataFrame(data_array,
index=["i" + str(i+1) for i in range(9)],
columns=['A', 'B', 'C'])
df
Out[86]:
Select a single row¶
The following instructions return a Series.
Label based selection¶
In [87]:
df.loc["i3"]
Out[87]:
In [88]:
df.loc["i3",:]
Out[88]:
Index based selection¶
In [89]:
df.iloc[2] # Select over index
Out[89]:
In [90]:
df.iloc[2,:] # Select over index
Out[90]:
Select multiple rows¶
Label based selection¶
In [91]:
df.loc[["i3", "i4"],:]
Out[91]:
Index based selection¶
In [92]:
df.iloc[2:4,:] # Select over index
Out[92]:
Select rows based on values¶
In [93]:
df.B < 50.
Out[93]:
In [94]:
type(df.B < 50.)
Out[94]:
In [95]:
df[[True, True, True, True, False, False, False, False, False]]
Out[95]:
In [96]:
series_mask = pd.Series({'i1': True,
'i2': True,
'i3': True,
'i4': True,
'i5': False,
'i6': False,
'i7': False,
'i8': False,
'i9': False})
df[series_mask]
Out[96]:
In [97]:
df[df.B < 50.]
Out[97]:
In [98]:
df[df['B'] < 50.]
Out[98]:
In [99]:
df[(df.A >= 2) & (df.B < 50)]
Out[99]:
This can be written:
In [100]:
df.loc[(df.A >= 2) & (df.B < 50)]
Out[100]:
This could be written df[df.A >= 2][df.B < 50] but this is a bad practice (named "chained indexing").
"When setting values in a pandas object, care must be taken to avoid what is called chained indexing".
See:
Select rows and columns¶
In [101]:
data_array = np.array([np.arange(1, 10, 1), np.arange(10, 100, 10), np.arange(100, 1000, 100)]).T
df = pd.DataFrame(data_array,
index=np.arange(1, 10, 1),
columns=['A', 'B', 'C'])
df
Out[101]:
In [102]:
df[(df.A >= 2) & (df.B < 50)]
Out[102]:
In [103]:
df[(df.B < 20) | (df.B > 50)]
Out[103]:
In [104]:
df.loc[(df.B < 20) | (df.B > 50), 'C']
Out[104]:
In [105]:
df[(df['A'] >= 2) & (df['B'] < 50)]
Out[105]:
In [106]:
df.loc[(df.A >= 2) & (df.B < 50), ['A','B']]
Out[106]:
Setting values¶
Apply a function to selected colunms values¶
In [107]:
data_array = np.array([np.arange(1, 10, 1), np.arange(10, 100, 10), np.arange(100, 1000, 100)]).T
df = pd.DataFrame(data_array,
index=np.arange(1, 10, 1),
columns=['A', 'B', 'C'])
df
Out[107]:
In [108]:
df.B *= 2.
df
Out[108]:
In [109]:
df.B = pow(df.B, 2)
df
Out[109]:
Apply a function to selected rows values¶
In [110]:
data_array = np.array([np.arange(1, 10, 1), np.arange(10, 100, 10), np.arange(100, 1000, 100)]).T
df = pd.DataFrame(data_array,
index=np.arange(1, 10, 1),
columns=['A', 'B', 'C'])
df
Out[110]:
In [111]:
df[df.B < 50.] *= -1.
df
Out[111]:
In [112]:
# df['B'][df['B'] < 50.] = 0 # OK but chain indexing is bad...
# df.A[df.B < 50.] = 0 # OK but chain indexing is bad...
df.loc[df.B < 50., 'A'] = 0
df
Out[112]:
WARNING: df[df.B < 50.].A = 0 does NOT work even if df.A[df.B < 50.] and df[df.B < 50.].A seems to produce the same result...
"When setting values in a pandas object, care must be taken to avoid what is called chained indexing".
See:
In [113]:
df.loc[(df.B < 50.) & (df.B > 20), 'C'] = 0
df
Out[113]:
In [114]:
df.loc[(df.B < 20) | (df.B > 50), 'C'] = -1
df
Out[114]:
In [115]:
df[df.B < 50.] = pow(df[df.B < 50.], 2)
df
Out[115]:
Sample rows¶
In [116]:
data_array = np.array([np.arange(1, 10, 1), np.arange(10, 100, 10), np.arange(100, 1000, 100)]).T
df = pd.DataFrame(data_array,
index=np.arange(1, 10, 1),
columns=['A', 'B', 'C'])
df
Out[116]:
With replacement¶
Draw 3 samples:
In [117]:
df.sample(n=30, replace=True)
Out[117]:
Sample 90% of the rows:
In [118]:
df.sample(frac=0.9, replace=True)
Out[118]:
Without replacement¶
Draw 3 samples:
In [119]:
df.sample(n=3)
Out[119]:
Sample 90% of the rows:
In [120]:
df.sample(frac=0.9)
Out[120]:
Weighted sampling¶
In [121]:
df.sample(n=30, replace=True, weights=np.arange(len(df)))
Out[121]:
Shuffle/permute rows¶
In [122]:
data_array = np.array([np.arange(1, 10, 1), np.arange(10, 100, 10), np.arange(100, 1000, 100)]).T
df = pd.DataFrame(data_array,
index=np.arange(1, 10, 1),
columns=['A', 'B', 'C'])
df
Out[122]:
In [123]:
df = df.sample(frac=1)
df
Out[123]:
To reset indexes too:
In [124]:
df = df.sample(frac=1).reset_index(drop=True)
df
Out[124]:
Sort a DataFrame¶
In [125]:
NROWS = 7
col1 = np.arange(1., NROWS, 1)
col2 = np.arange(10., NROWS*10, 10)
col3 = np.arange(100., NROWS*100, 100)
np.random.shuffle(col1)
np.random.shuffle(col2)
np.random.shuffle(col3)
data = np.array([col1,
col2,
col3]).T
index = np.arange(1, NROWS, 1)
columns = np.array(['A', 'B', 'C'])
np.random.shuffle(index)
np.random.shuffle(data)
np.random.shuffle(columns)
df = pd.DataFrame(data,
index=index,
columns=columns)
df
Out[125]:
Sorting by row index or column label¶
Sorting by rows¶
In [126]:
df.sort_index()
Out[126]:
In [127]:
df.sort_index(axis=0) # axis=0 -> sort by row index
Out[127]:
In [128]:
df.sort_index(ascending=False)
Out[128]:
Sorting by columns¶
In [129]:
df.sort_index(axis=1) # axis=1 -> sort by column label
Out[129]:
In [130]:
df.sort_index(axis=1, ascending=False)
Out[130]:
Sorting by values¶
In [131]:
df.sort_values(by='B')
Out[131]:
In [132]:
df.sort_values(by='B', ascending=False)
Out[132]:
In [133]:
df.sort_values(by='B', inplace=True)
df
Out[133]:
Missing data¶
In [134]:
a = np.array([[3, np.nan, 5, np.nan, 7],
[2, 4, np.nan, 3, 1],
[3, 4, 5, 6, 1]]).T
df = pd.DataFrame(a,
columns=['A', 'B', 'C'])
df
Out[134]:
Get the boolean mask where values are nan¶
In [135]:
df.isnull()
Out[135]:
Drop any rows that have missing data¶
In [136]:
df.dropna()
Out[136]:
In [137]:
df.dropna(how='any') # but 'any' is the default value...
Out[137]:
Drop any rows that have missing data in a given column¶
In [138]:
df.dropna(subset=['B'])
Out[138]:
In [139]:
df.dropna(subset=['B', 'C'])
Out[139]:
Drop any columns that have missing data¶
In [140]:
df.dropna(axis=1)
Out[140]:
In [141]:
df.dropna(axis=1, how='any') # but 'any' is the default value...
Out[141]:
Drop any columns that have missing data in a given row¶
In [142]:
df.dropna(axis=1, subset=[2])
Out[142]:
In [143]:
df.dropna(axis=1, subset=[1, 2])
Out[143]:
Filling missing data¶
In [144]:
df.fillna(value=999)
Out[144]:
Count the number of NaN values in a given column¶
In [145]:
df.A.isnull().sum()
Out[145]:
Miscellaneous operations on data frames¶
Transpose of a data frame¶
In [146]:
data_array = np.array([np.arange(1, 10, 1), np.arange(10, 100, 10), np.arange(100, 1000, 100)]).T
df = pd.DataFrame(data_array,
index=np.arange(1, 10, 1),
columns=['A', 'B', 'C'])
df
Out[146]:
In [147]:
df.T
Out[147]:
Merge¶
In [148]:
a1 = np.array([np.arange(1, 5, 1), np.arange(10, 50, 10), np.arange(100, 500, 100)]).T
df1 = pd.DataFrame(a1,
columns=['ID', 'B', 'C'])
a2 = np.array([np.arange(1, 5, 1), np.arange(1000, 5000, 1000), np.arange(10000, 50000, 10000)]).T
df2 = pd.DataFrame(a2,
columns=['ID', 'B', 'C'])
display(df1)
display(df2)
df = pd.merge(df1, df2, on="ID", suffixes=('_1', '_2')) #.dropna(how='any')
display(df)
Merge with NaN¶
In [149]:
a1 = np.array([np.arange(1, 5, 1), np.arange(10, 50, 10), np.arange(100, 500, 100)]).T
df1 = pd.DataFrame(a1,
columns=['ID', 'B', 'C'])
a2 = np.array([np.arange(1, 5, 1), np.arange(1000, 5000, 1000), np.arange(10000, 50000, 10000)]).T
df2 = pd.DataFrame(a2,
columns=['ID', 'B', 'C'])
df1.iloc[0,2] = np.nan
df1.iloc[1,1] = np.nan
df1.iloc[2,2] = np.nan
df1.iloc[3,1] = np.nan
df2.iloc[0,1] = np.nan
df2.iloc[1,2] = np.nan
df2.iloc[2,1] = np.nan
df2.iloc[3,2] = np.nan
df = pd.merge(df1, df2, on="ID", suffixes=('_1', '_2')) #.dropna(how='any')
display(df1)
display(df2)
display(df)
Merge with missing rows¶
In [150]:
a1 = np.array([np.arange(1, 5, 1), np.arange(10, 50, 10), np.arange(100, 500, 100)]).T
df1 = pd.DataFrame(a1,
columns=['ID', 'B', 'C'])
a2 = np.array([np.arange(1, 3, 1), np.arange(1000, 3000, 1000), np.arange(10000, 30000, 10000)]).T
df2 = pd.DataFrame(a2,
columns=['ID', 'B', 'C'])
display(df1)
display(df2)
print("Left: use only keys from left frame (SQL: left outer join)")
df = pd.merge(df1, df2, on="ID", how="left", suffixes=('_1', '_2')) #.dropna(how='any')
display(df)
print("Right: use only keys from right frame (SQL: right outer join)")
df = pd.merge(df1, df2, on="ID", how="right", suffixes=('_1', '_2')) #.dropna(how='any')
display(df)
print("Inner: use intersection of keys from both frames (SQL: inner join) [DEFAULT]")
df = pd.merge(df1, df2, on="ID", how="inner", suffixes=('_1', '_2')) #.dropna(how='any')
display(df)
print("Outer: use union of keys from both frames (SQL: full outer join)")
df = pd.merge(df1, df2, on="ID", how="outer", suffixes=('_1', '_2')) #.dropna(how='any')
display(df)
GroupBy¶
In [151]:
a = np.array([[3, 5, 5, 5, 7, 7, 7, 7],
[2, 4, 4, 3, 1, 3, 3, 2],
[3, 4, 5, 6, 1, 8, 9, 8]]).T
df = pd.DataFrame(a,
columns=['A', 'B', 'C'])
df
Out[151]:
GroupBy with single key¶
In [152]:
df.groupby(["A"]).count()
Out[152]:
In [153]:
df.groupby(["A"]).sum().B
Out[153]:
In [154]:
df.groupby(["A"]).mean().B
Out[154]:
GroupBy with multiple keys¶
In [155]:
df.groupby(["A","B"]).count()
Out[155]:
Rolling¶
Basic example¶
Rolling with an aggregation window of size 2.
In [156]:
s = pd.Series([1., 0., 5., 2., 1.])
print("DATA:")
print(s)
mean_s = s.rolling(2).mean()
print()
print("ROLLING MEAN:")
print(mean_s)
sum_s = s.rolling(2).sum()
print()
print("ROLLING SUM:")
print(sum_s)
min_s = s.rolling(2).min()
print()
print("ROLLING MIN:")
print(min_s)
max_s = s.rolling(2).max()
print()
print("ROLLING MAX:")
print(max_s)
ax = s.plot(figsize=(18, 3), color="blue")
mean_s.plot(color="red", label="mean", ax=ax)
sum_s.plot(color="green", label="sum", style="--", alpha=0.5, ax=ax)
min_s.plot(color="black", label="min", style=":", alpha=0.25, ax=ax)
max_s.plot(color="black", label="max", style=":", alpha=0.25, ax=ax)
ax.legend();
More realistic example¶
In [157]:
index = np.arange(0, 20, 0.05)
s = pd.Series(np.sin(index))
s = s + np.random.normal(scale=0.4, size=s.shape)
ax = s.plot(figsize=(18, 3))
In [158]:
s.shape
Out[158]:
Rolling with an aggregation window of size 20.
In [159]:
s_mean = s.rolling(20).mean()
s_median = s.rolling(20).median()
s_min = s.rolling(20).min()
s_max = s.rolling(20).max()
ax = s_mean.plot(y='duration', figsize=(18, 8), color="red", label="mean", alpha=0.75)
s_median.plot(ax=ax, color="blue", label="median", alpha=0.75)
s_min.plot(ax=ax, color="blue", alpha=0.5, style=":", label="min")
s_max.plot(ax=ax, color="blue", alpha=0.5, style=":", label="max")
plt.fill_between(s_min.index, s_min.values, s_max.values, facecolor='blue', alpha=0.1)
ax.legend()
ax.set_xlabel('Time');
In [160]:
s_mean.shape
Out[160]:
Pivot¶
In [161]:
df = pd.DataFrame([["i1", "A", 1],
["i1", "B", 2],
["i2", "A", 3],
["i2", "B", 4]], columns=["foo", "bar", "baz"])
df
Out[161]:
In [162]:
df.pivot(index="foo", columns="bar", values="baz")
Out[162]:
Count the number of occurrences of a column value¶
In [163]:
a = np.array([[3, 5, 5, 5, 7, 7, 7, 7],
[2, 4, 4, 3, 1, 3, 3, 2],
[3, 4, 5, 6, 1, 8, 9, 8]]).T
df = pd.DataFrame(a,
columns=['A', 'B', 'C'])
df
Out[163]:
In [164]:
df.A.value_counts()
Out[164]:
In [165]:
df.A.value_counts().plot.bar()
Out[165]:
Stats¶
In [166]:
df = pd.DataFrame(np.random.normal(size=100000))
In [167]:
df.quantile(0.50)
Out[167]:
In [168]:
df.quantile([0.25, 0.75])
Out[168]:
In [169]:
df.quantile([0.01, 0.001])
Out[169]:
Time series¶
There are 3 main time related types in Pandas (and the equivalent type for Series and DataFrame indices):
pandas.Timestamp(pandas.DatetimeIndexfor indices): pandas equivalent of python'sdatetime.datetimepandas.Period(pandas.PeriodIndexfor indices): represents a period of timepandas.Timedelta(pandas.TimedeltaIndexfor indices): represents a duration (the difference between two dates or times) i.e. the pandas equivalent of python'sdatetime.timedelta
A Timestamp is a point in time:
In [170]:
pd.Timestamp(year=2018, month=1, day=1, hour=12, minute=30)
Out[170]:
A Period is a range in time (with a "anchored" start time and a "anchored" end time):
In [171]:
p = pd.Period(freq='D', year=2018, month=1, day=1, hour=12, minute=30)
print(p)
print("Start time:", p.start_time)
print("End time:", p.end_time)
A Timedelta is a "floating" duration (i.e. not "anchored" in time):
In [172]:
print(pd.Timedelta(days=5, seconds=30))
ts1 = pd.Timestamp(year=2018, month=1, day=1, hour=12, minute=30)
ts2 = pd.Timestamp(year=2018, month=1, day=2, hour=12, minute=30)
print(ts2 - ts1)
Generate datetime index (with a fixed frequency)¶
In [173]:
pd.date_range('2018-01-01', '2018-03-01', freq='D')
Out[173]:
In [174]:
pd.date_range('2018-01-01', periods=10, freq='h')
Out[174]:
In [175]:
pd.date_range('1/1/2012', periods=10, freq='S')
Out[175]:
In [176]:
pd.date_range('3/6/2012 00:00', periods=5, freq='D')
Out[176]:
In [177]:
pd.date_range('1/1/2012', periods=5, freq='M')
Out[177]:
Generate period index¶
In [178]:
pd.period_range('2018-01-01', '2018-03-01', freq='D')
Out[178]:
In [179]:
pd.date_range('2018-01-01', '2018-03-01', freq='D').to_period()
Out[179]:
Plot time series¶
In [180]:
dti = pd.date_range('2012-01-01 00:00', periods=40, freq='D')
ts = pd.Series(np.random.randint(0, 200, len(dti)), index=dti)
In [181]:
ts.plot();
In [182]:
ts.plot(x_compat=True);
In [183]:
dti = pd.date_range('2018-01-01 00:00', '2018-01-03 00:00', freq='H')
ts = pd.Series(np.random.randint(0, 100, len(dti)), index=dti)
ax = ts.plot(x_compat=True, figsize=(16, 4)) # x_compat is required as matplotlib doesn't understand pandas datetime format -> x_compat=True makes the conversion...
# set monthly locator
ax.xaxis.set_major_locator(mdates.DayLocator(interval=1))
ax.xaxis.set_minor_locator(mdates.HourLocator(interval=1))
# set formatter
ax.xaxis.set_major_formatter(mdates.DateFormatter('%d-%m-%Y'))
# set font and rotation for date tick labels
plt.gcf().autofmt_xdate()
Indexing (select datetime)¶
In [184]:
dti = pd.date_range('2012-1-1 00:00', periods=40, freq='D')
ts = pd.Series(np.random.randint(0, 200, len(dti)), index=dti)
In [185]:
ts
Out[185]:
In [186]:
ts["2012-01-09"]
Out[186]:
In [187]:
ts[datetime.datetime(2012, 1, 9)]
Out[187]:
In [188]:
ts[ts.index < "2012-01-09"]
Out[188]:
In [189]:
ts[ts.index > "2012-01-20"]
Out[189]:
In [190]:
ts["2012-01-09":"2012-01-20"]
Out[190]:
In [191]:
ts[datetime.datetime(2012, 1, 9):datetime.datetime(2012, 1, 20)]
Out[191]:
In [192]:
ts[ts.index.day <= 3]
Out[192]:
In [193]:
ts[ts.index.month == 2]
Out[193]:
In [194]:
ts["2012-02"]
Out[194]:
In [195]:
ts[ts.index.dayofweek == 1]
Out[195]:
Rolling¶
Basic example¶
Rolling window size: 1 day
In [196]:
dti = pd.DatetimeIndex(['2018-1-1 00:00', '2018-1-1 06:45', '2018-1-1 12:00',
'2018-1-2 00:00', '2018-1-2 06:00', '2018-1-2 12:00'])
ts = pd.Series([2., 1., 3., 2., 2., 0.], index=dti)
print("DATA:")
print(ts)
ax = ts.plot(figsize=(18, 3), style="*-", color="blue")
ax.vlines(pd.DatetimeIndex(['2018-1-1 00:00', '2018-1-2 00:00']), ymin=0, ymax=8, color="red", linestyle=":", alpha=0.3);
ts_rw = ts.rolling('D').sum() # Rolling window size: 1 day
print()
print("MEAN:")
print(ts_rw)
ts_rw.plot(color="red", label="sum", style="*-", alpha=0.75, ax=ax)
ax.legend()
ax.set_xlabel('Time')
ax.grid(True);
In [197]:
ts.rolling('6h').min()
Out[197]:
In [198]:
ts.rolling('3h').mean()
Out[198]:
More realistic example¶
In [199]:
dti = pd.date_range('1/1/2018 00:00', periods=6*480, freq='10min')
ts = pd.Series(np.sin(dti.hour * 2. * np.pi / 24.), index=dti)
ts = ts + np.random.normal(scale=0.4, size=ts.shape)
ax = ts.plot(figsize=(18, 3))
ax.vlines(pd.date_range('1/1/2018 00:00', periods=480/24, freq='D'), ymin=-2, ymax=2, color="red", linestyle=":", alpha=0.3);