kw_tools/SEO/month_kw_rank.py

import pandas as pd
import time

def day_col(row):
    result = row['dt'][0:10]
    return result


custom_name = ['毛怪','火柴人','清原','仁本']
for name in custom_name:
    df = pd.read_csv(f"/Users/zooeytsai/Documents/{name}5月關鍵字排名進前十名.csv")
    df['dt2'] = df.apply(day_col, axis=1)
    df = df.drop_duplicates(subset=['dt2','kw'])
    df_kw_rank = df[['dt2','kw','ranking']].sort_values(by='dt2')
    df_kw_rank_2 = df_kw_rank.reset_index(drop=True)
    df_kw_rank_2.columns = ['日期','關鍵字','名次']
    print(df_kw_rank_2)
    # df_=pd.DataFrame(columns=list('  '))
    # print(pd.concat([df_kw_rank,df_]))
    data = []
    num = df.groupby('dt2',as_index=False).size()
    for index,row in num.iterrows():
        data.append([row['dt2'],row['size'],20,row['size']*20])
    df_first = pd.DataFrame(data,columns=['日期','關鍵字出現次數','首頁日費','首頁小計'])
    #前三名
    df_top_3 = df.loc[df['ranking']<=3]
    num_top_3 = df_top_3.groupby('dt2',as_index=False).size()
    data_2 = []
    for index,row in num_top_3.iterrows():
        data_2.append([row['dt2'],row['size'],40,row['size']*40])
    df_second = pd.DataFrame(data_2,columns=['日期','前3名字組數量','前3名字組日費','前3名字組小計'])
    df_result = pd.merge(df_first,df_second,on='日期',how='outer').fillna(0)
    new = pd.concat([df_kw_rank_2,df_result],axis=1)
    # df_result.insert(0,'日期 ',df_kw_rank['日期'])
    # df_result.insert(1,'關鍵字 ',df_kw_rank['關鍵字'])
    # df_result.insert(2,'名次 ',df_kw_rank['名次'])
    new.to_csv(f"/Users/zooeytsai/Documents/{name}5月績效報表2.csv",index=False)
    time.sleep(60)