How to create rows and columns in a .csv file from .log file
<p>I am trying to parse a <code>.log</code>-file from MTurk in to a <code>.csv</code>-file with rows and columns using Python. My data looks like:</p>

<blockquote>
  <p>P:,14142,GREEN,800,9;R:,14597,7,y,NaN,Correct;P:,15605,#E5DC22,800,9;R:,16108,7,f,NaN,Correct;P:,17115,GREEN,100,9;R:,17548,7,y,NaN,Correct;P:,18552,#E5DC22,100,9;R:,18972,7,f,NaN,Correct;P:,19979,GREEN,800,9;R:,20379,7,y,NaN,Correct;P:,21387,#E5DC22,800,9;R:,21733,7,f,NaN,Correct;P:,22740,RED,100,9;R:,23139,7,y,NaN,False;P:,24147,BLUE,100,9;R:,24547,7,f,NaN,False;P:,25555,RED,800,9;R:,26043,7,b,NaN,Correct;P:,27051,BLUE,800,9;</p>
</blockquote>

<p>Currently, I have this, which puts everything in to columns:</p>

<pre><code>import pandas as pd
from pandas import read_table
log_file = '3BF51CHDTWYBE3LE8DZRA0R5AFGH0H.log'
df = read_table(log_file, sep=';|,', header=None, engine='python')
</code></pre>

<p>Like this:</p>

<blockquote>
  <p>P|14142|GREEN|800|9|R|14597|7|y|NaN|Correct|P|15605|#E5DC22|800|9|R|16108</p>
</blockquote>

<p>However, I cannot seem to be able to break this in to multiple rows, so that it would look more like this:<br /></p>

<blockquote>
  <p>P|14142|GREEN|800|9|R|14597|7|y|NaN|Correct|<br />
  |P|15605|#E5DC22|800|9|R|16108</p>
</blockquote>

<p>i.e. where all the "P"s would be in one column, where all the colors would be in another, the "r"s, etc..</p>

<p>Another faster solution:</p>

<pre><code>import pandas as pd
import numpy as np
import io

temp=u"""P:,14142,GREEN,800,9;R:,14597,7,y,NaN,Correct;P:,15605,#E5DC22,800,9;R:,16108,7,f,NaN,Correct;P:,17115,GREEN,100,9;R:,17548,7,y,NaN,Correct;P:,18552,#E5DC22,100,9;R:,18972,7,f,NaN,Correct;P:,19979,GREEN,800,9;R:,20379,7,y,NaN,Correct;P:,21387,#E5DC22,800,9;R:,21733,7,f,NaN,Correct;P:,22740,RED,100,9;R:,23139,7,y,NaN,False;P:,24147,BLUE,100,9;R:,24547,7,f,NaN,False;P:,25555,RED,800,9;R:,26043,7,b,NaN,Correct;P:,27051,BLUE,800,9;"""
#after testing replace io.StringIO(temp) to filename
df = pd.read_csv(io.StringIO(temp), sep=':', header=None, lineterminator=';')

print (df)
    0                       1
0   P      ,14142,GREEN,800,9
1   R  ,14597,7,y,NaN,Correct
2   P    ,15605,#E5DC22,800,9
3   R  ,16108,7,f,NaN,Correct
4   P      ,17115,GREEN,100,9
5   R  ,17548,7,y,NaN,Correct
6   P    ,18552,#E5DC22,100,9
7   R  ,18972,7,f,NaN,Correct
8   P      ,19979,GREEN,800,9
9   R  ,20379,7,y,NaN,Correct
10  P    ,21387,#E5DC22,800,9
11  R  ,21733,7,f,NaN,Correct
12  P        ,22740,RED,100,9
13  R    ,23139,7,y,NaN,False
14  P       ,24147,BLUE,100,9
15  R    ,24547,7,f,NaN,False
16  P        ,25555,RED,800,9
17  R  ,26043,7,b,NaN,Correct
18  P       ,27051,BLUE,800,9
</code></pre>

<p>First <a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.set_index.html" rel="nofollow"><code>set_index</code></a> index from first column, then remove triling <code>,</code> by <a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.Series.str.strip.html" rel="nofollow"><code>strip</code></a> and create <code>DataFrame</code> by <a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.Series.str.split.html" rel="nofollow"><code>str.split</code></a>. Last need add <code>0</code> to column names and <a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.reset_index.html" rel="nofollow"><code>reset_index</code></a>:</p>

<pre><code>df1 = df.set_index(0)[1].str.strip(',').str.split(',', expand=True)
df1.columns = df1.columns + 1
df1.reset_index(inplace=True)
print (df1)
    0      1        2    3    4        5
0   P  14142    GREEN  800    9     None
1   R  14597        7    y  NaN  Correct
2   P  15605  #E5DC22  800    9     None
3   R  16108        7    f  NaN  Correct
4   P  17115    GREEN  100    9     None
5   R  17548        7    y  NaN  Correct
6   P  18552  #E5DC22  100    9     None
7   R  18972        7    f  NaN  Correct
8   P  19979    GREEN  800    9     None
9   R  20379        7    y  NaN  Correct
10  P  21387  #E5DC22  800    9     None
11  R  21733        7    f  NaN  Correct
12  P  22740      RED  100    9     None
13  R  23139        7    y  NaN    False
14  P  24147     BLUE  100    9     None
15  R  24547        7    f  NaN    False
16  P  25555      RED  800    9     None
17  R  26043        7    b  NaN  Correct
18  P  27051     BLUE  800    9     None
</code></pre>

<p><strong>Timings</strong>:</p>

<pre><code>def jez(df):
    df1 = df.set_index(0)[1].str.strip(',').str.split(',', expand=True)
    df1.columns = df1.columns + 1
    df1.reset_index(inplace=True)
    return (df1)

print (jez(df))

In [310]: %timeit (pd.concat([df[[0]], df[1].str.split(',').apply(pd.Series).iloc[:, 1: 6]], axis=1))
100 loops, best of 3: 4.85 ms per loop

In [311]: %timeit (jez(df))
1000 loops, best of 3: 1.61 ms per loop
</code></pre>

python csv pandas numpy