pandas对excel的操作实战句法

供大家随时查阅

import pandas as pd
import numpy as np
import re
#pd.options.display.max_columns = None
pd.set_option('display.max_columns', None)
pd.set_option('display.max_rows', None)
import os
path = 'C:\\Users\\Admin\\Documents\\vscode\\长江\\2000补充'
excel_name = list(os.listdir(path))
print(excel_name)

with pd.ExcelWriter("2000补充城市统计年鉴长三角.xls") as writer:
    for i_name_index,i_name in enumerate(excel_name):
        print(i_name)
        path = r'C:\\Users\\Admin\\Documents\\vscode\\长江\\2000补充'
        excelFile = os.path.join(path,i_name)
        data = pd.read_excel(excelFile)
        name = pd.read_csv('长三角城市.csv')
        sheet1 = pd.DataFrame([],columns=data.columns)
        sheet1 = sheet1.append(data.iloc[0:10,:])
        for j_inedx , j in enumerate(name.iloc[:,0]):
            for i_index,i in enumerate(data.iloc[:,0]):
                i = str(i)
                i_copy = ''.join(i.split())
                #print(i_copy)
                if i == j: #+ '市':
                    sheet1 = sheet1.append(data.iloc[i_index,:])
                    break
             #   elif j + '市'== i_copy:
                elif j == i_copy:
                    sheet1 = sheet1.append(data.iloc[i_index,:])
                    break
                else:
                    pass
        sheet1.to_excel(writer, sheet_name='{}'.format(i_name_index))
writer.save()
writer.close()

import pandas as pd
import numpy as np
#pd.options.display.max_columns = None
pd.set_option('display.max_columns', None)
pd.set_option('display.max_rows', None)

with pd.ExcelWriter("2006城市统计年鉴长三角.xls") as writer:
    for i_m in [0,1]:
        data = pd.read_excel('中国城市统计年鉴2006.xls',sheet_name = i_m,skiprows= 3,index_col=None)
        name = pd.read_csv('长三角城市.csv')
        sheet1 = pd.DataFrame([],columns = data.columns)
        sheet1 = sheet1.append(data.iloc[0:4,:])
        sheet1
        for j_inedx , j in enumerate(name.iloc[:,0]):
            for i_index,i in enumerate(data.iloc[:,0]):
                if i == j + '市':
                    sheet1 = sheet1.append(data.iloc[i_index,:])
        sheet1.to_excel(writer, sheet_name='{}'.format(i_m))

    for i_m in range(2,43):
        data = pd.read_excel('中国城市统计年鉴2006.xls',sheet_name = i_m,skiprows= 3,index_col=None)
        name = pd.read_csv('长三角城市.csv')
        sheet1 = pd.DataFrame([],columns = data.columns)
        sheet1 = sheet1.append(data.iloc[0:4,:])
        sheet1
        for j_inedx , j in enumerate(name.iloc[:,0]):
            for i_index,i in enumerate(data.iloc[:,1]):
                if i == j + '市':
                    sheet1 = sheet1.append(data.iloc[i_index,:])
        sheet1
        sheet1.to_excel(writer, sheet_name='{}'.format(i_m))
writer.save()
writer.close()

import pandas as pd
import numpy as np
import re
#pd.options.display.max_columns = None
pd.set_option('display.max_columns', None)
pd.set_option('display.max_rows', None)
import os
path = 'C:\\Users\\Admin\\Documents\\vscode\\长江\\中国城市统计年鉴2008（xls)\\中国城市统计年鉴2008（xls)'
excel_name = list(os.listdir(path))
print(excel_name)

with pd.ExcelWriter("2008城市统计年鉴长三角.xls") as writer:
    for i_name_index,i_name in enumerate(excel_name):
        print(i_name)
        data = pd.read_excel(path + '\\' + i_name,encoding='GBK')
        name = pd.read_csv('长三角城市.csv')
        sheet1 = pd.DataFrame([],columns=data.columns)
        sheet1 = sheet1.append(data.iloc[0:8,:])
        for j_inedx , j in enumerate(name.iloc[:,0]):
            for i_index,i in enumerate(data.iloc[:,0]):
                i = str(i)
                i_copy = ''.join(i.split())
                print(i_copy)
                if i == j + '市':
                    sheet1 = sheet1.append(data.iloc[i_index,:])
                    break
                elif j + '市'== i_copy:
                    sheet1 = sheet1.append(data.iloc[i_index,:])
                    break
                else:
                    pass
        sheet1.to_excel(writer, sheet_name='{}'.format(i_name_index))
writer.save()
writer.close()

# 读取分表
import pandas as pd
import numpy as np
import re
#pd.options.display.max_columns = None
pd.set_option('display.max_columns', None)
pd.set_option('display.max_rows', None)
excel = pd.ExcelFile("C:\\Users\\Admin\\Documents\\vscode\\长江\\中国城市统计年鉴2014\\二、地级及以上城市统计资料\\2-29固定资产投资情况.xls")
length = len(excel.sheet_names)