用Python處理"大"XLS文件

2019-11-14 08:56:42

字體：大中小

供稿：網(wǎng)友

權(quán)當(dāng)學(xué)習(xí)Python練手用的.

數(shù)據(jù)來(lái)data.gov.uk,大小有58.4MB

文件都是些什么內(nèi)容?

’Accident_Index’, ‘Location_Easting_OSGR’,‘Location_Northing_OSGR’, ‘Longitude’, ‘Latitude’, ‘Police_Force’, ‘Accident_Severity’, ‘Number_of_Vehicles’, ‘Number_of_Casualties’, ‘Date’, ‘Day_of_Week’, ‘Time’, ‘Local_Authority_(District)’, ‘Local_Authority_(Highway)’, ‘1st_Road_Class’, ‘1st_Road_Number’, ‘Road_Type’, ‘Speed_limit’, ‘Junction_Detail’, ‘Junction_Control’, ‘2nd_Road_Class’, ‘2nd_Road_Number’, ‘Pedestrian_Crossing-Human_Control’, ‘Pedestrian_Crossing_Physical_Facilities’, ’Light_Conditions’, ‘Weather_Conditions’, ‘Road_Surface_Conditions’, ‘Special_Conditions_at_Site’, ‘Carriageway_Hazards’, ‘Urban_or_Rural_Area’, ‘Did_Police_Officer_Attend_Scene_of_Accident’, ‘LSOA_of_Accident_Location’

這里寫圖片描述

LowMemory 方式讀取文件

#read the filefiledir='/home/derek/Desktop/python-data-analyis/large-Excel-files/Accidents_2013.csv'data = pd.read_csv(filedir,low_memory=False)PRint data.ix[:10]['Day_of_Week']SQL likes 提取數(shù)據(jù)信息print 'Accidents'print '----------'#選擇星期日發(fā)生的事故accidents_sunday = data[data.Day_of_Week==1]print 'Accidents which happended on a Sunday: ',len(accidents_sunday)#選擇星期日發(fā)生的且涉事人數(shù)在十人以上的事故accidents_sunday_twenty_cars = data[(data.Day_of_Week==1) & (data.Number_of_Vehicles>10)]print'Accidents which happened on a Sunday involving > 10 cars: ' , len(accidents_sunday_twenty_cars)#選擇星期日發(fā)生的且涉事人數(shù)在十人以上且天氣情況是下雨的事故(2對(duì)應(yīng)的是無(wú)風(fēng)下雨)accidents_sunday_twenty_cars_rain = data[(data.Day_of_Week==1) & (data.Number_of_Vehicles>10) & (data.Weather_Conditions==2)]print'Accidents which happened on a Sunday involving > 10 cars with rainning: ' , len(accidents_sunday_twenty_cars_rain)#選擇在倫敦的星期日發(fā)生的事故london_data = data[(data['Police_Force'] == 1) & (data.Day_of_Week==1)]print 'Accidents in London on a Sunday',len(london_data)#選擇在2000年的倫敦的星期日發(fā)生的事故london_data_2000 = london_data[((pd.to_datetime('2000-1-1', errors='coerce')) > (pd.to_datetime(london_data['Date'],errors='coerce'))) & (pd.to_datetime(london_data['Date'],errors='coerce') < (pd.to_datetime('2000-12-31', errors='coerce')))]print 'Accidents in London on a Sunday in 2000:',len(london_data_2000)

給人的感覺是特別像SQL語(yǔ)句,DataFrame的這種切片,方式特別好用,對(duì)不對(duì)?

pd.to_datetime(london_data['Date'],errors='coerce')

這里是日期轉(zhuǎn)換函數(shù).

輸出:

Accidents----------Accidents which happended on a Sunday: 14854Accidents which happened on a Sunday involving > 10 cars: 1Accidents which happened on a Sunday involving > 10 cars with rainning: 1Accidents in London on a Sunday 2374Accidents in London on a Sunday in 2000: 0

將部分DataFrame數(shù)據(jù)以XLSX文件存儲(chǔ)下來(lái) 確保你安裝了XlsxWriter

sudo pip install XlsxWriter

writer = pd.ExcelWriter('london_data.xlsx', engine='xlsxwriter')london_data.to_excel(writer, 'sheet1')writer.save()writer.close()塊讀取,分析一個(gè)星期中那一天最有出事故的概率最大代碼.2013,2014,2015三年的事故記錄,在’Accidents_2013.csv’,’Accidents_2014.csv’, ‘Accidents_2015.csv’這三個(gè)文件中import pandas as pdfrom pandas import Seriesimport matplotlib.pyplot as plt#read the filedir='/home/derek/Desktop/python-data-analyis/large-excel-files/'filedir=['Accidents_2013.csv','Accidents_2014.csv', 'Accidents_2015.csv']tot = Series([])for i in range(3): #塊讀取文件, 每次讀1000條記錄 data = pd.read_csv(dir + filedir[i],chunksize=1000) for piece in data: tot = tot.add(piece['Day_of_Week'].value_counts(), fill_value=0)day_index = ['Sun', 'Mon', 'Tues', 'Wed', 'Thur', 'Fri', 'Sat']print 'data like:'#tot = tot.sort_values(ascending=False)print tot#重新構(gòu)造一個(gè)Series,是為了給索引命名new_Series = Series(tot.values, index=day_index)new_Series.plot()plt.show()plt.close()

控制臺(tái)輸出:

data like:1 460522 609563 650064 640395 644456 693787 55162dtype: float64

圖: 這里寫圖片描述三年記錄在案的有425038條記錄.