Python 计算HHI指数

Untitled

In [1]:

import pandas as pd

Info = pd.read_excel("BANK_Info.xlsx")
Combas = pd.read_excel("Bank_Combas.xlsx")
Comins = pd.read_excel("Bank_Comins.xlsx")

 

d:\Anaconda3\lib\site-packages\openpyxl\styles\stylesheet.py:221: UserWarning: Workbook contains no default style, apply openpyxl's default
  warn("Workbook contains no default style, apply openpyxl's default")

In [5]:

Info = Info.drop(Info.head(2).index)
Info

Out[5]:

 

	Bankcd	Shortbnm	Clsdt	Stkcd	ProvinceName	CityName	BranchNumber	PrimaryBranches	AgencyNumber	DomesticInstitutions
2	000001	国家开发银行	2007-12-31	NaN	北京市	北京市	NaN	NaN	NaN	NaN
3	000001	国家开发银行	2010-12-31	NaN	北京市	北京市	NaN	NaN	NaN	NaN
4	000001	国家开发银行	2011-12-31	NaN	北京市	北京市	NaN	NaN	NaN	NaN
5	000001	国家开发银行	2009-12-31	NaN	北京市	北京市	NaN	NaN	NaN	NaN
6	000001	国家开发银行	2008-12-31	NaN	北京市	北京市	NaN	NaN	NaN	NaN
...	...	...	...	...	...	...	...	...	...	...
4993	000505	广州黄埔惠民村镇银行	2018-12-31	NaN	广东省	广州市	NaN	NaN	NaN	NaN
4994	000506	营口农商银行	2019-12-31	NaN	辽宁省	营口市	NaN	NaN	18	18
4995	000506	营口农商银行	2018-12-31	NaN	辽宁省	营口市	NaN	NaN	NaN	NaN
4996	000506	营口农商银行	2017-12-31	NaN	辽宁省	营口市	NaN	NaN	18	18
4997	000507	四川银行	2021-12-31	NaN	四川省	成都市	10	NaN	107	NaN

4996 rows × 10 columns

In [14]:

Combas = Combas[Combas['Typrep'] == "A"]
Combas['year'] = [x[0:4] for x in Combas['Accper']] 
Combas

Out[14]:

 

	Bankcd	Shortbnm	Stkcd	Accper	Typrep	A001000000	year
2	000001	国家开发银行	NaN	2007-12-31	A	2894657000000	2007
4	000001	国家开发银行	NaN	2008-12-31	A	3821218000000	2008
8	000001	国家开发银行	NaN	2009-12-31	A	4541058000000	2009
12	000001	国家开发银行	NaN	2010-12-31	A	5112297000000	2010
16	000001	国家开发银行	NaN	2011-12-31	A	6252256000000	2011
...	...	...	...	...	...	...	...
9479	000502	临澧沪农商村镇银行	NaN	2021-12-31	A	888532542.93	2021
9481	000504	广州白云民泰村镇银行	NaN	2015-12-31	A	1201919285.39	2015
9483	000504	广州白云民泰村镇银行	NaN	2020-12-31	A	1783605062.42	2020
9485	000504	广州白云民泰村镇银行	NaN	2021-12-31	A	2161335728.72	2021
9487	000507	四川银行	NaN	2021-12-31	A	184820482683.850006	2021

3655 rows × 7 columns

In [21]:

Comins = Comins[Comins['Typrep'] == "A"]
Comins['year'] = [x[0:4] for x in Comins['Accper']]
Comins

Out[21]:

 

	Bankcd	Shortbnm	Stkcd	Accper	Typrep	B001101000	year
2	000001	国家开发银行	NaN	2007-12-31	A	63428000000	2007
4	000001	国家开发银行	NaN	2008-12-31	A	84646000000	2008
8	000001	国家开发银行	NaN	2009-12-31	A	77444000000	2009
12	000001	国家开发银行	NaN	2010-12-31	A	91440000000	2010
16	000001	国家开发银行	NaN	2011-12-31	A	117425000000	2011
...	...	...	...	...	...	...	...
9417	000502	临澧沪农商村镇银行	NaN	2021-12-31	A	19520590.49	2021
9419	000504	广州白云民泰村镇银行	NaN	2015-12-31	A	71684662.24	2015
9421	000504	广州白云民泰村镇银行	NaN	2020-12-31	A	59947001.09	2020
9424	000504	广州白云民泰村镇银行	NaN	2021-12-31	A	82182252.91	2021
9425	000507	四川银行	NaN	2021-12-31	A	3507812232.44	2021

