Pandas_AutoReport_财务自动化统计报表

调用的module/package：

OS
Pandas_Series
Pandas_DataFrame

文件读入

加密的.xlsx转为.csv

copy .xlsx 的有效内容到 .txt
另存为 .csv文件

read_csv

read_csv(os.path.join,…)

 read_csv(os.path.join(path, file) + '.csv', ...)

read_csv(…, sep=’\t’)

data1 = pd.read_csv(..., sep='\t')

read_csv(…, delimiter=’\t’)

read_csv(..., delimiter='\t')

read_csv(…, parse_dates=[‘填表时间’])

read_csv(..., parse_dates=['填表时间'])

提取数据

基础

.loc / .iloc

print(type(df.loc[10, ['成单金额']]))
print(type(df.loc[10, '成单金额']))

<class 'pandas.core.series.Series'>
<class 'str'>

df = pd.DataFrame([[1, 2], [4, 5], [7, 8]],
                  index=['cobra', 'viper', 'sidewinder'],
                  columns=['max_speed', 'shield'])
 >>> df                     
            max_speed  shield
cobra               1       2
viper               4       5
sidewinder          7       8

cobra         1
viper         4
sidewinder    7
Name: max_speed, dtype: int64
------------------------------
df['max_speed']
<class 'pandas.core.series.Series'>
--------------------------------------------------
            max_speed
cobra               1
viper               4
sidewinder          7
------------------------------
df[['max_speed']]
<class 'pandas.core.frame.DataFrame'>
--------------------------------------------------
            max_speed  shield
cobra               1       2
viper               4       5
sidewinder          7       8
------------------------------
df[['max_speed', 'shield']]
<class 'pandas.core.frame.DataFrame'>
--------------------------------------------------
cobra         1
viper         4
sidewinder    7
Name: max_speed, dtype: int64
------------------------------
df.loc[:, 'max_speed']
<class 'pandas.core.series.Series'>
--------------------------------------------------
            max_speed  shield
cobra               1       2
viper               4       5
sidewinder          7       8
------------------------------
df.loc[:, 'max_speed':'shield']
<class 'pandas.core.frame.DataFrame'>
--------------------------------------------------
            max_speed
cobra               1
viper               4
sidewinder          7
------------------------------
df.loc[:, ['max_speed']]
<class 'pandas.core.frame.DataFrame'>
--------------------------------------------------

datetime

1，string变成datetime格式
dates = pd.to_datetime(pd.Series([‘20010101’, ‘20010331’]), format = ‘%Y%m%d’)
2，datetime变回string格式
dates.apply(lambda x: x.strftime(‘%Y-%m-%d’))

print(list(df['填表时间']))
print(list(df.loc[0, '填表时间']))
print(list(df.loc[0, '填表时间'].apply(lambda x: x.strftime("%Y%m%d"))))

[Timestamp('2019-11-05 00:00:00'), Timestamp('2019-11-05 00:00:00'), Timestamp('2019-11-05 00:00:00'), Timestamp('2019-11-05 00:00:00'), Timestamp('2019-11-05 00:00:00'), Timestamp('2019-11-05 00:00:00'), Timestamp('2019-10-30 00:00:00'), Timestamp('2019-10-30 00:00:00'), Timestamp('2019-10-30 00:00:00'), Timestamp('2019-10-30 00:00:00'), Timestamp('2019-10-30 00:00:00'), Timestamp('2019-10-30 00:00:00'), Timestamp('2019-10-30 00:00:00'), Timestamp('2019-10-30 00:00:00'), Timestamp('2019-10-30 00:00:00'), Timestamp('2019-10-30 00:00:00'), Timestamp('2019-10-30 00:00:00'), Timestamp('2019-10-30 00:00:00'), Timestamp('2019-10-30 00:00:00')]
[Timestamp('2019-11-05 00:00:00'), Timestamp('2019-10-30 00:00:00')]
['20191105', '20191030']

运算各项指标

基础

#算无重复的值的数量
data['商机编号'].unique() 
--> return *numpy.ndarray* of unique '商机编号'
len(data['商机编号'].unique())
--> return unique 商机编号个数

numpy.ndarray.append

#提取公司销售人员名单
print(data1['公司销售'].unique())
print(data2['公司销售'].unique())
salesperson = np.append(data1['公司销售'].unique(), data2['公司销售'].unique())
print(salesperson)

['A' 'B' 'C']
['a' 'b' 'c']
['A' 'B' 'C' 'a' 'b' 'c']

ppl = df[df['填表时间'] == pd.to_datetime(i)]['公司销售']
print(type(ppl))
print(type(ppl.unique()))

<class 'pandas.core.series.Series'>
<class 'numpy.ndarray'>

groupby

对比理解transform/agg的运用

data = data1.groupby('公司销售')['商机编号'].count()
print(data) -> *Series*
print('-'*20)
data = data1.groupby('公司销售')['商机编号'].transform(lambda x: x.count())
print(data) -> *Series*
print('-'*20)
data = data1.groupby('公司销售')['商机编号'].agg('count')
print(data) -> *Series*
print('-'*20)
data = data1.groupby('公司销售')['商机编号'].agg(['count'])
print(data) -> *DataFrame*

公司销售
00002319.小王     1
19001119.小强     3
19011148.小明     2
Name: 商机编号, dtype: int64
--------------------
0    3
1    3
2    3
3    2
4    1
5    2
Name: 商机编号, dtype: int32
公司销售
00002319.小王     1
19001119.小强     3
19011148.小明     2
Name: 商机编号, dtype: int64
--------------------
              count
公司销售               
00000019.邓涛       1
19000019.方舟       3
19000148.林宣臣      2

groupby以后series转为dataframe，groupby的key自动成为df的index

data = data1.groupby('公司销售')['商机编号'].count()
print(data)
print('-'*20)
data = pd.DataFrame(data)
print(data)

公司销售
00002319.小王     1
19001119.小强     3
19011148.小明     2
Name: 商机编号, dtype: int64
--------------------
              商机编号
公司销售              
00002319.小王     1
19001119.小强     3
19011148.小明     2

合并两个dataframe：append/join/update

append

data = pd.DataFrame(data1.groupby('公司销售')['商机编号'].count()).append(pd.DataFrame(data2.groupby('公司销售')['商机编号'].count()))

update

# update会按index更新，原df没有的columns会自动添加
tmp = df[df['商机状态'] == '赢单'].groupby('公司销售')['成单金额'].agg(['sum']).rename(columns={'sum': '本月实际收入'})
data.update(tmp)

join

 tmp = df[df['商机状态'].isin(['进行中', '未开始'])].groupby('公司销售')['商机编号'].agg(['count']).rename(
     columns={'count': '本月进行中商机数量'})
 data = data.join(tmp, how='outer')