mirror of
https://gitlab.com/CIEFWorldwideSdnBhd/cief-dashboard.git
synced 2026-08-25 15:34:20 +00:00
shangqian22
98 lines
5.0 KiB
Python
98 lines
5.0 KiB
Python
import pandas as pd
|
|
|
|
# May
|
|
df=pd.read_excel('5.1 May Report .xlsx',sheet_name=0)
|
|
df=df.loc[:,['Date ', 'Name ', 'Status', 'Traffic Source', 'Remarks', 'No. of employees', 'Selling Platform']]
|
|
df.columns=['Date','Name', 'Status','Traffic source','Remark','Number of employee', 'Selling platform']
|
|
df.dropna(subset=['Name'],inplace=True)
|
|
df2=df.copy()
|
|
df.loc[:,'Date']=pd.to_datetime(df['Date'],format='%Y-%d-%m %H:%M:%S',errors='coerce')
|
|
df.loc[:,'Date'].fillna(method='bfill',inplace=True)
|
|
df.dropna(subset=['Date'],inplace=True)
|
|
df2.loc[:,'Date']=pd.to_datetime(df2['Date'],format='%d/%m/%Y',errors='coerce')
|
|
df2.dropna(subset=['Date'],inplace=True)
|
|
df=pd.concat([df,df2])
|
|
|
|
df1=pd.read_excel('5.1 May Report .xlsx',sheet_name='May_Report')
|
|
df1.loc[:,'Date']=pd.to_datetime(df1['Date'],errors='coerce')
|
|
df1.loc[:,'Date'].fillna(method='ffill',inplace=True)
|
|
may_df=pd.merge(df,df1,how='left',on='Date')
|
|
|
|
# June
|
|
df=pd.read_excel('6.1 June Report .xlsx',sheet_name=0)
|
|
df=df.loc[:,['Date ', 'Name ', 'Email address', 'Status', 'Traffic Source', 'Remarks', 'No. of employees', 'Selling Platform']]
|
|
df.columns=['Date','Name', 'Email', 'Status','Traffic source','Remark','Number of employee', 'Selling platform']
|
|
df.dropna(subset=['Name'],inplace=True)
|
|
df2=df.copy()
|
|
df.loc[:,'Date']=pd.to_datetime(df['Date'],format='%Y-%d-%m %H:%M:%S',errors='coerce')
|
|
df.loc[:,'Date'].fillna(method='bfill',inplace=True)
|
|
df.dropna(subset=['Date'],inplace=True)
|
|
df2.loc[:,'Date']=pd.to_datetime(df2['Date'],format='%d/%m/%Y',errors='coerce')
|
|
df2.dropna(subset=['Date'],inplace=True)
|
|
df=pd.concat([df,df2])
|
|
|
|
df1=pd.read_excel('6.1 June Report .xlsx',sheet_name='June_Report')
|
|
df1.loc[:,'Date']=pd.to_datetime(df1['Date'],errors='coerce')
|
|
df1.loc[:,'Date'].fillna(method='ffill',inplace=True)
|
|
june_df=pd.merge(df,df1,how='left',on='Date')
|
|
|
|
# July
|
|
df=pd.read_excel('7.1 July Report .xlsx',sheet_name=0)
|
|
df=df.loc[:,['Date ', 'Name ', 'Email address', 'Status', 'Traffic Source', 'Remarks', 'No. of employees', 'Selling Platform','WeChat ID', 'Facebook Name']]
|
|
df.columns=['Date','Name', 'Email', 'Status','Traffic source','Remark','Number of employee', 'Selling platform', 'Wechat ID', 'Facebook ID']
|
|
df.dropna(subset=['Name'],inplace=True)
|
|
df2=df.copy()
|
|
df.loc[:,'Date']=pd.to_datetime(df['Date'],format='%Y-%d-%m %H:%M:%S',errors='coerce')
|
|
df.loc[:,'Date'].fillna(method='bfill',inplace=True)
|
|
df.dropna(subset=['Date'],inplace=True)
|
|
df2.loc[:,'Date']=pd.to_datetime(df2['Date'],format='%d/%m/%Y',errors='coerce')
|
|
df2.dropna(subset=['Date'],inplace=True)
|
|
df=pd.concat([df,df2])
|
|
|
|
df1=pd.read_excel('7.1 July Report .xlsx',sheet_name='July_Report')
|
|
df1.loc[:,'Date']=pd.to_datetime(df1['Date'],errors='coerce')
|
|
df1.loc[:,'Date'].fillna(method='ffill',inplace=True)
|
|
july_df=pd.merge(df,df1,how='left',on='Date')
|
|
|
|
# Augest
|
|
df=pd.read_excel('8.1 August Report .xlsx',sheet_name=0)
|
|
df=df.loc[:,['Date ', 'Name ', 'Email address', 'Status', 'Traffic Source', 'Remarks', 'No. of employees', 'Selling Platform','WeChat ID', 'Facebook Name']]
|
|
df.columns=['Date','Name', 'Email', 'Status','Traffic source','Remark','Number of employee', 'Selling platform', 'Wechat ID', 'Facebook ID']
|
|
df.dropna(subset=['Name'],inplace=True)
|
|
df.loc[:,'Date']=pd.to_datetime(df['Date'],errors='coerce')
|
|
|
|
df1=pd.read_excel('8.1 August Report .xlsx',sheet_name='August_Report')
|
|
df1.loc[:,'Date']=pd.to_datetime(df1['Date'],errors='coerce')
|
|
df1.loc[:,'Date'].fillna(method='ffill',inplace=True)
|
|
aug_df=pd.merge(df,df1,how='left',on='Date')
|
|
|
|
# September
|
|
df=pd.read_excel('9.1 September Report .xlsx',sheet_name=0)
|
|
df=df.loc[:,['Date ', 'Name ', 'Email address', 'Status', 'Traffic Source', 'Remarks', 'No. of employees', 'Selling Platform','WeChat ID', 'Facebook Name']]
|
|
df.columns=['Date','Name', 'Email', 'Status','Traffic source','Remark','Number of employee', 'Selling platform', 'Wechat ID', 'Facebook ID']
|
|
df.dropna(subset=['Name'],inplace=True)
|
|
df.loc[6:24,'Date']=pd.to_datetime(df.loc[6:24,'Date'].astype(str),format='%Y-%d-%m')
|
|
|
|
df1=pd.read_excel('9.1 September Report .xlsx',sheet_name='Sept Report')
|
|
df1.loc[:,'Date']=pd.to_datetime(df1['Date'],errors='coerce')
|
|
df1.loc[:,'Date'].fillna(method='ffill',inplace=True)
|
|
sep_df=pd.merge(df,df1,how='left',on='Date')
|
|
|
|
# October
|
|
df=pd.read_excel('10.1 October Report.xlsx',sheet_name=0)
|
|
df=df.loc[:,['Date ', 'Name ', 'Email address', 'Status', 'Traffic Source', 'Remarks', 'No. of employees', 'Selling Platform','WeChat ID', 'Facebook Name']]
|
|
df.columns=['Date','Name', 'Email', 'Status','Traffic source','Remark','Number of employee', 'Selling platform', 'Wechat ID', 'Facebook ID']
|
|
df.dropna(subset=['Name'],inplace=True)
|
|
df.loc[:,'Date']=pd.to_datetime(df['Date'],errors='coerce')
|
|
df.loc[:,'Date'].fillna(method='ffill',inplace=True)
|
|
|
|
df1=pd.read_excel('10.1 October Report.xlsx',sheet_name='Oct_Report')
|
|
df1.loc[:,'Date']=pd.to_datetime(df1['Date'],errors='coerce')
|
|
df1.loc[:,'Date'].fillna(method='ffill',inplace=True)
|
|
oct_df=pd.merge(df,df1,how='left',on='Date')
|
|
|
|
# November
|
|
df=pd.concat([may_df,june_df,july_df,aug_df,sep_df,oct_df])
|
|
df.drop_duplicates(inplace=True)
|
|
df.to_csv('../marketing1.csv',index=False)
|