pandas应用
1、读取数据及修改列名
常用的命令是pd.read_csv以及pd.read_excel，两个功能，附带多个属性，自己常用的是usecols、header 、encoding、sep（分隔符）、low_memory
data = pd.read_csv(filname,header=None, names=['x','y','桩号']) #读取没有列名的数据，names就是自己命名的列名
data = pd.read_csv(filname,encoding='gbk',usecols=[0, 1,2,3,4,15]) ##默认编码是UTF-8，只有读取测量数据是用到gbk，usecols就是读取制定的列，编号从0开始。
datareal = pd.read_csv(SJ,sep='\s+',low_memory=False) ##分隔符为空格
data = pd.read_excel(filname,usecols=[16,17,18,19,20,21,22,23,24,25,26,27]) ##选择部分列读取
df.columns = ['binum','x','y','fold','5/6'] ##读取有列名的数据，后期自己重新命名列名
2、选取指定列的前、后几位数值
字符型取值：
data['观测时间']= data['观测时间'].str[0:9] ##字符取前10位
df['crossline'] = df['binum'].str[-4:] #字符取后4位
数字型取值：
data['线号']= (data['桩号']/10000).astype('int') #提取线号编号
data['点号']=(data['桩号']-data['线号']*10000).astype('int')
data['束号']= ((data['点号']-657)/7+1).astype('int')
3、选取指定的区域数据
dataln=pd.read_csv(LN,sep='\s+',low_memory=False)
df = dataln[3:] ############第3行开始，全部的列数据
drilledday = data.loc[1：20,'fold'] ##选取第1到20行，列名为fold的数据区域
4、修改极值，当某一列满足一定条件时修改数据值
df.loc[df['fold']>56,'fold']=56 #0-3000m的5/6，超过给定覆盖次数值则给予固定赋值 168次覆盖0-3000的5/6值 当fold列大于56时，fold值就固定等于56，其中修改的这列也可以为其他列名，不一定是条件列
5、增加一列
data['束号']= ((data['点号']-657)/7+1).astype('int') ##数字型直接计算
df['crossline'] = 1 ##固定值dataframe
df['crossline'] = df['binum'].str[-4:] #字符型中增加与原数据有关的一列
6、提取满足一定条件的数据
inlinenumber = 491 ##inline编号，在绿山中读取
corrslinenumber = 562 ##crossline编号，在绿山中读取
inlinefold = df.loc[df['inline']==inlinenumber] #提取指定inline的数据
crosslinefold = df.loc[df['crossline']==corrslinenumber] #提取指定crossline的数据
datadiff = data3.loc[data3['diff']<0] ###挑选出不满足覆盖次数的面元
7、数据类型转换
print(df.info()) ##查看dataframe数据类型
df['fold'] = df['fold'].astype('int') #Object类型转为int类型
8、剔除部分列
datadiff2 = datadiff.drop(['x1','y1','fold','fold1','5/6'], axis=1) ##剔除部分列
9、dataframe转为list
drilledall = data.loc[start:ii,'Unnamed: 15'].values.tolist()#钻井累计进度，起始行，加指定列
10、重置索引
datadiff2.reset_index(drop=True, inplace=True) ###########重置index
11、禁止链式引用警告
pd.set_option('mode.chained_assignment', None) ######禁止链式引用警告
12、计算sps头卡的位置
data = []
for line in open(filename+'.X','r'):
data.append(line)
############计算头卡行数########
i = 0
while data[i][0]=='H':
i = i+1
ii=i
13、关系文件中读取桩号、文件号，合并到一个dataframe中
v = data[ii:] ###data为读取的关系文件，ii为sps头卡的位置
xlenth = len(v)
ffid = []
shot = []
for i in range(0,xlenth):
ffid.append(int(v[i][10:15]))
shot.append(int(float(v[i][17:24]))*10000+int(v[i][28:34]))
ffid2 = pd.DataFrame(ffid)
ffid2.columns=['文件号']
shot2 = pd.DataFrame(shot)
shot2.columns=['桩号']
fired = ffid2.join(shot2) ####横向合并####
data1ffid = fired.drop_duplicates() ##去重###
14、炮点文件中读取线号、点号等信息并合并为一个数组
v2 = data2[ii:] ###data为读取的炮点文件，ii为sps头卡的位置
xlenth2 = len(v2)
shotline = []
shotpoint = []
shotx = []
depth = []
charge = []
x = []
y = []
z = []
sercelday = [] ############日期########
serceltime = [] ###########时间##########
hour = []
sw = [] ############束线号#########
for i in range(0,xlenth2):
shotline.append(int(v2[i][2:8]))
shotpoint.append(int(v2[i][14:18]))
shotx.append(int(v2[i][2:8])*10000+int(v2[i][14:18]))
depth.append(float(v2[i][30:36]))
charge.append(int(v2[i][42:44]))
x.append(float(v2[i][47:56]))
y.append(float(v2[i][56:66]))
z.append(float(v2[i][66:71]))
sercelday.append(int(v2[i][71:74]))
serceltime.append(int(v2[i][74:80]))
hour.append(int(v2[i][74:76]))
sw.append(int((int(v2[i][14:18])-398)/7+1))
firedsss = np.vstack((shotline,shotpoint,shotx,depth,charge,x,y,z,sercelday,serceltime,hour,sw))
firedssss = firedsss.transpose()
15、输出csv格式
datadiff2.to_csv(SJ+'.csv',header=None)
16、dataframe格式转为list格式来处理日期
time2 = data.loc[:,'日期'].values.tolist() ###转为list格式数据
timenew = []
for time1 in time2:
delta=datetime.timedelta(days=time1) ##datetime格式
daynow=datetime.datetime.strptime('1899-12-30','%Y-%m-%d')+delta
day = daynow.strftime('%m{m}%d{d}').format( m='月', d='日') ## 月 日格式
# dayonline = daynow.strftime('%Y{y}%m{m}%d{d}').format(y='', m='', d='') ## 纯数字格式
# dayonline = daynow.strftime('%Y{y}%m{m}%d{d}').format(y='年', m='月', d='日') ## 年 月 日格式
timenew.append(day)
17、读、写数据库
使用pymssql、sqlalchemy的create_engine额外两个库，常见的问题是fast_executemany=True的用法，这个经常出现缓冲不够、memmory error的错误。针对缓冲不够，主要是因为数据中字符过多，将有些字符如object类型转为int类型，memory error的错误主要是因为数据库的设置问题，字符设置过大导致，如设置成nvchar(max)，一般设置成一个给定的数据例如nvchar(600)，日常使用是足够了。
import time
import pandas as pd
import pymssql #引入pymssql模块
from sqlalchemy import create_engine ##加速写入数据库速度引擎
#import datetime
################读取日报数据#########
print('读取下药数据......')
time_start=time.time()
filname = r'xxx.xlsx'
data = pd.read_excel(filname,usecols=[16,17,18,19,20,21,22,23,24,25,26,27]) ##选择部分列读取
#print(data.info())
data['线号']= (data['桩号']/10000).astype('int') #提取线号编号
data['点号']=(data['桩号']-data['线号']*10000).astype('int')
data['束号']= ((data['点号']-398)/7+1).astype('int')
########################################################
####################更新本地数据########################
##########################清空表#############
print('更新本地数据库......')
conn = pymssql.connect('(local)', 'username', 'pass', 'databasename')
sql = 'truncate table charge'
cursor = conn.cursor()
cursor.execute(sql)
conn.commit()
conn.close()
######################连接数据库写入数据############
USERSTR = 'sa:123qwe@127.0.0.1/databasename'
LINKSTR = 'mssql+pyodbc://' + USERSTR + '?driver=SQL+Server'
engine = create_engine(LINKSTR,fast_executemany = True)
data.to_sql('charge', engine, if_exists='append', index=False)
#################更新岩性、坐标######################
conn = pymssql.connect('127.0.0.1', 'username', 'pass', 'databasename')
sql1= ''' update charge
set 北坐标=3188810.0+(cast(right(桩号,4) as float)-0.0)*40.0 ,
东坐标=635653.0+(cast(left(桩号,5) as float)-20000.0)*40.0,
岩性= '砂岩' '''
sql2 = '''update charge
set charge.岩性 = Y.岩性 from yanxin Y where charge.桩号=Y.桩号'''
sql3 = ''' update charge
set charge.北坐标=P.y,
charge.东坐标=P.x
from celiangall P
where charge.桩号=P.桩号 '''
cursor = conn.cursor()
cursor.execute(sql1)
cursor.execute(sql2)
cursor.execute(sql3)
conn.commit()
conn.close()
18、读取数据库信息写入SPS
import pymssql #引入pymssql模块
conn = pymssql.connect('(local)', 'username', 'pass', 'database')
cursor = conn.cursor()
sql = '''select 线号,点号,x,y from celiangall where 点类型 = 'S' ''' ##获取测量放样炮点信息
#sql = '''select 线号,点号,坐标x,坐标y FROM drilltask''' ##获取设计炮点信息
#############################SPS格式在后面选择，提供2.1和1.0两种格式######################
try:
cursor.execute(sql)
results = cursor.fetchall()
# print(results)
except:
print(results)
conn.close()
lenth = len(results)
f=open("E:/2019x2三维/sps/设计/南xx.S",'w+') # 要更改文件名
f.write('H00 SPS format version num. SPSx.x; '+"\n")
f.write('H01 Description of survey area cd2,,N/A,N/A; '+"\n")
f.write('H02 Date of survey ; '+"\n")
f.write('H021Post-plot date of issue ; '+"\n")
f.write('H022Tape/disk identifier DISK; '+"\n")
f.write('H03 Client N/A; '+"\n")
f.write('H04 Geophysical contractor N/A; '+"\n")
f.write('H05 Positioning contractor N/A; '+"\n")
f.write('H06 Pos. proc. contractor N/A; '+"\n")
f.write('H07 Field computer system(s) 428XL; '+"\n")
f.write('H08 Coordinate location Center of source and of receiver pattern; '+"\n")
f.write('H09 Offset from coord. location 0.0M,0.0DEG; '+"\n")
f.write('H10 Clock time w.r.t. GMT ; '+"\n")
f.write('H11 Spare ; '+"\n")
f.write('H12 Geodetic datum,-spheroid ; '+"\n")
f.write('H13 Spare ; '+"\n")
f.write('H14 Geodetic datum parameters ; '+"\n")
f.write('H15 Spare ; '+"\n")
f.write('H16 Spare ; '+"\n")
f.write('H17 Vertical datum description ; '+"\n")
f.write('H18 Projection type UTM; '+"\n")
f.write('H19 Projection zone ; '+"\n")
f.write('H20 Description of grid units Metres; '+"\n")
f.write('H201Factor to metre 1.00000000; '+"\n")
f.write('H220Long. of central meridian ; '+"\n")
f.write('H231Grid origin ; '+"\n")
f.write('H232Grid coord. at origin ; '+"\n")
f.write('H241Scale factor ; '+"\n")
f.write('H242Lat., long. scale factor ; '+"\n")
f.write('H256Lat., long. initial line ; '+"\n")
f.write('H257Circular bearing of H256 ; '+"\n")
f.write('H258Quadrant bearing of H256 ; '+"\n")
f.write('H259Angle from skew ; '+"\n")
f.write('H300Project code and description; '+"\n")
f.write('H310Line number format ; '+"\n")
f.write('H400Type,Model,Polarity ; '+"\n")
f.write('H401Crew name,Comment ; '+"\n")
f.write('H402Sample int.,Record Len. 1,0.000000,N/A; '+"\n")
f.write('H403Number of channels 1,5376; '+"\n")
f.write('H404Tape type,format,density ; '+"\n")
f.write('H405Filter_alias Hz,dB pnt,slope; '+"\n")
f.write('H406Filter_notch Hz,-3dB points ; '+"\n")
f.write('H407Filter_low Hz,dB pnt,slope ; '+"\n")
f.write('H408Time delay FTB-SOD app Y/N ; '+"\n")
f.write('H409Multi component recording ; '+"\n")
f.write('H410Aux. channel 1 contents ; '+"\n")
f.write('H411Aux. channel 2 contents ; '+"\n")
f.write('H412Aux. channel 3 contents ; '+"\n")
f.write('H413Aux. channel 4 contents ; '+"\n")
f.write('H414Spare ; '+"\n")
f.write('H415Spare ; '+"\n")
f.write('H416Spare ; '+"\n")
f.write('H417Spare ; '+"\n")
f.write('H418Spare ; '+"\n")
f.write('H419Spare ; '+"\n")
f.write('H600Type,model,polarity G1,geophone 1; '+"\n")
f.write('H601Damp coeff,natural freq. ; '+"\n")
f.write('H602Nunits,len(X),width(Y) ; '+"\n")
f.write('H603Unit spacing X,Y ; '+"\n")
f.write('H604Spare ; '+"\n")
f.write('H605Spare ; '+"\n")
f.write('H606Spare ; '+"\n")
f.write('H607Spare ; '+"\n")
f.write('H608Spare ; '+"\n")
f.write('H700Type,model,polarity E1,explosive 1; '+"\n")
f.write('H701Size,vert. stk fold ; '+"\n")
f.write('H702Nunits,len(X),width(Y) ; '+"\n")
f.write('H703Unit spacing X,Y ; '+"\n")
f.write('H711Nom. shot depth,charge len. ; '+"\n")
f.write('H712Nom. soil,drill method ; '+"\n")
f.write('H713Weathering thickness ; '+"\n")
f.write('H26 5678901234567890123456789012345678901234567890123456789012345678901234567890'+"\n")
for i in range(lenth):
line = results[i][0]
point = results[i][1]
xcor = results[i][2]
ycor = results[i][3]
# zcor= results[i][4]
# f.write("R "+str(line)+"\n")
############sps2.1格式#############
# print('S ','%-6d %-6d %-.1f %-.1f ' %(line,point,xcor,ycor),file=f)
############sps1.0格式#############
print('S' '%d %6d1 %-.1f %-.1f ' %(line,point,xcor,ycor),file=f)
f.close()
print("SPS写入成功！")
19、读取数据库写入excel
import pymssql #引入pymssql模块
import openpyxl
import time
time_start=time.time()
#############################读取x1共享区检波点信息#################
place=r'xx.xlsx' ####excel保存位置
print('1-2，读取生产信息。')
conn = pymssql.connect('(local)', 'username', 'pass', 'database1')
cursor = conn.cursor()
sql = "select 桩号, 线号,点号,x,y from celiangall \
where 点类型 = 'R ' AND 线号 >=10566 AND 线号 <=11168 AND 点号 >=1120"
try:
cursor.execute(sql)
resultsr = cursor.fetchall()
# print(resultsr)
except:
print(resultsr)
conn.close()
##读取x2检波点信息###
#print('2-13，读取x2测量检波点信息')
conn = pymssql.connect('(local)', 'username', 'pass', 'database2')
cursor = conn.cursor()
sql = "select 桩号, 线号,点号,x,y from celiangall where 点类型 = 'R '"
try:
cursor.execute(sql)
resultsr_ycg = cursor.fetchall()
# print(resultsr)
except:
print(resultsr_ycg)
conn.close()
resultsr_all = resultsr + resultsr_ycg ####合并x1共享检波点与和x2测量检波点
################读取x1共享区域测量炮点信息###################
#print('3-13，读取x1测量炮点信息')
conn = pymssql.connect('(local)', 'username', 'pass', 'database1')
cursor = conn.cursor()
sql = "select 桩号, 线号,点号,x,y from celiangall \
where 点类型 = 'S ' AND 线号 >= 21263 AND 点号 >=657 AND 点号 <=1076"
try:
cursor.execute(sql)
resultscls = cursor.fetchall()
# print(resultsr)
except:
print(resultscls)
conn.close()
##读取x2测量炮点信息###
#print('4-13，读取x2测量炮点信息')
conn = pymssql.connect('(local)', 'username', 'pass', 'database2')
cursor = conn.cursor()
sql = "select 桩号, 线号,点号,x,y from celiangall where 点类型 = 'S '"
try:
cursor.execute(sql)
resultscls_ycg = cursor.fetchall()
# print(resultsr)
except:
print(resultscls_ycg)
conn.close()
resultscls_all = resultscls + resultscls_ycg ####合并x1共享测量炮点与和x2测量炮点
################读取x1共享区域设计炮点信息###################
#print('5-13，读取x1共享区设计炮点信息')
conn = pymssql.connect('(local)', 'username', 'pass', 'database1')
cursor = conn.cursor()
sql = "SELECT 桩号,线号,点号,坐标x,坐标y FROM drilltask \
where 线号 >= 21263 AND 点号 >=657 AND 点号 <=1076"
try:
cursor.execute(sql)
results = cursor.fetchall()
# print(results)
except:
print(results)
conn.close()
####读取x2设计炮点信息###############
#print('6-13，读取x2设计炮点信息')
conn = pymssql.connect('(local)', 'username', 'pass', 'database2')
cursor = conn.cursor()
sql = "select 桩号,线号,点号,坐标x,坐标y FROM drilltask"
try:
cursor.execute(sql)
results_ycg = cursor.fetchall()
# print(results_ycg)
except:
print(results_ycg)
conn.close()
results = results+ results_ycg ####合并x1设计炮点和x2设计炮点
#############读取x1共享区域钻井进度信息###################
#print('7-13，读取x1共享区钻井信息')
conn = pymssql.connect('(local)', 'username', 'pass', 'database1')
cursor = conn.cursor()
sql = "SELECT 桩号,线号,点号,束号,东坐标,北坐标 FROM drilledxitong \
where 线号 >= 21263 AND 点号 >=657 AND 点号 <=1076"
try:
cursor.execute(sql)
resultsls = cursor.fetchall()
# print(results)
except:
print(resultsls)
conn.close()
##读取x2钻井进度信息###############
#print('8-13，读取x2钻井信息')
conn = pymssql.connect('(local)', 'username', 'pass', 'database2')
cursor = conn.cursor()
#sql = "select 桩号,线号,点号,束号,东坐标,北坐标 from drilledxitong " ##信息系统
sql = "select 桩号,线号,点号,束号,x,y from drilled " ##透视表语句
try:
cursor.execute(sql)
results_ycgdrill = cursor.fetchall()
# print(results_ycg)
except:
print(results_ycgdrill)
conn.close()
results_drilledall = resultsls + results_ycgdrill ####合并x1钻井炮点和x2钻井
#########################################################
#############读取x1共享区域下药信息###################
#print('9-13，读取x1共享区下药信息')
conn = pymssql.connect('(local)', 'username', 'pass', 'database1')
cursor = conn.cursor()
sql = "SELECT 桩号,线号,点号,束号,下药药量,东坐标,北坐标 FROM charge \
where 线号 >= 21263 AND 点号 >=657 AND 点号 <=1076"
try:
cursor.execute(sql)
results_lscharge = cursor.fetchall()
# print(results)
except:
print(results_lscharge)
conn.close()
##读取x2下药信息###############
#print('10-13，读取x2下药信息')
conn = pymssql.connect('(local)', 'username', 'pass', 'database2')
cursor = conn.cursor()
sql = "select 桩号,线号,点号,束号,下药药量,东坐标,北坐标 from charge "
try:
cursor.execute(sql)
results_ycgcharge = cursor.fetchall()
# print(results_ycg)
except:
print(results_ycgcharge)
conn.close()
results_chargedall = results_lscharge + results_ycgcharge ####合并x1下药炮点和x2下药
###################################################################
#############读取x1共享区域采集信息###################
#print('11-13，读取x1共享区采集信息')
conn = pymssql.connect('(local)', 'username', 'pass', 'database1')
cursor = conn.cursor()
sql = "SELECT 桩号,线号,点号,束号,文件号,井深,药量,岩性,x,y FROM fired \
where 线号 >= 21263 AND 点号 >=657 AND 点号 <=1076 order by 文件号"
try:
cursor.execute(sql)
results_lsfired = cursor.fetchall()
# print(results)
except:
print(results_lsfired)
conn.close()
##读取x2采集信息###############
#print('12-13，读取x2采集信息')
conn = pymssql.connect('(local)', 'username', 'pass', 'database2')
cursor = conn.cursor()
sql = "select 桩号,线号,点号,束号,文件号,井深,药量,岩性,x,y from fired order by 文件号"
try:
cursor.execute(sql)
results_ycgfired = cursor.fetchall()
# print(results_ycg)
except:
print(results_ycgfired)
conn.close()
results_firedall = results_lsfired + results_ycgfired ####合并x1采集炮点和x2采集
###################################################################
print('2-2，开始写入表格。')
data = openpyxl.Workbook() # 新建工作簿
data.create_sheet(index=0, title="Sheet1") # 添加页
#indexr = len(resultsr_all)
#indexs = len(resultscls_all)
#index = len(results)
#index_drilledall = len(results_drilledall)
#index_chargedall = len(results_chargedall)
#index_firedall = len(results_firedall)
sheet = data.active
sheet.cell(1,1,'测量检波点桩号')
sheet.cell(1,2,'线号')
sheet.cell(1,3,'点号')
sheet.cell(1,4,'x')
sheet.cell(1,5,'y')
sheet.cell(1,6,'测量炮点桩号')
sheet.cell(1,7,'线号')
sheet.cell(1,8,'点号')
sheet.cell(1,9,'x1')
sheet.cell(1,10,'y1')
sheet.cell(1,11,'设计炮点桩号')
sheet.cell(1,12,'线号')
sheet.cell(1,13,'点号')
sheet.cell(1,14,'x2')
sheet.cell(1,15,'y2')
sheet.cell(1,16,'钻井桩号')
sheet.cell(1,17,'线号')
sheet.cell(1,18,'点号')
sheet.cell(1,19,'束号')
sheet.cell(1,20,'x3')
sheet.cell(1,21,'y3')
sheet.cell(1,22,'下药桩号')
sheet.cell(1,23,'线号')
sheet.cell(1,24,'点号')
sheet.cell(1,25,'束号')
sheet.cell(1,26,'下药药量')
sheet.cell(1,27,'x4')
sheet.cell(1,28,'y4')
sheet.cell(1,29,'采集桩号')
sheet.cell(1,30,'线号')
sheet.cell(1,31,'点号')
sheet.cell(1,32,'束号')
sheet.cell(1,33,'文件号')
sheet.cell(1,34,'井深')
sheet.cell(1,35,'药量')
sheet.cell(1,36,'岩性')
sheet.cell(1,37,'x5')
sheet.cell(1,38,'y5')
################写入测量检波点信息################
for i in range(0, len(resultsr_all)):
for j in range(0, len(resultsr_all[i])):
sheet.cell(row=i+2, column=j+1, value=resultsr_all[i][j])
#######################写入测量炮点桩号##############
for i in range(0, len(resultscls_all)):
for j in range(0, len(resultscls_all[i])):
sheet.cell(row=i+2, column=j+6, value=resultscls_all[i][j])
#######################写入设计炮点桩号##############
for i in range(0, len(results)):
for j in range(0, len(results[i])):
sheet.cell(row=i+2, column=j+11, value=results[i][j])
#######################写入钻井桩号##############
for i in range(0, len(results_drilledall)):
for j in range(0, len(results_drilledall[i])):
sheet.cell(row=i+2, column=j+16, value=results_drilledall[i][j])
######################写入下药桩号#################
for i in range(0, len(results_chargedall)):
for j in range(0, len(results_chargedall[i])):
sheet.cell(row=i+2, column=j+22, value=results_chargedall[i][j])
######################写入采集桩号#################
for i in range(0, len(results_firedall)):
for j in range(0, len(results_firedall[i])):
sheet.cell(row=i+2, column=j+29, value=results_firedall[i][j])
data.save(place)
print("xlsx格式表格写入数据成功！")
time_end=time.time()
print('totally cost',str(round(time_end-time_start,4)))
2993
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
