4
\$\begingroup\$

At the end of each billing cycle, Amazon generates a raw transaction file for my store's orders that cycle. I am converting that raw transaction file into a .csv file to be imported into my accounting software. My converter program takes two input files.

The first input, the Amazon data file, contains hundreds of lines, and looks like this:

settlement-id   settlement-start-date   settlement-end-date deposit-date    total-amount    currency    transaction-type    order-id    merchant-order-id   adjustment-id   shipment-id marketplace-name    amount-type amount-description  amount  fulfillment-id  posted-date posted-date-time    order-item-code merchant-order-item-id  merchant-adjustment-item-id sku quantity-purchased  promotion-id
11774871501 2019-04-01 13:09:26 UTC 2019-04-07 14:54:57 UTC 2019-04-09 14:54:57 UTC 11591.38    USD                                                                     
11774871501                     Order   111-3282062-5204245 111-3282062-5204245     DW6NY7djJ   Amazon.com  ItemPrice   Principal   29.95   AFN 2019-04-03  2019-04-03 17:13:29 UTC 04346910081818          D0-FMT7-C3G9    1   
11774871501                     Order   111-3282062-5204245 111-3282062-5204245     DW6NY7djJ   Amazon.com  ItemPrice   Shipping    4.42    AFN 2019-04-03  2019-04-03 17:13:29 UTC 04346910081818          D0-FMT7-C3G9    1   
11774871501                     Order   111-3282062-5204245 111-3282062-5204245     DW6NY7djJ   Amazon.com  ItemFees    FBAPerUnitFulfillmentFee    -3.19   AFN 2019-04-03  2019-04-03 17:13:29 UTC 04346910081818          D0-FMT7-C3G9    1   
11774871501                     Order   111-3282062-5204245 111-3282062-5204245     DW6NY7djJ   Amazon.com  ItemFees    Commission  -4.49   AFN 2019-04-03  2019-04-03 17:13:29 UTC 04346910081818          D0-FMT7-C3G9    1   
11774871501                     Order   111-3282062-5204245 111-3282062-5204245     DW6NY7djJ   Amazon.com  ItemFees    ShippingChargeback  -4.42   AFN 2019-04-03  2019-04-03 17:13:29 UTC 04346910081818          D0-FMT7-C3G9    1   
11774871501                     Order   114-8130626-1298654 114-8130626-1298654     D7RCVz0SP   Amazon.com  ItemPrice   Principal   173.08  AFN 2019-04-03  2019-04-03 22:32:57 UTC 50221749590266          E6-0OOH-4ASK    1   
11774871501                     Order   114-8130626-1298654 114-8130626-1298654     D7RCVz0SP   Amazon.com  ItemFees    FBAPerUnitFulfillmentFee    -9.02   AFN 2019-04-03  2019-04-03 22:32:57 UTC 50221749590266          E6-0OOH-4ASK    1   
11774871501                     Order   114-8130626-1298654 114-8130626-1298654     D7RCVz0SP   Amazon.com  ItemFees    Commission  -25.96  AFN 2019-04-03  2019-04-03 22:32:57 UTC 50221749590266          E6-0OOH-4ASK    1   
…

The second input is a CSV file that stores the necessary accounting codes, which contains a few dozen lines, and looks like this:

combined-type,AccountCode,Description
Non-AmazonOrderItemFeesFBAPerUnitFulfillmentFee,501,Non-Amazon - Order - ItemFees - FBAPerUnitFulfillmentFee
Amazon.comOrderItemPricePrincipal,400,Amazon.com - Order - ItemPrice - Principal
Amazon.comOrderItemPriceShipping,403,Amazon.com - Order - ItemPrice - Shipping
Amazon.comOrderItemPriceShippingTax,202,Amazon.com - Order - ItemPrice - ShippingTax
…

I used Python 3 and Pandas. Can you help me make this code bulletproof? My concern is the code does not catch errors and/or does not execute my intent.

Landmark 1: Read in the settlement file (raw transaction file). Read in the account codes file to match transactions to accounting codes.

Landmark 2: Clean up the settlement file by collecting the summary data and converting the four columns into a combined column for comparison and matching to the account codes file.