3637 rows × 7 columns

In [88]:

data = Combas.join(Comins.set_index(['year','Bankcd']), on = ['year','Bankcd'], how='outer', lsuffix="", rsuffix="_r")
data

Out[88]:

 

	Bankcd	Shortbnm	Stkcd	Accper	Typrep	A001000000	year	Shortbnm_r	Stkcd_r	Accper_r	Typrep_r	B001101000
2	000001	国家开发银行	NaN	2007-12-31	A	2894657000000	2007	国家开发银行	NaN	2007-12-31	A	63428000000
4	000001	国家开发银行	NaN	2008-12-31	A	3821218000000	2008	国家开发银行	NaN	2008-12-31	A	84646000000
8	000001	国家开发银行	NaN	2009-12-31	A	4541058000000	2009	国家开发银行	NaN	2009-12-31	A	77444000000
12	000001	国家开发银行	NaN	2010-12-31	A	5112297000000	2010	国家开发银行	NaN	2010-12-31	A	91440000000
16	000001	国家开发银行	NaN	2011-12-31	A	6252256000000	2011	国家开发银行	NaN	2011-12-31	A	117425000000
...	...	...	...	...	...	...	...	...	...	...	...	...
9487	000178	NaN	NaN	NaN	NaN	NaN	2012	莆田农商银行	NaN	2012-12-31	A	636968100
9487	000256	NaN	NaN	NaN	NaN	NaN	2020	宁德农商银行	NaN	2020-12-31	A	382777012.09
9487	000299	NaN	NaN	NaN	NaN	NaN	2011	广东省农信联社	NaN	2011-12-31	A	1942666541.02
9487	000299	NaN	NaN	NaN	NaN	NaN	2019	广东省农信联社	NaN	2019-12-31	A	1253444878.87
9487	000472	NaN	NaN	NaN	NaN	NaN	2019	休宁农商银行	NaN	2019-12-31	A	268233877.81

3664 rows × 12 columns

In [89]:

data = data.join(Info.set_index('Bankcd'), on='Bankcd', how='outer', lsuffix="",rsuffix="_info")

In [90]:

from pandas import DataFrame
from typing import Set, Any
def remove_others(df: DataFrame, columns: Set[Any]):
    cols_total: Set[Any] = set(df.columns)
    diff: Set[Any] = cols_total - columns
    df.drop(diff, axis=1, inplace=True)
    
remove_others(data, {"Bankcd","year","CityName","A001000000","B001101000"})

In [91]:

data.sort_values(['A001000000', 'B001101000'], ascending=[True, True])
data

Out[91]:

 

	Bankcd	A001000000	year	B001101000	CityName
2.0	000001	2894657000000	2007	63428000000	北京市
2.0	000001	2894657000000	2007	63428000000	北京市
2.0	000001	2894657000000	2007	63428000000	北京市
2.0	000001	2894657000000	2007	63428000000	北京市
2.0	000001	2894657000000	2007	63428000000	北京市
...	...	...	...	...	...
NaN	000505	NaN	NaN	NaN	广州市
NaN	000505	NaN	NaN	NaN	广州市
NaN	000506	NaN	NaN	NaN	营口市
NaN	000506	NaN	NaN	NaN	营口市
NaN	000506	NaN	NaN	NaN	营口市

44093 rows × 5 columns

In [92]:

data = data.drop_duplicates()
data.sort_values(['Bankcd', 'year'], ascending=[True, True])
data

Out[92]:

 

	Bankcd	A001000000	year	B001101000	CityName
2.0	000001	2894657000000	2007	63428000000	北京市
4.0	000001	3821218000000	2008	84646000000	北京市
8.0	000001	4541058000000	2009	77444000000	北京市
12.0	000001	5112297000000	2010	91440000000	北京市
16.0	000001	6252256000000	2011	117425000000	北京市
...	...	...	...	...	...
NaN	000496	NaN	NaN	NaN	漳平市
NaN	000500	NaN	NaN	NaN	长葛市
NaN	000503	NaN	NaN	NaN	广州市
NaN	000505	NaN	NaN	NaN	广州市
NaN	000506	NaN	NaN	NaN	营口市

