Import only first line of multiple csv, dummycode repeats and calculate conditional probability
<p>I am actually a R savvy person but for this task I am using python and am overwhelmed by little things. I really hope you guys may help me.</p>

<p>I have three large csv files that contain ids in the first column. Each csv contains presence data of a day and each unique id stands for a person. csv1 is day 1, csv2 is day 2 and csv3 is day 3.</p>

<p>What I want to do now is to import only the first column for each csv because I don't need the rest and the files are quite big. Then I'd like to compare the appearance for each person (so the appearance for each id) and dummycode the information into one data frame where the first column contains the unique ids of all three csv files, the second a binary code for the presence of the id in the first csv (0=id doesn't appear in csv1, 1=id appaers in csv1) and then in column two do the same for csv2 and so on.</p>

<p>Last but not least, I'd like then to calculate the conditional probability for example for the case that a person that didn't came the first two days would show up on the third.</p>

<p>Of course a whole solution would be great but I would be pretty happy with baby steps and some python for dummies advice here, too. The thing is that I know how to do this with R but not with python and I would really appreciate your help.</p>

<p>Thank you very much and excuse my English.</p>

<p>If you need sample data, maybe this helps (the real data is huge):</p>

<pre><code>    csv1
id col2 col3
1  x    x 
2  x    x
7  x    x

    csv2
id col2 col3
1  x    x 
3  x    x
7  x    x
4  x    x

    csv3
id col2 col3
1  x    x 
2  x    x
3  x    x
4  x    x
5  x    x
6  x    x
</code></pre>

<p>Dummycoded df would be:</p>

<pre><code>    df
id csv1 csv2 csv3
1  1    1    1 
2  1    0    1
3  0    1    1
4  0    1    1
5  0    0    1
6  0    0    1
7  1    1    0
</code></pre>

<p>You can simply open your <code>csv</code> files and store all the first columns (ids) in a list:</p>

<pre><code>with open(csv1_name) as f1, open(csv2_name) as f2,open(csv3_name) as f3:
    column1 = next(zip(*csv.reader(f1))) # in python 2 use zip(*csv.reader(f1))[0]
    column2 = next(zip(*csv.reader(f2)))
    column3 = next(zip(*csv.reader(f3)))
</code></pre>

<p>Now you have the indices of the items in new matrix that should be 1. So you can use a list comprehension to create your columns:</p>

<pre><code>a = [[1, 2, 7], [1, 3, 7, 4], [1, 2, 3, 4, 5, 6]]
[[1 if j in a[i] else 0 for j in range(1, 8)] for i in range(3)]
[[0, 1, 1, 0, 0, 0, 0], [0, 1, 0, 1, 1, 0, 0], [0, 1, 1, 1, 1, 1, 1]]
</code></pre>

<p>Or use <code>zip</code> again to create the rows:</p>

<pre><code>In [138]: zip(*[[1 if j in a[i] else 0 for j in range(1, 8)] for i in range(3)])
Out[138]: [(1, 1, 1), (1, 0, 1), (0, 1, 1), (0, 1, 1), (0, 0, 1), (0, 0, 1), (1, 1, 0)]
</code></pre>

<p>Note: if you don't know the maximum number of rows you can get that by flattening the columns list</p>

<p>So here is the general code:</p>

<pre><code>from itertools import chain
import csv
with open(csv1_name) as f1, open(csv2_name) as f2,open(csv3_name) as f3:
    column1 = next(zip(*csv.reader(f1))) # in python 2 use zip(*csv.reader(f1))[0]
    column2 = next(zip(*csv.reader(f2)))
    column3 = next(zip(*csv.reader(f3)))

columns = [column1, column2, column3]
m_row = max(chain.from_iterable(a))
f_number = 3
new_columns = [[1 if j in columns[i] else 0 for j in range(1, m_row + 1)] for i in range(f_number)]

# Creating new csv file
with open('new_file.csv', 'w') as f:
    csvwriter = csv.writer(f)
    for id, row in zip(*new_columns):
        csvwriter.writerow((id,)+row)
</code></pre>

python csv statistics