-
Notifications
You must be signed in to change notification settings - Fork 0
Expand file tree
/
Copy pathdata_preprocessing.py
More file actions
314 lines (236 loc) 路 10.2 KB
/
Copy pathdata_preprocessing.py
File metadata and controls
314 lines (236 loc) 路 10.2 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
import pandas as pd
input1_df = pd.read_csv('input1_df.csv')
print('The data of the CSV file:')
print(input1_df)
# 1.3 讛讚驻住转 诪讬讚注 注诇 诪注专讱 讛谞转讜谞讬诐
print("\nColumns names:")
print(input1_df.columns)
print('\nData types and missing values:')
print(input1_df.info())
#1.4
#get summary of continuos variables
print('\nThe summary of continuos variables')
print(input1_df.describe())
#get summary of continuos and categorical variables
print('\n The summary of continuos and categorical variables')
print(input1_df.describe(include='all'))
num_columns = len(input1_df.columns)
print("\nNumber of columns in the DataFrame:", num_columns)
# 讻诪讛 专砖讜诪讜转 讬砖 讘诪注专讱 讛谞转讜谞讬诐
num_rows = len(input1_df)
print("Number of rows in the DataFrame:", num_rows)
# 诪讛诐 住讜讙讬 讛谞转讜谞讬诐 讘讻诇 注诪讜讚讛
data_types = input1_df.dtypes
print("Data types of each column:\n", data_types)
# 讞讘专讜转 诪讻讜谞讬讜转 驻讜驻讜诇专讬讜转
popular_brands = input1_df['Brand'].value_counts().head(10)
print("\nTop 10 popular car brands:")
print(popular_brands)
# 诪讞讬专 诪讻讬专讛 诪诪讜爪注 诇驻讬 讞讘专讛
average_selling_price_by_brand = input1_df.groupby('Brand')['Price'].mean()
print("\nAverage selling price by brand:")
print(average_selling_price_by_brand)
# 住讜讙 讛讚诇拽
fuel_distribution = input1_df['Fuel'].value_counts()
print("\nFuel type distribution:")
print(fuel_distribution)
#1.5
# 住驻讬专讛 讜讞讬砖讜讘 讗讞讜讝讬诐 砖诇 诪住驻专 讛注专讻讬诐 讛讞住专讬诐 讘讻诇 注诪讜讚讛
missing_values_count = input1_df.isnull().sum()
percentage_missing_values = (missing_values_count / len(input1_df)) * 100
print("\nPercentage of missing values for each column:")
print(percentage_missing_values)
# 讞讬砖讜讘 住讱 诪住驻专 讛谞转讜谞讬诐 讛讞住专讬诐
total_missing_values = missing_values_count.sum()
# 讞讬砖讜讘 讗讞讜讝 讛谞转讜谞讬诐 讛讞住专讬诐 讘拽讜讘抓
total_entries = input1_df.size
percentage_total_missing_values = (total_missing_values / total_entries) * 100
print("Total number of missing values in the DataFrame:", total_missing_values)
print("Percentage of missing values in the entire DataFrame:", percentage_total_missing_values)
#1.8
# 讛讚驻住转 5 讛砖讜专讜转 讛专讗砖讜谞讜转
print("The first 5 rows of the DataFrame:")
print(input1_df.head())
# 讛讚驻住转 5 讛砖讜专讜转 讛讗讞专讜谞讜转
print("\nThe last 5 rows of the DataFrame:")
print(input1_df.tail())
# 讛讚驻住转 5 讻诇砖讛诐 砖讜专讜转 诪讗诪爪注 讛拽讜讘抓
print("\n5 rows from the middle of the DataFrame:")
print(input1_df[480:485])
#1.9
#住讟讟讬住讟讬拽讛 转讬讗讜专讬转
print("\nDescriptive statistics of the DataFrame:")
print(input1_df.describe(include='all'))
#------------
# 拽专讬讗转 讛拽讜讘抓 input2_df.csv
input2_df = pd.read_csv('input2_df.csv')
print('The data of the CSV file:')
print(input2_df)
# 1.3 讛讚驻住转 诪讬讚注 注诇 诪注专讱 讛谞转讜谞讬诐
print("\nColumns names:")
print(input2_df.columns)
print('\nData types and missing values:')
print(input2_df.info())
# 1.4 拽讘诇转 住讬讻讜诐 砖诇 诪砖转谞讬诐 专爪讬驻讬诐
print('\nThe summary of continuous variables:')
print(input2_df.describe())
# 拽讘诇转 住讬讻讜诐 砖诇 诪砖转谞讬诐 专爪讬驻讬诐 讜拽讟讙讜专讬讬诐
print('\nThe summary of continuous and categorical variables:')
print(input2_df.describe(include='all'))
# 诪住驻专 讛注诪讜讚讜转 讘诪注专讱 讛谞转讜谞讬诐
num_columns = len(input2_df.columns)
print("\nNumber of columns in the DataFrame:", num_columns)
# 诪住驻专 讛专砖讜诪讜转 讘诪注专讱 讛谞转讜谞讬诐
num_rows = len(input2_df)
print("Number of rows in the DataFrame:", num_rows)
# 诪讛诐 住讜讙讬 讛谞转讜谞讬诐 讘讻诇 注诪讜讚讛
data_types = input2_df.dtypes
print("\nData types of each column:\n", data_types)
# 诪讜转讙讬诐 驻讜驻讜诇专讬讬诐 讘拽讜讘抓
popular_brands = input2_df['Brand'].value_counts().head(10)
print("\nTop 10 popular car brands:")
print(popular_brands)
# 讛爪讙转 诪诪讜爪注 讚讬专讜讙 诇驻讬 诪讜转讙 (讘诪讬讚讛 讜讬砖)
if 'rating' in input2_df.columns:
average_rating_by_brand = input2_df.groupby('Brand')['rating'].mean()
print("\nAverage rating by brand:")
print(average_rating_by_brand)
else:
print("\n'Rating' column not found in the DataFrame.")
# 讛爪讙转 转驻诇讙讜转 住讜讙讬 讛诪谞讜注 (讗诐 拽讬讬诪转)
if 'engine_type' in input2_df.columns:
engine_type_distribution = input2_df['engine_type'].value_counts()
print("\nEngine type distribution:")
print(engine_type_distribution)
else:
print("\n'Engine_Type' column not found in the DataFrame.")
# 1.5 住驻讬专讛 讜讗讞讜讝讬诐 砖诇 诪住驻专 讛注专讻讬诐 讛讞住专讬诐 讘讻诇 注诪讜讚讛
missing_values_count = input2_df.isnull().sum()
percentage_missing_values = (missing_values_count / len(input2_df)) * 100
print("\nPercentage of missing values for each column:")
print(percentage_missing_values)
# 讞讬砖讜讘 住讱 诪住驻专 讛谞转讜谞讬诐 讛讞住专讬诐
total_missing_values = missing_values_count.sum()
# 讞讬砖讜讘 讗讞讜讝 讛谞转讜谞讬诐 讛讞住专讬诐 讘拽讜讘抓
total_entries = input2_df.size
percentage_total_missing_values = (total_missing_values / total_entries) * 100
print("Total number of missing values in the DataFrame:", total_missing_values)
print("Percentage of missing values in the entire DataFrame:", percentage_total_missing_values)
#1.11
# 讬讬讘讜讗 讛-DataFrames
input1_df = pd.read_csv('input1_df.csv') # 谞转讜谞讬 专讻讘
input2_df = pd.read_csv('input2_df.csv') # 讘讬拽讜专讜转 专讻讘
#诪讬讝讜讙
outer_join_df = input1_df.merge(input2_df, on='Brand', how='outer')
# 讛讚驻住转 5 讛砖讜专讜转 讛专讗砖讜谞讜转 诪讛-DataFrame 讛诪诪讜讝讙
print(outer_join_df.head())
# 讘讚讬拽转 注专讻讬诐 讞住专讬诐 讘-DataFrame 讛诪诪讜讝讙
missing_values_count = outer_join_df.isnull().sum()
print("\nMissing values in the merged DataFrame:")
print(missing_values_count)
# 讞讬砖讜讘 讗讞讜讝讬 讛注专讻讬诐 讛讞住专讬诐
percentage_missing_values = (missing_values_count / len(outer_join_df)) * 100
print("\nPercentage of missing values in the merged DataFrame:")
print(percentage_missing_values)
# 住讱 讻诇 讛注专讻讬诐 讛讞住专讬诐 讘诪注专讱 讛谞转讜谞讬诐 讛诪诪讜讝讙
total_missing_values = missing_values_count.sum()
print("Total number of missing values in the merged DataFrame:", total_missing_values)
# 讗讞讜讝 讛注专讻讬诐 讛讞住专讬诐 讘诪注专讱 讛谞转讜谞讬诐 讛诪诪讜讝讙 讻讜诇讜
percentage_total_missing = (total_missing_values / (len(outer_join_df) * len(outer_join_df.columns))) * 100
print("Percentage of missing values in the entire merged DataFrame:", percentage_total_missing)
# # 砖诪讬专转 讛拽讜讘抓 讛诪诪讜讝讙
# merged_df.to_csv('merged_car_data.csv', index=False)
#
# # 讛爪讙转 讛谞转讜谞讬诐 讛诪诪讜讝讙讬诐
# print('The merged data of the CSV file:')
# print(merged_df)
#1.12
# 讘讞讬专转 注诪讜讚讜转 注讘讜专 df1
df1 = outer_join_df[['Brand', 'Model', 'Year', 'Engine_Type', 'Price']]
# 讘讞讬专转 注诪讜讚讜转 注讘讜专 df2
df2 = outer_join_df[['Brand', 'Rating', 'Fuel', 'Horsepower', 'Transmission']]
df1.to_csv('df1.csv', index=False)
df2.to_csv('df2.csv', index=False)
# 讛讚驻住转 讛-DataFrame 讛讞讚砖讬诐
print("\nDataFrame df1:")
print(df1.head())
print("\nDataFrame df2:")
print(df2.head())
#---------
import pandas as pd
import numpy as np
# 拽专讬讗转 讛拽讘爪讬诐 讛诪拽讜专讬讬诐
df1 = pd.read_csv('df1.csv')
df2 = pd.read_csv('df2.csv')
#----------------------------------2.1
#转讬拽讜谉 谞转讜谞讬诐 诇讗 诪转讗讬诪讬诐 讘注诪讜讚讜转 诪住驻专讬讜转
# 注诪讜讚转 Year 讜-Price 讘-df1
for col in ['Year', 'Price']:
df1[col] = pd.to_numeric(df1[col], errors='coerce')
# 注诪讜讚转 Horsepower 讘-df2 讗诐 拽讬讬诪转
if 'Horsepower' in df2.columns:
df2['Horsepower'] = pd.to_numeric(df2['Horsepower'], errors='coerce')
print("\nCleaned df1:")
print(df1.head())
print("\nCleaned df2:")
print(df2.head())
#---------------2.2
#转讬拽讜谉 谞转讜谞讬诐 诇讗 诪转讗讬诪讬诐 讘注诪讜讚讜转 讟拽住讟
# 注诪讜讚讜转 讟拽住讟 讘-df1
for col in ['Brand', 'Model', 'Engine_Type']:
df1[col] = df1[col].apply(lambda x: x if isinstance(x, str) else np.nan)
# 注诪讜讚讜转 讟拽住讟 讘-df2
for col in ['Brand', 'Fuel', 'Transmission']:
df2[col] = df2[col].apply(lambda x: x if isinstance(x, str) else np.nan)
print("\nFixed text columns in df1:")
print(df1.head())
print("\nFixed text columns in df2:")
print(df2.head())
#---------------2.3
#讟讬驻讜诇 讘注专讻讬诐 讞住专讬诐
# 诪讬诇讜讬 注专讻讬诐 讞住专讬诐 讘诪诪讜爪注, 讛砖讻讬讞 讗讜 讛讞爪讬讜谉 讘注诪讜讚讜转 诪住驻专讬讜转 讜讟拽住讟
df1['Year'] = df1['Year'].fillna(df1['Year'].median())
df1['Price'] = df1['Price'].fillna(df1['Price'].mean())
df1['Model'] = df1['Model'].fillna(df1['Model'].mode()[0])
df1['Engine_Type'] = df1['Engine_Type'].fillna(df1['Engine_Type'].mode()[0])
df2['Rating'] = df2['Rating'].fillna(df2['Rating'].mean())
df2['Horsepower'] = df2['Horsepower'].fillna(df2['Horsepower'].median())
df2['Fuel'] = df2['Fuel'].fillna(df2['Fuel'].mode()[0])
df2['Transmission'] = df2['Transmission'].fillna(df2['Transmission'].mode()[0])
print("\nMissing values after handling in df1:")
print(df1.isnull().sum())
print("\nMissing values after handling in df2:")
print(df2.isnull().sum())
#---------------2.4
#谞专诪讜诇 注诪讜讚讜转 诪住驻专讬讜转
# Normalizing 'Year' and 'Price' columns in df1
df1['Year_norm'] = df1['Year'] / df1['Year'].max()
df1['Price_norm'] = df1['Price'] / df1['Price'].max()
# Normalizing 'Horsepower' column in df2 if exists
if 'Horsepower' in df2.columns:
df2['Horsepower_norm'] = df2['Horsepower'] / df2['Horsepower'].max()
print("\nNormalized df1:")
print(df1.head())
print("\nNormalized df2:")
print(df2.head())
#----------------2.5
#讛讚驻住转 砖讜专讜转 讻驻讜诇讜转
# 讛讚驻住转 砖讜专讜转 讻驻讜诇讜转 讘df1 讜诪讞讬拽转 讻驻讬诇讜讬讜转
df1_duplicates = df1[df1.duplicated()]
print("\nDuplicate rows in df1:")
print(df1_duplicates)
df1.drop_duplicates(inplace=True)
df1.reset_index(drop=True, inplace=True)
print("\nDataFrame df1 without duplicates:")
print(df1.head())
# 讛讚驻住转 砖讜专讜转 讻驻讜诇讜转 讘df2 讜诪讞讬拽转 讻驻讬诇讜讬讜转
df2_duplicates = df2[df2.duplicated()]
print("\nDuplicate rows in df2:")
print(df2_duplicates)
df2.drop_duplicates(inplace=True)
df2.reset_index(drop=True, inplace=True)
print("\nDataFrame df2 without duplicates:")
print(df2.head())
# 砖诪讬专转 讛谞转讜谞讬诐 讛诪注讜讚讻谞讬诐 讞讝专讛 诇-CSV
df1.to_csv('df1.csv', index=False)
df2.to_csv('df2.csv', index=False)