数据清洗之层次化索引

冷不防 2023-05-21 16:20 13阅读 0赞

# 层次化索引 #

*  在一个轴上拥有两个或者两个以上的索引
 *  使用loc语句进行访问
 *  loc里面接受tuple，如loc\[(a,b),:\]

import pandas as pd
    import numpy as np
    import os

os.getcwd()

'D:\\Jupyter\\notebook\\Python数据清洗实战\\数据清洗之数据表处理'

os.chdir('D:\\Jupyter\\notebook\\Python数据清洗实战\\数据')

df = pd.read_csv('baby_trade_history.csv', dtype={ 'user_id':str}, index_col=[3,0])  # 把第四列和第一列当成行索引标签

df.head(5)

# 取第一层索引为28，第二层索引为475046636 和 532110457的数据
    # 取多个要以列表形式传入
    df.loc[28].loc[[475046636, 532110457]]

<table> 
 <thead> 
  <tr> 
   <th></th> 
   <th>auction_id</th> 
   <th>cat_id</th> 
   <th>property</th> 
   <th>buy_mount</th> 
   <th>day</th> 
  </tr> 
  <tr> 
   <th>user_id</th> 
   <th></th> 
   <th></th> 
   <th></th> 
   <th></th> 
   <th></th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <th>475046636</th> 
   <td>10368360710</td> 
   <td>203527</td> 
   <td>22724:40168;22729:40278;21458:21817;2770200:24...</td> 
   <td>1</td> 
   <td>20121101</td> 
  </tr> 
  <tr> 
   <th>532110457</th> 
   <td>17916191097</td> 
   <td>50011993</td> 
   <td>21458:11399317;1628862:3251296;21475:137325;16...</td> 
   <td>1</td> 
   <td>20131011</td> 
  </tr> 
 </tbody> 
</table>

# 效果同上
    df.loc[(28, [475046636, 532110457]), ['auction_id', 'cat_id']]

<table> 
 <thead> 
  <tr> 
   <th></th> 
   <th></th> 
   <th>auction_id</th> 
   <th>cat_id</th> 
  </tr> 
  <tr> 
   <th>cat1</th> 
   <th>user_id</th> 
   <th></th> 
   <th></th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <th>28</th> 
   <th>532110457</th> 
   <td>17916191097</td> 
   <td>50011993</td> 
  </tr> 
  <tr> 
   <th>475046636</th> 
   <td>10368360710</td> 
   <td>203527</td> 
  </tr> 
 </tbody> 
</table>

# 取第一层索引为28和38的数据，以列表的形式传递
    df.loc[([28, 38])]

