数据清洗之数据筛选

妖狐艹你老母 2023-07-25 08:43 27阅读 0赞

# 数据常用筛选方法 #

*  在数据中，选择需要的行或者列
 *  基础索引方式，就是直接引用
 *  ioc\[行索引名称或者条件，列索引名称或者标签\]
 *  iloc\[行索引位置，列索引位置\]

import pandas as pd
    import os
    import numpy as np

os.getcwd()

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

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

df = pd.read_csv('baby_trade_history.csv', encoding='utf-8', dtype={ 'user_id':str})

<table> 
 <thead> 
  <tr> 
   <th></th> 
   <th>user_id</th> 
   <th>auction_id</th> 
   <th>cat_id</th> 
   <th>cat1</th> 
   <th>property</th> 
   <th>buy_mount</th> 
   <th>day</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <th>0</th> 
   <td>786295544</td> 
   <td>41098319944</td> 
   <td>50014866</td> 
   <td>50022520</td> 
   <td>21458:86755362;13023209:3593274;10984217:21985...</td> 
   <td>2</td> 
   <td>20140919</td> 
  </tr> 
  <tr> 
   <th>1</th> 
   <td>532110457</td> 
   <td>17916191097</td> 
   <td>50011993</td> 
   <td>28</td> 
   <td>21458:11399317;1628862:3251296;21475:137325;16...</td> 
   <td>1</td> 
   <td>20131011</td> 
  </tr> 
  <tr> 
   <th>2</th> 
   <td>249013725</td> 
   <td>21896936223</td> 
   <td>50012461</td> 
   <td>50014815</td> 
   <td>21458:30992;1628665:92012;1628665:3233938;1628...</td> 
   <td>1</td> 
   <td>20131011</td> 
  </tr> 
  <tr> 
   <th>3</th> 
   <td>917056007</td> 
   <td>12515996043</td> 
   <td>50018831</td> 
   <td>50014815</td> 
   <td>21458:15841995;21956:3494076;27000458:59723383...</td> 
   <td>2</td> 
   <td>20141023</td> 
  </tr> 
  <tr> 
   <th>4</th> 
   <td>444069173</td> 
   <td>20487688075</td> 
   <td>50013636</td> 
   <td>50008168</td> 
   <td>21458:30992;13658074:3323064;1628665:3233941;1...</td> 
   <td>1</td> 
   <td>20141103</td> 
  </tr> 
  <tr> 
   <th>5</th> 
   <td>152298847</td> 
   <td>41840167463</td> 
   <td>121394024</td> 
   <td>50008168</td> 
   <td>21458:3408353;13023209:727117752;22009:2741771...</td> 
   <td>1</td> 
   <td>20141103</td> 
  </tr> 
  <tr> 
   <th>6</th> 
   <td>513441334</td> 
   <td>19909384116</td> 
   <td>50010557</td> 
   <td>50008168</td> 
   <td>25935:21991;1628665:29784;22019:34731;22019:20...</td> 
   <td>1</td> 
   <td>20121212</td> 
  </tr> 
  <tr> 
   <th>7</th> 
   <td>297411659</td> 
   <td>13540124907</td> 
   <td>50010542</td> 
   <td>50008168</td> 
   <td>21458:60020529;25935:31381;1633959:27247291;16...</td> 
   <td>1</td> 
   <td>20121212</td> 
  </tr> 
  <tr> 
   <th>8</th> 
   <td>82830661</td> 
   <td>19948600790</td> 
   <td>50013874</td> 
   <td>28</td> 
   <td>21458:11580;21475:137325</td> 
   <td>1</td> 
   <td>20121101</td> 
  </tr> 
  <tr> 
   <th>9</th> 
   <td>475046636</td> 
   <td>10368360710</td> 
   <td>203527</td> 
   <td>28</td> 
   <td>22724:40168;22729:40278;21458:21817;2770200:24...</td> 
   <td>1</td> 
   <td>20121101</td> 
  </tr> 
  <tr> 
   <th>10</th> 
   <td>734147966</td> 
   <td>15307958346</td> 
   <td>50018202</td> 
   <td>38</td> 
   <td>21458:3270827;7361532:28710594;7397093:7536994...</td> 
   <td>2</td> 
   <td>20121101</td> 
  </tr> 
  <tr> 
   <th>11</th> 
   <td>68547330</td> 
   <td>21162876126</td> 
   <td>50012365</td> 
   <td>122650008</td> 
   <td>1628665:3233941;1628665:3233942;1628665:323393...</td> 
   <td>1</td> 
   <td>20121123</td> 
  </tr> 
  <tr> 
   <th>12</th> 
   <td>697081418</td> 
   <td>15898050723</td> 
   <td>50013636</td> 
   <td>50008168</td> 
   <td>21458:19726868;1633959:179425852;13836282:1290...</td> 
   <td>1</td> 
   <td>20121123</td> 
  </tr> 
  <tr> 
   <th>13</th> 
   <td>377550424</td> 
   <td>15771663914</td> 
   <td>50015841</td> 
   <td>28</td> 
   <td>1628665:3233941;1628665:3233942;3914866:11580;...</td> 
   <td>1</td> 
   <td>20121123</td> 
  </tr> 
  <tr> 
   <th>14</th> 
   <td>88313935</td> 
   <td>22532727492</td> 
   <td>50013711</td> 
   <td>50008168</td> 
   <td>1628665:3233941;1628665:3233942;22019:3340598;...</td> 
   <td>1</td> 
   <td>20131005</td> 
  </tr> 
  <tr> 
   <th>15</th> 
   <td>25918750</td> 
   <td>16078389250</td> 
   <td>50012359</td> 
   <td>122650008</td> 
   <td>21458:3405407;1633959:6186201;1628366:32799;81...</td> 
   <td>1</td> 
   <td>20131005</td> 
  </tr> 
  <tr> 
   <th>16</th> 
   <td>350288528</td> 
   <td>35086271572</td> 
   <td>50010544</td> 
   <td>50008168</td> 
   <td>21458:61813;25935:21991;1628665:3233938;162866...</td> 
   <td>1</td> 
   <td>20131129</td> 
  </tr> 
  <tr> 
   <th>17</th> 
   <td>348090113</td> 
   <td>17436967558</td> 
   <td>50009540</td> 
   <td>50014815</td> 
   <td>21458:21910;3110425:30696849;2191928:75373546;...</td> 
   <td>1</td> 
   <td>20131129</td> 
  </tr> 
  <tr> 
   <th>18</th> 
   <td>1635282280</td> 
   <td>36153356431</td> 
   <td>50013207</td> 
   <td>50008168</td> 
   <td>1628665:29784;1628665:29799;2904342:31004;2201...</td> 
   <td>1</td> 
   <td>20131129</td> 
  </tr> 
  <tr> 
   <th>19</th> 
   <td>530850018</td> 
   <td>22058239899</td> 
   <td>50024147</td> 
   <td>28</td> 
   <td>21458:205007542;43307470:5543413;2339128:62147...</td> 
   <td>1</td> 
   <td>20140210</td> 
  </tr> 
  <tr> 
   <th>20</th> 
   <td>749507708</td> 
   <td>19171641742</td> 
   <td>50018860</td> 
   <td>28</td> 
   <td>21458:3602856;1628665:3233941;1628665:3233942;...</td> 
   <td>1</td> 
   <td>20140210</td> 
  </tr> 
  <tr> 
   <th>21</th> 
   <td>201088567</td> 
   <td>38564176352</td> 
   <td>50013207</td> 
   <td>50008168</td> 
   <td>1628665:3233941;1628665:3233942;1628665:323393...</td> 
   <td>1</td> 
   <td>20140502</td> 
  </tr> 
  <tr> 
   <th>22</th> 
   <td>469517728</td> 
   <td>8232924597</td> 
   <td>211122</td> 
   <td>38</td> 
   <td>21458:21782;36786:42781029;13023102:6999219;22...</td> 
   <td>6</td> 
   <td>20140502</td> 
  </tr> 
  <tr> 
   <th>23</th> 
   <td>691367866</td> 
   <td>17712372914</td> 
   <td>121434042</td> 
   <td>50014815</td> 
   <td>21458:49341152;8021059:5525523;6851452:1398669...</td> 
   <td>1</td> 
   <td>20140804</td> 
  </tr> 
  <tr> 
   <th>24</th> 
   <td>77193822</td> 
   <td>35537441586</td> 
   <td>50006520</td> 
   <td>50014815</td> 
   <td>22277:6262384;21458:30992;1628665:3233941;1628...</td> 
   <td>2</td> 
   <td>20140804</td> 
  </tr> 
  <tr> 
   <th>25</th> 
   <td>605678021</td> 
   <td>15502618744</td> 
   <td>50010555</td> 
   <td>50008168</td> 
   <td>25935:31381;1628665:3233941;1628665:3233942;16...</td> 
   <td>1</td> 
   <td>20130226</td> 
  </tr> 
  <tr> 
   <th>26</th> 
   <td>47702620</td> 
   <td>26481508332</td> 
   <td>121412034</td> 
   <td>50014815</td> 
   <td>21458:49341152;11057903:4036007;130475532:7537...</td> 
   <td>1</td> 
   <td>20140918</td> 
  </tr> 
  <tr> 
   <th>27</th> 
   <td>763560371</td> 
   <td>40945285800</td> 
   <td>50012365</td> 
   <td>122650008</td> 
   <td>21458:30992;1628665:3233939;22007:30338;22007:...</td> 
   <td>1</td> 
   <td>20150201</td> 
  </tr> 
  <tr> 
   <th>28</th> 
   <td>408028533</td> 
   <td>35838498718</td> 
   <td>50012442</td> 
   <td>50008168</td> 
   <td>21458:3596449;6811831:3446999;13023209:3446999...</td> 
   <td>1</td> 
   <td>20141009</td> 
  </tr> 
  <tr> 
   <th>29</th> 
   <td>53566371</td> 
   <td>27177784760</td> 
   <td>121394024</td> 
   <td>50008168</td> 
   <td>21458:42090508;1628665:3233941;1628665:3233942...</td> 
   <td>1</td> 
   <td>20141009</td> 
  </tr> 
  <tr> 
   <th>...</th> 
   <td>...</td> 
   <td>...</td> 
   <td>...</td> 
   <td>...</td> 
   <td>...</td> 
   <td>...</td> 
   <td>...</td> 
  </tr> 
  <tr> 
   <th>29941</th> 
   <td>413188001</td> 
   <td>16521677358</td> 
   <td>50012478</td> 
   <td>50014815</td> 
   <td>21458:28155;5434803:3636603;2815901:22583732;1...</td> 
   <td>1</td> 
   <td>20130107</td> 
  </tr> 
  <tr> 
   <th>29942</th> 
   <td>474062095</td> 
   <td>21129724585</td> 
   <td>50013207</td> 
   <td>50008168</td> 
   <td>21458:21599;1628665:29798;1628665:3233938;1628...</td> 
   <td>1</td> 
   <td>20130107</td> 
  </tr> 
  <tr> 
   <th>29943</th> 
   <td>797710454</td> 
   <td>18176728510</td> 
   <td>50013177</td> 
   <td>28</td> 
   <td>1628665:3233941;1628665:3233942;1628665:323393...</td> 
   <td>1</td> 
   <td>20130107</td> 
  </tr> 
  <tr> 
   <th>29944</th> 
   <td>1716505453</td> 
   <td>37844041565</td> 
   <td>50010555</td> 
   <td>50008168</td> 
   <td>21458:30992;25935:31381;1628665:3233941;162866...</td> 
   <td>1</td> 
   <td>20141231</td> 
  </tr> 
  <tr> 
   <th>29945</th> 
   <td>1966692323</td> 
   <td>42504930457</td> 
   <td>50012359</td> 
   <td>122650008</td> 
   <td>21458:3379652;1628665:3233940;1628665:3233938;...</td> 
   <td>1</td> 
   <td>20141231</td> 
  </tr> 
  <tr> 
   <th>29946</th> 
   <td>641734831</td> 
   <td>22105131076</td> 
   <td>50014277</td> 
   <td>50014815</td> 
   <td>21458:21906;13227811:51479;13230966:75369014;3...</td> 
   <td>2</td> 
   <td>20141016</td> 
  </tr> 
  <tr> 
   <th>29947</th> 
   <td>731030177</td> 
   <td>41666438142</td> 
   <td>121394024</td> 
   <td>50008168</td> 
   <td>21458:3443560;1628665:3233942;1628665:3233938;...</td> 
   <td>1</td> 
   <td>20141016</td> 
  </tr> 
  <tr> 
   <th>29948</th> 
   <td>68515755</td> 
   <td>13953276547</td> 
   <td>50012788</td> 
   <td>28</td> 
   <td>21458:12376977;2112993:32075;1628665:92012;162...</td> 
   <td>1</td> 
   <td>20130729</td> 
  </tr> 
  <tr> 
   <th>29949</th> 
   <td>180436843</td> 
   <td>23375100402</td> 
   <td>50012451</td> 
   <td>50008168</td> 
   <td>21458:33514;1633959:13343071;33030:29800;33162...</td> 
   <td>1</td> 
   <td>20130729</td> 
  </tr> 
  <tr> 
   <th>29950</th> 
   <td>801784345</td> 
   <td>17629938386</td> 
   <td>50023670</td> 
   <td>28</td> 
   <td>21458:3550980;29154281:231350353;11684888:1045...</td> 
   <td>1</td> 
   <td>20130729</td> 
  </tr> 
  <tr> 
   <th>29951</th> 
   <td>124458824</td> 
   <td>19739113764</td> 
   <td>50013636</td> 
   <td>50008168</td> 
   <td>21458:30992;13658074:9306734;1628665:3233941;1...</td> 
   <td>1</td> 
   <td>20140322</td> 
  </tr> 
  <tr> 
   <th>29952</th> 
   <td>602141957</td> 
   <td>37251457564</td> 
   <td>50012360</td> 
   <td>122650008</td> 
   <td>21458:21599;1628665:29798;1628665:82340;162866...</td> 
   <td>1</td> 
   <td>20140322</td> 
  </tr> 
  <tr> 
   <th>29953</th> 
   <td>595095853</td> 
   <td>41160643364</td> 
   <td>121364022</td> 
   <td>50008168</td> 
   <td>21458:80090256;1628665:29784;1628665:29796;162...</td> 
   <td>1</td> 
   <td>20150111</td> 
  </tr> 
  <tr> 
   <th>29954</th> 
   <td>1905258237</td> 
   <td>42298652641</td> 
   <td>121452056</td> 
   <td>50008168</td> 
   <td>21458:30992;1628665:3233942;1628665:31614;1628...</td> 
   <td>1</td> 
   <td>20150111</td> 
  </tr> 
  <tr> 
   <th>29955</th> 
   <td>1957645413</td> 
   <td>36768778465</td> 
   <td>121448033</td> 
   <td>38</td> 
   <td>6940834:29865;1628149:137593;21475:114226;2275...</td> 
   <td>1</td> 
   <td>20140815</td> 
  </tr> 
  <tr> 
   <th>29956</th> 
   <td>1854778218</td> 
   <td>37200665444</td> 
   <td>50012361</td> 
   <td>122650008</td> 
   <td>21458:3645338;13023209:544768204;122217803:309...</td> 
   <td>1</td> 
   <td>20140815</td> 
  </tr> 
  <tr> 
   <th>29957</th> 
   <td>268356658</td> 
   <td>36932456353</td> 
   <td>50010236</td> 
   <td>50014815</td> 
   <td>21458:10513072;12474507:706291650;3091143:9208...</td> 
   <td>1</td> 
   <td>20141027</td> 
  </tr> 
  <tr> 
   <th>29958</th> 
   <td>196272909</td> 
   <td>10066997901</td> 
   <td>50009540</td> 
   <td>50014815</td> 
   <td>21458:21906;13229910:32056435;2191928:73664723...</td> 
   <td>1</td> 
   <td>20141104</td> 
  </tr> 
  <tr> 
   <th>29959</th> 
   <td>23473499</td> 
   <td>38019470815</td> 
   <td>50010236</td> 
   <td>50014815</td> 
   <td>1628665:61550;1628665:3233940;1628665:3233936;...</td> 
   <td>1</td> 
   <td>20141104</td> 
  </tr> 
  <tr> 
   <th>29960</th> 
   <td>816394377</td> 
   <td>19835118833</td> 
   <td>50003700</td> 
   <td>28</td> 
   <td>24448:73774385;6725953:48332;22044:30715;80047...</td> 
   <td>1</td> 
   <td>20130912</td> 
  </tr> 
  <tr> 
   <th>29961</th> 
   <td>164859586</td> 
   <td>15842319049</td> 
   <td>50012479</td> 
   <td>28</td> 
   <td>NaN</td> 
   <td>1</td> 
   <td>20130912</td> 
  </tr> 
  <tr> 
   <th>29962</th> 
   <td>119149466</td> 
   <td>26396292642</td> 
   <td>50008875</td> 
   <td>28</td> 
   <td>21458:30992;11684888:104528258;21475:11488282;...</td> 
   <td>1</td> 
   <td>20130912</td> 
  </tr> 
  <tr> 
   <th>29963</th> 
   <td>704655047</td> 
   <td>10506866020</td> 
   <td>50007011</td> 
   <td>50008168</td> 
   <td>1628665:3233941;1628665:3233942;1628665:323393...</td> 
   <td>1</td> 
   <td>20121206</td> 
  </tr> 
  <tr> 
   <th>29964</th> 
   <td>45662429</td> 
   <td>20745380642</td> 
   <td>50010555</td> 
   <td>50008168</td> 
   <td>25935:31381;1628665:3233941;1628665:3233942;16...</td> 
   <td>1</td> 
   <td>20121206</td> 
  </tr> 
  <tr> 
   <th>29965</th> 
   <td>35711492</td> 
   <td>16563353438</td> 
   <td>50010544</td> 
   <td>50008168</td> 
   <td>21458:11580;25935:21991;1628665:92012;1628665:...</td> 
   <td>1</td> 
   <td>20121206</td> 
  </tr> 
  <tr> 
   <th>29966</th> 
   <td>57747284</td> 
   <td>35169635909</td> 
   <td>50010549</td> 
   <td>50008168</td> 
   <td>21458:125202070;22019:3228688;22019:3248884;22...</td> 
   <td>1</td> 
   <td>20140109</td> 
  </tr> 
  <tr> 
   <th>29967</th> 
   <td>287541325</td> 
   <td>19778523000</td> 
   <td>50007011</td> 
   <td>50008168</td> 
   <td>21458:112788583;1633959:3523439;3130834:209537...</td> 
   <td>2</td> 
   <td>20140109</td> 
  </tr> 
  <tr> 
   <th>29968</th> 
   <td>82915321</td> 
   <td>12766532512</td> 
   <td>50011993</td> 
   <td>28</td> 
   <td>21475:137325;1628665:3233937;1628665:29798;162...</td> 
   <td>1</td> 
   <td>20131008</td> 
  </tr> 
  <tr> 
   <th>29969</th> 
   <td>78259523</td> 
   <td>18309305134</td> 
   <td>50013711</td> 
   <td>50008168</td> 
   <td>21458:30992;1628665:29778;1628665:29793;163395...</td> 
   <td>1</td> 
   <td>20131008</td> 
  </tr> 
  <tr> 
   <th>29970</th> 
   <td>758305789</td> 
   <td>20177445814</td> 
   <td>50018860</td> 
   <td>28</td> 
   <td>21458:3602856;1628665:29784;1628665:3233941;73...</td> 
   <td>1</td> 
   <td>20131008</td> 
  </tr> 
 </tbody> 
