04 数据分析与挖掘练习题

egon新书python全套来袭请看：https://egonlin.com/book.html

1.0 背景

该数据集是澳大利亚某公司无人机送货的记录（2018年8月之前），主要包括以下的列：

  • 'Id' ： 记录的ID
  • 'Drone Type' ： 无人机的类别分 1类 2类 3类
  • 'Post Type' ： 运送的类别 0为普通运送 1为速运
  • 'Package Weight' ：包裹的重量
  • 'Origin Region' ：出发地的区域代码
  • 'Destination Region' ：目的地的区域代码
  • 'Origin Latitude' ：出发纬度
  • 'Origin Longitude' ：出发经度
  • 'Destination Latitude' ：目的地纬度
  • 'Destination Longitude' ：目的地经度
  • 'Journey Distance' ：运送距离
  • 'Departure Date' ：出发日期
  • 'Departure Time' ：出发时间
  • 'Travel Time' ：飞行时间
  • 'Delivery Time' ：到达时间
  • 'Delivery Fare' ：运送费用
pd.options.display.max_rows = 10

2.0 载入包和数据

#loading library
import pandas as pd
import re
import matplotlib.pyplot as plt
#import seaborn as sns  !pip intall seaborn
import scipy.stats as st
import numpy as np
import math
from math import *
from datetime import datetime,timedelta

任务1：载入名为‘data.csv’的数据

data = pd.read_csv('data.csv')

DataFrame
Series

type(data)

pandas.core.frame.DataFrame

3.0 数据初步探索

任务2：找出数据有多少行列

data.shape

(37903, 16)

任务3：查看列的统计信息

提示：describe()

data.describe()
Drone Type Post Type Package Weight Origin Region Destination Region Origin Latitude Origin Longitude Destination Latitude Destination Longitude Journey Distance Travel Time Delivery Fare
count 37893.000000 37883.000000 37903.000000 37893.000000 37893.000000 37903.000000 37903.000000 37903.000000 37903.000000 37903.000000 37863.000000 37874.000000
mean 1.699285 0.298709 25.669901 20.476684 20.452722 -37.728867 145.423058 -37.722054 145.434035 221.954150 208.794518 126.814976
std 0.779845 0.457698 12.107150 11.501110 11.509311 1.899183 6.923993 1.895621 6.909055 116.604355 107.612447 59.314445
min 1.000000 0.000000 5.001000 1.000000 1.000000 -39.006941 -148.337157 -39.006941 -147.691902 0.664000 7.420000 54.020000
25% 1.000000 0.000000 15.199000 11.000000 11.000000 -38.443034 143.965002 -38.431293 143.951543 131.044500 125.165000 97.440000
50% 2.000000 0.000000 25.446000 20.000000 20.000000 -37.707244 145.423386 -37.700695 145.450794 209.796000 196.370000 120.045000
75% 2.000000 1.000000 35.953500 30.000000 30.000000 -37.094433 147.170334 -37.080256 147.216886 302.052000 281.250000 145.800000
max 3.000000 1.000000 55.992000 40.000000 40.000000 38.986998 148.450576 38.989473 148.450576 556.637000 545.460000 1217.690000

任务4：找出每个列名称

data.columns


Index(['Id', 'Drone Type', 'Post Type', 'Package Weight', 'Origin Region',
       'Destination Region', 'Origin Latitude', 'Origin Longitude',
       'Destination Latitude', 'Destination Longitude', 'Journey Distance',
       'Departure Date', 'Departure Time', 'Travel Time', 'Delivery Time',
       'Delivery Fare'],
      dtype='object')

任务5：找出每个列的属性

是Obeject 还是 float

data.info()
data['Drone Type'] = data['Drone Type'].astype('str')


<class 'pandas.core.frame.DataFrame'>
RangeIndex: 37903 entries, 0 to 37902
Data columns (total 16 columns):
Id                       37878 non-null object
Drone Type               37893 non-null float64
Post Type                37883 non-null float64
Package Weight           37903 non-null float64
Origin Region            37893 non-null float64
Destination Region       37893 non-null float64
Origin Latitude          37903 non-null float64
Origin Longitude         37903 non-null float64
Destination Latitude     37903 non-null float64
Destination Longitude    37903 non-null float64
Journey Distance         37903 non-null float64
Departure Date           37903 non-null object
Departure Time           37903 non-null object
Travel Time              37863 non-null float64
Delivery Time            37903 non-null object
Delivery Fare            37874 non-null float64
dtypes: float64(12), object(4)
memory usage: 4.6+ MB

data.info()


<class 'pandas.core.frame.DataFrame'>
RangeIndex: 37903 entries, 0 to 37902
Data columns (total 16 columns):
Id                       37878 non-null object
Drone Type               37903 non-null object
Post Type                37883 non-null float64
Package Weight           37903 non-null float64
Origin Region            37893 non-null float64
Destination Region       37893 non-null float64
Origin Latitude          37903 non-null float64
Origin Longitude         37903 non-null float64
Destination Latitude     37903 non-null float64
Destination Longitude    37903 non-null float64
Journey Distance         37903 non-null float64
Departure Date           37903 non-null object
Departure Time           37903 non-null object
Travel Time              37863 non-null float64
Delivery Time            37903 non-null object
Delivery Fare            37874 non-null float64
dtypes: float64(11), object(5)
memory usage: 4.6+ MB

任务6：找出数据的前5行和后5行

data.head()
Id Drone Type Post Type Package Weight Origin Region Destination Region Origin Latitude Origin Longitude Destination Latitude Destination Longitude Journey Distance Departure Date Departure Time Travel Time Delivery Time Delivery Fare
0 ID1645282128 2.0 0.0 21.686 19.0 38.0 -37.089338 144.429529 -37.639134 142.891391 149.212 2018-01-16 09:38:17 140.19 11:58:28 99.25
1 ID1697620764 nan 0.0 39.075 15.0 15.0 -38.481935 146.009567 -38.585528 146.199827 20.185 2018-02-10 04:28:17 22.84 4:51:07 149.04
2 ID1543933503 2.0 0.0 7.243 33.0 28.0 -38.754167 144.509664 -38.242224 147.855342 296.975 2018-05-05 01:38:03 272.52 6:10:34 141.48
3 ID1756517608 2.0 0.0 13.383 10.0 38.0 -37.240526 147.568019 -37.687178 142.991188 407.396 2018-06-11 11:43:04 371.40 17:54:27 122.82
4 ID1832325834 2.0 0.0 8.123 1.0 8.0 -38.143985 143.798292 -38.548315 144.769228 95.974 2018-03-16 14:50:25 92.51 16:22:55 111.97
5 ID1802448576 2.0 0.0 32.859 2.0 28.0 -37.421211 148.044072 -38.159627 148.194048 83.250 2018-05-15 16:35:50 81.12 17:56:57 113.88
6 ID1940231408 1.0 0.0 20.616 29.0 36.0 -37.173949 143.140662 -37.021605 145.197043 183.363 2018-04-01 19:31:12 184.22 22:35:25 85.60
7 ID1299303958 2.0 0.0 44.577 36.0 31.0 -37.123190 145.236196 -37.667199 143.877650 134.543 2018-05-01 18:39:36 127.05 20:46:38 114.22
8 ID1752722028 1.0 0.0 15.363 20.0 30.0 -38.850561 148.317253 -38.024914 144.823938 318.132 2018-05-27 14:48:17 314.64 20:02:55 87.39
9 ID5995243590 1.0 1.0 36.190 18.0 28.0 -38.070189 142.950207 -37.996817 148.026520 445.106 2018-06-17 12:53:02 437.52 20:10:33 142.95 
data.tail()
Id Drone Type Post Type Package Weight Origin Region Destination Region Origin Latitude Origin Longitude Destination Latitude Destination Longitude Journey Distance Departure Date Departure Time Travel Time Delivery Time Delivery Fare
37898 NaN 3.0 1.0 27.153 39.0 16.0 -38.446310 148.292498 -36.739777 143.604529 454.968 2018-07-23 08:29:19 366.09 14:35:24 188.49
37899 ID5862552991 1.0 1.0 40.363 9.0 38.0 -38.983710 145.320518 -37.673908 142.879230 258.259 2018-06-26 15:55:37 256.70 20:12:18 122.98
37900 ID5339104082 1.0 1.0 35.955 13.0 32.0 -38.292301 147.562013 -36.605285 148.293183 198.597 2018-03-19 16:41:10 198.97 20:00:08 118.47
37901 ID5468787866 2.0 1.0 29.566 33.0 23.0 -38.853243 144.508346 -37.727691 145.662270 160.816 2018-02-26 04:22:30 150.58 6:53:04 161.96
37902 ID1448126768 3.0 0.0 44.070 36.0 34.0 -37.129313 145.266426 -38.428477 143.341632 222.687 2018-07-07 08:01:42 182.71 11:04:24 144.41

任务7：找出所有列的缺失值个数并且按照多到少排列

隐藏任务：可视化缺失值的列

data.isnull().sum().sort_values(ascending=False)


Travel Time              40
Delivery Fare            29
Id                       25
Post Type                20
Destination Region       10
Origin Region            10
Delivery Time             0
Departure Time            0
Departure Date            0
Journey Distance          0
Destination Longitude     0
Destination Latitude      0
Origin Longitude          0
Origin Latitude           0
Package Weight            0
Drone Type                0
dtype: int64

count = {}
for col in data.columns:
    count_null = data[col].isnull().sum()
    count[col] = count_null
for i,j in sorted(count.items(),key = lambda s: s[1], reverse=True):
    print('列名：%s，存在缺失值 %s 个'%(i,j))


