پاکسازی داده (Data Cleaning) در پایتون با استفاده از NumPy و Pandas — راهنمای جامع

1>>> import pandas as pd
2>>> import numpy as np

1>>> df = pd.read_csv('Datasets/BL-Flickr-Images-Book.csv')
2>>> df.head()
3
4    Identifier             Edition Statement      Place of Publication  \
50         206                           NaN                    London
61         216                           NaN  London; Virtue & Yorston
72         218                           NaN                    London
83         472                           NaN                    London
94         480  A new edition, revised, etc.                    London
10
11  Date of Publication              Publisher  \
120         1879 [1878]       S. Tinsley & Co.
131                1868           Virtue & Co.
142                1869  Bradbury, Evans & Co.
153                1851          James Darling
164                1857   Wertheim & Macintosh
17
18                                               Title     Author  \
190                  Walter Forbes. [A novel.] By A. A      A. A.
201  All for Greed. [A novel. The dedication signed...  A., A. A.
212  Love the Avenger. By the author of “All for Gr...  A., A. A.
223  Welsh Sketches, chiefly ecclesiastical, to the...  A., E. S.
234  [The World in which I live, and my place in it...  A., E. S.
24
25                                   Contributors  Corporate Author  \
260                               FORBES, Walter.               NaN
271  BLAZE DE BURY, Marie Pauline Rose - Baroness               NaN
282  BLAZE DE BURY, Marie Pauline Rose - Baroness               NaN
293                   Appleyard, Ernest Silvanus.               NaN
304                           BROOME, John Henry.               NaN
31
32   Corporate Contributors Former owner  Engraver Issuance type  \
330                     NaN          NaN       NaN   monographic
341                     NaN          NaN       NaN   monographic
352                     NaN          NaN       NaN   monographic
363                     NaN          NaN       NaN   monographic
374                     NaN          NaN       NaN   monographic
38
39                                          Flickr URL  \
400  http://www.flickr.com/photos/britishlibrary/ta...
411  http://www.flickr.com/photos/britishlibrary/ta...
422  http://www.flickr.com/photos/britishlibrary/ta...
433  http://www.flickr.com/photos/britishlibrary/ta...
444  http://www.flickr.com/photos/britishlibrary/ta...
45
46                            Shelfmarks
470    British Library HMNTS 12641.b.30.
481    British Library HMNTS 12626.cc.2.
492    British Library HMNTS 12625.dd.1.
503    British Library HMNTS 10369.bbb.15.
514    British Library HMNTS 9007.d.28.

1>>> to_drop = ['Edition Statement',
2...            'Corporate Author',
3...            'Corporate Contributors',
4...            'Former owner',
5...            'Engraver',
6...            'Contributors',
7...            'Issuance type',
8...            'Shelfmarks']
9
10>>> df.drop(to_drop, inplace=True, axis=1)

1>>> df.head()
2   Identifier      Place of Publication Date of Publication  \
30         206                    London         1879 [1878]
41         216  London; Virtue & Yorston                1868
52         218                    London                1869
63         472                    London                1851
74         480                    London                1857
8
9               Publisher                                              Title  \
100       S. Tinsley & Co.                  Walter Forbes. [A novel.] By A. A
111           Virtue & Co.  All for Greed. [A novel. The dedication signed...
122  Bradbury, Evans & Co.  Love the Avenger. By the author of “All for Gr...
133          James Darling  Welsh Sketches, chiefly ecclesiastical, to the...
144   Wertheim & Macintosh  [The World in which I live, and my place in it...
15
16      Author                                         Flickr URL
170      A. A.  http://www.flickr.com/photos/britishlibrary/ta...
181  A., A. A.  http://www.flickr.com/photos/britishlibrary/ta...
192  A., A. A.  http://www.flickr.com/photos/britishlibrary/ta...
203  A., E. S.  http://www.flickr.com/photos/britishlibrary/ta...
214  A., E. S.  http://www.flickr.com/photos/britishlibrary/ta...

1>>> df.drop(columns=to_drop, inplace=True)

1>>> df['Identifier'].is_unique
2True

1>>> df = df.set_index('Identifier')
2>>> df.head()
3                Place of Publication Date of Publication  \
4206                           London         1879 [1878]
5216         London; Virtue & Yorston                1868
6218                           London                1869
7472                           London                1851
8480                           London                1857
9
10                        Publisher  \
11206              S. Tinsley & Co.
12216                  Virtue & Co.
13218         Bradbury, Evans & Co.
14472                 James Darling
15480          Wertheim & Macintosh
16
17                                                        Title     Author  \
18206                         Walter Forbes. [A novel.] By A. A      A. A.
19216         All for Greed. [A novel. The dedication signed...  A., A. A.
20218         Love the Avenger. By the author of “All for Gr...  A., A. A.
21472         Welsh Sketches, chiefly ecclesiastical, to the...  A., E. S.
22480         [The World in which I live, and my place in it...  A., E. S.
23
24                                                   Flickr URL
25206         http://www.flickr.com/photos/britishlibrary/ta...
26216         http://www.flickr.com/photos/britishlibrary/ta...
27218         http://www.flickr.com/photos/britishlibrary/ta...
28472         http://www.flickr.com/photos/britishlibrary/ta...
29480         http://www.flickr.com/photos/britishlibrary/ta...

1>>> df.loc[206]
2Place of Publication                                               London
3Date of Publication                                           1879 [1878]
4Publisher                                                S. Tinsley & Co.
5Title                                   Walter Forbes. [A novel.] By A. A
6Author                                                              A. A.
7Flickr URL              http://www.flickr.com/photos/britishlibrary/ta...
8Name: 206, dtype: object

1df.set_index('Identifier', inplace=True)

1>>> df.get_dtype_counts()
2object    6

1>>> df.loc[1905:, 'Date of Publication'].head(10)
2Identifier
31905           1888
41929    1839, 38-54
52836        [1897?]
62854           1865
72956        1860-63
82957           1873
93017           1866
103131           1899
114598           1814
124884           1820
13Name: Date of Publication, dtype: object

1regex = r'^(\d{4})'

1>>> extr = df['Date of Publication'].str.extract(r'^(\d{4})', expand=False)
2>>> extr.head()
3Identifier
4206    1879
5216    1868
6218    1869
7472    1851
8480    1857
9Name: Date of Publication, dtype: object

1>>> df['Date of Publication'] = pd.to_numeric(extr)
2>>> df['Date of Publication'].dtype
3dtype('float64')

1>>> df['Date of Publication'].isnull().sum() / len(df)
20.11717147339205986

1>>> np.where(condition, then, else)

1>>> np.where(condition1, x1, 
2        np.where(condition2, x2, 
3            np.where(condition3, x3, ...)))

1>>> df['Place of Publication'].head(10)
2Identifier
3206                                  London
4216                London; Virtue & Yorston
5218                                  London
6472                                  London
7480                                  London
8481                                  London
9519                                  London
10667     pp. 40. G. Bryan & Co: Oxford, 1898
11874                                 London]
121143                                 London
13Name: Place of Publication, dtype: object

1>>> df.loc[4157862]
2Place of Publication                                  Newcastle-upon-Tyne
3Date of Publication                                                  1867
4Publisher                                                      T. Fordyce
5Title                   Local Records; or, Historical Register of rema...
6Author                                                        T.  Fordyce
7Flickr URL              http://www.flickr.com/photos/britishlibrary/ta...
8Name: 4157862, dtype: object
9
10>>> df.loc[4159587]
11Place of Publication                                  Newcastle upon Tyne
12Date of Publication                                                  1834
13Publisher                                                Mackenzie & Dent
14Title                   An historical, topographical and descriptive v...
15Author                                               E. (Eneas) Mackenzie
16Flickr URL              http://www.flickr.com/photos/britishlibrary/ta...
17Name: 4159587, dtype: object

1>>> pub = df['Place of Publication']
2>>> london = pub.str.contains('London')
3>>> london[:5]
4Identifier
5206    True
6216    True
7218    True
8472    True
9480    True
10Name: Place of Publication, dtype: bool
11
12>>> oxford = pub.str.contains('Oxford')

1df['Place of Publication'] = np.where(london, 'London',
2                                      np.where(oxford, 'Oxford',
3                                               pub.str.replace('-', ' ')))
4
5>>> df['Place of Publication'].head()
6Identifier
7206    London
8216    London
9218    London
10472    London
11480    London
12Name: Place of Publication, dtype: object

1>>> df.head()
2           Place of Publication Date of Publication              Publisher  \
3206                      London                1879        S. Tinsley & Co.
4216                      London                1868           Virtue & Co.
5218                      London                1869  Bradbury, Evans & Co.
6472                      London                1851          James Darling
7480                      London                1857   Wertheim & Macintosh
8
9                                                        Title    Author  \
10206                         Walter Forbes. [A novel.] By A. A        AA
11216         All for Greed. [A novel. The dedication signed...   A. A A.
12218         Love the Avenger. By the author of “All for Gr...   A. A A.
13472         Welsh Sketches, chiefly ecclesiastical, to the...   E. S A.
14480         [The World in which I live, and my place in it...   E. S A.
15
16                                                   Flickr URL
17206         http://www.flickr.com/photos/britishlibrary/ta...
18216         http://www.flickr.com/photos/britishlibrary/ta...
19218         http://www.flickr.com/photos/britishlibrary/ta...
20472         http://www.flickr.com/photos/britishlibrary/ta...
21480         http://www.flickr.com/photos/britishlibrary/ta...

1$ head Datasets/univerisity_towns.txt
2Alabama[edit]
3Auburn (Auburn University)[1]
4Florence (University of North Alabama)
5Jacksonville (Jacksonville State University)[2]
6Livingston (University of West Alabama)[2]
7Montevallo (University of Montevallo)[2]
8Troy (Troy University)[2]
9Tuscaloosa (University of Alabama, Stillman College, Shelton State)[3][4]
10Tuskegee (Tuskegee University)[5]
11Alaska[edit]

1>>> university_towns = []
2>>> with open('Datasets/university_towns.txt') as file:
3...     for line in file:
4...         if '[edit]' in line:
5...             # Remember this `state` until the next is found
6...             state = line
7...         else:
8...             # Otherwise, we have a city; keep `state` as last-seen
9...             university_towns.append((state, line))
10
11>>> university_towns[:5]
12[('Alabama[edit]\n', 'Auburn (Auburn University)[1]\n'),
13 ('Alabama[edit]\n', 'Florence (University of North Alabama)\n'),
14 ('Alabama[edit]\n', 'Jacksonville (Jacksonville State University)[2]\n'),
15 ('Alabama[edit]\n', 'Livingston (University of West Alabama)[2]\n'),
16 ('Alabama[edit]\n', 'Montevallo (University of Montevallo)[2]\n')]

1>>> towns_df = pd.DataFrame(university_towns,
2...                         columns=['State', 'RegionName'])
3
4>>> towns_df.head()
5 State                                         RegionName
60  Alabama[edit]\n                    Auburn (Auburn University)[1]\n
71  Alabama[edit]\n           Florence (University of North Alabama)\n
82  Alabama[edit]\n  Jacksonville (Jacksonville State University)[2]\n
93  Alabama[edit]\n       Livingston (University of West Alabama)[2]\n
104  Alabama[edit]\n         Montevallo (University of Montevallo)[2]

1        0           1
20    Mock     Dataset
31  Python     Pandas
42    Real     Python
53   NumPy     Clean

1>>> def get_citystate(item):
2...     if ' (' in item:
3...         return item[:item.find(' (')]
4...     elif '[' in item:
5...         return item[:item.find('[')]
6...     else:
7...         return item

1>>> towns_df =  towns_df.applymap(get_citystate)

1>>> towns_df.head()
2     State    RegionName
30  Alabama        Auburn
41  Alabama      Florence
52  Alabama  Jacksonville
63  Alabama    Livingston
74  Alabama    Montevallo

1$ head -n 5 Datasets/olympics.csv
20,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15
3,? Summer,01 !,02 !,03 !,Total,? Winter,01 !,02 !,03 !,Total,? Games,01 !,02 !,03 !,Combined total
4Afghanistan (AFG),13,0,0,2,2,0,0,0,0,0,13,0,0,2,2
5Algeria (ALG),12,5,2,8,15,3,0,0,0,0,15,5,2,8,15
6Argentina (ARG),23,18,24,28,70,18,0,0,0,0,41,18,24,28,70

1>>> olympics_df = pd.read_csv('Datasets/olympics.csv')
2>>> olympics_df.head()
3                   0         1     2     3     4      5         6     7     8  \
40                NaN  ? Summer  01 !  02 !  03 !  Total  ? Winter  01 !  02 !
51  Afghanistan (AFG)        13     0     0     2      2         0     0     0
62      Algeria (ALG)        12     5     2     8     15         3     0     0
73    Argentina (ARG)        23    18    24    28     70        18     0     0
84      Armenia (ARM)         5     1     2     9     12         6     0     0
9
10      9     10       11    12    13    14              15
110  03 !  Total  ? Games  01 !  02 !  03 !  Combined total
121     0      0       13     0     0     2               2
132     0      0       15     5     2     8              15
143     0      0       41    18    24    28              70
154     0      0       11     1     2     9              12

1>>> olympics_df = pd.read_csv('Datasets/olympics.csv', header=1)
2>>> olympics_df.head()
3          Unnamed: 0  ? Summer  01 !  02 !  03 !  Total  ? Winter  \
40        Afghanistan (AFG)        13     0     0     2      2         0
51            Algeria (ALG)        12     5     2     8     15         3
62          Argentina (ARG)        23    18    24    28     70        18
73            Armenia (ARM)         5     1     2     9     12         6
84  Australasia (ANZ) [ANZ]         2     3     4     5     12         0
9
10   01 !.1  02 !.1  03 !.1  Total.1  ? Games  01 !.2  02 !.2  03 !.2  \
110       0       0       0        0       13       0       0       2
121       0       0       0        0       15       5       2       8
132       0       0       0        0       41      18      24      28
143       0       0       0        0       11       1       2       9
154       0       0       0        0        2       3       4       5
16
17   Combined total
180               2
191              15
202              70
213              12
224              12

1>>> new_names =  {'Unnamed: 0': 'Country',
2...               '? Summer': 'Summer Olympics',
3...               '01 !': 'Gold',
4...               '02 !': 'Silver',
5...               '03 !': 'Bronze',
6...               '? Winter': 'Winter Olympics',
7...               '01 !.1': 'Gold.1',
8...               '02 !.1': 'Silver.1',
9...               '03 !.1': 'Bronze.1',
10...               '? Games': '# Games',
11...               '01 !.2': 'Gold.2',
12...               '02 !.2': 'Silver.2',
13...               '03 !.2': 'Bronze.2'}

1>>> olympics_df.rename(columns=new_names, inplace=True)

1>>> olympics_df.head()
2                   Country  Summer Olympics  Gold  Silver  Bronze  Total  \
30        Afghanistan (AFG)               13     0       0       2      2
41            Algeria (ALG)               12     5       2       8     15
52          Argentina (ARG)               23    18      24      28     70
63            Armenia (ARM)                5     1       2       9     12
74  Australasia (ANZ) [ANZ]                2     3       4       5     12
8
9   Winter Olympics  Gold.1  Silver.1  Bronze.1  Total.1  # Games  Gold.2  \
100                0       0         0         0        0       13       0
111                3       0         0         0        0       15       5
122               18       0         0         0        0       41      18
133                6       0         0         0        0       11       1
144                0       0         0         0        0        2       3
15
16   Silver.2  Bronze.2  Combined total
170         0         2               2
181         2         8              15
192        24        28              70
203         2         9              12
214         4         5              12