PostgreSQL और पायथन में पिवट टेबल के बारे में थोड़ा

शुभ दिन।

जब मैं संस्थान में काम करता हूं, तो मुझे कई अर्ध-संरचित जानकारी से निपटना पड़ता है। यहां उपसर्ग "सेमी" का मतलब है कि सामान्य रूप से सभी डेटा समान हैं, लेकिन, एक नियम के रूप में, उन्हें स्थानीय फ़ोल्डर में कर्मचारियों के कंप्यूटरों में, .xls, .txt या बाइनरी प्रारूप में कैश किया जाता है। जानकारी विभिन्न उपकरणों (स्तर सेंसर, तापमान, वर्तमान वेग, वायुमंडलीय दबाव, आर्द्रता, और इतने पर 20-30 विभिन्न मापदंडों) से प्राप्त डेटा है। सभी डिवाइस प्रत्येक डेटा को अपने स्वयं के प्रारूप में अपलोड करते हैं: या तो एएससीआई या एक द्विआधारी प्रारूप में, जिसे तब संसाधित किया जाता है, और, परिणामस्वरूप, एएससीआई को फिर से प्राप्त किया जाता है। खैर, सामान्य तौर पर, सब कुछ हमेशा की तरह होता है, आप खुद इस अराजकता का प्रतिनिधित्व करते हैं।

मैं पूरी चीज़ को एक सामान्य डेटाबेस में रखना चाहता था, ताकि सही फ़ोल्डर में सही संस्करण के आवश्यक डेटा की खोज न कर सकूं, जिसमें बहुत लंबा समय लगता है। विभिन्न प्रणालियों (मुख्य रूप से भू-सूचना) विकसित करने का अनुभव उपलब्ध है। लेकिन विशेष रूप से संसाधित डेटा में निहित से पहले क्या किया गया था, और सामान्य तौर पर, इन सभी प्रणालियों को ऑर्डर करने के लिए बनाया गया था। अपने लिए कोई स्वचालन परिसर नहीं था।

इस पूरे फ़ार्म को संसाधित करना काफी मानक चीज़ है, नया और दिलचस्प कुछ भी नहीं: अखंडता के लिए समय श्रृंखला की जाँच करना (यदि आवश्यक हो तो इंटरपोलेशन), विभिन्न ग्राफ़ का एक गुच्छा बनाना, इस डेटा पर विभिन्न मॉडल चलाना, मॉडल के आउटपुट को संसाधित करना (फिर से, ग्राफ़ का एक गुच्छा), और आउटपुट डेटा। मैं इस लेख में उत्तरार्द्ध के बारे में बात करूंगा।

आंकड़े

सब कुछ इतना दुखद नहीं होगा अगर न केवल सामान्य संकेतक (न्यूनतम, अधिकतम, औसत, विचलन, आदि) प्राप्त करने की आवश्यकता थी, बल्कि विभिन्न प्रकारों के सारांश टेबल (पिवट टेबल) भी थे।

सरल

हमारे पास गति डेटा है, हमें फ़ॉर्म की तालिका बनाने की आवश्यकता है:

गति के स्नातक, सेमी / एस	Repeatability,%
0-10	60%
10-20	30%
20-30	10%

मूल श्रृंखला अपने आप में लगभग 10k गति मान है। मैं एक एक्सेल गुरु नहीं हूं और VBA को छोड़कर, मुझे यह करने का एक सरल तरीका नहीं पता है (नहीं, निश्चित रूप से अभी भी सूत्रों को फैलाने का एक विकल्प है, फिर क्रमबद्ध करें और इसी तरह, साथ ही एक्सेल में सारांश तालिकाएं भी करें, लेकिन प्रत्येक डिवाइस के लिए हर बार। यह ओवरकिल है)।
डेटाबेस में (मैं PostgresSQL का उपयोग करता हूं) कार्य इस तरह दिखता है:

REATE TABLE tbldata ( dataid serial NOT NULL, measurementdate timestamp without time zone, velocity double precision, direction double precision ); Select 100*(count(velocity)/(select count(*) from tbldata)::float) from tbldata velocity>0 and measurement<10; 

कितने उन्नयन - इतने सारे अनुरोध, यह आसानी से स्वचालित है, यह जल्दी से काम करता है। आप एक संग्रहीत प्रक्रिया लिख ​​सकते हैं जो मानों को "से", "चरण", "चरणों की संख्या" और जो SETOF RECORDS को आउटपुट करेगा। सामान्य तौर पर, समस्या हल हो जाती है।

अधिक जटिल

समस्या तब शुरू होती है जब फॉर्म की तालिका की आवश्यकता होती है:

गति के स्नातक, सेमी / एस	रंबम द्वारा दोहराव,%
	सी	NE		एसई	Yoo	दप	डब्ल्यू	NW
0 - 10	10.8	8.2	1.3	1.3	2.1	10.1	6.9	25.4
10 - 20	4.0	0.1	0.1	1.6	3.3	0.6	0.1	10.9
20 - 30	1.8	0.0	0.0	1.2	3.4	0.1	0.0	2.2
३० - ४०	0.7	0.0	0.0	0.8	1.2	0.0	0.0	0.3
४० - ५०	0.1	0.0	0.0	0.3	0.2	0.0	0.0	0.1
५० - ६०	0.1	0.0	0.0	0.2	0.1	0.0	0.0	0.0
60 - 70	0.0	0.0	0.0	0.1	0.0	0.0	0.0	0.0
० - 80०	0.0	0.0	0.0	0.1	0.0	0.0	0.0	0.0

यही है, आपको दो चर के लिए ग्रेडेशन के प्रतिशत की गणना करने की आवश्यकता है:

 Select 100*(count(*)/(select count(*) from tbldata)::float) from tbldata where velocity >0 and velocity<10 and (direction>337.5 or direction<22.5); 

आगे आप अद्भुत क्रॉसस्टैब का लाभ उठा सकते हैं। ऐसा करने के लिए, आपको गति में क्रमिक क्रम संख्या और दिशा में क्रमिक क्रम संख्या के बारे में जानकारी जोड़कर SQL को थोड़ा संशोधित करने की आवश्यकता है:

 select 1 as VelGrd,1 as DirGrid, 100*(count(*)/(select count(*) from tbldata)::float) from tbldata where velocity >0 and velocity<10 and (direction>337.5 or direction<22.5); 

इसके अलावा, इसे एक फ़ंक्शन में लपेटें, जिसमें पहले से ही गति और दिशा दोनों के लिए शुरुआत, चरण और संख्या शामिल होगी, जो इन सभी प्रश्नों को UNION देगा।
परिणामस्वरूप, हमारा अनुरोध इस तरह दिखाई देगा

 select * from crosstab('fnMakePercentageByTwoVariables(0,10,10,22.5,45,8)') as tbldata(velGrid int,dirGrid1 double precision, …,dirGrid8 double precision); 

काफी बुरा

गति के स्नातक, सेमी / एस	रंबम द्वारा दोहराव,%								योग	सुरक्षा,%
	सी	NE		एसई	Yoo	दप	डब्ल्यू	NW
0 - 10	10.8	8.2	1.3	1.3	2.1	10.1	6.9	25.4	66.1	100.0
10 - 20	4.0	0.1	0.1	1.6	3.3	0.6	0.1	10.9	20.6	33.9
20 - 30	1.8	0.0	0.0	1.2	3.4	0.1	0.0	2.2	8.8	13.3
३० - ४०	0.7	0.0	0.0	0.8	1.2	0.0	0.0	0.3	3.1	4.5
४० - ५०	0.1	0.0	0.0	0.3	0.2	0.0	0.0	0.1	0.7	1.4
५० - ६०	0.1	0.0	0.0	0.2	0.1	0.0	0.0	0.0	0.4	0.7
60 - 70	0.0	0.0	0.0	0.1	0.0	0.0	0.0	0.0	0.2	0.3
० - 80०	0.0	0.0	0.0	0.1	0.0	0.0	0.0	0.0	0.1	0.1
योग	17.6	8.3	1.4	5.5	10.3	10.9	7.1	39.0	100.0
औसत गति, सेमी / एस	10.7	2.6	2.9	22.6	19.9	4.3	3.3	9.2	9.7
अधिकतम गति, सेमी / एस	76.2	57.8	50.2	78.3	61.1	48.8	42.9	62.5	78.3