列名：Travel Time，存在缺失值 40 个
列名：Delivery Fare，存在缺失值 29 个
列名：Id，存在缺失值 25 个
列名：Post Type，存在缺失值 20 个
列名：Drone Type，存在缺失值 10 个
列名：Origin Region，存在缺失值 10 个
列名：Destination Region，存在缺失值 10 个
列名：Package Weight，存在缺失值 0 个
列名：Origin Latitude，存在缺失值 0 个
列名：Origin Longitude，存在缺失值 0 个
列名：Destination Latitude，存在缺失值 0 个
列名：Destination Longitude，存在缺失值 0 个
列名：Journey Distance，存在缺失值 0 个
列名：Departure Date，存在缺失值 0 个
列名：Departure Time，存在缺失值 0 个
列名：Delivery Time，存在缺失值 0 个

任务8：找出所有至少含有一个缺失值的行，并统计有多少行

data.isnull().any(axis=1)  # 判断至少有一个缺失值


0        False
1         True
2        False
3        False
4        False
         ...  
37898     True
37899    False
37900    False
37901    False
37902    False
Length: 37903, dtype: bool

data.drop(data.iloc[0,2])
Id Drone Type Post Type Package Weight Origin Region Destination Region Origin Latitude Origin Longitude Destination Latitude Destination Longitude Journey Distance Departure Date Departure Time Travel Time Delivery Time Delivery Fare
1 ID1697620764 NaN 0.0 39.075 15.0 15.0 -38.481935 146.009567 -38.585528 146.199827 20.185 2018-02-10 04:28:17 22.84 4:51:07 149.04
2 ID1543933503 2.0 0.0 7.243 33.0 28.0 -38.754167 144.509664 -38.242224 147.855342 296.975 2018-05-05 01:38:03 272.52 6:10:34 141.48
3 ID1756517608 2.0 0.0 13.383 10.0 38.0 -37.240526 147.568019 -37.687178 142.991188 407.396 2018-06-11 11:43:04 371.40 17:54:27 122.82
4 ID1832325834 2.0 0.0 8.123 1.0 8.0 -38.143985 143.798292 -38.548315 144.769228 95.974 2018-03-16 14:50:25 92.51 16:22:55 111.97
5 ID1802448576 2.0 0.0 32.859 2.0 28.0 -37.421211 148.044072 -38.159627 148.194048 83.250 2018-05-15 16:35:50 81.12 17:56:57 113.88
6 ID1940231408 1.0 0.0 20.616 29.0 36.0 -37.173949 143.140662 -37.021605 145.197043 183.363 2018-04-01 19:31:12 184.22 22:35:25 85.60
7 ID1299303958 2.0 0.0 44.577 36.0 31.0 -37.123190 145.236196 -37.667199 143.877650 134.543 2018-05-01 18:39:36 127.05 20:46:38 114.22
8 ID1752722028 1.0 0.0 15.363 20.0 30.0 -38.850561 148.317253 -38.024914 144.823938 318.132 2018-05-27 14:48:17 314.64 20:02:55 87.39
9 ID5995243590 1.0 1.0 36.190 18.0 28.0 -38.070189 142.950207 -37.996817 148.026520 445.106 2018-06-17 12:53:02 437.52 20:10:33 142.95
10 ID1483358088 2.0 0.0 23.172 13.0 27.0 -38.225456 147.425515 -37.642798 147.124104 70.051 2018-03-19 09:59:10 69.30 11:08:27 96.95
11 ID1626798395 2.0 0.0 19.754 23.0 26.0 -37.625368 145.838281 -36.789955 147.133916 147.791 2018-02-28 17:40:59 138.92 19:59:54 117.48
12 ID5277549009 3.0 1.0 12.807 4.0 6.0 -36.855984 142.929596 -36.906838 145.696986 246.465 2018-03-26 07:55:48 201.49 11:17:17 173.32
13 ID1950928883 2.0 0.0 22.332 33.0 19.0 -38.894115 144.457143 -37.173740 144.152105 193.365 2018-06-28 16:05:55 179.73 19:05:38 117.67
14 ID5143738648 2.0 1.0 25.880 33.0 5.0 -38.872372 144.606034 -37.553304 145.120753 153.580 2018-05-09 08:33:29 144.10 10:57:34 132.98
15 ID5132897910 2.0 1.0 38.691 7.0 15.0 -38.844622 144.093195 -38.476630 145.849992 158.102 2018-01-05 15:55:00 148.15 18:23:09 147.73
16 ID1290889802 1.0 0.0 30.742 19.0 14.0 -37.178059 144.403991 -37.713867 146.382965 184.783 2018-06-05 13:03:43 185.60 16:09:18 84.82
17 ID5226355535 1.0 1.0 18.055 10.0 18.0 -37.141728 147.256091 -37.983127 143.290272 362.227 2018-05-04 07:42:51 357.32 13:40:10 116.35
18 ID1898978312 1.0 0.0 5.986 23.0 18.0 -37.706960 145.718119 -37.983127 143.182464 224.997 2018-05-23 12:53:12 224.51 16:37:42 82.47
19 ID5284908619 2.0 1.0 30.664 13.0 14.0 -38.250238 147.366610 -37.774617 146.503568 92.371 2018-07-11 12:52:57 89.29 14:22:14 143.96
20 ID1585556406 3.0 0.0 24.942 3.0 39.0 -38.322643 145.505910 -38.453191 148.300405 244.251 2018-03-14 22:16:33 199.74 1:36:17 167.45
21 ID1901962779 1.0 0.0 34.012 27.0 37.0 -37.516090 146.969053 -38.852488 147.816987 166.236 2018-07-17 16:26:55 167.65 19:14:34 88.98
22 ID5590279060 1.0 1.0 55.229 7.0 6.0 -38.876145 143.911302 -36.875012 145.759049 275.633 2018-05-01 12:42:19 273.52 17:15:50 688.24
23 ID1473718059 3.0 0.0 40.741 33.0 17.0 -38.756634 144.375564 -38.817727 147.071495 234.014 2018-06-21 01:04:15 191.66 4:15:54 165.37
24 ID5551646734 3.0 1.0 15.419 35.0 21.0 -36.922413 146.362349 -37.240137 143.768208 233.068 2018-01-05 07:19:48 190.91 10:30:42 174.52
25 ID1772122934 2.0 0.0 10.663 29.0 1.0 -37.019910 142.798295 -38.358124 143.943955 179.929 2018-02-27 04:42:03 167.70 7:29:44 124.95
26 ID1987608852 1.0 0.0 20.685 9.0 35.0 -38.970092 145.435801 -37.068624 146.317717 225.349 2018-05-15 15:35:10 224.85 19:20:00 80.66
27 ID1249352358 2.0 0.0 10.272 8.0 34.0 -38.485641 144.522135 -38.505484 143.311788 105.471 2018-02-05 06:13:29 101.02 7:54:30 102.09
28 ID1611614450 1.0 0.0 7.373 16.0 38.0 -36.600877 143.566811 -37.804113 142.793139 150.483 2018-06-18 14:01:42 152.40 16:34:06 82.11
29 ID1262379299 2.0 0.0 31.358 13.0 16.0 -38.195911 147.436921 -36.734698 143.764279 362.941 2018-04-24 22:40:54 331.59 4:12:29 147.21
30 ID5498216777 2.0 1.0 6.383 40.0 29.0 -37.692167 147.890721 -37.273398 142.951899 438.698 2018-05-09 10:31:10 399.43 17:10:35 152.50
... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ...
37873 NaN 1.0 0.0 39.038 7.0 22.0 -38.664403 143.702592 -36.670478 144.312065 228.361 2018-02-02 18:03:25 227.77 21:51:11 87.87
37874 NaN 1.0 0.0 7.171 14.0 1.0 -37.589382 146.559422 -38.229669 143.710836 260.125 2018-02-03 22:11:31 258.51 2:30:01 106.47
37875 NaN 1.0 0.0 39.141 5.0 1.0 -37.491746 145.168879 -38.168680 143.936761 131.959 2018-04-19 22:54:02 134.48 1:08:30 94.83
37876 NaN 2.0 0.0 42.330 25.0 5.0 -36.618297 147.643165 -37.563856 145.038804 254.069 2018-01-27 11:06:09 234.09 15:00:14 116.10
37877 NaN 1.0 1.0 43.965 25.0 38.0 -36.542082 147.863736 -37.549355 142.980000 448.104 2018-06-14 22:15:50 440.42 5:36:15 149.26
37878 ID1525565031 2.0 0.0 27.560 7.0 35.0 -38.689638 144.093996 -37.046945 146.460389 276.894 2018-02-22 23:43:48 254.53 3:58:19 135.68
37879 NaN 1.0 0.0 18.540 36.0 18.0 -36.974023 145.036046 -38.015640 142.955101 217.299 2018-05-28 07:14:50 217.06 10:51:53 76.31
37880 ID1272392458 1.0 0.0 44.226 40.0 3.0 -37.740012 147.768225 -38.276119 145.403536 215.813 2018-02-21 21:28:22 215.63 1:03:59 105.89
37881 ID1909131399 2.0 0.0 6.415 25.0 27.0 -36.585246 147.853062 -37.700285 147.027318 144.135 2018-01-16 08:26:33 135.64 10:42:11 100.45
37882 ID5420355345 3.0 1.0 30.471 3.0 15.0 -38.345519 145.542105 -38.712933 145.977537 55.772 2018-07-22 00:13:54 50.94 1:04:50 183.73
37883 ID5164753016 2.0 1.0 6.553 5.0 15.0 -37.625311 145.097611 -38.630132 146.040376 139.018 2018-01-14 16:51:56 131.06 19:02:59 145.71
37884 ID1122103211 1.0 0.0 37.850 38.0 10.0 -37.510578 142.826103 -37.337327 147.315144 397.278 2018-04-27 14:54:35 391.24 21:25:49 102.88
37885 NaN 1.0 0.0 13.544 34.0 38.0 -38.528632 143.386140 -37.561137 142.835312 118.028 2018-04-09 11:48:46 120.99 13:49:45 69.88
37886 ID1970402579 2.0 0.0 38.656 1.0 32.0 -38.171542 143.871582 -36.756484 148.322553 423.585 2018-03-18 01:01:33 385.90 7:27:26 142.19
37887 ID1388385049 1.0 0.0 23.699 10.0 10.0 -37.352794 147.476662 -37.104700 147.417027 28.118 2018-05-24 00:41:08 33.99 1:15:07 83.77
37888 NaN 1.0 1.0 34.923 36.0 28.0 -37.044972 144.920592 -38.218799 148.050871 305.307 2018-07-20 03:13:54 302.23 8:16:07 130.70
37889 ID1281653747 1.0 0.0 34.130 8.0 36.0 -38.434373 144.730220 -37.047801 145.309517 162.554 2018-02-06 06:27:55 164.08 9:11:59 69.44
37890 ID5349085772 1.0 1.0 18.286 22.0 14.0 -36.720129 144.588398 -37.695893 146.430793 196.154 2018-02-15 17:04:44 196.60 20:21:19 121.11
37891 ID5972337482 1.0 1.0 11.538 33.0 2.0 -38.836802 144.357057 -37.549931 148.306793 374.024 2018-05-26 02:07:50 368.73 8:16:33 141.06
37892 NaN 3.0 1.0 5.416 20.0 33.0 -38.959090 148.294700 -38.930545 144.661887 314.514 2018-04-27 20:53:02 255.21 1:08:14 185.96
37893 ID1539650034 2.0 0.0 34.355 8.0 39.0 -38.520278 144.408786 -38.447195 148.416066 349.251 2018-05-09 07:26:49 319.33 12:46:08 121.30
37894 NaN 2.0 0.0 41.232 38.0 39.0 -37.657406 142.777301 -38.622040 148.366529 500.901 2018-07-02 08:59:29 455.14 16:34:37 139.79
37895 ID1796943211 1.0 0.0 44.341 23.0 24.0 -37.777223 146.024184 -38.913981 142.913934 299.552 2018-02-20 05:08:12 296.66 10:04:51 113.70
37896 ID5429883749 2.0 1.0 17.798 11.0 40.0 -38.045551 146.736254 -37.633007 147.639273 91.711 2018-05-03 10:19:32 88.70 11:48:14 130.51
37897 NaN 1.0 0.0 8.865 9.0 2.0 -38.839254 145.226776 -37.695101 148.251214 293.394 2018-03-11 12:18:21 290.70 17:09:02 88.71
37898 NaN 3.0 1.0 27.153 39.0 16.0 -38.446310 148.292498 -36.739777 143.604529 454.968 2018-07-23 08:29:19 366.09 14:35:24 188.49
37899 ID5862552991 1.0 1.0 40.363 9.0 38.0 -38.983710 145.320518 -37.673908 142.879230 258.259 2018-06-26 15:55:37 256.70 20:12:18 122.98
37900 ID5339104082 1.0 1.0 35.955 13.0 32.0 -38.292301 147.562013 -36.605285 148.293183 198.597 2018-03-19 16:41:10 198.97 20:00:08 118.47
37901 ID5468787866 2.0 1.0 29.566 33.0 23.0 -38.853243 144.508346 -37.727691 145.662270 160.816 2018-02-26 04:22:30 150.58 6:53:04 161.96
37902 ID1448126768 3.0 0.0 44.070 36.0 34.0 -37.129313 145.266426 -38.428477 143.341632 222.687 2018-07-07 08:01:42 182.71 11:04:24 144.41

