CODEWITHSUNDEEP
pythonpython-2.7csv
Heinz
Heinz

Reputation: 2467

Extract designated data from one csv file then assign to another csv file using python

I got a csv file containing data in this form,

enter image description here

I want to extract data from column C and write them into a new csv file, like this,

enter image description here

So I need to do 2 things：

enter image description here

enter image description here

I need to transpose those data every 22 rows one time and fill them in row starts from B2 position in excel, then B3, B4,...etc.

It's clear that I must loop through every row to do this efficiently, but I don't know how to apply the csv module in python.

Should I download the xlrd package, or can I handle this only use the built-in csv module？

I am working with python 2.7.6 and pyscripter under Windows 8.1 x64. Feel free to give me any suggestion, thanks a lot!

Upvotes: 1

Views: 1906

Answers (1)

bveb333
bveb333

Reputation: 106

Read the csv python documentation.

The simple way to iterate through rows with csv reader:

import csv

X = []
spamreader = csv.reader('path_to_file/filename.csv',delimiter=',')
for row in spamreader:
    X.append(row)

This creates a variable with all the csv data. The structure of your file will make it difficult to read because the cell_separator is ',' but there are also multiple commas within each cell and because of the parentheses there will be a mixture of string and numerical data that will require some cleaning. If you have access to reformatting the csv it might be easier if each cell looked like 1,2,0.01 instead of (1,2,0.01), also consider using a different delimiter between cells such as ';'.

If not expect some tedious data cleaning, and definitely read through the documentation linked above.

Edit: Try the following

import csv
X = [] 
with open('path_to_file/filename.csv','rb') as csvfile:
    spamreader = csv.reader(csvfile,delimiter=',')
    for row in spamreader:
        rowTemp = []
        for i in range(len(row)):
            if (i+1)%3==0:  #gets every third cell
                rowTemp.append(row[i])
        X.append(rowTemp)

This is a matrix of all the distance values. Then try:

with open('path_to_output_file/output_file.csv','wb') as csvfile:
spamwriter = csv.writer(csvfile,delimter=',')
for sublist in X:
    spamwriter.writerow(sublist)

Not sure if this is exactly what you're looking for but it should be close. It ouputs a csv file that is stripped of all the node pairs

Upvotes: 1

Related Questions