Pandas dataframe output formatting
<p>I'm importing a trade list and trying to consolidate it into a position file with summed quantities and average prices.  I'm grouping based on (ticker, type, expiration and strike).  Two questions:</p>

<ol>
<li>Output has the index group (ticker, type, expiration and strike) in the first column.  How can I change this so that each index column outputs to its own column so the output csv is formatted the same way as the input data?</li>
<li>I currently force the stock trades to have values ("1") because leaving the cells blank will cause an error, but this adds bad data since "1" is not meaningful.  Is there a way to preserve "" without causing a problem?</li>
</ol>

<p>Dataframe:</p>

<pre><code>    GM      stock   1           1       32      100
    AAPL    call    201612      120     3.5     1000
    AAPL    call    201612      120     3.25    1000
    AAPL    call    201611      120     2.5     2000
    AAPL    put     201612      115     2.5     500
    AAPL    stock   1            1      117     100
</code></pre>

<p>Code:</p>

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

    df = pd.read_csv(input_file, index_col=['ticker', 'type', 'expiration', 'strike'], names=['ticker', 'type', 'expiration', 'strike', 'price', 'quantity'])
    df_output = df.groupy(df.index).agg({'price':np.mean, 'quantity':np.sum})
    df_output.to_csv(output_file, sep=',')
</code></pre>

<p>csv output comes out in this format:</p>

<pre><code>(ticker, type, expiration, strike), price, quantity
</code></pre>

<p>desired format:</p>

<pre><code>ticker, type, expiration, strike, price, quantity
</code></pre>

<p>For the first question, you should use groupby(df.index_col) instead of groupby(df.index)</p>

<p>For the second, I am not sure why you couldn't preserve "", is that numeric?</p>

<p>I mock some data like below:</p>

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

d = [                                                                                                               
    {'ticker':'A', 'type':'M', 'strike':'','price':32},                                                             
    {'ticker':'B', 'type':'F', 'strike':100,'price':3.5},                                                           
    {'ticker':'C', 'type':'F', 'strike':'', 'price':2.5}                                                            

]                                                                                                                   
df = pd.DataFrame(d)                                                                                                
print df                                                                                                            

#dgroup = df.groupby(['ticker', 'type']).agg({'price':np.mean})                                                     
df.index_col = ['ticker', 'type', 'strike']                                                                         
dgroup = df.groupby(df.index_col).agg({'price':np.mean})   
#dgroup = df.groupby(df.index).agg({'price':np.mean})                                                 
print dgroup                                                                                                        
print type(dgroup)                                                                                                  
dgroup.to_csv('check.csv') 
</code></pre>

<p>output in check.csv:</p>

<pre><code>ticker,type,strike,price                                                                                            
A,M,,32.0                                                                                                           
B,F,100,3.5                                                                                                         
C,F,,2.5  
</code></pre>

python pandas format export-to-csv