37902 rows × 16 columns

# axis=1针对的是行；=0针对的是列
data[data.isnull().any(axis=1)].shape
#（data.isnull().sum(axis=1) >= 1）.sum()
data[data.isnull().any(axis=1)].shape
data.isnull().any(axis=1)


0        False
1         True
2        False
3        False
4        False
5        False
6        False
7        False
8        False
9        False
10       False
11       False
12       False
13       False
14       False
15       False
16       False
17       False
18       False
19       False
20       False
21       False
22       False
23       False
24       False
25       False
26       False
27       False
28       False
29       False
         ...  
37873     True
37874     True
37875     True
37876     True
37877     True
37878    False
37879     True
37880    False
37881    False
37882    False
37883    False
37884    False
37885     True
37886    False
37887    False
37888     True
37889    False
37890    False
37891    False
37892     True
37893    False
37894     True
37895    False
37896    False
37897     True
37898     True
37899    False
37900    False
37901    False
37902    False
dtype: bool

4.0 数据清洗

任务9： 填补 'Id'列的空值

任务9.1 统计‘id’列有多少个空值

data['Id'].isnull().sum()


25

任务9.2 找出所有‘id’为空的行

data[data['Id'].isnull()]
Id Drone Type Post Type Package Weight Origin Region Destination Region Origin Latitude Origin Longitude Destination Latitude Destination Longitude Journey Distance Departure Date Departure Time Travel Time Delivery Time Delivery Fare
37844 NaN 1.0 0.0 22.498 30.0 15.0 -37.885792 144.875305 -38.680341 145.874064 124.252 2018-01-28 13:07:09 127.02 15:14:10 74.69
37845 NaN 3.0 0.0 32.300 16.0 36.0 -36.571169 143.741010 -36.993435 144.983048 120.297 2018-03-26 02:34:49 101.88 4:16:41 162.20
37846 NaN 3.0 0.0 18.601 38.0 29.0 -37.694132 142.851548 -37.058014 142.873698 70.838 2018-03-02 20:33:11 62.83 21:36:00 142.10
37850 NaN 2.0 1.0 28.203 18.0 21.0 -38.139260 143.345778 -37.279348 143.604189 98.391 2018-04-17 03:28:41 94.68 5:03:21 152.30
37851 NaN 2.0 1.0 45.696 28.0 1.0 -38.152835 147.793072 -38.162103 144.043047 328.220 2018-03-25 13:50:57 300.50 18:51:27 167.39
37852 NaN 1.0 0.0 27.143 38.0 32.0 -37.613135 142.854194 -36.713765 148.383062 500.500 2018-05-12 08:54:52 491.13 17:05:59 97.44
37854 NaN 1.0 0.0 13.002 27.0 3.0 -37.428594 147.056992 -38.383631 145.590528 167.005 2018-02-20 03:35:43 168.39 6:24:06 92.12
37857 NaN 1.0 0.0 19.468 5.0 31.0 -37.570514 145.253281 -37.824303 143.862472 125.716 2018-04-20 11:48:14 128.43 13:56:39 69.86
37860 NaN 1.0 0.0 10.647 21.0 5.0 -37.239046 143.524656 -37.546884 145.204154 152.434 2018-06-13 23:05:39 154.29 1:39:56 95.65
37861 NaN 2.0 0.0 40.775 22.0 19.0 -36.678761 144.345069 -37.167099 144.257043 54.922 2018-03-27 04:31:43 55.75 5:27:28 120.70
37863 NaN 1.0 0.0 35.410 9.0 16.0 -39.006941 145.406988 -36.525200 143.517906 322.399 2018-07-21 06:48:39 318.77 12:07:25 88.14
37866 NaN 1.0 1.0 40.151 14.0 34.0 -37.644061 146.625820 -38.518928 143.369714 301.447 2018-04-14 07:29:13 298.50 12:27:43 118.89
37869 NaN 3.0 1.0 44.559 33.0 10.0 -38.733722 144.474460 -37.220132 147.498884 314.323 2018-04-18 04:57:37 255.06 9:12:40 201.89
37873 NaN 1.0 0.0 39.038 7.0 22.0 -38.664403 143.702592 -36.670478 144.312065 228.361 2018-02-02 18:03:25 227.77 21:51:11 87.87
37874 NaN 1.0 0.0 7.171 14.0 1.0 -37.589382 146.559422 -38.229669 143.710836 260.125 2018-02-03 22:11:31 258.51 2:30:01 106.47
37875 NaN 1.0 0.0 39.141 5.0 1.0 -37.491746 145.168879 -38.168680 143.936761 131.959 2018-04-19 22:54:02 134.48 1:08:30 94.83
37876 NaN 2.0 0.0 42.330 25.0 5.0 -36.618297 147.643165 -37.563856 145.038804 254.069 2018-01-27 11:06:09 234.09 15:00:14 116.10
37877 NaN 1.0 1.0 43.965 25.0 38.0 -36.542082 147.863736 -37.549355 142.980000 448.104 2018-06-14 22:15:50 440.42 5:36:15 149.26
37879 NaN 1.0 0.0 18.540 36.0 18.0 -36.974023 145.036046 -38.015640 142.955101 217.299 2018-05-28 07:14:50 217.06 10:51:53 76.31
37885 NaN 1.0 0.0 13.544 34.0 38.0 -38.528632 143.386140 -37.561137 142.835312 118.028 2018-04-09 11:48:46 120.99 13:49:45 69.88
37888 NaN 1.0 1.0 34.923 36.0 28.0 -37.044972 144.920592 -38.218799 148.050871 305.307 2018-07-20 03:13:54 302.23 8:16:07 130.70
37892 NaN 3.0 1.0 5.416 20.0 33.0 -38.959090 148.294700 -38.930545 144.661887 314.514 2018-04-27 20:53:02 255.21 1:08:14 185.96
37894 NaN 2.0 0.0 41.232 38.0 39.0 -37.657406 142.777301 -38.622040 148.366529 500.901 2018-07-02 08:59:29 455.14 16:34:37 139.79
37897 NaN 1.0 0.0 8.865 9.0 2.0 -38.839254 145.226776 -37.695101 148.251214 293.394 2018-03-11 12:18:21 290.70 17:09:02 88.71
37898 NaN 3.0 1.0 27.153 39.0 16.0 -38.446310 148.292498 -36.739777 143.604529 454.968 2018-07-23 08:29:19 366.09 14:35:24 188.49