ईमानदार होने के लिए, मेरा धैर्य पहले से ही खत्म हो गया है, मुझे तीन यूनियनों को जोड़ने और एक पिछले अनुरोध में शामिल होने की आवश्यकता है। और यदि आप द्विघात विचलन और कुछ और जानना चाहते हैं? एक और संघ, और इतने पर, पर्याप्त कल्पना है। सुरक्षा (वास्तव में, यह नीचे से ऊपर एक संचयी प्रतिशत है) मुझे अभी भी पता नहीं है कि कलमों को छोड़कर गणना कैसे की जाती है। सामान्य तौर पर, यदि तालिका की उपस्थिति नहीं बदलती है, तो इसके साथ तिरस्कृत किया जा सकता है।

बहुत बुरा

लेकिन समस्या दो उपकरणों के रीडिंग की तुलना करने के लिए उत्पन्न होती है। तब यह पता चलता है कि प्रत्येक स्तंभ को अभी भी दो में विभाजित करने की आवश्यकता है:

गति के स्नातक, सेमी / एस	रंबम द्वारा दोहराव,%
	सी		...	NW
	डिवाइस 1	उपकरण २	...	डिवाइस 1	उपकरण २
0 - 10	10.8	8.2	...	1.3	1.3
...	...	...	...	...	...

हम तुरंत रीडिंग की तुलना करने में रुचि रखते हैं? मेरे लिए, हाँ। जब मैंने इस तरह की तुलना के बारे में सोचा, तो मुझे वाकई दुख हुआ। SPSS में डेटा अपलोड करने, सबकुछ ट्रांसकोड करने और आपको हेट टन के बिना ठीक उसी तरह आउटपुट करने की तुलना में कोई विकल्प नहीं था। तालिकाओं का होना भी महत्वपूर्ण है, जहां गति में वृद्धि के अनुसार, आपके पास संचयी प्रतिशत भी होना चाहिए, जिसे आपको एक्सेल में हाथ से करना होगा।

निर्णय

काम करने के लिए मेरा मुख्य उपकरण अजगर है, अनुप्रयोगों को विकसित करने के लिए Django का उपयोग कर रहा है। और अंत में मैंने फैसला किया - सब कुछ नरक के साथ, पर्याप्त दर्द और पीड़ा। संग्रहीत प्रक्रियाएं तेज़ और अच्छी हैं, लेकिन उन्हें बनाए रखना मेरे लिए कठिन है। साथ ही, काम की गति कोई कारक नहीं है - जो लोग मेरे साथ यह सब करते हैं, भगवान न करे, अगर आप अपने हाथों से सब कुछ नहीं मानते हैं। तो एक मिनट रुकिए - एक दो घंटे के लिए डेटा की एक श्रृंखला को साझा करने के बजाय - यह सिर्फ स्वर्गीय आनंद है।

