数据清洗数据清洗的对象就是缺失值，重复值，异常值一 重复值的处理一般用删除法，但是在业务上有意义的重复值不能删除import numpy as npimport pandas as pdimport matplotlib.pyplot as plt import osimport missingno as msnoos.chdir(r"D:/jupyter_notebook/eg/eg3-零售行业数据分析")data = pd.read_csv("data.csv",encoding="ISO-8859-1")data.head()InvoiceNoStockCodeDescriptionQuantityInvoiceDateUnitPriceCustomerIDCountry053636585123AWHITE HANGING HEART T-LIGHT HOLDER612/1/ 8:262.5517850.0United Kingdom153636571053WHITE METAL LANTERN612/1/ 8:263.3917850.0United Kingdom253636584406BCREAM CUPID HEARTS COAT HANGER812/1/ 8:262.7517850.0United Kingdom353636584029GKNITTED UNION FLAG HOT WATER BOTTLE612/1/ 8:263.3917850.0United Kingdom453636584029ERED WOOLLY HOTTIE WHITE HEART.612/1/ 8:263.3917850.0United Kingdom#1.判断是否有重复值data.duplicated()#2 查看某行是否有重复值data.duplicated(subset = ['StockCode'],keep='last')0True1True2True3True4True5True6True7True8True9True10 True11 True12 True13 True14 True15 True16 True17 True18 True19 True20 True21 True22 True23 True24 True25 True26 True27 True28 True29 True... 541879True541880True541881 False541882 False541883 False541884 False541885 False541886 False541887 False541888 False541889 False541890 False541891 False541892 False541893 False541894 False541895 False541896 False541897 False541898 False541899 False541900 False541901 False541902 False541903 False541904 False541905 False541906 False541907 False541908 FalseLength: 541909, dtype: bool#3 查看有多少行重复np.sum(data.duplicated())#4 查看某一列有多少重复值np.sum(data.duplicated(subset=['StockCode']))537839#5 删除重复行data.drop_duplicates(inplace=True)#6 根据列重复进行删除data.drop_duplicates(subset=['StockCode'],keep='first')InvoiceNoStockCodeDescriptionQuantityInvoiceDateUnitPriceCustomerIDCountry053636585123AWHITE HANGING HEART T-LIGHT HOLDER612/1/ 8:262.5517850.0United Kingdom153636571053WHITE METAL LANTERN612/1/ 8:263.3917850.0United Kingdom253636584406BCREAM CUPID HEARTS COAT HANGER812/1/ 8:262.7517850.0United Kingdom353636584029GKNITTED UNION FLAG HOT WATER BOTTLE612/1/ 8:263.3917850.0United Kingdom453636584029ERED WOOLLY HOTTIE WHITE HEART.612/1/ 8:263.3917850.0United Kingdom553636522752SET 7 BABUSHKA NESTING BOXES212/1/ 8:267.6517850.0United Kingdom653636521730GLASS STAR FROSTED T-LIGHT HOLDER612/1/ 8:264.2517850.0United Kingdom753636622633HAND WARMER UNION JACK612/1/ 8:281.8517850.0United Kingdom853636622632HAND WARMER RED POLKA DOT612/1/ 8:281.8517850.0United Kingdom953636784879ASSORTED COLOUR BIRD ORNAMENT3212/1/ 8:341.6913047.0United Kingdom1053636722745POPPY'S PLAYHOUSE BEDROOM612/1/ 8:342.1013047.0United Kingdom1153636722748POPPY'S PLAYHOUSE KITCHEN612/1/ 8:342.1013047.0United Kingdom1253636722749FELTCRAFT PRINCESS CHARLOTTE DOLL812/1/ 8:343.7513047.0United Kingdom1353636722310IVORY KNITTED MUG COSY612/1/ 8:341.6513047.0United Kingdom1453636784969BOX OF 6 ASSORTED COLOUR TEASPOONS612/1/ 8:344.2513047.0United Kingdom1553636722623BOX OF VINTAGE JIGSAW BLOCKS312/1/ 8:344.9513047.0United Kingdom1653636722622BOX OF VINTAGE ALPHABET BLOCKS212/1/ 8:349.9513047.0United Kingdom1753636721754HOME BUILDING BLOCK WORD312/1/ 8:345.9513047.0United Kingdom1853636721755LOVE BUILDING BLOCK WORD312/1/ 8:345.9513047.0United Kingdom1953636721777RECIPE BOX WITH METAL HEART412/1/ 8:347.9513047.0United Kingdom2053636748187DOORMAT NEW ENGLAND412/1/ 8:347.9513047.0United Kingdom2153636822960JAM MAKING SET WITH JARS612/1/ 8:344.2513047.0United Kingdom2253636822913RED COAT RACK PARIS FASHION312/1/ 8:344.9513047.0United Kingdom2353636822912YELLOW COAT RACK PARIS FASHION312/1/ 8:344.9513047.0United Kingdom2453636822914BLUE COAT RACK PARIS FASHION312/1/ 8:344.9513047.0United Kingdom2553636921756BATH BUILDING BLOCK WORD312/1/ 8:355.9513047.0United Kingdom2653637022728ALARM CLOCK BAKELIKE PINK2412/1/ 8:453.7512583.0France2753637022727ALARM CLOCK BAKELIKE RED2412/1/ 8:453.7512583.0France2853637022726ALARM CLOCK BAKELIKE GREEN1212/1/ 8:453.7512583.0France2953637021724PANDA AND BUNNIES STICKER SHEET1212/1/ 8:450.8512583.0France...........................47116857662423449SET OF 6 EASTER RAINBOW CHICKS1211/15/ 17:141.2516156.0United Kingdom47573157691123428IVORY RETRO KITCHEN WALL CLOCK111/17/ 10:018.1517461.0United Kingdom47807357707823652SET 10 CARD CHRISTMAS STAMPS 16963211/17/ 15:172.91NaNUnited Kingdom48338857750384206BCAT WITH SUNGLASSES BLANK CARD511/20/ 12:340.1918110.0United Kingdom48512657755423430BLUE RETRO KITCHEN WALL CLOCK111/20/ 15:358.1515533.0United Kingdom48575957763323429RED RETRO KITCHEN WALL CLOCK211/21/ 10:358.1514194.0United Kingdom49146857806790179BPURPLE FINE BEAD NECKLACE W TASSEL111/22/ 15:435.82NaNUnited Kingdom49257757812923441HAND PAINTED HANGING EASTER EGG1011/23/ 10:390.4213502.0United Kingdom49298857814972802cVANILLA SCENT CANDLE JEWELLED BOX111/23/ 11:118.29NaNUnited Kingdom49301157814985049cROMANTIC PINKS RIBBONS111/23/ 11:112.46NaNUnited Kingdom49730157836084971LNaN211/24/ 10:360.00NaNUnited Kingdom49891457854423440PAINT YOUR OWN EGGS IN CRATE611/24/ 15:012.0817096.0United Kingdom49991357870223552BICYCLE PUNCTURE REPAIR KIT611/25/ 10:582.0815382.0United Kingdom49991457870223498CLASSIC BICYCLE CLIPS1211/25/ 10:581.4515382.0United Kingdom50115657882484550CROCHET LILAC/RED BEAR KEYRING111/25/ 14:021.6517883.0United Kingdom50204657883384971lLARGE HEART FLOWERS HOOK211/25/ 15:232.46NaNUnited Kingdom50204757883385034b3 WHITE CHOC MORRIS BOXED CANDLES111/25/ 15:238.29NaNUnited Kingdom50297157885623578SNACK TRAY RED GINGHAM311/27/ 11:181.9517769.0United Kingdom50315057892123580SNACK TRAY HAPPY FOREST211/27/ 11:571.9513596.0United Kingdom50319557892123575SNACK TRAY PAISLEY PARK311/27/ 11:571.9513596.0United Kingdom50319857892123560SET OF 6 RIBBONS COUNTRY STYLE111/27/ 11:572.8913596.0United Kingdom50337557892523576SNACK TRAY RED VINTAGE DOILY111/27/ 12:131.9517365.0United Kingdom50360257892823562SET OF 6 RIBBONS PERFECTLY PRETTY311/27/ 12:292.8915443.0United Kingdom50410457894223561SET OF 6 RIBBONS PARTY111/27/ 13:292.8917288.0United Kingdom50786757918723609SET 10 CARDS SNOWY ROBIN 17099111/28/ 15:312.91NaNUnited Kingdom50936957929785179aGREEN BITTY LIGHT CHAIN111/29/ 11:232.46NaNUnited Kingdom51258857951223617SET 10 CARDS SWIRLY XMAS TREE 17104211/29/ 16:472.91NaNUnited Kingdom52706558069190214ULETTER "U" BLING KEY RING1212/5/ 15:480.2913790.0United Kingdom53722458123847591bSCOTTIES CHILDRENS APRON112/8/ 10:534.13NaNUnited Kingdom54042158148323843PAPER CRAFT , LITTLE BIRDIE8099512/9/ 9:152.0816446.0United Kingdom4070 rows × 8 columns二 缺失值的处理采用删除法，替换法，插值法 。缺失值需要根据实际情况定义#1. 缺失值判断data.isnull()#2. 缺失值计数np.sum(data.isnull(),axis=0) #默认沿着行操作，对列进行统计#3. 计算缺失值比例data.apply(lambda x: sum(x.isnull())/len(x),axis=0)InvoiceNo0.000000StockCode0.000000Description 0.002709Quantity 0.000000InvoiceDate 0.000000UnitPrice0.000000CustomerID0.251634Country 0.000000dtype: float64#4. 删除缺失值data.dropna()#一行上有一个缺失值就会删除此行#删除行数据缺失值data.dropna(how='any') #all - 全部，any- 任意data.head()InvoiceNoStockCodeDescriptionQuantityInvoiceDateUnitPriceCustomerIDCountry053636585123AWHITE HANGING HEART T-LIGHT HOLDER612/1/ 8:262.5517850.0United Kingdom153636571053WHITE METAL LANTERN612/1/ 8:263.3917850.0United Kingdom253636584406BCREAM CUPID HEARTS COAT HANGER812/1/ 8:262.7517850.0United Kingdom353636584029GKNITTED UNION FLAG HOT WATER BOTTLE612/1/ 8:263.3917850.0United Kingdom453636584029ERED WOOLLY HOTTIE WHITE HEART.612/1/ 8:263.3917850.0United Kingdom#5. 填充缺失值 #5,1 分类数据用众数填补data.Description.fillna(data.Description.mode()[0])#5.2 数值型数据用均值，中位数填补data.CustomerID.fillna(data.CustomerID.mean())data.CustomerID.fillna(data.CustomerID.median())data.CustomerID.fillna(1000)#5.3 不同值采取不同处理方式-字典形式填补data.fillna(value={'CustomerID':data.CustomerID.mean(),'Description':data.Description.mode()[0]})#5.4 前向填补和后向填补data.fillna(method='ffil') #第一个数据缺失无法填充data.fillna(method='bfill') #最后一个数据缺失无法填充#5.5 差值填补（一般不用） - 根据线性插值函数自动计算data.interpolate(method='polynomial',order=1)InvoiceNoStockCodeDescriptionQuantityInvoiceDateUnitPriceCustomerIDCountry053636585123AWHITE HANGING HEART T-LIGHT HOLDER612/1/ 8:262.5517850.0United Kingdom153636571053WHITE METAL LANTERN612/1/ 8:263.3917850.0United Kingdom253636584406BCREAM CUPID HEARTS COAT HANGER812/1/ 8:262.7517850.0United Kingdom353636584029GKNITTED UNION FLAG HOT WATER BOTTLE612/1/ 8:263.3917850.0United Kingdom453636584029ERED WOOLLY HOTTIE WHITE HEART.612/1/ 8:263.3917850.0United Kingdom553636522752SET 7 BABUSHKA NESTING BOXES212/1/ 8:267.6517850.0United Kingdom653636521730GLASS STAR FROSTED T-LIGHT HOLDER612/1/ 8:264.2517850.0United Kingdom753636622633HAND WARMER UNION JACK612/1/ 8:281.8517850.0United Kingdom853636622632HAND WARMER RED POLKA DOT612/1/ 8:281.8517850.0United Kingdom953636784879ASSORTED COLOUR BIRD ORNAMENT3212/1/ 8:341.6913047.0United Kingdom1053636722745POPPY'S PLAYHOUSE BEDROOM612/1/ 8:342.1013047.0United Kingdom1153636722748POPPY'S PLAYHOUSE KITCHEN612/1/ 8:342.1013047.0United Kingdom1253636722749FELTCRAFT PRINCESS CHARLOTTE DOLL812/1/ 8:343.7513047.0United Kingdom1353636722310IVORY KNITTED MUG COSY612/1/ 8:341.6513047.0United Kingdom1453636784969BOX OF 6 ASSORTED COLOUR TEASPOONS612/1/ 8:344.2513047.0United Kingdom1553636722623BOX OF VINTAGE JIGSAW BLOCKS312/1/ 8:344.9513047.0United Kingdom1653636722622BOX OF VINTAGE ALPHABET BLOCKS212/1/ 8:349.9513047.0United Kingdom1753636721754HOME BUILDING BLOCK WORD312/1/ 8:345.9513047.0United Kingdom1853636721755LOVE BUILDING BLOCK WORD312/1/ 8:345.9513047.0United Kingdom1953636721777RECIPE BOX WITH METAL HEART412/1/ 8:347.9513047.0United Kingdom2053636748187DOORMAT NEW ENGLAND412/1/ 8:347.9513047.0United Kingdom2153636822960JAM MAKING SET WITH JARS612/1/ 8:344.2513047.0United Kingdom2253636822913RED COAT RACK PARIS FASHION312/1/ 8:344.9513047.0United Kingdom2353636822912YELLOW COAT RACK PARIS FASHION312/1/ 8:344.9513047.0United Kingdom2453636822914BLUE COAT RACK PARIS FASHION312/1/ 8:344.9513047.0United Kingdom2553636921756BATH BUILDING BLOCK WORD312/1/ 8:355.9513047.0United Kingdom2653637022728ALARM CLOCK BAKELIKE PINK2412/1/ 8:453.7512583.0France2753637022727ALARM CLOCK BAKELIKE RED2412/1/ 8:453.7512583.0France2853637022726ALARM CLOCK BAKELIKE GREEN1212/1/ 8:453.7512583.0France2953637021724PANDA AND BUNNIES STICKER SHEET1212/1/ 8:450.8512583.0France...........................54187958158522726ALARM CLOCK BAKELIKE GREEN812/9/ 12:313.7515804.0United Kingdom54188058158522727ALARM CLOCK BAKELIKE RED412/9/ 12:313.7515804.0United Kingdom54188158158516016LARGE CHINESE STYLE SCISSOR1012/9/ 12:310.8515804.0United Kingdom54188258158521916SET 12 RETRO WHITE CHALK STICKS2412/9/ 12:310.4215804.0United Kingdom54188358158584692BOX OF 24 COCKTAIL PARASOLS2512/9/ 12:310.4215804.0United Kingdom54188458158584946ANTIQUE SILVER T-LIGHT GLASS1212/9/ 12:311.2515804.0United Kingdom54188558158521684SMALL MEDINA STAMPED METAL BOWL1212/9/ 12:310.8515804.0United Kingdom54188658158522398MAGNETS PACK OF 4 SWALLOWS1212/9/ 12:310.3915804.0United Kingdom54188758158523328SET 6 SCHOOL MILK BOTTLES IN CRATE412/9/ 12:313.7515804.0United Kingdom54188858158523145ZINC T-LIGHT HOLDER STAR LARGE1212/9/ 12:310.9515804.0United Kingdom54188958158522466FAIRY TALE COTTAGE NIGHT LIGHT1212/9/ 12:311.9515804.0United Kingdom54189058158622061LARGE CAKE STAND HANGING STRAWBERY812/9/ 12:492.9513113.0United Kingdom54189158158623275SET OF 3 HANGING OWLS OLLIE BEAK2412/9/ 12:491.2513113.0United Kingdom54189258158621217RED RETROSPOT ROUND CAKE TINS2412/9/ 12:498.9513113.0United Kingdom54189358158620685DOORMAT RED RETROSPOT1012/9/ 12:497.0813113.0United Kingdom54189458158722631CIRCUS PARADE LUNCH BOX1212/9/ 12:501.9512680.0France54189558158722556PLASTERS IN TIN CIRCUS PARADE1212/9/ 12:501.6512680.0France54189658158722555PLASTERS IN TIN STRONGMAN1212/9/ 12:501.6512680.0France54189758158722728ALARM CLOCK BAKELIKE PINK412/9/ 12:503.7512680.0France54189858158722727ALARM CLOCK BAKELIKE RED412/9/ 12:503.7512680.0France54189958158722726ALARM CLOCK BAKELIKE GREEN412/9/ 12:503.7512680.0France54190058158722730ALARM CLOCK BAKELIKE IVORY412/9/ 12:503.7512680.0France54190158158722367CHILDRENS APRON SPACEBOY DESIGN812/9/ 12:501.9512680.0France54190258158722629SPACEBOY LUNCH BOX1212/9/ 12:501.9512680.0France54190358158723256CHILDRENS CUTLERY SPACEBOY412/9/ 12:504.1512680.0France54190458158722613PACK OF 20 SPACEBOY NAPKINS1212/9/ 12:500.8512680.0France54190558158722899CHILDREN'S APRON DOLLY GIRL612/9/ 12:502.1012680.0France54190658158723254CHILDRENS CUTLERY DOLLY GIRL412/9/ 12:504.1512680.0France54190758158723255CHILDRENS CUTLERY CIRCUS PARADE412/9/ 12:504.1512680.0France54190858158722138BAKING SET 9 PIECE RETROSPOT312/9/ 12:504.9512680.0France536641 rows × 8 columns三 异常值的处理异常值：偏离正常范围的值，不是错误值，出现频率较低异常值判断：均值±两个标准差为正常值，范围之外则为异常值，（箱线图判断）data['price'] = data['Quantity']*data['UnitPrice']q_mean = data['price'].mean()q_std = data['price'].std()any(data['price']>q_mean+2*q_std)any(data['price']<q_mean-2*q_std)True#2分位数判断#2.1 下四分位数Q1=data['price'].quantile(0.25)#2.2 上四分位数Q3=data['price'].quantile(0.75)#2.3 分位差IQR= Q3-Q1any(data['price']>Q3 + 1.5*IQR)Trueany(data['price']>Q1 - 1.5*IQR)True#2.4 箱线图plt.figure(figsize=(6.4,4.8))data['price'].plot(kind="box")<matplotlib.axes._subplots.AxesSubplot at 0x28636ab1400>#3. 异常值处理 - 替换值UL = Q3+1.5*IQR#3.1 计算数据在正常范围内的最大值replace_value = data['price'][data['price']<UL].max() print(replace_value)#3.2 进行离群点的替换data.loc[data['price']<UL,'price'] = replace_value23.400000000000002data['price'].describe()count 541909.000000mean 114.159394std 2186.736084min23.40000025%28.75000050%48.30000075%95.450000max896310.000000Name: price, dtype: float64#3.2 百分数替换 - 百分之一分位数替换，百分之九十九分位数替换quantile()
如果觉得《数据清洗 - 缺失值 异常值 重复值处理》对你有帮助，请点赞、收藏，并留下你的观点哦！