9.2.1 删除 除ID列之外其余数据重复的行

data[
    ['Drone Type', 'Post Type', 'Package Weight', 'Origin Region',
       'Destination Region', 'Origin Latitude', 'Origin Longitude',
       'Destination Latitude', 'Destination Longitude', 'Journey Distance',
       'Departure Date', 'Departure Time', 'Travel Time', 'Delivery Time',
       'Delivery Fare']
]
Drone Type Post Type Package Weight Origin Region Destination Region Origin Latitude Origin Longitude Destination Latitude Destination Longitude Journey Distance Departure Date Departure Time Travel Time Delivery Time Delivery Fare
0 2.0 0.0 21.686 19.0 38.0 -37.089338 144.429529 -37.639134 142.891391 149.212 2018-01-16 09:38:17 140.19 11:58:28 99.25
1 NaN 0.0 39.075 15.0 15.0 -38.481935 146.009567 -38.585528 146.199827 20.185 2018-02-10 04:28:17 22.84 4:51:07 149.04
2 2.0 0.0 7.243 33.0 28.0 -38.754167 144.509664 -38.242224 147.855342 296.975 2018-05-05 01:38:03 272.52 6:10:34 141.48
3 2.0 0.0 13.383 10.0 38.0 -37.240526 147.568019 -37.687178 142.991188 407.396 2018-06-11 11:43:04 371.40 17:54:27 122.82
4 2.0 0.0 8.123 1.0 8.0 -38.143985 143.798292 -38.548315 144.769228 95.974 2018-03-16 14:50:25 92.51 16:22:55 111.97
5 2.0 0.0 32.859 2.0 28.0 -37.421211 148.044072 -38.159627 148.194048 83.250 2018-05-15 16:35:50 81.12 17:56:57 113.88
6 1.0 0.0 20.616 29.0 36.0 -37.173949 143.140662 -37.021605 145.197043 183.363 2018-04-01 19:31:12 184.22 22:35:25 85.60
7 2.0 0.0 44.577 36.0 31.0 -37.123190 145.236196 -37.667199 143.877650 134.543 2018-05-01 18:39:36 127.05 20:46:38 114.22
8 1.0 0.0 15.363 20.0 30.0 -38.850561 148.317253 -38.024914 144.823938 318.132 2018-05-27 14:48:17 314.64 20:02:55 87.39
9 1.0 1.0 36.190 18.0 28.0 -38.070189 142.950207 -37.996817 148.026520 445.106 2018-06-17 12:53:02 437.52 20:10:33 142.95
10 2.0 0.0 23.172 13.0 27.0 -38.225456 147.425515 -37.642798 147.124104 70.051 2018-03-19 09:59:10 69.30 11:08:27 96.95
11 2.0 0.0 19.754 23.0 26.0 -37.625368 145.838281 -36.789955 147.133916 147.791 2018-02-28 17:40:59 138.92 19:59:54 117.48
12 3.0 1.0 12.807 4.0 6.0 -36.855984 142.929596 -36.906838 145.696986 246.465 2018-03-26 07:55:48 201.49 11:17:17 173.32
13 2.0 0.0 22.332 33.0 19.0 -38.894115 144.457143 -37.173740 144.152105 193.365 2018-06-28 16:05:55 179.73 19:05:38 117.67
14 2.0 1.0 25.880 33.0 5.0 -38.872372 144.606034 -37.553304 145.120753 153.580 2018-05-09 08:33:29 144.10 10:57:34 132.98
15 2.0 1.0 38.691 7.0 15.0 -38.844622 144.093195 -38.476630 145.849992 158.102 2018-01-05 15:55:00 148.15 18:23:09 147.73
16 1.0 0.0 30.742 19.0 14.0 -37.178059 144.403991 -37.713867 146.382965 184.783 2018-06-05 13:03:43 185.60 16:09:18 84.82
17 1.0 1.0 18.055 10.0 18.0 -37.141728 147.256091 -37.983127 143.290272 362.227 2018-05-04 07:42:51 357.32 13:40:10 116.35
18 1.0 0.0 5.986 23.0 18.0 -37.706960 145.718119 -37.983127 143.182464 224.997 2018-05-23 12:53:12 224.51 16:37:42 82.47
19 2.0 1.0 30.664 13.0 14.0 -38.250238 147.366610 -37.774617 146.503568 92.371 2018-07-11 12:52:57 89.29 14:22:14 143.96
20 3.0 0.0 24.942 3.0 39.0 -38.322643 145.505910 -38.453191 148.300405 244.251 2018-03-14 22:16:33 199.74 1:36:17 167.45
21 1.0 0.0 34.012 27.0 37.0 -37.516090 146.969053 -38.852488 147.816987 166.236 2018-07-17 16:26:55 167.65 19:14:34 88.98
22 1.0 1.0 55.229 7.0 6.0 -38.876145 143.911302 -36.875012 145.759049 275.633 2018-05-01 12:42:19 273.52 17:15:50 688.24
23 3.0 0.0 40.741 33.0 17.0 -38.756634 144.375564 -38.817727 147.071495 234.014 2018-06-21 01:04:15 191.66 4:15:54 165.37
24 3.0 1.0 15.419 35.0 21.0 -36.922413 146.362349 -37.240137 143.768208 233.068 2018-01-05 07:19:48 190.91 10:30:42 174.52
25 2.0 0.0 10.663 29.0 1.0 -37.019910 142.798295 -38.358124 143.943955 179.929 2018-02-27 04:42:03 167.70 7:29:44 124.95
26 1.0 0.0 20.685 9.0 35.0 -38.970092 145.435801 -37.068624 146.317717 225.349 2018-05-15 15:35:10 224.85 19:20:00 80.66
27 2.0 0.0 10.272 8.0 34.0 -38.485641 144.522135 -38.505484 143.311788 105.471 2018-02-05 06:13:29 101.02 7:54:30 102.09
28 1.0 0.0 7.373 16.0 38.0 -36.600877 143.566811 -37.804113 142.793139 150.483 2018-06-18 14:01:42 152.40 16:34:06 82.11
29 2.0 0.0 31.358 13.0 16.0 -38.195911 147.436921 -36.734698 143.764279 362.941 2018-04-24 22:40:54 331.59 4:12:29 147.21
... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ...
37873 1.0 0.0 39.038 7.0 22.0 -38.664403 143.702592 -36.670478 144.312065 228.361 2018-02-02 18:03:25 227.77 21:51:11 87.87
37874 1.0 0.0 7.171 14.0 1.0 -37.589382 146.559422 -38.229669 143.710836 260.125 2018-02-03 22:11:31 258.51 2:30:01 106.47
37875 1.0 0.0 39.141 5.0 1.0 -37.491746 145.168879 -38.168680 143.936761 131.959 2018-04-19 22:54:02 134.48 1:08:30 94.83
37876 2.0 0.0 42.330 25.0 5.0 -36.618297 147.643165 -37.563856 145.038804 254.069 2018-01-27 11:06:09 234.09 15:00:14 116.10
37877 1.0 1.0 43.965 25.0 38.0 -36.542082 147.863736 -37.549355 142.980000 448.104 2018-06-14 22:15:50 440.42 5:36:15 149.26
37878 2.0 0.0 27.560 7.0 35.0 -38.689638 144.093996 -37.046945 146.460389 276.894 2018-02-22 23:43:48 254.53 3:58:19 135.68
37879 1.0 0.0 18.540 36.0 18.0 -36.974023 145.036046 -38.015640 142.955101 217.299 2018-05-28 07:14:50 217.06 10:51:53 76.31
37880 1.0 0.0 44.226 40.0 3.0 -37.740012 147.768225 -38.276119 145.403536 215.813 2018-02-21 21:28:22 215.63 1:03:59 105.89
37881 2.0 0.0 6.415 25.0 27.0 -36.585246 147.853062 -37.700285 147.027318 144.135 2018-01-16 08:26:33 135.64 10:42:11 100.45
37882 3.0 1.0 30.471 3.0 15.0 -38.345519 145.542105 -38.712933 145.977537 55.772 2018-07-22 00:13:54 50.94 1:04:50 183.73
37883 2.0 1.0 6.553 5.0 15.0 -37.625311 145.097611 -38.630132 146.040376 139.018 2018-01-14 16:51:56 131.06 19:02:59 145.71
37884 1.0 0.0 37.850 38.0 10.0 -37.510578 142.826103 -37.337327 147.315144 397.278 2018-04-27 14:54:35 391.24 21:25:49 102.88
37885 1.0 0.0 13.544 34.0 38.0 -38.528632 143.386140 -37.561137 142.835312 118.028 2018-04-09 11:48:46 120.99 13:49:45 69.88
37886 2.0 0.0 38.656 1.0 32.0 -38.171542 143.871582 -36.756484 148.322553 423.585 2018-03-18 01:01:33 385.90 7:27:26 142.19
37887 1.0 0.0 23.699 10.0 10.0 -37.352794 147.476662 -37.104700 147.417027 28.118 2018-05-24 00:41:08 33.99 1:15:07 83.77
37888 1.0 1.0 34.923 36.0 28.0 -37.044972 144.920592 -38.218799 148.050871 305.307 2018-07-20 03:13:54 302.23 8:16:07 130.70
37889 1.0 0.0 34.130 8.0 36.0 -38.434373 144.730220 -37.047801 145.309517 162.554 2018-02-06 06:27:55 164.08 9:11:59 69.44
37890 1.0 1.0 18.286 22.0 14.0 -36.720129 144.588398 -37.695893 146.430793 196.154 2018-02-15 17:04:44 196.60 20:21:19 121.11
37891 1.0 1.0 11.538 33.0 2.0 -38.836802 144.357057 -37.549931 148.306793 374.024 2018-05-26 02:07:50 368.73 8:16:33 141.06
37892 3.0 1.0 5.416 20.0 33.0 -38.959090 148.294700 -38.930545 144.661887 314.514 2018-04-27 20:53:02 255.21 1:08:14 185.96
37893 2.0 0.0 34.355 8.0 39.0 -38.520278 144.408786 -38.447195 148.416066 349.251 2018-05-09 07:26:49 319.33 12:46:08 121.30
37894 2.0 0.0 41.232 38.0 39.0 -37.657406 142.777301 -38.622040 148.366529 500.901 2018-07-02 08:59:29 455.14 16:34:37 139.79
37895 1.0 0.0 44.341 23.0 24.0 -37.777223 146.024184 -38.913981 142.913934 299.552 2018-02-20 05:08:12 296.66 10:04:51 113.70
37896 2.0 1.0 17.798 11.0 40.0 -38.045551 146.736254 -37.633007 147.639273 91.711 2018-05-03 10:19:32 88.70 11:48:14 130.51
37897 1.0 0.0 8.865 9.0 2.0 -38.839254 145.226776 -37.695101 148.251214 293.394 2018-03-11 12:18:21 290.70 17:09:02 88.71
37898 3.0 1.0 27.153 39.0 16.0 -38.446310 148.292498 -36.739777 143.604529 454.968 2018-07-23 08:29:19 366.09 14:35:24 188.49
37899 1.0 1.0 40.363 9.0 38.0 -38.983710 145.320518 -37.673908 142.879230 258.259 2018-06-26 15:55:37 256.70 20:12:18 122.98
37900 1.0 1.0 35.955 13.0 32.0 -38.292301 147.562013 -36.605285 148.293183 198.597 2018-03-19 16:41:10 198.97 20:00:08 118.47
37901 2.0 1.0 29.566 33.0 23.0 -38.853243 144.508346 -37.727691 145.662270 160.816 2018-02-26 04:22:30 150.58 6:53:04 161.96
37902 3.0 0.0 44.070 36.0 34.0 -37.129313 145.266426 -38.428477 143.341632 222.687 2018-07-07 08:01:42 182.71 11:04:24 144.41

