Lesson 04 - 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))
#recall index objects: http://pandas.pydata.org/pandas-docs/stable/generated/pandas.Index.html

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

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

#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

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)
#print(tipsgroups.groups)

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)})
#see also .apply()

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])
#print(df1)
#print(df2)

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

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

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

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
"""