Lesson 05 - pandas part 2

from pandas import DataFrame, Series
import pandas as pd
import io
import numpy as np

#using a string as example
#we could refer to file names if we had the file saved
data = '''
date,A,B,C
20090101,a,1,2
20090102,b,3,4
20090103,c,4,5
'''
print(data)

#by default, the index is arange(nrows)
pd.read_csv(io.StringIO(data))

#we can specify the index:
pd.read_csv(io.StringIO(data), index_col=0)
#also index_col='date'

#we can also use nested indices:
pd.read_csv(io.StringIO(data), index_col=['date','A'])

#we can specify data type (it will speed things up, or avoid conversion)
pd.read_csv(io.StringIO(data), index_col=['date'], dtype={'A' : str, 'B':np.int32, 'C':np.float64})

#We can throw out names and use our own
pd.read_csv(io.StringIO(data), index_col=[0],
            dtype={'A' : str, 'B':np.int32, 'C':np.float64},
           names=["foo", 'bar', "baz"],
           header = 0)

#filter out some unneeded columns:
pd.read_csv(io.StringIO(data),
           names=['date', 'foo', 'bar', "baz"],
           header = 0,
           usecols = ['foo', 'baz'])

#dates! more to come
dat = pd.read_csv(io.StringIO(data),
           parse_dates = True,
            index_col = [0]
           )
dat.index

dat = pd.read_csv("http://jeremy.kiwi.nz/pythoncourse/assets/tests/r&d/test1data.csv")[1:20]
dat

# get the column names
dat.columns
#get the first five rows
dat.head()
#pick out specific columns
DataFrame(dat,columns=['TripType','VisitNumber'])
#same as
dat[['TripType','VisitNumber']]
#get one specific column
dat.TripType
#get one specific column
dat['TripType']
#if we want rows, we need ix, loc or iloc
#loc works on labels in the index:
dat.loc[1]
#iloc works on integer index:
dat.iloc[1]
#ix tries to work like loc, but falls back to iloc if not found:
dat.ix[1]
#add a new column
dat['foo']="spam"
#using other columns:
dat['foo'] = dat['VisitNumber'] + dat['ScanCount']
#add a new column with specific values
dat['foo']=Series(['spam', 'more spam'],index=[4,10])

#delete a column
del dat['foo']
#'http://pandas.pydata.org/pandas-docs/dev/generated/pandas.DataFrame.html'

#recall, indexes are immutable?
#how to reindex?
dat = dat.reindex(np.arange(5))
dat

dat.reindex(np.arange(7),fill_value=0)
dat.reindex(np.arange(10),method='ffill')

dat.drop(1)
#dat.drop('foo', axis = 1)

#getting data
dat[['TripType','Upc']]
dat['ScanCount']>1
dat[dat['ScanCount']>1]

tips = pd.read_csv('https://raw.github.com/pydata/pandas/master/pandas/tests/data/tips.csv')
tips.head()

tips[['total_bill', 'tip', 'smoker', 'time']].head(5)

tips[(tips['time'] == 'Dinner') & (tips['tip'] > 5.00)].head(5)

tipsgroups = tips.groupby('sex')
tipsgroups

for i,j in tipsgroups.groups.items():
    print(i)

def myfun(index):
    if len(index) >= 5:
        return 1
    else:
        return 0

group2 = tips.groupby(myfun, axis = 1)
group2.groups

from matplotlib import pyplot as plt
%matplotlib inline
tipsgroups.mean()
#tipsgroups.boxplot();

for name, group in tipsgroups:
    print(name)
    print(group.head(5))

tipsgroups.aggregate(np.mean)
#selecting columns:
tipsgroups['tip'].aggregate(np.mean)

#.agg is short for agg
tipsgroups.agg([np.mean, np.sum, np.std])

#we can also use a dict, to do different things to different rows:
tipsgroups.agg({'tip': [np.mean, np.sum], 'size':lambda x: max(x)})

df1 = 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])
df2 = 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])

#joins on index
pd.concat([df1, df2])

#joins on index!
pd.concat([df1, df2], axis = 1)

#we can ignore the index!
df1.append(df2, ignore_index=True)

df1 = DataFrame({'key': ['A', 'B', 'C', 'D'],
                 'value': np.random.randn(4)})
df2 = pd.DataFrame({'key': ['B', 'D', 'D', 'E'],
                    'value': np.random.randn(4)})
#SQL:
#SELECT *
#FROM df1
#INNER JOIN df2
#  ON df1.key = df2.key;
pd.merge(df1, df2, on='key')
#SQL:
#SELECT *
#FROM df1
#LEFT OUTER JOIN df2
#  ON df1.key = df2.key;
pd.merge(df1, df2, on='key', how='left')
#SQL:
#SELECT *
#FROM df1
#RIGHT OUTER JOIN df2
#  ON df1.key = df2.key;
pd.merge(df1, df2, on='key', how='right')
#SQL:
#SELECT *
#FROM df1
#FULL OUTER JOIN df2
#  ON df1.key = df2.key;
pd.merge(df1, df2, on='key', how='outer')

data = """\
10/08/2012,12:10:10,name1,0.81,4.02,50;18.5701400N,4;07.7693770E,7.92,10.50,0.0106,4.30,0.0301
10/08/2012,12:10:11,name2,-999,-999,-999,-999,10.87,1.40,0.0099,9.70,0.0686
"""