37903 rows × 15 columns

# drop_duplicates返回一个dataframe，重复的行会标为False
# data_1 = data.drop_duplicates(data[['Drone Type', 'Post Type', 'Package Weight', 'Origin Region',
#        'Destination Region', 'Origin Latitude', 'Origin Longitude',
#        'Destination Latitude', 'Destination Longitude', 'Journey Distance',
#        'Departure Date', 'Departure Time', 'Travel Time', 'Delivery Time',
#        'Delivery Fare']])
# 2
data_1 = data.drop_duplicates(['Drone Type', 'Post Type', 'Package Weight', 'Origin Region',
    'Destination Region', 'Origin Latitude', 'Origin Longitude',
    'Destination Latitude', 'Destination Longitude', 'Journey Distance',
    'Departure Date', 'Departure Time', 'Travel Time', 'Delivery Time',
    'Delivery Fare'])
data_1
Id Drone Type Post Type Package Weight Origin Region Destination Region Origin Latitude Origin Longitude Destination Latitude Destination Longitude Journey Distance Departure Date Departure Time Travel Time Delivery Time Delivery Fare
0 ID1645282128 2.0 0.0 21.686 19.0 38.0 -37.089338 144.429529 -37.639134 142.891391 149.212 2018-01-16 09:38:17 140.19 11:58:28 99.25
1 ID1697620764 NaN 0.0 39.075 15.0 15.0 -38.481935 146.009567 -38.585528 146.199827 20.185 2018-02-10 04:28:17 22.84 4:51:07 149.04
2 ID1543933503 2.0 0.0 7.243 33.0 28.0 -38.754167 144.509664 -38.242224 147.855342 296.975 2018-05-05 01:38:03 272.52 6:10:34 141.48
3 ID1756517608 2.0 0.0 13.383 10.0 38.0 -37.240526 147.568019 -37.687178 142.991188 407.396 2018-06-11 11:43:04 371.40 17:54:27 122.82
4 ID1832325834 2.0 0.0 8.123 1.0 8.0 -38.143985 143.798292 -38.548315 144.769228 95.974 2018-03-16 14:50:25 92.51 16:22:55 111.97
... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ...
37839 ID1879423081 2.0 0.0 17.081 20.0 14.0 -38.860358 148.174855 -37.562224 146.443991 209.278 2018-02-15 12:09:15 193.98 15:23:13 121.33
37840 ID5705840841 1.0 1.0 35.164 29.0 23.0 -37.250331 142.837244 -37.739285 145.963420 281.403 2018-01-01 00:07:35 279.10 4:46:41 136.58
37841 ID1276239209 3.0 0.0 36.704 12.0 11.0 -36.578568 145.273744 -38.305557 146.997297 245.269 2018-01-24 06:48:05 200.54 10:08:37 146.69
37842 ID1432868583 3.0 0.0 13.195 37.0 21.0 -38.755802 147.744770 -37.487866 143.585293 390.599 2018-04-07 18:57:52 315.28 0:13:08 170.52
37889 ID1281653747 1.0 0.0 34.130 8.0 36.0 -38.434373 144.730220 -37.047801 145.309517 162.554 2018-02-06 06:27:55 164.08 9:11:59 69.44

37844 rows × 16 columns

9.2.2 设置ID列相同，但其余数据不重复的ID为np.nan

# 返回一个布尔型的series，表示id是否重复行
data_1['Id'].duplicated()


0        False
1        False
2        False
3        False
4        False
         ...  
37839    False
37840    False
37841    False
37842    False
37889     True
Name: Id, Length: 37844, dtype: bool

(data_1['Id'].duplicated()) & ~(data_1[['Drone Type', 'Post Type', 'Package Weight', 'Origin Region',
       'Destination Region', 'Origin Latitude', 'Origin Longitude',
       'Destination Latitude', 'Destination Longitude', 'Journey Distance',
       'Departure Date', 'Departure Time', 'Travel Time', 'Delivery Time',
       'Delivery Fare']].duplicated())


0        False
1        False
2        False
3        False
4        False
         ...  
37839    False
37840    False
37841    False
37842    False
37889     True
Length: 37844, dtype: bool

data_1.loc[(data_1['Id'].duplicated()) & ~(data_1[['Drone Type', 'Post Type', 'Package Weight', 'Origin Region',
       'Destination Region', 'Origin Latitude', 'Origin Longitude',
       'Destination Latitude', 'Destination Longitude', 'Journey Distance',
       'Departure Date', 'Departure Time', 'Travel Time', 'Delivery Time',
       'Delivery Fare']].duplicated()),'Id'] = np.nan


data_1['Id'].value_counts()


ID1858018960    1
ID1792956453    1
ID1574575344    1
ID1146475421    1
ID1897230811    1
               ..
ID5349642032    1
ID5451065609    1
ID5131869013    1
ID1550669799    1
ID5260585195    1
Name: Id, Length: 37843, dtype: int64

任务9.3 想出空值的填补的2种方法，并且选一种应用

#方法1 ：随机数
import random
# 'Id'+str(random.randint(1000000000,2000000000)) in data_1['Id']
#方法2 ：随机数填补

def fill_id(Id):
    Id_out = Id
    while Id_out in data_1['Id'].tolist():
        Id_out = 'ID'+str(random.randint(1000000000,2000000000))
        return Id_out
data_1.loc[(data_1['Post Type']==0)&(data_1['Id'].isnull()),'Id'].apply(fill_id)


37889    ID1117118084
Name: Id, dtype: object

data_1.loc[(data_1['Post Type']==0)&(data_1['Id'].isnull()),'Id']


37889    NaN
Name: Id, dtype: object

data_1['Id']


0        ID1645282128
1        ID1697620764
2        ID1543933503
3        ID1756517608
4        ID1832325834
             ...     
37839    ID1879423081
37840    ID5705840841
37841    ID1276239209
37842    ID1432868583
37889             NaN
Name: Id, Length: 37844, dtype: object

任务9.4 检查‘id’是否还有空值

data_1['Id'].isnull().sum()


1

任务10：找出所有重复的id

data[data.Id.duplicated()].Id


37843    ID1874340610
37845             NaN
37846             NaN
37847    ID5156350605
37848    ID1176413101
             ...     
37898             NaN
37899    ID5862552991
37900    ID5339104082
37901    ID5468787866
37902    ID1448126768
Name: Id, Length: 59, dtype: object

data['Id'].value_counts() >= 2


