Ajeet Ajeet - 2 months ago 5
Python Question

Pivot Table with All the Index

I am using python pandas to create a pivot table from df. The df looks like:

enter image description here

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.

What I want to get the Output is This:

enter image description here

I have the following code for the output above:

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


The actual output I am getting is

enter image description here

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

Answer

You can use ffill, what is same as Series.fillna with method='ffill':

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

You can fillna by first non NaN value from column Landing date:

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