4155 rows × 5 columns

In [93]:

data_sumbycity = data.groupby(['CityName','year']).sum()
remove_others(data_sumbycity,{'Bankcd','year','A001000000','B001101000'})
data_sumbycity

Out[93]:

 

		Bankcd	A001000000	B001101000
CityName	year
三明市	2013	000312000317	7.574215e+09	4.505046e+08
	2014	000315000320000324	1.122321e+10	6.630297e+08
	2015	000312000316000317000320000324	2.247993e+10	1.062760e+09
	2016	000312000316000317000320000324000326000334	3.093930e+10	1.078822e+09
	2017	000312000317000320000324000326000334	2.707526e+10	1.156870e+09
...	...	...	...	...
龙岩市	2019	000411000426000427	3.044012e+10	1.317649e+09
	2020	000411	1.878094e+10	6.037049e+08
	2021	000411	2.125090e+10	5.633289e+08
龙海市	2020	000406	1.712601e+10	6.794343e+08
	2021	000406	2.016687e+10	6.620722e+08

1981 rows × 3 columns

In [94]:

data_sumbycity.reset_index(inplace=True)
data_sumbycity

Out[94]:

 

	CityName	year	Bankcd	A001000000	B001101000
0	三明市	2013	000312000317	7.574215e+09	4.505046e+08
1	三明市	2014	000315000320000324	1.122321e+10	6.630297e+08
2	三明市	2015	000312000316000317000320000324	2.247993e+10	1.062760e+09
3	三明市	2016	000312000316000317000320000324000326000334	3.093930e+10	1.078822e+09
4	三明市	2017	000312000317000320000324000326000334	2.707526e+10	1.156870e+09
...	...	...	...	...	...
1976	龙岩市	2019	000411000426000427	3.044012e+10	1.317649e+09
1977	龙岩市	2020	000411	1.878094e+10	6.037049e+08
1978	龙岩市	2021	000411	2.125090e+10	5.633289e+08
1979	龙海市	2020	000406	1.712601e+10	6.794343e+08
1980	龙海市	2021	000406	2.016687e+10	6.620722e+08

1981 rows × 5 columns

In [95]:

data = data.join(data_sumbycity.set_index(['CityName','year']), on=['CityName','year'], how='left', lsuffix="",rsuffix="_sumbycity")
data

Out[95]:

 

	Bankcd	A001000000	year	B001101000	CityName	Bankcd_sumbycity	A001000000_sumbycity	B001101000_sumbycity
2.0	000001	2894657000000	2007	63428000000	北京市	0000010000020000030000040000050000060000070000...	3.547693e+13	1.003285e+12
4.0	000001	3821218000000	2008	84646000000	北京市	0000010000020000030000040000050000060000070000...	4.150743e+13	1.275437e+12
8.0	000001	4541058000000	2009	77444000000	北京市	0000010000020000030000040000050000060000070000...	5.214055e+13	1.287555e+12
12.0	000001	5112297000000	2010	91440000000	北京市	0000010000020000030000040000050000060000070000...	6.036077e+13	1.602859e+12
16.0	000001	6252256000000	2011	117425000000	北京市	0000010000020000030000040000050000060000070000...	7.011701e+13	2.038011e+12
...	...	...	...	...	...	...	...	...
NaN	000496	NaN	NaN	NaN	漳平市	NaN	NaN	NaN
NaN	000500	NaN	NaN	NaN	长葛市	NaN	NaN	NaN
NaN	000503	NaN	NaN	NaN	广州市	NaN	NaN	NaN
NaN	000505	NaN	NaN	NaN	广州市	NaN	NaN	NaN
NaN	000506	NaN	NaN	NaN	营口市	NaN	NaN	NaN

4155 rows × 8 columns

In [106]:

# df['result'] = df.one.div(df.two)
# data.loc[~np.isnan(data['A001000000_sumbycity']), 'A001000000_sumbycity'] = 0

import numpy as np
datanona1 = data[data['A001000000_sumbycity'].notna()]
datanona1 = data[data['A001000000_sumbycity']!=0]
datanona2 = data[data['B001101000_sumbycity'].notna()]
datanona2 = data[data['B001101000_sumbycity']!=0]
datanona1['size_share'] = datanona1['A001000000']/datanona1['A001000000_sumbycity']
datanona2['sale_share'] = datanona2['B001101000']/datanona2['B001101000_sumbycity']
# data = data.dropna(axis=0,inplace=True, how='all')

 

