【python】データサイエンス100本ノック（構造化データ加工編）1~50【丁寧にやってみる】ちょっとずつ書き足します

df_receipt[['sales_ymd', 'customer_id', 'product_cd', 'amount']].head(10)

df_receipt[['sales_ymd','customer_id','product_cd','amount']].head(10)

df_receipt[['sales_ymd', 'customer_id', 'product_cd', 'amount']].rename(columns={'sales_ymd': 'sales_date'}).head(10)

df_receipt[['sales_ymd', 'customer_id', 'product_cd', 'amount']].query('customer_id == "CS018205000001"')

df_receipt[['sales_ymd', 'customer_id', 'product_cd', 'amount']]\
            .query('customer_id == "CS018205000001" & amount >= 1000')

df_receipt[['sales_ymd', 'customer_id', 'product_cd', 'quantity', 'amount']].query('customer_id == "CS018205000001" & (amount >= 1000 | quantity >=5)')

df_receipt[['sales_ymd', 'customer_id', 'product_cd', 'amount']] \
    .query('customer_id == "CS018205000001" & 1000 <= amount <= 2000')

df_receipt[['sales_ymd', 'customer_id', 'product_cd', 'amount']].query('customer_id == "CS018205000001" & product_cd != "P071401019"')

df_store.query('prefecture_cd != "13" & floor_area <= 900')

df_store.query("store_cd.str.startswith('S14')", engine='python').head(10)

df_customer.query("customer_id.str.endswith('1')", engine='python').head(10)

df_store.query("address.str.contains('横浜市')", engine='python').head(10)

df_customer.query("status_cd.str.contains('^[A-F]', regex=True)", engine='python').head(10)

df_customer.query("status_cd.str.contains('[1-9]$', regex=True)", engine='python').head(10)

df_customer.query("status_cd.str.startswith('^[A-F]', regex=True)", engine='python').head(10)

df_store.query("tel_no.str.contains('^[0-9]{3}-[0-9]{3}-[0-9]{4}$', regex=True)", engine='python')

df_customer.sort_values('birth_day', ascending=True).head(10)

df_customer.sort_values('birth_day', ascending=False).head(10)

df_tmp = pd.concat([df_receipt[['customer_id', 'amount']] 
                     ,df_receipt['amount'].rank(method='min', ascending=False)], axis=1)
df_tmp.columns = ['customer_id', 'amount', 'ranking']
df_tmp.sort_values('ranking', ascending=True).head(10)

df_tmp = pd.concat([df_receipt[['customer_id', 'amount']] 
                     ,df_receipt['amount'].rank(method='first', ascending=False)], axis=1)
df_tmp.columns = ['customer_id', 'amount', 'ranking']
df_tmp.sort_values('ranking', ascending=True).head(10)

len(df_receipt)

len(df_receipt['customer_id'].unique())

df_receipt.groupby('store_cd').agg({'amount':'sum', 'quantity':'sum'}).reset_index()

df_receipt.groupby('customer_id').sales_ymd.max().reset_index().head(10)

df_receipt.groupby('customer_id').sales_ymd.min().reset_index().head(10)

# これでも良い
# df_receipt.groupby('customer_id').agg({'sales_ymd':'min'}).head(10)

df_tmp = df_receipt.groupby('customer_id').agg({'sales_ymd':['max','min']}).reset_index()
df_tmp.columns = ["_".join(pair) for pair in df_tmp.columns]
df_tmp.query('sales_ymd_max != sales_ymd_min').head(10)

df_receipt.groupby('store_cd').agg({'amount':'median'}).reset_index().sort_values('amount', ascending=False).head(5)

df_receipt.groupby('store_cd').agg({'amount':'median'}).reset_index().sort_values('amount', ascending=False).head(5)

df_receipt.groupby('store_cd').product_cd.apply(lambda x: x.mode()).reset_index()

df_receipt.groupby('store_cd').amount.var(ddof=0).reset_index().sort_values('amount', ascending=False).head(5)

df_receipt.groupby('store_cd').amount.std(ddof=0).reset_index().sort_values('amount', ascending=False).head(5)

np.percentile(df_receipt['amount'], q=[25, 50, 75,100])

df_receipt.amount.quantile(q=np.arange(5)/4)

df_receipt.groupby('store_cd').amount.mean().reset_index().query('amount >= 330')

df_receipt[~df_receipt['customer_id'].str.startswith("Z")].groupby('customer_id').amount.sum().mean()

df_receipt.query('not customer_id.str.startswith("Z")', engine='python').groupby('customer_id').amount.sum().mean()

