import pandas as pd
from operator import index
import numpy as np
from datetime import datetime
 
df= pd.read_excel('C:/Users/user/Desktop/2022년 매출.매입 현황_TEST.xlsx',
                  sheet_name='22-01',usecols='AE,D,C,U,Y',header=6)
df=df.iloc[0:67]
df=df.rename(columns={'인력':'성명','Unnamed: 20':'금액(Free)', 'Unnamed: 24':'금액(사업자)','Unnamed: 30':'지급일'})
 
df['용역비지급']=' '
df=df[['지급일','용역비지급','소속','성명','금액(Free)','금액(사업자)']]
 
df=df.sort_values(by='소속')
df=df.iloc[0:31]
df['금액(Free)']=df['금액(Free)'].apply(lambda x : "{:,}".format(x))
df['금액(사업자)']=df['금액(사업자)'].apply(lambda x : "{:,}".format(x))
df['금액(Free)']= np.where(df['금액(Free)']=='0',df['금액(사업자)'],df['금액(Free)'])
 
df=df.drop(['금액(사업자)'],axis=1)
df=df.rename(columns={'금액(Free)':'금액'})
 
df=df.replace(15,'22-02-15')
df=df.replace(10,'22-02-10')
df=df.replace(30,'22-02-28')
df=df.replace('말일','22-02-28')
df=df.replace(20,'22-02-18')
df=df.replace(31,'22-02-28')
 
df=df.set_index("지급일")
 
df=df.sort_values(by=['지급일','소속','성명'])
 
df.to_excel('C:/Users/user/Desktop/출금.xlsx')