Python - Read and assign data from a .csv to pre-defined variables
<p>I am trying to read data from a CSV file (A), extract data, and write that to a different CSV file (B). In the new file B, I want to have two rows. The first row should contain all the predefined variables and row 2 should be filled with all the values that belong to the specific variable in row 1.</p>

<p>I hope anyone can tell me the best way to achieve this. (i added the .csv file i used at the end of this post)</p>

<p><strong>(A) Python code</strong></p>

<pre><code>import re
import csv

#Call for the export file
data = open('C:/Exports/Export 3.csv')

#Make a list with the predefined variables
definition = ["record_id", "abbreviation", "study_id", "step_count",
"distance", "ambulation_time", "velocity", "cadence", "norm_velocity",
"step_time_differential", "step_length_differential",
"cycle_time_differential", "step_time", "step_length", "step_extremity",
"cycle_time", "stride_length", "hh_base_support", "swing_time",
"stance_time", "single_support_time", "double_support_time", "toe_in_out"]

my_data = {}

#Show data for each row without whitespace
for line in data:
    line = line.rstrip()
    #print(line)
    values = re.findall("-?[0-9].+", line)
    print(values)
</code></pre>

<p><strong>This is a part of the output the above code will generate:</strong></p>

<pre><code>[]
['3;']
['292,34;']
['1,67;']
['175,1;']
['107,8;']
[]
['0,004;']
['1,051;']
['0,008;']
[]
[]
['0,558;0,554']
['96,746;97,797']
[]
['1,116;1,108']
['192,159;197,122']
['2,988;6,32']
['0,466;0,466']
['0,65;0,642']
['0,466;0,466']
['0,184;0,176']
['41,8;42,1']
['58,2;57,9']
['41,8;42,1']
['16,5;15,9']
['-1,1;4']
</code></pre>

<p>As you can see in the output code, there are some lines that contain two values like: ['2,988;6,32'] these need to become 1 value, by calculating the average over the two values before writing them to a csv file.</p>

<p><strong>(B) Desired output</strong></p>

<pre><code>record_id  abbreviation  study_id  step_count  distance 
1                                  3           292,34
</code></pre>

<p>If you like, you can play with the export file you can download it here:
<a href="http://s000.tinyupload.com/?file_id=03062446993293567917" rel="nofollow">CSV export file</a></p>