Pandas

import numpy as np
import pandas as pd
import matplotlib.pyplot as plt
%matplotlib inline

# create a series
s = pd.Series([1,3,5,np.nan,6,8])
s

# create a data frame
dates = pd.date_range('20130101',periods=6)
df = pd.DataFrame(np.random.randn(6,4),index=dates,columns=list('ABCD'))
df

# another way to create a data frame
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' : 'foo' })
df2

df2.dtypes

df.head()

df.index

df.columns

df.values

# quick data summary
df.describe()

df.T

# axis 0 is index, axis 1 is columns
df.sort_index(axis=1, ascending=False)

# can sort by values too
df.sort_values(by=['B'])

# select a column (yields a series)
df['A']

# column names also attached to the object
df.A

# slicing works
df[0:3]

df['20130102':'20130104']

# cross-section using a label
df.loc[dates[0]]

# getting a scalar value
df.loc[dates[0], 'A']

# select via position
df.iloc[3]

df.iloc[3:5,0:2]

# column slicing
df.iloc[:,1:3]

# get a value by index
df.iloc[1,1]

# boolean indexing
df[df.A > 0]

df[df > 0]

# filtering
df3 = df.copy()
df3['E'] = ['one', 'one', 'two', 'three', 'four', 'three']
df3[df3['E'].isin(['two', 'four'])]

# setting examples
df.at[dates[0],'A'] = 0
df.iat[0,1] = 0
df.loc[:, 'D'] = np.array([5] * len(df))
df

# dealing with missing data
df4 = df.reindex(index=dates[0:4],columns=list(df.columns) + ['E'])
df4.loc[dates[0]:dates[1],'E'] = 1
df4

# drop rows with missing data
df4.dropna(how='any')

# fill missing data
df4.fillna(value=5)

# boolean mask for nan values
pd.isnull(df4)

df.mean()

# pivot the mean calculation
df.mean(1)

# aligning objects with different dimensions
s = pd.Series([1,3,5,np.nan,6,8],index=dates).shift(2)
df.sub(s,axis='index')

# applying functions
df.apply(np.cumsum)

df.apply(lambda x: x.max() - x.min())

# simple count aggregation
s = pd.Series(np.random.randint(0,7,size=10))
s.value_counts()

# concatenation
df = pd.DataFrame(np.random.randn(10, 4))
pieces = [df[:3], df[3:7], df[7:]]
pd.concat(pieces)

# SQL-style join
left = pd.DataFrame({'key': ['foo', 'foo'], 'lval': [1, 2]})
right = pd.DataFrame({'key': ['foo', 'foo'], 'rval': [4, 5]})
pd.merge(left, right, on='key')

# append
df = pd.DataFrame(np.random.randn(8, 4), columns=['A', 'B', 'C', 'D'])
s = df.iloc[3]
df.append(s, ignore_index=True)

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

# group by
df.groupby('A').sum()

# group by multiple columns
df.groupby(['A','B']).sum()

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

# pivot table
pd.pivot_table(df, values='D', index=['A', 'B'], columns=['C'])

# time period resampling
rng = pd.date_range('1/1/2012', periods=100, freq='S')
ts = pd.Series(np.random.randint(0, 500, len(rng)), index=rng)
ts.resample('5Min').sum()

rng = pd.date_range('1/1/2012', periods=5, freq='M')
ts = pd.Series(np.random.randn(len(rng)), index=rng)
ts

ps = ts.to_period()
ps.to_timestamp()

# time series plot
ts = pd.Series(np.random.randn(1000), index=pd.date_range('1/1/2000', periods=1000))
ts = ts.cumsum()
ts.plot()

# plot with a data frame
df = pd.DataFrame(np.random.randn(1000, 4), index=ts.index, columns=['A', 'B', 'C', 'D'])
df = df.cumsum()
plt.figure(); df.plot(); plt.legend(loc='best')

# write to a csv file
df.to_csv('foo.csv', index=False)

# read file back in
path = r'foo.csv'
newDf = pd.read_csv(path)
newDf.head()

# remove the file
import os
os.remove(path)

# can also do Excel
df.to_excel('foo.xlsx', sheet_name='Sheet1')

newDf2 = pd.read_excel('foo.xlsx', 'Sheet1', index_col=None, na_values=['NA'])
newDf2.head()

os.remove('foo.xlsx')