<ipython-input-106-24cdfe4e81ca>:9: SettingWithCopyWarning: 
A value is trying to be set on a copy of a slice from a DataFrame.
Try using .loc[row_indexer,col_indexer] = value instead

See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy
  datanona1['size_share'] = datanona1['A001000000']/datanona1['A001000000_sumbycity']
<ipython-input-106-24cdfe4e81ca>:10: SettingWithCopyWarning: 
A value is trying to be set on a copy of a slice from a DataFrame.
Try using .loc[row_indexer,col_indexer] = value instead

See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy
  datanona2['sale_share'] = datanona2['B001101000']/datanona2['B001101000_sumbycity']

In [112]:

datanona = datanona1.join(datanona2.set_index(['Bankcd','year']), on = ['Bankcd','year'], lsuffix = "", rsuffix = "_nona", how = 'outer')
remove_others(datanona, {"Bankcd","year","CityName","size_share","sale_share"})
datanona = datanona.drop_duplicates()
datanona

Out[112]:

 

	Bankcd	year	CityName	size_share	sale_share
2.0	000001	2007	北京市	0.081593	0.06322
4.0	000001	2008	北京市	0.092061	0.066366
8.0	000001	2009	北京市	0.087093	0.060148
12.0	000001	2010	北京市	0.084696	0.057048
16.0	000001	2011	北京市	0.089169	0.057617
...	...	...	...	...	...
NaN	000496	2021	NaN	NaN	NaN
NaN	000500	2021	NaN	NaN	NaN
NaN	000503	2021	NaN	NaN	NaN
NaN	000505	2021	NaN	NaN	NaN
NaN	000506	2021	NaN	NaN	NaN

5061 rows × 5 columns

In [123]:

# datanona_sumbycity = data.groupby(['CityName','year']).sum()
# remove_others(data_sumbycity,{'Bankcd','year','A001000000','B001101000'})
datanona = datanona.assign(size_share2 = lambda x: (x['size_share'].pow(2)))
datanona = datanona.assign(sale_share2 = lambda x: (x['sale_share'].pow(2)))
datanona

Out[123]:

 

	Bankcd	year	CityName	size_share	sale_share	size_share2	sale_share2
2.0	000001	2007	北京市	0.081593	0.06322	0.006657	0.003997
4.0	000001	2008	北京市	0.092061	0.066366	0.008475	0.004404
8.0	000001	2009	北京市	0.087093	0.060148	0.007585	0.003618
12.0	000001	2010	北京市	0.084696	0.057048	0.007173	0.003254
16.0	000001	2011	北京市	0.089169	0.057617	0.007951	0.00332
...	...	...	...	...	...	...	...
NaN	000496	2021	NaN	NaN	NaN	NaN	NaN
NaN	000500	2021	NaN	NaN	NaN	NaN	NaN
NaN	000503	2021	NaN	NaN	NaN	NaN	NaN
NaN	000505	2021	NaN	NaN	NaN	NaN	NaN
NaN	000506	2021	NaN	NaN	NaN	NaN	NaN

5061 rows × 7 columns

In [126]:

datanona_sumbycity = datanona.groupby(['CityName','year']).sum()
remove_others(datanona_sumbycity,{'Bankcd','year','size_share2','sale_share2'})
datanona_sumbycity.reset_index(inplace = True)
datanona_sumbycity

Out[126]:

 

	CityName	year	Bankcd	size_share2	sale_share2
0	三明市	2013	000312000317	0.500036	0.501207
1	三明市	2014	000315000320000324	0.351456	0.356867
2	三明市	2015	000312000316000317000320000324	0.209161	0.209976
3	三明市	2016	000312000316000317000320000324000326000334	0.155380	0.158590
4	三明市	2017	000312000317000320000324000326000334	0.176918	0.186416
...	...	...	...	...	...
1972	龙岩市	2019	000411000426000427	0.406262	0.363928
1973	龙岩市	2020	000411	1.000000	1.000000
1974	龙岩市	2021	000411	1.000000	1.000000
1975	龙海市	2020	000406000406	2.000000	1.045466
1976	龙海市	2021	000406000406	2.000000	1.052799