amount_mean = df_receipt[~df_receipt['customer_id'].str.startswith("Z")].groupby('customer_id').amount.sum().mean()
df_amount_sum = df_receipt.groupby('customer_id').amount.sum().reset_index()
df_amount_sum[df_amount_sum['amount'] >= amount_mean].head(10)

pd.merge(df_receipt, df_store[['store_cd','store_name']], how='inner', on='store_cd').head(10)

pd.merge(df_product
         , df_category[['category_small_cd', 'category_small_name']]
         , how='inner', on='category_small_cd').head(10)

df_amount_sum = df_receipt.groupby('customer_id').amount.sum().reset_index()
df_tmp = df_customer.query('gender_cd == "1" and not customer_id.str.startswith("Z")', engine='python')
pd.merge(df_tmp['customer_id'], df_amount_sum, how='left', on='customer_id').fillna(0).head(10)

df_sum = df_receipt.groupby('customer_id').amount.sum().reset_index()
df_sum = df_sum.query('not customer_id.str.startswith("Z")', engine='python')
df_sum = df_sum.sort_values('amount', ascending=False).head(20)

df_cnt = df_receipt[~df_receipt.duplicated(subset=['customer_id', 'sales_ymd'])]
df_cnt = df_cnt.query('not customer_id.str.startswith("Z")', engine='python')
df_cnt = df_cnt.groupby('customer_id').sales_ymd.count().reset_index()
df_cnt = df_cnt.sort_values('sales_ymd', ascending=False).head(20)

pd.merge(df_sum, df_cnt, how='outer', on='customer_id')

df_store_tmp = df_store.copy()
df_product_tmp = df_product.copy()

df_store_tmp['key'] = 0
df_product_tmp['key'] = 0
len(pd.merge(df_store_tmp, df_product_tmp, how='outer', on='key'))

df_sales_amount_by_date = df_receipt[['sales_ymd', 'amount']].groupby('sales_ymd').sum().reset_index()
df_sales_amount_by_date = pd.concat([df_sales_amount_by_date, df_sales_amount_by_date.shift()], axis=1)
df_sales_amount_by_date.columns = ['sales_ymd','amount','lag_ymd','lag_amount']
df_sales_amount_by_date['diff_amount'] = df_sales_amount_by_date['amount'] - df_sales_amount_by_date['lag_amount']
df_sales_amount_by_date.head(10)

df_sales_amount_by_date = df_receipt[['sales_ymd', 'amount']].groupby('sales_ymd').sum().reset_index()
for i in range(1, 4):
    if i == 1:
        df_lag = pd.concat([df_sales_amount_by_date, df_sales_amount_by_date.shift(i)],axis=1)
    else:
        df_lag = df_lag.append(pd.concat([df_sales_amount_by_date, df_sales_amount_by_date.shift(i)],axis=1))
df_lag.columns = ['sales_ymd', 'amount', 'lag_ymd', 'lag_amount']
df_lag.dropna().sort_values(['sales_ymd','lag_ymd']).head(10)

df_tmp = pd.merge(df_receipt, df_customer, how ='inner', on="customer_id")
df_tmp['era'] = df_tmp['age'].apply(lambda x: math.floor(x / 10) * 10)
df_sales_summary = pd.pivot_table(df_tmp, index='era', columns='gender_cd', values='amount', aggfunc='sum').reset_index()
df_sales_summary.columns = ['era', 'male', 'female', 'unknown']
df_sales_summary

df_sales_summary = df_sales_summary.set_index('era'). \
        stack().reset_index().replace({'female':'01',
                                        'male':'00',
                                        'unknown':'99'}).rename(columns={'level_1':'gender_cd', 0: 'amount'})
df_sales_summary

pd.concat([df_customer['customer_id'],
pd.to_datetime(df_customer['birth_day']).dt.strftime('%Y%m%d')],
axis = 1).head(10)

pd.concat([df_customer['customer_id'],pd.to_datetime(df_customer['application_date'])], axis=1).head(10)

pd.concat([df_receipt[['receipt_no', 'receipt_sub_no']],
           pd.to_datetime(df_receipt['sales_ymd'].astype('str'))],axis=1).head(10)

pd.concat([df_receipt[['receipt_no', 'receipt_sub_no']],
           pd.to_datetime(df_receipt['sales_epoch'], unit='s')],axis=1).head(10)

pd.concat([df_receipt[['receipt_no', 'receipt_sub_no']],
           pd.to_datetime(df_receipt['sales_epoch'], unit='s').dt.year],axis=1).head(10)

# dt.monthでも月を取得できるが、ここでは0埋め２桁で取り出すためstrftimeを利用している
pd.concat([df_receipt[['receipt_no', 'receipt_sub_no']],
           pd.to_datetime(df_receipt['sales_epoch'], unit='s').dt.strftime('%m')],axis=1).head(10)