<jupyter_start><jupyter_text>Reading CSV files<jupyter_code>data = pd.read_csv(r"./data/students.txt")
data.head(5)
data.tail(5)<jupyter_output><empty_output><jupyter_text>how to change seperator, skip 2 lines at start and end of file?<jupyter_code>pd.read_csv?
data = pd.read_csv(r"./data/students.txt",
                   sep='|',
                   header=2,
                   skipfooter=2)<jupyter_output><empty_output><jupyter_text>How to include column(s) as my index?<jupyter_code>data
data = pd.read_csv(r"./data/students.txt",
                   sep='|',
                   header=2,
                   skipfooter=2,
                   index_col=0)
data
data = pd.read_csv(r"./data/students.txt",
                   sep='|',
                   header=2,
                   skipfooter=2,
                   index_col=['first_name','last_name'])
data
data = pd.read_csv(r"./data/students.txt",
                   sep='|',
                   header=2,
                   skipfooter=2,
                   index_col='date_of_birth')
data<jupyter_output><empty_output><jupyter_text>Reading excel files - sample data from FredSheet1. RRPONTSYDSheet2. IOER<jupyter_code>xls = pd.read_excel(io=r'./data/fred.xls',
                    sheet_name = None)
xls
xls['RRPONTSYD'].head(11)
RRPONTSYD = pd.read_excel(io=r'./data/fred.xls',
                          sheet_name = 'RRPONTSYD',
                         header=10,
                         index_col='observation_date')
IOER = pd.read_excel(io=r'./data/fred.xls',
                          sheet_name = 'IOER',
                         header=0,
                        index_col=0)
RRPONTSYD.head(5)
IOER.head(5)<jupyter_output><empty_output><jupyter_text>How to plot 2 time series with 2 axes?<jupyter_code>import matplotlib.pyplot as plt

fig,ax = plt.subplots()
ax.plot(IOER, color="red")
ax.set_xlabel("Date",fontsize=9)
ax.set_ylabel("O/N RR by Fed",color="red",fontsize=9)

ax2=ax.twinx()
ax2.plot(RRPONTSYD,color="blue")
ax2.set_ylabel("IOER",color="blue",fontsize=9)
plt.show()<jupyter_output><empty_output><jupyter_text>Selecting rows where a column satisfy some conditions<jupyter_code>A = (data.gender == 'F')
A<jupyter_output><empty_output><jupyter_text>why this works when DataFrame is column oriented?<jupyter_code>type(A)
A.values
A.index<jupyter_output><empty_output><jupyter_text>Because A is a Series, Series is like a dictionaryMap contains the exact same row indexes with a 1-1 mapping to a boolean value i.e. to include the row?<jupyter_code>data[A]
data[(data.gender == 'F') & (data.ethnicity == 'White')]<jupyter_output><empty_output><jupyter_text>How to format my index to datetime format?<jupyter_code>data.index
data.index.values
type(data.index.values)
data.index.values<jupyter_output><empty_output><jupyter_text>how to find all methods of an object?<jupyter_code>[ i for i in dir(data.index) if i[0] != '_']<jupyter_output><empty_output><jupyter_text>Being aware of object type means you know what methods that object has<jupyter_code>type(data.index.str)
A = data.index.str.contains('/')
A
data[A]
data[~A]<jupyter_output><empty_output><jupyter_text>how to drop rows from dataframe?<jupyter_code>try:
    data = data.drop(data[A].index,axis=0)
except:
    pass<jupyter_output><empty_output><jupyter_text>how about drop columns?<jupyter_code>data.drop(['status','ethnicity'], axis=1)
