#2023년 테이블
order_2023 = main_order.query('order_year == 2023')
order_2023['new_or_exist'] = np.where(order_2023['created_year']==2023,'new','exist')
order_2023 = order_2023.groupby(['order_year','new_or_exist']).agg(customer_count=('customer_id','nunique'),total_revenue=('total_price','sum'),order_count=('order_id','count')).reset_index()
#2022년 테이블
order_2022 = main_order.query('order_year == 2022')
order_2022['new_or_exist']=np.where(order_2022['created_year']==2022,'new','exist')
order_2022 = order_2022.groupby(['order_year','new_or_exist']).agg(customer_count=('customer_id','nunique'),total_revenue=('total_price','sum'),order_count=('order_id','count')).reset_index()
#2021년 테이블
order_2021 = main_order.query('order_year == 2021')
order_2021['new_or_exist']=np.where(order_2021['created_year']==2021,'new','exist')
order_2021 = order_2021.groupby(['order_year','new_or_exist']).agg(customer_count=('customer_id','nunique'),total_revenue=('total_price','sum'),order_count=('order_id','count')).reset_index()
#2020년 테이블
order_2020 = main_order.query('order_year == 2020')
order_2020['new_or_exist']=np.where(order_2020['created_year']==2020,'new','exist')
order_2020 = order_2020.groupby(['order_year','new_or_exist']).agg(customer_count=('customer_id','nunique'),total_revenue=('total_price','sum'),order_count=('order_id','count')).reset_index()
#2019년 테이블
order_2019 = main_order.query('order_year == 2019')
order_2019['new_or_exist']=np.where(order_2019['created_year']==2019,'new','exist')
order_2019 = order_2019.groupby(['order_year','new_or_exist']).agg(customer_count=('customer_id','nunique'),total_revenue=('total_price','sum'),order_count=('order_id','count')).reset_index()
#테이블 병합
from_2019_to_2023 = pd.concat([order_2019,order_2020,order_2021,order_2022,order_2023])
from_2019_to_2023['고객당 평균 매출'] = (from_2019_to_2023['total_revenue']/from_2019_to_2023['customer_count']).round(1)
from_2019_to_2023['고객당 평균 주문 수'] = (from_2019_to_2023['order_count']/from_2019_to_2023['customer_count']).round(1)
from_2019_to_2023 = from_2019_to_2023.rename(columns={'new_or_exist':'신규 or 기존','customer_count':'고객 수','total_revenue':'총 구매금액','order_count':'총 주문 수'})
from_2019_to_2023
