寒假数据处理任务总结

寒假数据处理任务总结

任务描述

本次任务要处理的数据共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函数可以用指定的分隔符进行列表分隔。

运行结果