ID5281864060     True
ID1796943211     True
ID1877344172     True
ID5122284320     True
ID1238297934     True
                ...  
ID5672029782    False
ID1114364309    False
ID5495523518    False
ID1874532678    False
ID5260585195    False
Name: Id, Length: 37843, dtype: bool

任务11：删除重复行

data.duplicated()


0        False
1        False
2        False
3        False
4        False
5        False
6        False
7        False
8        False
9        False
10       False
11       False
12       False
13       False
14       False
15       False
16       False
17       False
18       False
19       False
20       False
21       False
22       False
23       False
24       False
25       False
26       False
27       False
28       False
29       False
         ...  
37873    False
37874    False
37875    False
37876    False
37877    False
37878     True
37879    False
37880     True
37881     True
37882     True
37883     True
37884     True
37885    False
37886     True
37887     True
37888    False
37889    False
37890     True
37891     True
37892    False
37893     True
37894    False
37895     True
37896     True
37897    False
37898    False
37899     True
37900     True
37901     True
37902     True
dtype: bool

data.drop_duplicates()
Id Drone Type Post Type Package Weight Origin Region Destination Region Origin Latitude Origin Longitude Destination Latitude Destination Longitude Journey Distance Departure Date Departure Time Travel Time Delivery Time Delivery Fare
0 ID1645282128 2.0 0.0 21.686 19.0 38.0 -37.089338 144.429529 -37.639134 142.891391 149.212 2018-01-16 09:38:17 140.19 11:58:28 99.25
1 ID1697620764 NaN 0.0 39.075 15.0 15.0 -38.481935 146.009567 -38.585528 146.199827 20.185 2018-02-10 04:28:17 22.84 4:51:07 149.04
2 ID1543933503 2.0 0.0 7.243 33.0 28.0 -38.754167 144.509664 -38.242224 147.855342 296.975 2018-05-05 01:38:03 272.52 6:10:34 141.48
3 ID1756517608 2.0 0.0 13.383 10.0 38.0 -37.240526 147.568019 -37.687178 142.991188 407.396 2018-06-11 11:43:04 371.40 17:54:27 122.82
4 ID1832325834 2.0 0.0 8.123 1.0 8.0 -38.143985 143.798292 -38.548315 144.769228 95.974 2018-03-16 14:50:25 92.51 16:22:55 111.97
5 ID1802448576 2.0 0.0 32.859 2.0 28.0 -37.421211 148.044072 -38.159627 148.194048 83.250 2018-05-15 16:35:50 81.12 17:56:57 113.88
6 ID1940231408 1.0 0.0 20.616 29.0 36.0 -37.173949 143.140662 -37.021605 145.197043 183.363 2018-04-01 19:31:12 184.22 22:35:25 85.60
7 ID1299303958 2.0 0.0 44.577 36.0 31.0 -37.123190 145.236196 -37.667199 143.877650 134.543 2018-05-01 18:39:36 127.05 20:46:38 114.22
8 ID1752722028 1.0 0.0 15.363 20.0 30.0 -38.850561 148.317253 -38.024914 144.823938 318.132 2018-05-27 14:48:17 314.64 20:02:55 87.39
9 ID5995243590 1.0 1.0 36.190 18.0 28.0 -38.070189 142.950207 -37.996817 148.026520 445.106 2018-06-17 12:53:02 437.52 20:10:33 142.95
10 ID1483358088 2.0 0.0 23.172 13.0 27.0 -38.225456 147.425515 -37.642798 147.124104 70.051 2018-03-19 09:59:10 69.30 11:08:27 96.95
11 ID1626798395 2.0 0.0 19.754 23.0 26.0 -37.625368 145.838281 -36.789955 147.133916 147.791 2018-02-28 17:40:59 138.92 19:59:54 117.48
12 ID5277549009 3.0 1.0 12.807 4.0 6.0 -36.855984 142.929596 -36.906838 145.696986 246.465 2018-03-26 07:55:48 201.49 11:17:17 173.32
13 ID1950928883 2.0 0.0 22.332 33.0 19.0 -38.894115 144.457143 -37.173740 144.152105 193.365 2018-06-28 16:05:55 179.73 19:05:38 117.67
14 ID5143738648 2.0 1.0 25.880 33.0 5.0 -38.872372 144.606034 -37.553304 145.120753 153.580 2018-05-09 08:33:29 144.10 10:57:34 132.98
15 ID5132897910 2.0 1.0 38.691 7.0 15.0 -38.844622 144.093195 -38.476630 145.849992 158.102 2018-01-05 15:55:00 148.15 18:23:09 147.73
16 ID1290889802 1.0 0.0 30.742 19.0 14.0 -37.178059 144.403991 -37.713867 146.382965 184.783 2018-06-05 13:03:43 185.60 16:09:18 84.82
17 ID5226355535 1.0 1.0 18.055 10.0 18.0 -37.141728 147.256091 -37.983127 143.290272 362.227 2018-05-04 07:42:51 357.32 13:40:10 116.35
18 ID1898978312 1.0 0.0 5.986 23.0 18.0 -37.706960 145.718119 -37.983127 143.182464 224.997 2018-05-23 12:53:12 224.51 16:37:42 82.47
19 ID5284908619 2.0 1.0 30.664 13.0 14.0 -38.250238 147.366610 -37.774617 146.503568 92.371 2018-07-11 12:52:57 89.29 14:22:14 143.96
20 ID1585556406 3.0 0.0 24.942 3.0 39.0 -38.322643 145.505910 -38.453191 148.300405 244.251 2018-03-14 22:16:33 199.74 1:36:17 167.45
21 ID1901962779 1.0 0.0 34.012 27.0 37.0 -37.516090 146.969053 -38.852488 147.816987 166.236 2018-07-17 16:26:55 167.65 19:14:34 88.98
22 ID5590279060 1.0 1.0 55.229 7.0 6.0 -38.876145 143.911302 -36.875012 145.759049 275.633 2018-05-01 12:42:19 273.52 17:15:50 688.24
23 ID1473718059 3.0 0.0 40.741 33.0 17.0 -38.756634 144.375564 -38.817727 147.071495 234.014 2018-06-21 01:04:15 191.66 4:15:54 165.37
24 ID5551646734 3.0 1.0 15.419 35.0 21.0 -36.922413 146.362349 -37.240137 143.768208 233.068 2018-01-05 07:19:48 190.91 10:30:42 174.52
25 ID1772122934 2.0 0.0 10.663 29.0 1.0 -37.019910 142.798295 -38.358124 143.943955 179.929 2018-02-27 04:42:03 167.70 7:29:44 124.95
26 ID1987608852 1.0 0.0 20.685 9.0 35.0 -38.970092 145.435801 -37.068624 146.317717 225.349 2018-05-15 15:35:10 224.85 19:20:00 80.66
27 ID1249352358 2.0 0.0 10.272 8.0 34.0 -38.485641 144.522135 -38.505484 143.311788 105.471 2018-02-05 06:13:29 101.02 7:54:30 102.09
28 ID1611614450 1.0 0.0 7.373 16.0 38.0 -36.600877 143.566811 -37.804113 142.793139 150.483 2018-06-18 14:01:42 152.40 16:34:06 82.11
29 ID1262379299 2.0 0.0 31.358 13.0 16.0 -38.195911 147.436921 -36.734698 143.764279 362.941 2018-04-24 22:40:54 331.59 4:12:29 147.21
... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ...
37839 ID1879423081 2.0 0.0 17.081 20.0 14.0 -38.860358 148.174855 -37.562224 146.443991 209.278 2018-02-15 12:09:15 193.98 15:23:13 121.33
37840 ID5705840841 1.0 1.0 35.164 29.0 23.0 -37.250331 142.837244 -37.739285 145.963420 281.403 2018-01-01 00:07:35 279.10 4:46:41 136.58
37841 ID1276239209 3.0 0.0 36.704 12.0 11.0 -36.578568 145.273744 -38.305557 146.997297 245.269 2018-01-24 06:48:05 200.54 10:08:37 146.69
37842 ID1432868583 3.0 0.0 13.195 37.0 21.0 -38.755802 147.744770 -37.487866 143.585293 390.599 2018-04-07 18:57:52 315.28 0:13:08 170.52
37844 NaN 1.0 0.0 22.498 30.0 15.0 -37.885792 144.875305 -38.680341 145.874064 124.252 2018-01-28 13:07:09 127.02 15:14:10 74.69
37845 NaN 3.0 0.0 32.300 16.0 36.0 -36.571169 143.741010 -36.993435 144.983048 120.297 2018-03-26 02:34:49 101.88 4:16:41 162.20
37846 NaN 3.0 0.0 18.601 38.0 29.0 -37.694132 142.851548 -37.058014 142.873698 70.838 2018-03-02 20:33:11 62.83 21:36:00 142.10
37850 NaN 2.0 1.0 28.203 18.0 21.0 -38.139260 143.345778 -37.279348 143.604189 98.391 2018-04-17 03:28:41 94.68 5:03:21 152.30
37851 NaN 2.0 1.0 45.696 28.0 1.0 -38.152835 147.793072 -38.162103 144.043047 328.220 2018-03-25 13:50:57 300.50 18:51:27 167.39
37852 NaN 1.0 0.0 27.143 38.0 32.0 -37.613135 142.854194 -36.713765 148.383062 500.500 2018-05-12 08:54:52 491.13 17:05:59 97.44
37854 NaN 1.0 0.0 13.002 27.0 3.0 -37.428594 147.056992 -38.383631 145.590528 167.005 2018-02-20 03:35:43 168.39 6:24:06 92.12
37857 NaN 1.0 0.0 19.468 5.0 31.0 -37.570514 145.253281 -37.824303 143.862472 125.716 2018-04-20 11:48:14 128.43 13:56:39 69.86
37860 NaN 1.0 0.0 10.647 21.0 5.0 -37.239046 143.524656 -37.546884 145.204154 152.434 2018-06-13 23:05:39 154.29 1:39:56 95.65
37861 NaN 2.0 0.0 40.775 22.0 19.0 -36.678761 144.345069 -37.167099 144.257043 54.922 2018-03-27 04:31:43 55.75 5:27:28 120.70
37863 NaN 1.0 0.0 35.410 9.0 16.0 -39.006941 145.406988 -36.525200 143.517906 322.399 2018-07-21 06:48:39 318.77 12:07:25 88.14
37866 NaN 1.0 1.0 40.151 14.0 34.0 -37.644061 146.625820 -38.518928 143.369714 301.447 2018-04-14 07:29:13 298.50 12:27:43 118.89
37869 NaN 3.0 1.0 44.559 33.0 10.0 -38.733722 144.474460 -37.220132 147.498884 314.323 2018-04-18 04:57:37 255.06 9:12:40 201.89
37873 NaN 1.0 0.0 39.038 7.0 22.0 -38.664403 143.702592 -36.670478 144.312065 228.361 2018-02-02 18:03:25 227.77 21:51:11 87.87
37874 NaN 1.0 0.0 7.171 14.0 1.0 -37.589382 146.559422 -38.229669 143.710836 260.125 2018-02-03 22:11:31 258.51 2:30:01 106.47
37875 NaN 1.0 0.0 39.141 5.0 1.0 -37.491746 145.168879 -38.168680 143.936761 131.959 2018-04-19 22:54:02 134.48 1:08:30 94.83
37876 NaN 2.0 0.0 42.330 25.0 5.0 -36.618297 147.643165 -37.563856 145.038804 254.069 2018-01-27 11:06:09 234.09 15:00:14 116.10
37877 NaN 1.0 1.0 43.965 25.0 38.0 -36.542082 147.863736 -37.549355 142.980000 448.104 2018-06-14 22:15:50 440.42 5:36:15 149.26
37879 NaN 1.0 0.0 18.540 36.0 18.0 -36.974023 145.036046 -38.015640 142.955101 217.299 2018-05-28 07:14:50 217.06 10:51:53 76.31
37885 NaN 1.0 0.0 13.544 34.0 38.0 -38.528632 143.386140 -37.561137 142.835312 118.028 2018-04-09 11:48:46 120.99 13:49:45 69.88
37888 NaN 1.0 1.0 34.923 36.0 28.0 -37.044972 144.920592 -38.218799 148.050871 305.307 2018-07-20 03:13:54 302.23 8:16:07 130.70
37889 ID1281653747 1.0 0.0 34.130 8.0 36.0 -38.434373 144.730220 -37.047801 145.309517 162.554 2018-02-06 06:27:55 164.08 9:11:59 69.44
37892 NaN 3.0 1.0 5.416 20.0 33.0 -38.959090 148.294700 -38.930545 144.661887 314.514 2018-04-27 20:53:02 255.21 1:08:14 185.96
37894 NaN 2.0 0.0 41.232 38.0 39.0 -37.657406 142.777301 -38.622040 148.366529 500.901 2018-07-02 08:59:29 455.14 16:34:37 139.79
37897 NaN 1.0 0.0 8.865 9.0 2.0 -38.839254 145.226776 -37.695101 148.251214 293.394 2018-03-11 12:18:21 290.70 17:09:02 88.71
37898 NaN 3.0 1.0 27.153 39.0 16.0 -38.446310 148.292498 -36.739777 143.604529 454.968 2018-07-23 08:29:19 366.09 14:35:24 188.49