</table>

29971 rows × 7 columns

df.info()

<class 'pandas.core.frame.DataFrame'>
    RangeIndex: 29971 entries, 0 to 29970
    Data columns (total 7 columns):
    user_id       29971 non-null object
    auction_id    29971 non-null int64
    cat_id        29971 non-null int64
    cat1          29971 non-null int64
    property      29827 non-null object
    buy_mount     29971 non-null int64
    day           29971 non-null int64
    dtypes: int64(5), object(2)
    memory usage: 1.6+ MB

df.head(10)

df.columns  # 查看数据字段

Index(['user_id', 'auction_id', 'cat_id', 'cat1', 'property', 'buy_mount',
           'day'],
          dtype='object')

df['user_id'].head(5)  # 相当于嵌套列表

0    786295544
    1    532110457
    2    249013725
    3    917056007
    4    444069173
    Name: user_id, dtype: object

df[['user_id', 'cat1']].head(5)

df[['user_id', 'cat1']][1:5]  # 分片选择

df.loc[3:4]  # 定位标签，不是位置

# 行不做限制
    # 列标签为user_id 和 buiy_mount 字段
    df.loc[:,['user_id','buy_mount']].head(5)

<table> 
 <thead> 
  <tr> 
   <th></th> 
   <th>user_id</th> 
   <th>buy_mount</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <th>0</th> 
   <td>786295544</td> 
   <td>2</td> 
  </tr> 
  <tr> 
   <th>1</th> 
   <td>532110457</td> 
   <td>1</td> 
  </tr> 
  <tr> 
   <th>2</th> 
   <td>249013725</td> 
   <td>1</td> 
  </tr> 
  <tr> 
   <th>3</th> 
   <td>917056007</td> 
   <td>2</td> 
  </tr> 
  <tr> 
   <th>4</th> 
   <td>444069173</td> 
   <td>1</td> 
  </tr> 
 </tbody> 
