1.数据的格式如下图:
2.脚本如下:
#!/usr/bin/env python
# -*- coding: UTF-8 -*-
import pandas as pd
import numpy as np
from pandas import DataFrame
import sys
df = pd.read_csv("/路径/list", sep="\t")
df1 = df.drop_duplicates(subset=['proj_id', 'customer', 'customer'])
dict = {'proj_id':df1['proj_id']}
df2 = DataFrame(dict)
#df2.insert(0,'proj_id',df1['proj_id'])
df2.insert(1,'customer',df1['customer'])
df2.insert(2,'customer',df1['customer'])
d1 = {}
d2 = {}
list1 = list(df['proj_id'])
list2 = list(df['analysis'])
for i in range(len(list2)):
if list2[i] == 'done':
if list1[i] in d1:
d1[list1[i]]+=1
else:
d1[list1[i]]=1
else:
if list1[i] in d2:
d2[list1[i]]+=1
else:
d2[list1[i]]=1
df3 = pd.DataFrame.from_dict(d1, orient='index', columns = ['done'])
df4 = df3.reset_index().rename(columns = {'index':'proj_id'})
df5 = pd.merge(df2,df4,how='left')
df6 = pd.DataFrame.from_dict(d2, orient='index', columns = ['todo'])
df7 = df6.reset_index().rename(columns = {'index':'proj_id'})
df8 = pd.merge(df5,df7,how='left')
df8.to_excel(sys.argv[1], index=False)
#print(df8)