37869 rows × 16 columns

任务12：填补 'Post Type'的空值

提示：可能与id有关

data_1[data_1['Post Type'].isnull()]
Id Drone Type Post Type Package Weight Origin Region Destination Region Origin Latitude Origin Longitude Destination Latitude Destination Longitude Journey Distance Departure Date Departure Time Travel Time Delivery Time Delivery Fare
2179 ID5742860733 1.0 NaN 27.126 22.0 34.0 -36.679647 144.463364 -38.469625 143.202877 228.181 2018-02-28 14:01:50 227.59 17:49:25 114.27
4212 ID1710452907 2.0 NaN 37.466 22.0 21.0 -36.695380 144.474875 -37.357030 143.508873 113.113 2018-07-28 11:30:33 107.86 13:18:24 103.59
4219 ID1787036788 1.0 NaN 30.877 3.0 6.0 -38.311620 145.478355 -36.832092 145.600747 165.050 2018-07-18 11:58:01 166.50 14:44:31 75.01
4253 ID1680028038 2.0 NaN 13.860 22.0 19.0 -36.837363 144.515369 -37.089526 144.531402 28.106 2018-06-17 04:43:00 31.74 5:14:44 120.43
6299 ID1377767619 1.0 NaN 45.416 23.0 13.0 -37.720326 146.008038 -38.222304 147.498125 142.197 2018-01-13 09:36:02 144.38 12:00:24 69.85
... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ...
26778 ID1571528676 1.0 NaN 15.062 33.0 9.0 -38.965718 144.650812 -38.962812 145.292314 55.525 2018-04-28 21:13:25 60.51 22:13:55 89.97
28691 ID5883461461 1.0 NaN 40.325 4.0 29.0 -36.542055 143.024959 -37.232887 142.980514 77.003 2018-05-01 04:18:09 81.29 5:39:26 124.92
30861 ID1971183564 3.0 NaN 42.700 19.0 4.0 -37.120747 144.287009 -36.762751 143.078632 114.656 2018-02-25 16:19:23 97.43 17:56:48 144.33
32921 ID5291598933 2.0 NaN 27.189 2.0 7.0 -37.564903 148.097962 -38.797131 143.873724 394.211 2018-01-09 12:39:15 359.59 18:38:50 165.54
36991 ID1484986030 1.0 NaN 27.517 37.0 36.0 -38.938504 147.755657 -37.060130 144.993660 320.003 2018-01-07 20:24:23 316.45 1:40:49 99.58

20 rows × 16 columns

#用投递速度来区分
def speed(df):
    speed = df['Journey Distance']/df['Travel Time']
    return round(speed,2)

data_1.loc[data_1['Post Type']==0].apply(speed,axis=1)


0        1.06
1        0.88
2        1.09
3        1.10
4        1.04
         ... 
37837    0.99
37839    1.08
37841    1.22
37842    1.24
37889    0.99
Length: 26529, dtype: float64

#用投递价格来区分
def price(df):
    price = df['Delivery Fare']/df['Package Weight']
    return round(price,2)
data_1.loc[data_1['Post Type']==0,['Delivery Fare','Package Weight']].apply(price,axis=1)


0         4.58
1         3.81
2        19.53
3         9.18
4        13.78
         ...  
37837    13.20
37839     7.10
37841     4.00
37842    12.92
37889     2.03
Length: 26529, dtype: float64

#ID以5还是1开头为区别
data_1.loc[data_1['Post Type'].isnull(),'Post Type'] = data_1.loc[data_1['Post Type'].isnull(),'Id'].apply(lambda s:s[2]=='1')

任务13：修复 'Origin Longitude'与 'Origin Latitude' 列中错误的值

data_1['Origin Longitude'].describe()
#有负值


count    37844.000000
mean       145.423081
std          6.929107
min       -148.337157
25%        143.964265
50%        145.424189
75%        147.171954
max        148.450576
Name: Origin Longitude, dtype: float64

def fix_Longitude_Latitude(flt):
    if flt<0:
        return -flt
    else:
        return flt
data_1['Origin Longitude'].apply(fix_Longitude_Latitude).describe()


count    37844.000000
mean       145.577375
std          1.764044
min        142.769991
25%        143.966143
50%        145.426161
75%        147.172965
max        148.450576
Name: Origin Longitude, dtype: float64

data_1['Origin Longitude'] = data_1['Origin Longitude'].apply(fix_Longitude_Latitude)


/anaconda3/lib/python3.7/site-packages/ipykernel_launcher.py:1: SettingWithCopyWarning: 
A value is trying to be set on a copy of a slice from a DataFrame.
Try using .loc[row_indexer,col_indexer] = value instead

See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy
  """Entry point for launching an IPython kernel.

data_1['Origin Latitude'].describe()


count    37844.000000
mean       -37.728738
std          1.900393
min        -39.006941
25%        -38.442905
50%        -37.707244
75%        -37.094433
max         38.986998
Name: Origin Latitude, dtype: float64

data_1['Origin Latitude'] = data_1['Origin Latitude'].apply(fix_Longitude_Latitude)


/anaconda3/lib/python3.7/site-packages/ipykernel_launcher.py:1: SettingWithCopyWarning: 
A value is trying to be set on a copy of a slice from a DataFrame.
Try using .loc[row_indexer,col_indexer] = value instead

See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy
  """Entry point for launching an IPython kernel.

任务14：修复 'Destination Latitude' 与 'Destination Longitude'列中错误的值

data_1['Destination Latitude'] = data_1['Destination Latitude'].apply(fix_Longitude_Latitude)


/anaconda3/lib/python3.7/site-packages/ipykernel_launcher.py:1: SettingWithCopyWarning: 
A value is trying to be set on a copy of a slice from a DataFrame.
Try using .loc[row_indexer,col_indexer] = value instead