data.index = pd.to_datetime(data.index, format='%b-%y')
data.index
data<jupyter_output><empty_output><jupyter_text>How to sort by index?<jupyter_code>data.sort_index()
data.sort_index(ascending=False)<jupyter_output><empty_output><jupyter_text>Stacking 2 dataframes create sample dataframes<jupyter_code>t1, t2 = data[data.entry_age < 18.5], data[data.entry_age >= 18.5]
t1.describe
t2.shape<jupyter_output><empty_output><jupyter_text>**with same column names**<jupyter_code>pd.concat([t1,t2])
pd.concat([t1,t2]) is data<jupyter_output><empty_output><jupyter_text>memory address of an object<jupyter_code>id(data)
id(pd.concat([t1,t2]))<jupyter_output><empty_output><jupyter_text>**with same row index** how to create a new dataframe without reading?<jupyter_code>newColumns = pd.DataFrame({"colA":[1,2,3,4,5],
                          "colB":['test']*5},
                         index = data.index)
newColumns
data = pd.concat([newColumns, data, ], axis=1)
data<jupyter_output><empty_output><jupyter_text>Filtering columns this works because?<jupyter_code>data[['colA','colB']]
data.loc[:,['colA','colB']]
data.iloc[:,0:2]<jupyter_output><empty_output><jupyter_text>Storing a sparse table Keep primary index, change column names to a column itself<jupyter_code>sparseMat = pd.DataFrame({"colA":[np.nan,'*','*',np.nan,np.nan],
                          "colB":[np.nan,np.nan,np.nan,'*','*']},
                         index = [1,2,3,4,5])
sparseMat
sparseMatIdx = pd.melt(sparseMat)
sparseMatIdx
sparseMatIdx.dropna(axis=0)<jupyter_output><empty_output><jupyter_text>Drop rows where **all** one column value is NaN<jupyter_code>sparseMat.dropna(axis=0,how='all')<jupyter_output><empty_output><jupyter_text>Drop rows where **any** one column value is NaN<jupyter_code>sparseMat.dropna(axis=0,how='any')
sparseMat['colA'] == np.nan<jupyter_output><empty_output><jupyter_text>why don't work? cause np.nan is an instance of an object, not a static object<jupyter_code>id(np.nan)
[id(i) for i in sparseMat['colA']]
sparseMat[sparseMat['colA'].notna()]<jupyter_output><empty_output><jupyter_text>pd.Dataframe.notna**??** returns its inline code documentation<jupyter_code>print(sparseMat['colA'].notna.__doc__)<jupyter_output><empty_output><jupyter_text>Aggregationnow, lets load a larger cross-sectional dataset fromhttps://data.gov.sg/dataset/resale-flat-prices<jupyter_code>dtypes = {'month': 'str',
          'town': 'category',
          'flat_type': 'category',
          'block': 'str',
          'street_name':'category',
          'storey_range':'category',
          'floor_area':'int',
          'flat_model':'category',
          'lease_commencement_date':'int',
          'remaining_lease':'str',
          'resale_price':'float'}
resale_px = pd.read_excel(io=r'./data/resale-flat-prices-based-on-registration-date-from-jan-2017-onwards.xlsx',
                          sheet_name = 0,
                         header=0,
                         index_col=None,
                         dtype = dtypes)
resale_px.head()
resale_px.shape
resale_px.dtypes
resale_px.flat_type.values
print("categorical column [flat_type] used {:.2f}kB".format(resale_px.flat_type.memory_usage()/1024))
print("string column [remaining_lease] used {:.2f}kB".format(resale_px.remaining_lease.memory_usage()/1024))<jupyter_output><empty_output><jupyter_text>Group by 2 columns<jupyter_code>resale_px.groupby(by=['town','flat_type'])
resale_px.groupby(by=['town','flat_type'], dropna=True)['resale_price']
resale_px.groupby(by=['town','flat_type'], dropna=True)[['resale_price']]
p1 = resale_px.groupby(by=['town','flat_type'], dropna=True)[['resale_price']].median()
p1
p1.index
p1.loc['TAMPINES']
p1.loc[('TAMPINES','5 ROOM')]
rm = p1.xs('5 ROOM', level="flat_type")
rm
rm.plot.bar()<jupyter_output><empty_output><jupyter_text>PIVOT  TABLES pd.Dataframe.pivot do not support aggregation<jupyter_code>i = ['town','flat_type']
c = []
v = "resale_price"
resale_px.pivot(index=i,
                columns=c,
                values=v)<jupyter_output><empty_output><jupyter_text>pd.pivot_table(DataFrame) allow a range of aggfunc<jupyter_code># pandas.pivot_table(data, 
