表格打码助于提取

#!/usr/bin/env python
# coding: utf-8

# In[1]:


import pandas as pd 
import numpy as np
import os
pd.set_option("display.max_rows",None)
pd.set_option("display.max_columns",None)


# In[2]:


city_code_data = pd.read_excel('./长三角城市名/长三角城市名.xlsx',sheet_name='三省全部编码')
city_code_data


# In[3]:


anhui_data = pd.read_excel('../数据处理/安徽数据全汇总已经校正归类城市名.xlsx',sheet_name='2009年')
anhui_data


# In[4]:


#对比
hang,lie = anhui_data.shape


# In[ ]:


for file in ['安徽','江苏','浙江']:
    if file == '安徽':
        r = [i for i in range(0,106)]
    elif file =='江苏':
        r = [i for i in range(105,227)]
    elif file =='浙江':
        r = [i for i in range(226,316)]
    else:
        pass
    writer = pd.ExcelWriter(r'{0}数据全汇总已经校正归类城市名.xlsx'.format(file))
    for p in range(2009,2022):
        anhui_data = pd.read_excel('../数据处理/{0}数据全汇总已经校正归类城市名.xlsx'.format(file),sheet_name='{0}年'.format(p))
        hang,lie = anhui_data.shape
        for i in range(lie):
            print(i,p,file)
            for j in range(hang):
                unit = str(anhui_data.iloc[j,i])
                if unit == 'nan':
                    pass
                else:
                    for k in r:
                        unit_test_1 = str(city_code_data.iloc[k,5])
                        unit_test_2 = str(city_code_data.iloc[k,2])
                        unit_test_3 = str(city_code_data.iloc[k,2]) + '市'
                        unit_test_4 = str(city_code_data.iloc[k,2]) + '市辖区'
                        if unit == unit_test_1:
                            anhui_data.iloc[j,i-1] = city_code_data.iloc[k,4]
                            anhui_data.iloc[j,i-2] = city_code_data.iloc[k,1]
                        elif unit == unit_test_2:
                            anhui_data.iloc[j,i-1] = city_code_data.iloc[k,4]
                            anhui_data.iloc[j,i-2] = city_code_data.iloc[k,1]
                        elif unit == unit_test_3:
                            anhui_data.iloc[j,i-1] = city_code_data.iloc[k,4]
                            anhui_data.iloc[j,i-2] = city_code_data.iloc[k,1]
                        elif unit == unit_test_4:
                            anhui_data.iloc[j,i-1] = city_code_data.iloc[k,4]
                            anhui_data.iloc[j,i-2] = city_code_data.iloc[k,1]
                        else:
                            pass
        anhui_data.to_excel(writer,sheet_name='{0}'.format(p))
    writer.save()