और इसलिए मोक्ष पंडों के व्यक्ति में आया, जिसके बारे में मुझे हबेरा पर एक भी उल्लेख नहीं मिला। उनके डेटा प्रकारों में "पांडा" का मुख्य आकर्षण। एक-आयामी - श्रृंखला । वास्तव में, श्रृंखला में वह सब कुछ है जो मैं तानाशाह में याद करता था। शब्दकोश की कार्यक्षमता का विस्तार करने के लिए बहुत सारी साइकिलें बनाई गई हैं और यह सब पांडा की अज्ञानता से है।
आप प्रमुख s ['a'] के साथ-साथ sa का उपयोग करके शब्दकोश तक पहुँच सकते हैं। बाकी सब, पांडा संरचनाएं numpy से वेक्टरकृत कार्यों के साथ काम करती हैं (मैं numpy.where के बिना नहीं रह सकता)। एक DataFrame में श्रृंखला ढेर - पहले से ही एक दो आयामी संरचना। पैनल में DataFrame। यह सब स्लाइस, सबइंडेक्स, सूचकांकों के एक समूह के साथ काम करता है, मिश्रित डेटा के साथ - सामान्य तौर पर, सब कुछ दिव्य है!

और मेरे कार्य के लिए मुझे वह तालिका मिलनी चाहिए जो मुझे चाहिए:

सबसे पहले, पोस्टग्रेज से कनेक्ट करें और अनुरोध का परिणाम प्राप्त करें

 import psycopg2 import pandas as pd import numpy as np from pandas.io.sql import frame_query conn_string = "host='localhost' dbname='test' user='postgres' password='**********'" conn = psycopg2.connect(conn_string) #frame_query   sql ,   DataFrame df = frame_query('select velocity,direction from tbldata', con=conn) 

उनके लिए ग्रेडेशन और लेबल बनाएं।

 def makeVelocityLabels(c): ret = dict() for levidx in xrange(len(c.levels)): lev=c.levels[levidx] ret[levidx]=str(lev).replace('(',"").replace(", ","-").replace(']',"") return ret velGrid = np.linspace(0,100,11) dirGrid = np.linspace(-22.5,337.5,9) dirLabels=['','',"","","","","",""] velLabels = makeVelocityLabels(pd.cut(velGrid,velGrid)).values() 

एक जिज्ञासु देखो कट समारोह देखा। वह इस तथ्य में लगी हुई है कि एक निरंतर श्रृंखला से वह एक असतत बनाता है, वास्तव में, चर को एक श्रेणी प्रकार में बदल देता है। अगर पहले, मैं हमेशा numpy.where का उपयोग करता था , तो कुछ इस तरह से:

 for i in xrange(len(velGrid)-1): veloity[np.where((velocity>velGrid[i]) & (velocity<velGrid[i+1]))]=velGrid[i] 

तो अब मैं करता हूँ

 pd.cut(velocity,velGrid) 

अगला, एक नया डेटाफ़्रेम बनाएँ, इसमें श्रेणीबद्ध चर डालें:

 resultDf = pd.DataFrame(index=df.index,columns=df.columns) #df[df.direction>337.5].direction -   . # ,     . df.direction[df.direction>337.5]-=360 resultDf.velocity = pd.cut(df.velocity,velGrid,labels=velLabels,include_lowest=True) resultDf.direction = pd.cut(df.direction,dirGrid,labels=dirLabels,include_lowest=True) 

तब सबसे दिलचस्प हिस्सा शुरू होता है। चूंकि तालिका के शीर्ष पर दिशाएं हैं (वास्तव में, मैं दिशाओं में गति को पार करता हूं), आंकड़ों की गणना करने के लिए (न्यूनतम, अधिकतम, आदि), मुझे उन्हें प्रत्येक दिशा के लिए समान पढ़ने की आवश्यकता है।
हम समझेंगे कि दिशा के प्रत्येक उन्नयन के लिए हमारे पास कुल कितने माप हैं।

 totalDir = resultDf.groupby("direction").size() direction  97  503  1251  592  2773  736  388  773 

