寒假数据处理任务总结
任务描述
本次任务要处理的数据共101227行,样例如下:
18 Jogging 102271561469000 -13.53 16.89 -6.4
18 Jogging 102271641608000 -5.75 16.89 -0.46
18 Jogging 102271681617000 -2.18 16.32 11.07
18 Jogging 3.36
18 Downstairs 103260201636000 -4.44 7.06 1.95
18 Downstairs 103260241614000 -3.87 7.55 3.3
18 Downstairs 103260321693000 -4.06 8.08 4.79
18 Downstairs 103260365577000 -6.32 8.66 4.94
18 Downstairs 103260403083000 -5.37 11.22 3.06
18 Downstairs 103260443305000 -5.79 9.92 2.53
6 Walking 0 0 0 3.214402
Step 1
将数据集中所有信息异常的行删除。
比如上面的样例中第4行数据只有3个元素,而其他行都有6个元素,所以第4行是信息异常的行,将其删除。再如第12行数据的第3个元素明显也是有问题的,所以它也是信息异常的行,将其删除。
数据集中可能还会存在一些其他异常。
将全部信息处理之后,每行的元素以逗号为分隔符,写入文件test1
。
文件test1
共100471行,样例如下:
6,Walking,23445542281000,-0.72,9.62,0.14982383
6,Walking,23445592299000,-4.02,11.03,3.445948
6,Walking,23470662276000,0.95,14.71,3.636633
...
step1 思路
第一步主要为处理文件中的异常行,并将文件每行元素以逗号相隔。任务为筛选特定的行,通过观察,筛选的行满足的特征为1.每一行有六个元素2.每一行的第三个数据不为零。
test1.py代码
# test1.py
import csv
input_file="OriginalData.csv"
output_file="test1.csv"
with open(input_file,'r',newline='') as csv_in_file:
with open(output_file,'w',newline='') as csv_out_file:
filereader=csv.reader(csv_in_file, delimiter=' ')
filewriter=csv.writer(csv_out_file, delimiter=',')
for row_list in filereader:
length=len(row_list)
if length==6 and int(row_list[2])!=0:
filewriter.writerow(row_list)
运行结果
Step 2
统计文件test1
的数据中所有动作的数目并打印到屏幕,然后将动作数目对100取整后写入test2
文件,多余的信息行抛弃。比如统计出Jogging
的数量为3021
次,则在屏幕上打印Movement: Jogging Amount: 3021
,然后将前3000行信息写入test2
文件。
文件test2
共100200行。
step2思路
第二步主要为遍历test1
的每一行,统计不同动作的数目,之后创建不同的列表,将动作数目对100取整后写入test2
,但写的代码比较长,可以进行优化。
test2.py代码
#test2.py
import csv
input_file="test1.csv"
output_file="test2.csv"
a = b = c = d = e = f = 0
list1 = []
list2 = []
list3 = []
list4 = []
list5 = []
list6 = []
with open(input_file,'r',newline='') as csv_in_file:
with open(output_file,'w',newline='') as csv_out_file:
filereader=csv.reader(csv_in_file, delimiter=',')
filewriter=csv.writer(csv_out_file, delimiter=',')
for row_list in filereader:
Movement = str(row_list[1])
if Movement == 'Walking':
list1.append(row_list)
a+=1
elif Movement == 'Jogging':
list2.append(row_list)
b+=1
elif Movement == 'Upstairs':
list3.append(row_list)
c+=1
elif Movement == 'Downstairs':
list4.append(row_list)
d+=1
elif Movement == 'Standing':
list5.append(row_list)
e+=1
elif Movement == 'Sitting':
list6.append(row_list)
f+=1
print("Movement: " + "Walking" + "\n" + "Amount: " + str(a))
print("Movement: " + "Jogging" + "\n" + "Amount: " + str(b))
print("Movement: " + "Upstairs" + "\n" + "Amount: " + str(c))
print("Movement: " + "Downstairs" + "\n" + "Amount: " + str(d))
print("Movement: " + "Standing" + "\n" + "Amount: " + str(e))
print("Movement: " + "Sitting" + "\n" + "Amount: " + str(f))
for list in list1[0:36500]:
filewriter.writerow(list)
for list in list2[0:36400]:
filewriter.writerow(list)
for list in list3[0:11000]:
filewriter.writerow(list)
for list in list4[0:8800]:
filewriter.writerow(list)
for list in list5[0:3800]:
filewriter.writerow(list)
for list in list6[0:3700]:
filewriter.writerow(list)
运行结果
Step 3
读取文件test2
的数据,取每行的后3列元素,以空格为分隔符写入文件test3
。
文件test3
共100200行,样例如下:
-0.72 9.62 0.14982383
-4.02 11.03 3.445948
0.95 14.71 3.636633
...
step3思路
第三步主要是列的读取,与书上内容大同小异,只需注意用delimiter将空格符改为空格。
test3.py代码
#test3.py
import csv
input_file="test2.csv"
output_file="test3.csv"
my_columus = [3,4,5]
with open(input_file,'r',newline='') as csv_in_file:
with open(output_file,'w',newline='') as csv_out_file:
filereader=csv.reader(csv_in_file, delimiter=',')
filewriter=csv.writer(csv_out_file, delimiter=' ')
for row_list in filereader:
row_list_output = []
for index_value in my_columus:
row_list_output.append(row_list[index_value])
filewriter.writerow(row_list_output)
运行结果
Step 4
读取文件test3
的数据,每行数据为一组,每组组内的元素以空格为分隔符,组与组之间的数据以逗号为分隔符,每20组元素为一行,写入文件finally
。
文件finally
共5010行,样例如下:
-0.72 9.62 0.14982383,-4.02 11.03 3.445948,0.95 14.71 3.636633,-3.57 5.75 -5.407278,-5.28 8.85 -9.615966,-1.14 15.02 -3.8681788,7.86 11.22 -1.879608,6.28 4.9 -2.3018389,0.95 7.06 -3.445948,-1.61 9.7 0.23154591,6.44 12.18 -0.7627395,5.83 12.07 -0.53119355,7.21 12.41 0.3405087,6.17 12.53 -6.701211,-1.08 17.54 -6.701211,-1.69 16.78 3.214402,-2.3 8.12 -3.486809,-2.91 0 -4.7535014,-2.91 0 -4.7535014,-4.44 1.84 -2.8330324
step4思路
第四步主要为创建两个列表,遍历test3
存入list1,采用计数原则,以20行为一组存入list2写入文件将row_counter清零,list2清空,循环遍历。
test4.py代码
#test4.py
import csv
input_file="test3.csv"
output_file="finally.csv"
list1 = []
list2 = []
row_counter = 0
with open(input_file,'r',newline='') as csv_in_file:
with open(output_file,'w',newline='') as csv_out_file:
filereader = csv.reader(csv_in_file)
filewriter = csv.writer(csv_out_file)
for row in filereader:
list1.append(''.join(row))
for line in list1:
if(row_counter < 20):
list2.append(line)
row_counter += 1
if(row_counter == 20):
filewriter.writerow(list2)
list2=[]
row_counter=0
其中的join函数可以用指定的分隔符进行列表分隔。
运行结果