I've a dataframe which looks like this:
Customer ID Invoice ID Invoice Total Customer Total
8063863 110100456 41,47 248,82
8063863 110100677 41,47 248,82
8063863 110100838 41,47 248,82
8063863 110101106 41,47 248,82
8063863 110101259 41,47 248,82
8063863 110101401 41,47 248,82
What I'd like to have is something like this:
Customer ID Invoice_1 Invoice_Total_1 Invoice_2 Invoice_Total_2 ... Customer_Total
8063863 110100456 41,47 110100677 41,47 248,82
I'd like to then export the Dataframe to csv and use it in Word to Mail Merge the different clients their summary.
I added the Customer Total in Pandas using pivot_table but I'm stuck on the flattening of the Dataframe.
Let's try this:
def f(x):
n,i = pd.factorize(x['Invoice ID'])
df1 = pd.DataFrame([x.loc[(x['Invoice ID']==i.values),'Invoice Total'].values], columns=(n+1).astype(str)).add_prefix('Invoice_Total_')
df2 = pd.DataFrame([i.values],columns=(n+1).astype(str)).add_prefix('Invoice_')
return pd.concat([df1,df2],axis=1).assign(Customer_Total=x['Customer Total'].max()),drop=True)
df_out = df.groupby('Customer ID').apply(f).reset_index(-1,drop=True)
Output:
Invoice_Total_1 Invoice_Total_2 Invoice_Total_3 Invoice_Total_4 \
Customer ID
8063863 41,47 41,47 41,47 41,47
Invoice_Total_5 Invoice_Total_6 Invoice_1 Invoice_2 Invoice_3 \
Customer ID
8063863 41,47 41,47 110100456 110100677 110100838
Invoice_4 Invoice_5 Invoice_6 Customer_Total
Customer ID
8063863 110101106 110101259 110101401 248,82