मैं आकार () के बजाय गिनती () फ़ंक्शन का उपयोग करने की सलाह दूंगा, जो सभी मूल्यों को वापस कर देगा जो गायब नहीं हैं।
अगला, हमें आँकड़ों की गणना करने की जरूरत है, दिशाओं में भी, अधिकतम के लिए, औसत और तालिका का निर्माण स्वयं।

 dfDGr = df.copy() dfDGr.direction = resultDf.direction meanVel = dfDGr.groupby("direction").mean() maxVel = dfDGr.groupby("direction").max() totalVel = resultDf.groupby("velocity").size() pivot = 100 * pd.pivot_table(resultDf,rows='velocity',cols='direction',aggfunc=len, fill_value=0)/totalDir.sum() direction         velocity 0-10 1.279348 6.916913 10.839308 8.224378 25.432307 2.066639 1.265289 10.066076 10-20 0.056235 0.126529 3.992689 0.070294 10.881485 3.303810 1.560523 0.632644 20-30 0.000000 0.014059 1.813581 0.014059 2.235344 3.388163 1.223113 0.112470 30-40 0.014059 0.000000 0.674821 0.000000 0.323352 1.237171 0.801350 0.014059 40-50 0.000000 0.014059 0.140588 0.000000 0.056235 0.224940 0.253058 0.042176 50-60 0.014059 0.000000 0.084353 0.014059 0.042176 0.112470 0.168705 0.000000 60-70 0.000000 0.000000 0.028118 0.000000 0.014059 0.014059 0.126529 0.000000 70-80 0.000000 0.000000 0.014059 0.000000 0.000000 0.000000 0.056235 0.000000 

यह केवल प्लेटों को आंकड़े संलग्न करने के लिए बनी हुई है, गोल और सही क्रम में कॉलम डालती है।

 def getCumulate(s): ns = s.copy() for val in xrange(s.size): ns[ns.index[val]]=np.sum(s[s.index[val]:s.index[s.size-1]]) return ns pivot[""] = 100 * np.round(totalVel/float(np.sum(totalVel)),3) pivot[',%']=getCumulate(pivot[""]) totalDirFr = pd.DataFrame(100*totalDir/float(np.sum(totalDir))).T totalDirFr.index= [""] pivot = pivot.append(totalDirFr) meanVelFr = meanVel.T meanVelFr.index=[" "] pivot = pivot.append(meanVelFr) maxVelFr = maxVel.T maxVelFr.index=[" "] pivot = pivot.append(maxVelFr) #    -   ,    ,     NA pivot=pivot.reindex(columns=np.append(dirLabels,["",",%"])) pivot = np.round(pivot,1).fillna(0)          ,% 0-10 10.8 8.2 1.3 1.3 2.1 10.1 6.9 25.4 66.1 100.0 10-20 4.0 0.1 0.1 1.6 3.3 0.6 0.1 10.9 20.6 33.9 20-30 1.8 0.0 0.0 1.2 3.4 0.1 0.0 2.2 8.8 13.3 30-40 0.7 0.0 0.0 0.8 1.2 0.0 0.0 0.3 3.1 4.5 40-50 0.1 0.0 0.0 0.3 0.2 0.0 0.0 0.1 0.7 1.4 50-60 0.1 0.0 0.0 0.2 0.1 0.0 0.0 0.0 0.4 0.7 60-70 0.0 0.0 0.0 0.1 0.0 0.0 0.0 0.0 0.2 0.3 70-80 0.0 0.0 0.0 0.1 0.0 0.0 0.0 0.0 0.1 0.1  17.6 8.3 1.4 5.5 10.3 10.9 7.1 39.0 0.0 0.0   10.7 2.6 2.9 22.6 19.9 4.3 3.3 9.2 0.0 0.0   76.2 57.8 50.2 78.3 61.1 48.8 42.9 62.5 0.0 0.0 

सारांश:

परिणाम ठीक वही है जो आपको चाहिए। कम खून? - हां। संशोधित करने के लिए आसान है? - हां।
वास्तव में, पूरे बवासीर है कि चर श्रेणियां नहीं हैं, अगर वे तुरंत इनपुट थे, तो सब कुछ बहुत आसान होगा। मेरे कमजोर लैपटॉप पर, पूरी स्क्रिप्ट 7113 मानों के लिए 0.2 सेकंड में चलती है।

आपका ध्यान देने के लिए धन्यवाद।