See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy
  """Entry point for launching an IPython kernel.

data_1['Destination Longitude'] = data_1['Destination Longitude'].apply(fix_Longitude_Latitude)


/anaconda3/lib/python3.7/site-packages/ipykernel_launcher.py:1: SettingWithCopyWarning: 
A value is trying to be set on a copy of a slice from a DataFrame.
Try using .loc[row_indexer,col_indexer] = value instead

See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy
  """Entry point for launching an IPython kernel.

任务 15：填补'Origin Region'和‘Destination Region’中的空值

data_1[data_1['Origin Region'].isnull()]
Id Drone Type Post Type Package Weight Origin Region Destination Region Origin Latitude Origin Longitude Destination Latitude Destination Longitude Journey Distance Departure Date Departure Time Travel Time Delivery Time Delivery Fare
2080 ID1634835235 1.0 0 31.871 NaN 22.0 37.300712 143.610855 36.794684 144.280083 81.904 2018-06-09 03:21:49 86.04 4:47:51 95.70
4442 ID1321659107 2.0 0 12.991 NaN 10.0 38.180248 146.775985 37.396662 147.313624 99.224 2018-04-12 11:25:54 95.42 13:01:19 98.48
8220 ID1522212019 1.0 0 25.282 NaN 23.0 36.756457 148.437792 37.665363 145.818608 253.275 2018-03-27 17:56:31 251.88 22:08:23 94.56
8228 ID1946204979 2.0 0 12.779 NaN 25.0 37.391203 148.116452 36.520786 147.557258 108.914 2018-07-08 10:57:12 104.10 12:41:17 102.49
15023 ID1891262263 1.0 0 36.466 NaN 7.0 37.537619 145.202427 38.723869 143.685908 187.265 2018-03-13 07:21:08 188.00 10:29:08 72.78
20513 ID1490968406 1.0 0 42.648 NaN 9.0 36.654443 143.691689 38.849982 145.479151 290.643 2018-02-16 18:46:02 288.04 23:34:04 99.81
20515 ID5148310393 1.0 1 44.505 NaN 7.0 38.973121 142.989422 38.823172 143.990199 88.293 2018-04-18 23:35:27 92.22 1:07:40 119.24
28710 ID5941350307 1.0 1 17.232 NaN 32.0 37.616289 146.052853 36.696768 148.280560 222.563 2018-02-22 02:17:46 222.16 5:59:55 125.46
34853 ID5234294750 2.0 1 29.063 NaN 7.0 37.048542 142.843945 38.852320 143.963431 223.542 2018-07-14 13:22:02 206.75 16:48:47 151.47
36904 ID1724943602 1.0 0 31.774 NaN 31.0 36.736858 143.688416 37.548149 143.885770 91.993 2018-02-12 14:31:24 95.80 16:07:11 81.57 
data_1.loc[data_1['Origin Region'].notnull()]
Id Drone Type Post Type Package Weight Origin Region Destination Region Origin Latitude Origin Longitude Destination Latitude Destination Longitude Journey Distance Departure Date Departure Time Travel Time Delivery Time Delivery Fare
0 ID1645282128 2.0 0 21.686 19.0 38.0 37.089338 144.429529 37.639134 142.891391 149.212 2018-01-16 09:38:17 140.19 11:58:28 99.25
1 ID1697620764 NaN 0 39.075 15.0 15.0 38.481935 146.009567 38.585528 146.199827 20.185 2018-02-10 04:28:17 22.84 4:51:07 149.04
2 ID1543933503 2.0 0 7.243 33.0 28.0 38.754167 144.509664 38.242224 147.855342 296.975 2018-05-05 01:38:03 272.52 6:10:34 141.48
3 ID1756517608 2.0 0 13.383 10.0 38.0 37.240526 147.568019 37.687178 142.991188 407.396 2018-06-11 11:43:04 371.40 17:54:27 122.82
4 ID1832325834 2.0 0 8.123 1.0 8.0 38.143985 143.798292 38.548315 144.769228 95.974 2018-03-16 14:50:25 92.51 16:22:55 111.97
... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ...
37839 ID1879423081 2.0 0 17.081 20.0 14.0 38.860358 148.174855 37.562224 146.443991 209.278 2018-02-15 12:09:15 193.98 15:23:13 121.33
37840 ID5705840841 1.0 1 35.164 29.0 23.0 37.250331 142.837244 37.739285 145.963420 281.403 2018-01-01 00:07:35 279.10 4:46:41 136.58
37841 ID1276239209 3.0 0 36.704 12.0 11.0 36.578568 145.273744 38.305557 146.997297 245.269 2018-01-24 06:48:05 200.54 10:08:37 146.69
37842 ID1432868583 3.0 0 13.195 37.0 21.0 38.755802 147.744770 37.487866 143.585293 390.599 2018-04-07 18:57:52 315.28 0:13:08 170.52
37889 NaN 1.0 0 34.130 8.0 36.0 38.434373 144.730220 37.047801 145.309517 162.554 2018-02-06 06:27:55 164.08 9:11:59 69.44

37834 rows × 16 columns

data_2 = data_1.loc[data_1['Origin Region'].notnull()]
def fill_region(df):
    longitude,latitude = df['Origin Longitude'],df['Origin Latitude']
    distance_0 = 100
    region_out = ''

    for index in data_2.index:
        longitude_ = data_2.loc[index,'Origin Longitude']
        latitude_ = data_2.loc[index,'Origin Latitude']
        region = data_2.loc[index,'Origin Region']
        
        distance = (longitude-longitude_)**2+(latitude_-latitude)**2
        if distance<distance_0 :
            region_out = region
            distance_0 = distance
    return region_out


data_1.loc[data_1['Origin Region'].isnull()].apply(fill_region,axis=1)


2080     21.0
4442     11.0
8220     32.0
8228      2.0
15023     5.0
20513    16.0
20515    24.0
28710    23.0
34853    29.0
36904    16.0
dtype: float64

data_1.loc[data_1['Origin Region'].isnull()]
Id Drone Type Post Type Package Weight Origin Region Destination Region Origin Latitude Origin Longitude Destination Latitude Destination Longitude Journey Distance Departure Date Departure Time Travel Time Delivery Time Delivery Fare
2080 ID1634835235 1.0 0 31.871 NaN 22.0 37.300712 143.610855 36.794684 144.280083 81.904 2018-06-09 03:21:49 86.04 4:47:51 95.70
4442 ID1321659107 2.0 0 12.991 NaN 10.0 38.180248 146.775985 37.396662 147.313624 99.224 2018-04-12 11:25:54 95.42 13:01:19 98.48
8220 ID1522212019 1.0 0 25.282 NaN 23.0 36.756457 148.437792 37.665363 145.818608 253.275 2018-03-27 17:56:31 251.88 22:08:23 94.56
8228 ID1946204979 2.0 0 12.779 NaN 25.0 37.391203 148.116452 36.520786 147.557258 108.914 2018-07-08 10:57:12 104.10 12:41:17 102.49
15023 ID1891262263 1.0 0 36.466 NaN 7.0 37.537619 145.202427 38.723869 143.685908 187.265 2018-03-13 07:21:08 188.00 10:29:08 72.78
20513 ID1490968406 1.0 0 42.648 NaN 9.0 36.654443 143.691689 38.849982 145.479151 290.643 2018-02-16 18:46:02 288.04 23:34:04 99.81
20515 ID5148310393 1.0 1 44.505 NaN 7.0 38.973121 142.989422 38.823172 143.990199 88.293 2018-04-18 23:35:27 92.22 1:07:40 119.24
28710 ID5941350307 1.0 1 17.232 NaN 32.0 37.616289 146.052853 36.696768 148.280560 222.563 2018-02-22 02:17:46 222.16 5:59:55 125.46
34853 ID5234294750 2.0 1 29.063 NaN 7.0 37.048542 142.843945 38.852320 143.963431 223.542 2018-07-14 13:22:02 206.75 16:48:47 151.47
36904 ID1724943602 1.0 0 31.774 NaN 31.0 36.736858 143.688416 37.548149 143.885770 91.993 2018-02-12 14:31:24 95.80 16:07:11 81.57

任务16：找出 'Departure Date'中错误的值

data_1['Departure Date'].sort_values(ascending=False)


31975    2018-30-06
4990     2018-28-06
17911    2018-28-06
28350    2018-28-05
35740    2018-28-05
            ...    
12702    2018-01-01
36755    2018-01-01
12684    2018-01-01
36782    2018-01-01
27230    2018-01-01
Name: Departure Date, Length: 37844, dtype: object

def fix_date(dt):
    split_ = dt.split('-')
    year,month,day = split_[0],split_[1],split_[2]
    if month > '08':
        return year+'-'+day+'-'+month
    else:
        return dt
data_1['Departure Date'] = data_1['Departure Date'].apply(fix_date)

/anaconda3/lib/python3.7/site-packages/ipykernel_launcher.py:8: SettingWithCopyWarning: 
A value is trying to be set on a copy of a slice from a DataFrame.
Try using .loc[row_indexer,col_indexer] = value instead

See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy

data_1['Departure Date'].sort_values(ascending=False)

17849    2018-07-28
17570    2018-07-28
988      2018-07-28
592      2018-07-28
12772    2018-07-28
            ...    
29092    2018-01-01
5232     2018-01-01
12584    2018-01-01
29041    2018-01-01
5519     2018-01-01
Name: Departure Date, Length: 37844, dtype: object

任务17：输出数据集为‘solution.csv’到当前目录下面

data_1.to_csv('solution.csv')
posted @ 2020-01-16 16:22  小猿取经-林海峰老师  阅读(597)  评论(0)  编辑  收藏  举报