<table> 
 <thead> 
  <tr> 
   <th></th> 
   <th></th> 
   <th>auction_id</th> 
   <th>cat_id</th> 
   <th>property</th> 
   <th>buy_mount</th> 
   <th>day</th> 
  </tr> 
  <tr> 
   <th>cat1</th> 
   <th>user_id</th> 
   <th></th> 
   <th></th> 
   <th></th> 
   <th></th> 
   <th></th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <th>28</th> 
   <th>532110457</th> 
   <td>17916191097</td> 
   <td>50011993</td> 
   <td>21458:11399317;1628862:3251296;21475:137325;16...</td> 
   <td>1</td> 
   <td>20131011</td> 
  </tr> 
  <tr> 
   <th>82830661</th> 
   <td>19948600790</td> 
   <td>50013874</td> 
   <td>21458:11580;21475:137325</td> 
   <td>1</td> 
   <td>20121101</td> 
  </tr> 
  <tr> 
   <th>475046636</th> 
   <td>10368360710</td> 
   <td>203527</td> 
   <td>22724:40168;22729:40278;21458:21817;2770200:24...</td> 
   <td>1</td> 
   <td>20121101</td> 
  </tr> 
  <tr> 
   <th>38</th> 
   <th>734147966</th> 
   <td>15307958346</td> 
   <td>50018202</td> 
   <td>21458:3270827;7361532:28710594;7397093:7536994...</td> 
   <td>2</td> 
   <td>20121101</td> 
  </tr> 
  <tr> 
   <th>28</th> 
   <th>377550424</th> 
   <td>15771663914</td> 
   <td>50015841</td> 
   <td>1628665:3233941;1628665:3233942;3914866:11580;...</td> 
   <td>1</td> 
   <td>20121123</td> 
  </tr> 
  <tr> 
   <th>530850018</th> 
   <td>22058239899</td> 
   <td>50024147</td> 
   <td>21458:205007542;43307470:5543413;2339128:62147...</td> 
   <td>1</td> 
   <td>20140210</td> 
  </tr> 
  <tr> 
   <th>749507708</th> 
   <td>19171641742</td> 
   <td>50018860</td> 
   <td>21458:3602856;1628665:3233941;1628665:3233942;...</td> 
   <td>1</td> 
   <td>20140210</td> 
  </tr> 
  <tr> 
   <th>38</th> 
   <th>469517728</th> 
   <td>8232924597</td> 
   <td>211122</td> 
   <td>21458:21782;36786:42781029;13023102:6999219;22...</td> 
   <td>6</td> 
   <td>20140502</td> 
  </tr> 
  <tr> 
   <th>28</th> 
   <th>938309370</th> 
   <td>14149079479</td> 
   <td>50023669</td> 
   <td>21458:4204704;11820090:105550653;11644036:2861...</td> 
   <td>1</td> 
   <td>20120819</td> 
  </tr> 
  <tr> 
   <th>14466144</th> 
   <td>17610665576</td> 
   <td>50011993</td> 
   <td>21458:104000;21475:137325</td> 
   <td>1</td> 
   <td>20130327</td> 
  </tr> 
  <tr> 
   <th>38</th> 
   <th>177724549</th> 
   <td>14228645401</td> 
   <td>50018824</td> 
   <td>21475:108284;6933666:96059;33595:16453265;2145...</td> 
   <td>1</td> 
   <td>20130327</td> 
  </tr> 
  <tr> 
   <th>28</th> 
   <th>659020106</th> 
   <td>40484992676</td> 
   <td>50011993</td> 
   <td>21458:16162126;13023209:10551667;122218042:605...</td> 
   <td>1</td> 
   <td>20140813</td> 
  </tr> 
  <tr> 
   <th>18100946</th> 
   <td>38451267766</td> 
   <td>121540027</td> 
   <td>21458:215485914;125501489:19689726;11945782:78...</td> 
   <td>1</td> 
   <td>20140911</td> 
  </tr> 
  <tr> 
   <th>262519726</th> 
   <td>19051046285</td> 
   <td>121398041</td> 
   <td>11666049:40203;21458:3961150;17472269:13302841...</td> 
   <td>1</td> 
   <td>20140703</td> 
  </tr> 
  <tr> 
   <th>115566151</th> 
   <td>14778919435</td> 
   <td>50013187</td> 
   <td>1628665:3233938;1628665:29796;1628665:133527;1...</td> 
   <td>1</td> 
   <td>20140113</td> 
  </tr> 
  <tr> 
   <th>1714403831</th> 
   <td>22443564698</td> 
   <td>50014129</td> 
   <td>21458:57737100;12102318:7282254;11945782:78135...</td> 
   <td>1</td> 
   <td>20131106</td> 
  </tr> 
  <tr> 
   <th>38</th> 
   <th>1970876909</th> 
   <td>20197969079</td> 
   <td>211122</td> 
   <td>6940834:29865;21458:3270820;1629375:3253542;32...</td> 
   <td>1</td> 
   <td>20141017</td> 
  </tr> 
  <tr> 
   <th>28</th> 
   <th>113473924</th> 
   <td>15486726090</td> 
   <td>50014250</td> 
   <td>21458:30015090;1633959:43047819;1627584:28619;...</td> 
   <td>1</td> 
   <td>20120905</td> 
  </tr> 
  <tr> 
   <th>348660284</th> 
   <td>10896577394</td> 
   <td>50014250</td> 
   <td>1628665:29796;1628665:108579;1627584:11580;116...</td> 
   <td>1</td> 
   <td>20130525</td> 
  </tr> 
  <tr> 
   <th>38</th> 
   <th>151915451</th> 
   <td>17305821144</td> 
   <td>211122</td> 
   <td>21458:21782;36786:42781029;6933553:3313169;130...</td> 
   <td>2</td> 
   <td>20140104</td> 
  </tr> 
  <tr> 
   <th>28</th> 
   <th>745002413</th> 
   <td>36815797313</td> 
   <td>50023645</td> 
   <td>1628665:82340;21475:11488282;21458:56610575;49...</td> 
   <td>1</td> 
   <td>20140104</td> 
  </tr> 
  <tr> 
   <th>654037597</th> 
   <td>13775864723</td> 
   <td>50011993</td> 
   <td>21458:116116655;1633959:3276615;1628862:50276;...</td> 
   <td>1</td> 
   <td>20130513</td> 
  </tr> 
  <tr> 
   <th>38</th> 
   <th>277279277</th> 
   <td>18024521052</td> 
   <td>211122</td> 
   <td>21458:33516;33480:3238774;2653417:7353464;3359...</td> 
   <td>12</td> 
   <td>20130513</td> 
  </tr> 
  <tr> 
   <th>28</th> 
   <th>1721792494</th> 
   <td>36154660054</td> 
   <td>50008845</td> 
   <td>21458:3400531;5653832:7049425;13023209:7049425...</td> 
   <td>1</td> 
   <td>20140312</td> 
  </tr> 
  <tr> 
   <th>56549058</th> 
   <td>26930668292</td> 
   <td>50003700</td> 
   <td>21458:3351431;123273479:31526;1628665:3233941;...</td> 
   <td>1</td> 
   <td>20140312</td> 
  </tr> 
  <tr> 
   <th>696527486</th> 
   <td>37269469522</td> 
   <td>50011993</td> 
   <td>21458:118564374;13023209:547499553;122218042:3...</td> 
   <td>1</td> 
   <td>20140718</td> 
  </tr> 
  <tr> 
   <th>643153890</th> 
   <td>17954181229</td> 
   <td>50003700</td> 
   <td>123273479:41376163;21475:135183931;1628665:323...</td> 
   <td>1</td> 
   <td>20140718</td> 
  </tr> 
  <tr> 
   <th>1097191176</th> 
   <td>39095838474</td> 
   <td>50015841</td> 
   <td>1628665:3233941;1628665:3233942;1628665:323393...</td> 
   <td>1</td> 
   <td>20150203</td> 
  </tr> 
  <tr> 
   <th>669370290</th> 
   <td>40584762520</td> 
   <td>121540027</td> 
   <td>21458:215485914;125501489:598926205;11945782:7...</td> 
   <td>1</td> 
   <td>20141026</td> 
  </tr> 
  <tr> 
   <th>371153601</th> 
   <td>19893996909</td> 
   <td>251725</td> 
   <td>26318:52993;1627584:30689;11684888:104528258;2...</td> 
   <td>1</td> 
   <td>20121013</td> 
  </tr> 
  <tr> 
   <th>...</th> 
   <td>...</td> 
   <td>...</td> 
   <td>...</td> 
   <td>...</td> 
   <td>...</td> 
  </tr> 
  <tr> 
   <th>173729389</th> 
   <td>13926583575</td> 
   <td>50012474</td> 
   <td>21458:123482676;13023209:598168191;15673795:59...</td> 
   <td>1</td> 
   <td>20140501</td> 
  </tr> 
  <tr> 
   <th>129479111</th> 
   <td>39677605554</td> 
   <td>50013187</td> 
   <td>1628665:3233938;1628665:82340;1628665:29793;21...</td> 
   <td>2</td> 
   <td>20141229</td> 
  </tr> 
  <tr> 
   <th>788572516</th> 
   <td>18991044719</td> 
   <td>121466040</td> 
   <td>1628665:29784;1628665:29793;6907553:40595450;1...</td> 
   <td>2</td> 
   <td>20141129</td> 
  </tr> 
  <tr> 
   <th>1031198213</th> 
   <td>22309900818</td> 
   <td>50011993</td> 
   <td>21458:113001124;13023209:52138064;29183816:568...</td> 
   <td>1</td> 
   <td>20141129</td> 
  </tr> 
  <tr> 
   <th>860528533</th> 
   <td>14620263044</td> 
   <td>50011993</td> 
   <td>21458:115601688;1633959:138499706;1628862:3241...</td> 
   <td>1</td> 
   <td>20130925</td> 
  </tr> 
  <tr> 
   <th>1614610402</th> 
   <td>17337612618</td> 
   <td>50016010</td> 
   <td>21458:30992;3989588:31285;3998392:40870415;399...</td> 
   <td>1</td> 
   <td>20131223</td> 
  </tr> 
  <tr> 
   <th>696020002</th> 
   <td>23953356568</td> 
   <td>50011993</td> 
   <td>21458:34865735;1633959:3334315;1628862:33255;3...</td> 
   <td>1</td> 
   <td>20130523</td> 
  </tr> 
  <tr> 
   <th>626575260</th> 
   <td>38862644631</td> 
   <td>50011993</td> 
   <td>122218042:21671;21475:135183931;1628861:21671;...</td> 
   <td>2</td> 
   <td>20140625</td> 
  </tr> 
  <tr> 
   <th>2139869403</th> 
   <td>40339820434</td> 
   <td>50011993</td> 
   <td>122218042:117201278;21475:135183931;1628861:21...</td> 
   <td>1</td> 
   <td>20141210</td> 
  </tr> 
  <tr> 
   <th>49369084</th> 
   <td>37942071105</td> 
   <td>50013190</td> 
   <td>21458:211472372;44818245:223495732;1628665:323...</td> 
   <td>1</td> 
   <td>20140615</td> 
  </tr> 
  <tr> 
   <th>128447452</th> 
   <td>37728960808</td> 
   <td>50023670</td> 
   <td>21458:3482061;29121644:299472011;1628665:32339...</td> 
   <td>1</td> 
   <td>20140615</td> 
  </tr> 
  <tr> 
   <th>1768157748</th> 
   <td>24644048682</td> 
   <td>50024150</td> 
   <td>21458:8244918;122277639:14934675;11688815:2167...</td> 
   <td>2</td> 
   <td>20141115</td> 
  </tr> 
  <tr> 
   <th>45914430</th> 
   <td>16066366006</td> 
   <td>50014129</td> 
   <td>NaN</td> 
   <td>1</td> 
   <td>20130331</td> 
  </tr> 
  <tr> 
   <th>1372572</th> 
   <td>16915013171</td> 
   <td>50008845</td> 
   <td>21458:30992;1628665:3233941;1628665:3233942;16...</td> 
   <td>1</td> 
   <td>20130327</td> 
  </tr> 
  <tr> 
   <th>167288532</th> 
   <td>18836766607</td> 
   <td>50012788</td> 
   <td>2112993:29274;122277833:104377;1628665:3233941...</td> 
   <td>1</td> 
   <td>20140913</td> 
  </tr> 
  <tr> 
   <th>2183330808</th> 
   <td>19704833222</td> 
   <td>50011993</td> 
   <td>21458:119679150;13023209:4067094;122218042:216...</td> 
   <td>1</td> 
   <td>20140913</td> 
  </tr> 
  <tr> 
   <th>113446171</th> 
   <td>37455013590</td> 
   <td>50023663</td> 
   <td>11760865:6120557;21458:3314841;122277597:2054;...</td> 
   <td>1</td> 
   <td>20140831</td> 
  </tr> 
  <tr> 
   <th>522850986</th> 
   <td>18220260582</td> 
   <td>122678023</td> 
   <td>21458:91312408;138253785:653822445;135925585:1...</td> 
   <td>1</td> 
   <td>20140831</td> 
  </tr> 
  <tr> 
   <th>2212582093</th> 
   <td>16684531006</td> 
   <td>50006095</td> 
   <td>21458:3532516;8088680:4482698;1628665:3233941;...</td> 
   <td>1</td> 
   <td>20150205</td> 
  </tr> 
  <tr> 
   <th>326432534</th> 
   <td>40599422228</td> 
   <td>50012428</td> 
   <td>1628665:3233941;1628665:29790;1628665:3233936;...</td> 
   <td>1</td> 
   <td>20150110</td> 
  </tr> 
  <tr> 
   <th>2074613684</th> 
   <td>40798454555</td> 
   <td>203319</td> 
   <td>122217795:29277;134943792:29774;134943792:2977...</td> 
   <td>1</td> 
   <td>20141108</td> 
  </tr> 
  <tr> 
   <th>797710454</th> 
   <td>18176728510</td> 
   <td>50013177</td> 
   <td>1628665:3233941;1628665:3233942;1628665:323393...</td> 
   <td>1</td> 
   <td>20130107</td> 
  </tr> 
  <tr> 
   <th>68515755</th> 
   <td>13953276547</td> 
   <td>50012788</td> 
   <td>21458:12376977;2112993:32075;1628665:92012;162...</td> 
   <td>1</td> 
   <td>20130729</td> 
  </tr> 
  <tr> 
   <th>801784345</th> 
   <td>17629938386</td> 
   <td>50023670</td> 
   <td>21458:3550980;29154281:231350353;11684888:1045...</td> 
   <td>1</td> 
   <td>20130729</td> 
  </tr> 
  <tr> 
   <th>38</th> 
   <th>1957645413</th> 
   <td>36768778465</td> 
   <td>121448033</td> 
   <td>6940834:29865;1628149:137593;21475:114226;2275...</td> 
   <td>1</td> 
   <td>20140815</td> 
  </tr> 
  <tr> 
   <th>28</th> 
   <th>816394377</th> 
   <td>19835118833</td> 
   <td>50003700</td> 
   <td>24448:73774385;6725953:48332;22044:30715;80047...</td> 
   <td>1</td> 
   <td>20130912</td> 
  </tr> 
  <tr> 
   <th>164859586</th> 
   <td>15842319049</td> 
   <td>50012479</td> 
   <td>NaN</td> 
   <td>1</td> 
   <td>20130912</td> 
  </tr> 
  <tr> 
   <th>119149466</th> 
   <td>26396292642</td> 
   <td>50008875</td> 
   <td>21458:30992;11684888:104528258;21475:11488282;...</td> 
   <td>1</td> 
   <td>20130912</td> 
  </tr> 
  <tr> 
   <th>82915321</th> 
   <td>12766532512</td> 
   <td>50011993</td> 
   <td>21475:137325;1628665:3233937;1628665:29798;162...</td> 
   <td>1</td> 
   <td>20131008</td> 
  </tr> 
  <tr> 
   <th>758305789</th> 
   <td>20177445814</td> 
   <td>50018860</td> 
   <td>21458:3602856;1628665:29784;1628665:3233941;73...</td> 
   <td>1</td> 
   <td>20131008</td> 
  </tr> 
 </tbody> 
</table>

8166 rows × 5 columns