stack.py

import pandas as pd
import datetime as DT

values_a = range(16)
values_b = range(10, 26)
states = ['Georgia']*8 + ['Alabama']*8
cities = ['Atlanta']*4 + ['Savanna']*4 + ['Mobile']*4 + ['Montgomery']*4
dates = pd.DatetimeIndex([DT.date(2012,1,1)+DT.timedelta(days = i) for i in range(4)]*4)
df = pd.DataFrame(
    {'value_a': values_a, 'value_b': values_b},
    index = [states, cities, dates])
df.index.names = ['State', 'City', 'Date']
df.reset_index(level=[0, 1], inplace=True)
df.groupby(['State','City'])
print(df.groupby(['State','City']).resample('2D', how='sum'))


# resample method should understand timestamp
stack.transaction_time = pd.to_datetime(stack.transaction_time)
stack11=stack.set_index(stack.transaction_time)

stack11.resample('D')


# audit data


import pandas as pd
import numpy as np
col_list = ['transaction_id', 'request_id', 'table_name', 'table_unique_field', 'table_unique_value', 'field_name', 'old_value', 'new_value', 'client_id', 'client_type', 'transaction_date']
audit = pd.read_csv('head1.txt', sep="|" , names = col_list, index_col='transaction_date' )
audit.transaction_date = pd.to_datetime(audit.index)
audit=audit.set_index(audit.transaction_date)
audit.index.names=['transaction_date']

pd.pivot_table(audit, values='transaction_id', rows=['table_name'], cols=['table_unique_field'], aggfunc=len).fillna(0)

pd.pivot_table(audit[audit['request_id'] == 2], values='transaction_id', rows=['table_name'], cols=['table_unique_field'], aggfunc=len)

audit['n'] = 1

audit['n'].resample('D', how=sum)
transaction_date
2010-12-01            9520
2010-12-02          451487
2010-12-03          216061
2010-12-04          222830
2010-12-05          100102
Freq: D, Name: n, dtype: int64

audit.dtypes

audit.groupby('request_id').size()
audit.request_id.value_counts()

audit[audit['request_id'] == 2]

audit.transaction_id.cumsum()

audit.head().values


audit.table_name.unique()

audit.table_name.value_counts()

mydf[np.isfinite(mydf['user_cd'])]

audit.columns
audit.index
audit.axes




audit.columns
audit.index
audit.axes
audit.info()
audit.shape
audit.count()

audit.groupby('table_name').sum()
audit.table_name.unique()

combine
combine_first

a’s values prioritized, use values from b to fill holes:

>>> a.combine_first(b)

audit.table_name.value_counts()
audit.groupby('table_name')['transaction_id'].count()
pd.value_counts(audit.table_name)





>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>


df = pandas.io.sql.read_sql_table('issues', e)
df.columns
df.issue.count()
df.customer.nunique()

x = df.groupby('customer').issue.count()
x.sort(ascending=False)
x[:5]

df.groupby('customer').issue.count().sort(inplace=False, ascending=False)[:5]

df[df.status == 'Support'].issue.count()
df[df.status == 'Support'].severity.value_counts()
df[df.status == 'Support'].issue

df.set_index('created', drop=False, inplace=True)
pi = df.index.to_period('M')
df['2013'].issue.count()
df['2014Q1'].issue.count()

df.groupby(pi).issue.count()[-5:]
df.groupby(pi.asfreq('Q')).issue.count()[-5:]

df.groupby(pi).issue.count().plot(legend=True, label="Inflow")
pandas.rolling_mean(df.groupby(pi).issue.count(), 6).plot(legend=True, label="Average")
pandas.rolling_mean(df.groupby(df.index.to_period('W')).issue.count(),6).plot()
df.groupby(pi.asfreq('Q')).customer.nunique().plot()
df.groupby(pi).product.value_counts().unstack().plot()
df.groupby(pi.asfreq('Q')).issue.count()[:-1].plot(kind='bar')

https://mariadb.com/blog/reporting-pandas-and-seals-and-pythons-oh-my


import sqlalchemy
import pandas
pandas.set_option('display.mpl_style', 'default')
e = sqlalchemy.create_engine('mysql+pymysql://root:MyNewPass@localhost/test')
 
df = pandas.io.sql.read_sql_table('dep_list', e)