#                    values=None, index=None, columns=None, 
#                    aggfunc='mean',
#                    fill_value=None,
#                    margins=False,
#                    dropna=True,
#                    margins_name='All',
#                    observed=False,
#                    sort=True)

i = ['town','flat_type']
c = []
v = "resale_price"
pd.pivot_table(data=resale_px,
               values=v,
               index=i,
               columns=c,
               aggfunc=np.median)
i = ['street_name','flat_type']
c = ['storey_range']
v = "resale_price"
p2 = pd.pivot_table(data=resale_px,
               values=v,
               index=i,
               columns=c,
               aggfunc=np.median)
p2
p2.index.get_level_values('street_name')
p2.iloc[p2.index.get_level_values('street_name').str.contains("SIMEI"), 0:4]<jupyter_output><empty_output><jupyter_text>Joining tablesPython calls it "merging" Create normalised tables first for illustrationUnique index is block + street_nameDimension table will containing remaining_lease<jupyter_code>dimTbl = resale_px[['block','street_name','lease_commencement_date','remaining_lease']]
dimTbl
dimTbl.drop_duplicates?
dimTbl = dimTbl.drop_duplicates()
dimTbl.shape
stgTbl = resale_px.drop(['lease_commencement_date','remaining_lease'], axis=1)
stgTbl<jupyter_output><empty_output><jupyter_text>Verify if staging tables are really normalised... is there no duplicated rows?<jupyter_code>stgTbl.duplicated?
stgTbl.duplicated(keep=False)
resale_px[stgTbl.duplicated(keep=False)]
A = stgTbl[['block','street_name','storey_range']].duplicated(keep=False)
stgTbl[A]
stgTbl.sort_values?
stgTbl.sort_values(by=['block','street_name','storey_range'], axis=0)
stgTbl.sort_values(by=['month','block','street_name','storey_range'], axis=0, ascending=False)
pd.merge?
resale_px2 = pd.merge(left=stgTbl,
                      right=dimTbl,
                      how='inner',
                      on=['block','street_name'])
resale_px2
resale_px2.shape
resale_px<jupyter_output><empty_output><jupyter_text>AnalyticsQuestion: Have resale buyers been more receptive to older flats with delay in BTO completion dates due to Covid-19?<jupyter_code>s = resale_px.remaining_lease[0]
s
def convertor(s):
    ss = s.split(sep=' ')
    y = float(ss[0])
    m=0
    try:
        m = float(ss[2])/12
    except:
        pass
    return y + m
convertor(s)
resale_px.remaining_lease.apply(lambda s: convertor(s))
resale_px.remaining_lease = resale_px.remaining_lease.apply(lambda s: convertor(s))
resale_px
df = resale_px[resale_px['month'].apply(lambda x: any([k in x for k in ['2021','2020','2019','2018']]))]
fig = plt.figure(figsize=(24,12))
fig = df[['month','remaining_lease']].boxplot(by='month').get_figure()
plt.xticks(size=8,rotation=90)
fig.savefig('test.pdf')<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.14517218095881163, 'fraction_numerical': 0.013311469084595351, 'mean_word_length': 4.052631578947368, 'pattern_counts': {'":': 4, '<': 126, '<?xml version=': 0, '>': 126, 'https://': 1, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '31', 'dir': 'jupyter-structured-clean-dedup', 'id': '652902', 'license': 'MIT', 'n_tokens_mistral': 3872, 'n_tokens_neox': 3743, 'n_words': 571, 'path': 'Pandas in Banking Operations.ipynb', 'repo_name': 'Justinysm/learnpython', 'size': '10367'}