In [1]:
import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
import seaborn as sns
%matplotlib inline
In [2]:
df = pd.read_csv('inraw_tycobib13_16.csv')
print(df.shape)
df.head()
Out[2]:
In [3]:
# generate id for join
df['tycobib'] = df.bibNumber.astype('str') + df.collcode + df.itemtype
In [4]:
biba = pd.read_csv('tycobib_unique.csv')
print(biba.shape)
biba.head()
Out[4]:
In [5]:
biba = biba.drop(['Unnamed: 0'], axis=1)
biba.head()
Out[5]:
In [6]:
biba.loc[biba.tycobib=='2511808ncbocdjccd']
Out[6]:
In [7]:
merger = pd.merge(df, biba[['ItemLocation', 'tycobib', 'PublicationYear']], on='tycobib', how='left')
print(merger.shape)
In [8]:
merger.head()
Out[8]:
In [9]:
dur = merger[['bibNumber','barcode','cout', 'cin', 'PublicationYear', 'deweyClass', 'ItemLocation', 'tycobib']]
dur.dtypes
Out[9]:
In [10]:
# check missing values in PublicationYear
dur.loc[dur.PublicationYear.isnull()].shape
Out[10]:
In [11]:
dur['cout'] = pd.to_datetime(dur.cout)
dur['cin'] = pd.to_datetime(dur.cin)
In [12]:
dur['duration'] = (dur.cin - dur.cout).astype('timedelta64[D]')
print(dur.shape)
dur.head()
Out[12]:
In [13]:
dur5 = dur.loc[dur.duration<=2000,]
print(dur5.shape)
In [14]:
dur5.duration.max()
Out[14]:
In [15]:
fig, ax = plt.subplots(figsize=(16,6))
sns.distplot(dur5.duration, ax = ax)
Out[15]:
In [16]:
dur5['month_out'] = dur5.cout.dt.month
dur5['month_in'] = dur5.cin.dt.month
dur5['hh_out'] = dur5.cout.dt.hour
dur5['hh_in'] = dur5.cin.dt.hour
dur5['dow_out'] = dur5.cout.dt.dayofweek
dur5['dow_in'] = dur5.cin.dt.dayofweek
dur5.head()
Out[16]:
In [17]:
dur5['dew'] = dur5.deweyClass.fillna(0).astype('int')
dur5.head()
Out[17]:
In [18]:
dur5.cout.min()
#dur5[dur5.cout.dt.year!=2012]
Out[18]:
In [19]:
dur5['dd_from_cout'] = (dur5.cout - dur5.cout.min()).astype('timedelta64[D]')
dur5['dd_from_cin'] = (dur5.cin - dur5.cout.min()).astype('timedelta64[D]')
dur5.head(10)
Out[19]:
In [20]:
# count null dewey
dur5.loc[dur5.deweyClass.isnull(),].shape
Out[20]:
In [21]:
print(dur5.dd_from_cin.max())
print(dur5.duration.max())
print(np.log(dur5.duration.max()))
In [22]:
print(dur5.dew.nunique())
print(dur5.deweyClass.nunique())
print(dur5.dd_from_cin.max())
In [23]:
# sort by location and create id_key
#dur5 = dur5.sort_values(by=['ItemLocation'])
# generate id variable
#dur5.insert(0, 'branch_order', range(0, len(dur5)))
#
#dur5.head(10)
In [24]:
# sort by location and create id_key
#dur5 = dur5.sort_values(by=['bibNumber'])
# generate id variable
#dur5.insert(0, 'bib_order', range(0, len(dur5)))
#
#dur5.head(10)
In [25]:
#dur5 = dur5.sort_values(by = ['cout'])
# generate id variable
#dur5.insert(0, 'date_order', range(0, len(dur5)))
#dur5.head()
In [26]:
# check if some of the fields are unique
dur5['ranker'] = dur5.barcode.rank(method='dense')
print(dur5.ranker.max())
In [27]:
dur5.loc[dur5.barcode==10061216916,]
Out[27]:
In [28]:
# check if ranker and barcode have identical number of unqiue
print(dur5.ranker.nunique())
print(dur5.barcode.nunique())
print(dur5.ranker.max())
In [29]:
#
dur5.ItemLocation.value_counts()
Out[29]:
In [30]:
dur5.sort_values(by='ItemLocation', inplace=True)
dur5.tail(10)
Out[30]:
In [31]:
# generate numeric category for coloring later
dur5['col'] = dur5.ItemLocation.astype('category')
dur5['col_code'] = dur5.col.cat.codes.astype('int64')
dur5.drop('col', axis=1, inplace=True)
print(dur5.col_code.max())
dur5.head()
Out[31]:
In [32]:
# get borders for branches
dur5.loc[dur5.ItemLocation=='cen']
Out[32]:
In [33]:
dur5['ordered_idx'] = dur5.groupby(['ItemLocation', 'barcode']).ngroup()
print(dur5.ordered_idx.nunique())
print(dur5.ordered_idx.max())
In [43]:
dur5.groupby(['ItemLocation', 'col_code']).size().sort_values(ascending=False)
Out[43]:
In [35]:
dur5.to_csv('prep_ink.csv')
In [36]:
dur5.iloc[:5000,].to_csv('prep_ink2.csv')