from sqlalchemy import and_ from sqlalchemy.orm import sessionmaker import dataset from pandas import DataFrame, Series import argparse, textwrap import sys import datetime import dateutil
def get_frame_from_query(the_query): “”“make a dataframe from an sqlalchemy query”“” colnames=[col[‘name’] for col in the_query.column_descriptions] df=DataFrame.from_records(list(the_query),columns=colnames) return df
file_db=’/tera/phil/diskinventory/files_tera.db’ dbstring=’sqlite:///{:s}’.format(file_db) print(dbstring) db = dataset.connect(dbstring) session=sessionmaker(bind=db.engine) thesession=session() file_table=db.metadata.tables[‘files’] direc_table=db.metadata.tables[‘direcs’] print(db.metadata.tables)
size=20.e6 large_query=thesession.query(file_table).filter(file_table.c.size > size)
df_files=get_frame_from_query(large_query) records=df_files.to_dict(‘records’)
import csv import pandas as pd filename=’e340_clickers.csv’ thefile=open(filename,’r’) reader=csv.reader(thefile,delimiter=’,’) colnames=reader.__next__() the_lines=[] for line in reader: the_lines.append(dict(zip(colnames,line)))
df_clickers=pd.DataFrame.from_records(the_lines)
from openpyxl import load_workbook import numpy as np import pandas as pd #<name>;<uid>;<comment>;<email>;<password>;<group,group,…>;<disallowusermod> #<name>;;<comment>;<email>;<password>;users,<group>;;
from random import randint
def random_with_N_digits(n): range_start = 10**(n-1) range_end = (10**n)-1 return randint(range_start, range_end)
the_file=’CMOS_database_export_31March.xlsx’
wb=load_workbook(the_file,data_only=True,use_iterators=True) names=wb.get_sheet_names() print(names) speakers=wb[‘All Submissions’] sessions=wb[‘Sessions and rooms’] speak_iter=speakers.iter_rows() next(speak_iter) next(speak_iter) headers=[c.value for c in next(speak_iter) if c.value] df_speak_str=np.zeros(1,dtype=[(‘first’,’unicode_’),(‘last’,’unicode_’),(‘email’,’unicode_’),(‘id’,’int_’),(‘preference’,’unicode_’), (‘reviewer’,’<U10’),(‘title’,’<U10’),(‘time’,’<U10’),(‘section’,’unicode_’), (‘session’,’<U10’),(‘status’,’<U10’),(‘publish’,’unicode_’)]) df_speak=pd.DataFrame(df_speak_str) colnames=list(df_speak.columns) for count,row in enumerate(speak_iter): out=[item.value for item in row] df_speak.loc[count]=out
colnames=[‘Theme’,’Chair’,’Sessions’,’Number’,’Date’,’Time’,’Room’,’Chair’]
session_iter=sessions.iter_rows() the_list=[] session_dict=dict(I=’01’,II=’02’,III=’03’) for items in session_iter: values=[the_item.value for the_item in items[1:-1]] out=dict(zip(colnames,values)) try: number=int(out[‘Number’]) except (TypeError,ValueError): try: front,back=out[‘Number’].split(’ ‘) back=session_dict[back] number=int(front + back) except (AttributeError,ValueError): continue out[‘Number’]=number the_list.append(out) if out[‘Sessions’]: keep_session=out[‘Sessions’] else: out[‘Sessions’]=keep_session df_sessions=pd.DataFrame.from_records(the_list) print(df_sessions.to_string()) ## print(dict(zip(colnames,out))) ## if count > 10: ## break
def ascii_filt(my_string): return ”.join([i for i in my_string if ord(i) < 128])
rows=df_speak.to_dict(‘records’) keep_groups=[] with open(‘accounts.txt’,’w’) as f: for row in rows: try: row[‘comment’]=’{first:s} {last:s}’.format(**row) row[‘comment’]=ascii_filt(row[‘comment’]) row[‘id’]=int(row[‘id’]) row[‘name’]=’{last:s}{id:d}’.format(**row) row[‘name’]=row[‘name’].lower() transtab={ord(“’”):None,ord(’ ’ ):None,ord(‘-‘):None} row[‘name’]=ascii_filt(row[‘name’]) row[‘name’] = row[‘name’].translate(transtab) row[‘group’]=str(row[‘session’]) row[‘password’]=’{:04d}’.format(random_with_N_digits(4)) line=’{name:s};;{comment:s};{email:s};{password:s};users,{group:s};;\n’.format(**row) keep_groups.append(row[‘group’]) f.write(line) except: pass
with open(‘groups.txt’,’w’) as f: groups=set(keep_groups) for a_group in groups: f.write(‘{}\n’.format(a_group))
assigns=list(assign_score_dict.keys())[:-1] df_gradebook[assigns].values
df_gradebook=pd.DataFrame.from_records(the_list) df_gradebook[‘studentid’]=[str(c) for c in df_gradebook[‘studentid’]]
with pd.HDFStore(‘tera.h5’,’w’) as store: store.put(‘tera_files’,saveit,format=’table’)
http://stackoverflow.com/questions/13703720/converting-between-datetime-timestamp-and-datetime64
the_time=list(pd.to_datetime([make_time(item) for item in times])) — timestamp objects
out.to_datetime() – back to python datetime
datetime.datetime(2012, 12, 4, 19, 51, 25, 362455) >>> dt64 = np.datetime64(dt) >>> ts = (dt64 - np.datetime64(‘1970-01-01T00:00:00Z’)) / np.timedelta64(1, ‘s’) >>> ts 1354650685.3624549 >>> datetime.utcfromtimestamp(ts) datetime.datetime(2012, 12, 4, 19, 51, 25, 362455) >>> np.__version__
n [11]: ts = pd.Timestamp(‘2014-01-23 00:00:00’, tz=None)
In [12]: ts.to_pydatetime() Out[12]: datetime.datetime(2014, 1, 23, 0, 0)
out[‘day’]=pd.Categorical(out[‘day’],ordered=True) out[‘interval’]=pd.Categorical(out[‘interval’],ordered=True)
merged[‘username’]=merged.apply(make_username,axis=1)
merged=pd.merge(df_papers,df_sessions,how=’left’,left_on=’sessions’,right_on=’session_id’)
http://stackoverflow.com/questions/16384332/how-to-speed-up-pandas-row-filtering-by-string-matching
In [11]: df = df.sort(‘STK_ID’) # skip this if you’re sure it’s sorted
In [12]: df[‘STK_ID’].searchsorted(‘A0003’, ‘left’) Out[12]: 6000
In [13]: df[‘STK_ID’].searchsorted(‘A0003’, ‘right’) Out[13]: 8000
In [14]: timeit df[6000:8000] 10000 loops, best of 3: 134 µs per loop
http://stackoverflow.com/questions/22845959/return-dataframe-item-using-partial-string-match-on-rows-pandas-python df[df[‘RSD_TYPE’].str.contains(“AQ5”)][‘FILTER LIST’]
with pd.HDFStore(‘paper_table.h5’,’w’) as store: store.put(‘cases’,df_cases,format=’table’) store.get_storer(‘cases’).attrs.history = ‘written 2015/8/5’
import pandas as pd with pd.HDFStore(‘paper_table.h5’) as store: print(store.keys()) print(store.get(‘cases’)) out=store.get_storer(‘cases’).attrs print(dir(out)) print(out.__dict__.keys()) print(type(out)) print(out.__dict__[‘history’])
or use h5py
with h5py.File(name,’a’) as f: for key,value in attr_dict.items(): print(‘writing key: ‘,key) f.attrs[key]=value
df_overview.to_dict(‘records’)
rows_list = [] for row in input_rows:
dict1 = {}
dict1.update(blah..)
rows_list.append(dict1)
df = pd.DataFrame(rows_list)
df.to_dict(‘records’)
#http://stackoverflow.com/questions/11346283/renaming-columns-in-pandas
df.rename(columns = {‘$b’:’B’}, inplace = True)