Pivot Table with All the Index
<p>I am using python pandas to create a pivot table from df. The df looks like:</p>

<p><a href="http://i.stack.imgur.com/uId4U.png" rel="nofollow"><img src="http://i.stack.imgur.com/uId4U.png" alt="enter image description here"></a></p>

<p>The fields that have missing values are: Origin City, Shipment Date, Volume and Landing Date. Note that Landing Date is the sum of Shipment date and TAT.</p>

<p>What I want to get the Output is This:</p>

<p><a href="http://i.stack.imgur.com/ntdxH.png" rel="nofollow"><img src="http://i.stack.imgur.com/ntdxH.png" alt="enter image description here"></a></p>

<p>I have the following code for the output above:</p>

<pre><code>pd.pivot_table(df, values='Volume', index=['DC'], columns=['Landing date'], aggfunc=np.sum, fill_value = 0)
</code></pre>

<p>The actual output I am getting is</p>

<p><a href="http://i.stack.imgur.com/DcXGM.png" rel="nofollow"><img src="http://i.stack.imgur.com/DcXGM.png" alt="enter image description here"></a></p>

<p>The problem here is my code removes the <code>DC = DLT</code> as it has missing value while pivoting. Any ideas?</p>

<p>You can use <code>ffill</code>, what is same as <a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.Series.fillna.html" rel="nofollow"><code>Series.fillna</code></a> with <code>method='ffill'</code>:</p>

<pre><code>print (df)
   DC Landing date  Volume
0  MAR     02-09-16    50.0
1  MAR     03-09-16    98.0
2  MAR          NaN     NaN
3  BOY     05-09-16    60.0
4  BOY     06-09-16    14.0
5  DLT          NaN     NaN
6  DLT          NaN     NaN

df['Landing date'] = df['Landing date'].ffill()
print (df)
    DC Landing date  Volume
0  MAR     02-09-16    50.0
1  MAR     03-09-16    98.0
2  MAR     03-09-16     NaN
3  BOY     05-09-16    60.0
4  BOY     06-09-16    14.0
5  DLT     06-09-16     NaN
6  DLT     06-09-16     NaN

df1 = pd.pivot_table(df, values='Volume', index=['DC'], columns=['Landing date'], aggfunc=np.sum, fill_value = 0)    
df1.index.name = None
df1.columns.name = None
print (df1)
     02-09-16  03-09-16  05-09-16  06-09-16
BOY         0         0        60        14
DLT         0         0         0         0
MAR        50        98         0         0
</code></pre>

<hr>

<p>You can <a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.Series.fillna.html" rel="nofollow"><code>fillna</code></a> by first non NaN value from column <code>Landing date</code>:</p>

<pre><code>val = df['Landing date'].dropna().iloc[0]
print (val)
02-09-16

df['Landing date'] = df['Landing date'].fillna(val)
print (df)
    DC Landing date  Volume
0  MAR     02-09-16    50.0
1  MAR     03-09-16    98.0
2  MAR     02-09-16     NaN
3  BOY     05-09-16    60.0
4  BOY     06-09-16    14.0
5  DLT     02-09-16     NaN
6  DLT     02-09-16     NaN
</code></pre>

python python-2.7 pandas pivot pivot-table