Load CSV Strings With Different Types into Pandas Dataframe, Split Columns, Parse Date
<p>I have two questions concerning a large csv file which contains data in the following way formatted as strings:</p>

<pre><code> "XAU=,XAU=,XAG=,XAG="  
 "25/08/2014 6:00:05,1200.343,25/08/2014 6:00:03,19.44,"            
 "25/08/2014 6:00:05,1200,,,"
</code></pre>

<p>Is there a way to efficiently load this into a pandas dataframe object? Alternatively, also multiple pandas Series objects would do the job. So far I tried:                                                                           </p>

<pre><code>df = read_csv(path, header=None)
df[0].str[0:-1].str.split(',', return_type='frame')
</code></pre>

<p>The second line is an answer from this thread <a href="http://stackoverflow.com/questions/29370211/pandas-split-string-into-columns">pandas split string into columns</a>. However, I wonder if there is an even better way, especially since I have different data types? Secondly, how can I correctly parse the dates with <code>to_datetime()</code>. I tried to reindex <code>df</code> and used <code>df.index = df.index.to_datetime()</code>. This worked only half way because it did not strictly keep the <code>dd/mm/yyyy ...</code> format. Some dates were incorrectly parsed as <code>mm/dd/yyyy ...</code>. I'm looking for fast ways because eventually I will loop over many such csv's. Thx for any help!</p>

<p>EDIT: Ideally data in this form should be handled as well:   </p>

<pre><code>     "XAU=,XAU=,XAG=,XAG="  
     "25/08/2014 6:00:05,1200.343,25/08/2014 6:00:03,19.44,"            
     ",,25/08/2014 6:00:05,19.50,"
</code></pre>

<p>So with the answer provided below,</p>

<pre><code>    data = StringIO(
    '''
    "XAU=,XAU=,XAG=,XAG="  
    "25/08/2014 6:00:05,1200.343,25/08/2014 6:00:03,19.44,"            
    ",,25/08/2014 6:00:05,19.5,"
    ''')
</code></pre>

<p>df would become:</p>

<pre><code>                  XAU                    XAU                       XAG      XAG
0  25/08/2014 6:00:05               1200.343        25/08/2014 6:00:03    19.44
1                   ,     25/08/2014 6:00:05                      19.5       \n
</code></pre>

<p>You can preprocess everything inside the <a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.read_csv.html" rel="nofollow"><code>read_csv</code></a> as shown:</p>

<pre><code>import csv

data = StringIO(
'''
"XAU=,XAU=,XAG=,XAG="  
"25/08/2014 6:00:05,1200.343,25/08/2014 6:00:03,19.44,"            
"25/08/2014 6:00:05,1200,,,"
''')

df = pd.read_csv(data, quoting=csv.QUOTE_NONE, index_col=False, escapechar='"',   \
                 parse_dates=[0, 2]).rename(columns=lambda x: x.split("=")[0])
df
</code></pre>

<p><a href="http://i.stack.imgur.com/x80Br.png" rel="nofollow"><img src="http://i.stack.imgur.com/x80Br.png" alt="Image"></a></p>

<pre><code>df.dtypes

XAU    datetime64[ns]
XAU           float64
XAG    datetime64[ns]
XAG           float64
dtype: object
</code></pre>

<p><strong>Break-up:</strong></p>

<p><em>quoting=csv.QUOTE_NONE</em> : Instructs writer objects to never quote fields <br></p>

<p><em>index_col=False</em>: Do not use the first column as the index   <br></p>

<p><em>escapechar=</em>": string used to escape delimiter   <br></p>

<p><em>parse_dates=[0, 2]</em>: convert columns(0,2 â order) to <code>datetime</code> objects</p>

<hr>

<p>To read a subset of the columns, you can do so with the help of <code>usecols</code> by supplying appropriate integer indices as shown:</p>

<pre><code>df = pd.read_csv(data, quoting=csv.QUOTE_NONE, index_col=False, escapechar='"',   \
                 parse_dates=[0], usecols=[0,1]).rename(columns=lambda x: x.split("=")[0])

df
</code></pre>

<p><a href="http://i.stack.imgur.com/qdVFP.png" rel="nofollow"><img src="http://i.stack.imgur.com/qdVFP.png" alt="Image"></a></p>

<p>If you want to convert the two columns of <code>XAU</code> into a series object:</p>

<pre><code>df.columns = df.columns + [str('_%d'%(i)) for i in list(range(len(df.columns)))]

ser = pd.Series(data=df['XAU_1'].values, index=df['XAU_0'].values, name='XAU')
ser

2014-08-25 06:00:05    1200.343
2014-08-25 06:00:05    1200.000
Name: XAU, dtype: float64

type(ser)
pandas.core.series.Series
</code></pre>

<hr>

<p>The reason that fails for a newer case is because <code>escapechar</code> skips the first delimiter, as a result the empty strings aren't captured properly.</p>

<p>If that's the case, you are better off ignoring <code>escapechar</code> altogether and continuing as shown:</p>

<p>For the combination of old and new data:</p>

<pre><code>data = StringIO(
'''
"XAU=,XAU=,XAG=,XAG="   
"25/08/2014 6:00:05,1200.343,25/08/2014 6:00:03,19.44," 
"25/08/2014 6:00:05,1200,,,"
",,25/08/2014 6:00:05,19.50,"       
''')


df = pd.read_csv(data, quoting=csv.QUOTE_NONE, index_col=False, na_values=[""], 
                 parse_dates=[2]).rename(columns=lambda x: x.strip('"').split("=")[0])

old_cols = df.columns
# Index(['XAU', 'XAU', 'XAG', 'XAG'], dtype='object')

new_cols = [col[0] for col in list(enumerate(df.columns))]
# [0, 1, 2, 3]
df.columns = new_cols

# Converting first column to datetime dtype
df[0] = pd.to_datetime(df[0].str.replace('"', ''))   
df.columns = old_cols

df
</code></pre>

<p><a href="http://i.stack.imgur.com/e6BxF.png" rel="nofollow"><img src="http://i.stack.imgur.com/e6BxF.png" alt="Image"></a></p>

<pre><code>df.dtypes

XAU    datetime64[ns]
XAU           float64
XAG    datetime64[ns]
XAG           float64
dtype: object
</code></pre>

python csv datetime pandas time-series