Landmark 3: Find the matching account code for each transaction and sum the total. So now we should have a summary amount for each account code.

Landmark 4: Clean up the new combined dataframe into the proper template format for our accounting software. Export new file as .csv for uploading into accounting software.

#!/usr/bin/env python3.6
# -*- coding: utf-8 -*-

import pandas as pd
import numpy as np

#Read in Amazon settlement - can be picked up by Python script later
az_data = pd.read_csv('/Users/XXX/Desktop/az_data.txt', sep='\t', header=0, parse_dates=['settlement-start-date', 'settlement-end-date'])
df = pd.DataFrame(az_data)

#Read in Account codes - this can be SQL storage later
acct_codes = pd.read_csv('/Users/XXX/Desktop/acct_codes.csv', sep=',', header=0)
df_accts = pd.DataFrame(acct_codes)

#Take summary data from first row of Amazon settlement and use as check-data
settlement_id = df.iloc[0,0]
settlement_start_date = df.iloc[0,1]
settlement_end_date = df.iloc[0,2]
deposit_date = df.iloc[0,3]
invoice_total = df.iloc[0,4]

#Drop summary row as it is no longer needed and doesn't match
df.drop(df.index[0], inplace=True)

#Replace blank values in 'marketplace-name' column with 'alt-transaction' so groupby doesn't skip those values. And replace all other blank values with 'NA' value
fillvalues = {'marketplace-name': 'alt-transaction'}
df.fillna(value=fillvalues, inplace=True)
df.fillna('NA', inplace=True)

#Create combined column to use as a key
df['combined-type'] = df['marketplace-name'] + df['transaction-type'] + df['amount-type']+ df['amount-description']

#Groupby combined column and take sum of categories
df_mod = df.groupby(['combined-type'])[['amount']].sum()

#Merge dataframes to get account codes and descriptions
df_results = df_mod.merge(df_accts, on='combined-type', how='left')

#Drop row from un-used account
df_results = df_results[df_results['combined-type'] != 'Non-AmazonOrderItemPricePrincipal']

#Rename columns to match Xero template
df_results.rename(columns={'amount':'UnitAmount'}, inplace=True)

#Drop the now un-needed combined-type column
df_results.drop(columns=['combined-type'], inplace=True)

#Add invoice template columns with data
df_results['ContactName'] = 'Amazon.com'
df_results['InvoiceNumber'] = 'INV_' + str(settlement_id)
df_results['Reference'] = 'AZ_Xero_Py_' + str(settlement_id)
df_results['InvoiceDate'] = settlement_start_date
df_results['DueDate'] = settlement_end_date
df_results['Quantity'] = 1
df_results['Currency'] = 'USD'
df_results['TaxType'] = 'Tax on Sales'
df_results['TaxAmount'] = 0
df_results['TrackingName1'] = 'Channel'
df_results['TrackingOption1'] = 'Amazon'

#Re-order columns to match template
all_column_list = ['ContactName','EmailAddress','POAddressLine1','POAddressLine2','POAddressLine3','POAddressLine4','POCity','PORegion','POPostalCode','POCountry','InvoiceNumber','Reference','InvoiceDate','DueDate','InventoryItemCode','Description','Quantity','UnitAmount','Discount','AccountCode','TaxType','TrackingName1','TrackingOption1','TrackingName2','TrackingOption2','Currency','BrandingTheme']
df_final = df_results.reindex(columns=all_column_list)

print (df_final.dtypes)

#Re-format the datetimes to be dates
df_final['InvoiceDate'] = df_final['InvoiceDate'].dt.date
df_final['DueDate'] = df_final['DueDate'].dt.date

#Export final df to csv
df_final.to_csv('/Users/XXX/Desktop/invoice' + str(settlement_id) + '.csv', index=False)

\$\endgroup\$
  • \$\begingroup\$ Welcome to Code Review! I hope you'll get some great feedback. \$\endgroup\$ – AlexV Apr 11 at 20:50
  • \$\begingroup\$ Thanks @Alex , I am looking forward to learning! \$\endgroup\$ – tulanejosh Apr 11 at 20:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.