</table>

# 行标签为1到3，列标签为user_id 和 buy_mount 字段
    df.loc[1:3, ['user_id','buy_mount']]

<table> 
 <thead> 
  <tr> 
   <th></th> 
   <th>user_id</th> 
   <th>buy_mount</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <th>1</th> 
   <td>532110457</td> 
   <td>1</td> 
  </tr> 
  <tr> 
   <th>2</th> 
   <td>249013725</td> 
   <td>1</td> 
  </tr> 
  <tr> 
   <th>3</th> 
   <td>917056007</td> 
   <td>2</td> 
  </tr> 
 </tbody> 
</table>

df.loc[df.user_id=='249013725', ['user_id', 'buy_mount']]  # 条件筛选

<table> 
 <thead> 
  <tr> 
   <th></th> 
   <th>user_id</th> 
   <th>buy_mount</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <th>2</th> 
   <td>249013725</td> 
   <td>1</td> 
  </tr> 
 </tbody> 
</table>

df.loc[(df.user_id=='249013725') | (df.buy_mount>=1000), ['user_id', 'buy_mount']]  # 条件取值

<table> 
 <thead> 
  <tr> 
   <th></th> 
   <th>user_id</th> 
   <th>buy_mount</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <th>2</th> 
   <td>249013725</td> 
   <td>1</td> 
  </tr> 
  <tr> 
   <th>1164</th> 
   <td>1945590674</td> 
   <td>1500</td> 
  </tr> 
  <tr> 
   <th>5536</th> 
   <td>2288344467</td> 
   <td>10000</td> 
  </tr> 
  <tr> 
   <th>6627</th> 
   <td>117730165</td> 
   <td>2800</td> 
  </tr> 
  <tr> 
   <th>10402</th> 
   <td>32141414</td> 
   <td>1000</td> 
  </tr> 
  <tr> 
   <th>25675</th> 
   <td>173701616</td> 
   <td>2748</td> 
  </tr> 
 </tbody> 
</table>

# loc选择的是标签，iloc选择的是位置
    df.iloc[1:3]

df.iloc[1:3, 1:4]

<table> 
 <thead> 
  <tr> 
   <th></th> 
   <th>auction_id</th> 
   <th>cat_id</th> 
   <th>cat1</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <th>1</th> 
   <td>17916191097</td> 
   <td>50011993</td> 
   <td>28</td> 
  </tr> 
  <tr> 
   <th>2</th> 
   <td>21896936223</td> 
   <td>50012461</td> 
   <td>50014815</td> 
  </tr> 
 </tbody> 
</table>

df.iloc[:, [0, 2]].head(5)

# 选择第二行和第十一行，第1列和第三列
    df.iloc[[1,10], [0,2]]

>  l o c 按 标 签 选 择 ， i l o c 按 顺 序 选 择 \\color\{red\}loc按标签选择，iloc按顺序选择 loc按标签选择，iloc按顺序选择