1977 rows × 5 columns

In [132]:

datanona = datanona.join(datanona_sumbycity.set_index(['CityName','year']),on = ['CityName','year'],how="left",lsuffix="",rsuffix="sumby")
datanona

Out[132]:

 

	Bankcd	year	CityName	size_share	sale_share	size_share2	sale_share2	Bankcdsumby	size_share2sumby	sale_share2sumby
2.0	000001	2007	北京市	0.081593	0.06322	0.006657	0.003997	0000010000020000030000040000050000060000070000...	0.162151	0.175584
4.0	000001	2008	北京市	0.092061	0.066366	0.008475	0.004404	0000010000020000030000040000050000060000070000...	0.157091	0.170013
8.0	000001	2009	北京市	0.087093	0.060148	0.007585	0.003618	0000010000020000030000040000050000060000070000...	0.154007	0.172281
12.0	000001	2010	北京市	0.084696	0.057048	0.007173	0.003254	0000010000020000030000040000050000060000070000...	0.152562	0.167121
16.0	000001	2011	北京市	0.089169	0.057617	0.007951	0.00332	0000010000020000030000040000050000060000070000...	0.148335	0.160992
...	...	...	...	...	...	...	...	...	...	...
NaN	000496	2021	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN
NaN	000500	2021	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN
NaN	000503	2021	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN
NaN	000505	2021	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN
NaN	000506	2021	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN

5061 rows × 10 columns

In [137]:

datanona.sort_values('year',ascending=True)
datanona

Out[137]:

 

	Bankcd	year	CityName	size_share	sale_share	size_share2	sale_share2	Bankcdsumby	size_share2sumby	sale_share2sumby
2874.0	000075	2007	苏州市	0.323322	0.33266	0.104537	0.110663	000075000093000171000171	0.498349	1.336809
1140.0	000023	2007	济南市	1.0	0.729809	1.0	0.532621	000023000023	2.000000	1.532621
4227.0	000119	2007	郑州市	1.0	1.0	1.0	1.0	000119	1.000000	1.000000
1789.0	000039	2007	北京市	0.005149	0.005304	0.000027	0.000028	0000010000020000030000040000050000060000070000...	0.162151	0.175584
4755.0	000134	2007	台州市	1.0	1.0	1.0	1.0	000134	1.000000	1.000000
...	...	...	...	...	...	...	...	...	...	...
NaN	000258	NaN	龙岩市	NaN	NaN	NaN	NaN	NaN	NaN	NaN
NaN	000410	NaN	龙岩市	NaN	NaN	NaN	NaN	NaN	NaN	NaN
NaN	000204	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN
NaN	000230	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN
NaN	000239	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN

5061 rows × 10 columns

In [139]:

datanona.loc[(datanona['size_share2sumby'] > 1), 'size_share2sumby'] = 1
datanona.loc[(datanona['sale_share2sumby'] > 1), 'sale_share2sumby'] = 1
datanona

Out[139]:

 

	Bankcd	year	CityName	size_share	sale_share	size_share2	sale_share2	Bankcdsumby	size_share2sumby	sale_share2sumby
2874.0	000075	2007	苏州市	0.323322	0.33266	0.104537	0.110663	000075000093000171000171	0.498349	1.000000
1140.0	000023	2007	济南市	1.0	0.729809	1.0	0.532621	000023000023	1.000000	1.000000
4227.0	000119	2007	郑州市	1.0	1.0	1.0	1.0	000119	1.000000	1.000000
1789.0	000039	2007	北京市	0.005149	0.005304	0.000027	0.000028	0000010000020000030000040000050000060000070000...	0.162151	0.175584
4755.0	000134	2007	台州市	1.0	1.0	1.0	1.0	000134	1.000000	1.000000
...	...	...	...	...	...	...	...	...	...	...
NaN	000258	NaN	龙岩市	NaN	NaN	NaN	NaN	NaN	NaN	NaN
NaN	000410	NaN	龙岩市	NaN	NaN	NaN	NaN	NaN	NaN	NaN
NaN	000204	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN
NaN	000230	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN
NaN	000239	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN

5061 rows × 10 columns

In [140]:

datanona.to_excel("datanona.xlsx")

In [ ]: