Coinbase IT Hosting

# Start coding here... 
import pandas as pd
import re
from datetime import datetime 

#import data

filename = 'CoinbaseCommerce-all-2023-05-01-2023-06-05-report.csv'
df = pd.read_csv(filename, parse_dates=[0,1])

df

#Metadata preparation
print(df.METADATA)

pattern = r'"email"=>"[^"]*", '
#data = []

# Remove "email" elements from each string in the array

#for i in range(len(df.METADATA)):
#    df.METADATA[i] = re.sub(pattern, '', df.METADATA[i])
    
    
#df.METADATA1 = [s.replace(pattern, '') for s in df.METADATA]

#print(df.METADATA)
md = df.METADATA.to_frame()

md

#md[0]
#md.describe()
#md.info()

#boats['Country'] = boats['Location'].str.split(' » ').str[0] [16:-1] [18:-1] [19:-1]

md['invoice_id']=md['METADATA'].str.split(',').str[0]
md['customer_name']=md['METADATA'].str.split(',').str[1]
md['customer_email']=md['METADATA'].str.split(',').str[2]

md

md['invoice_id']=md['invoice_id'].str.split('"').str[3]
md['customer_name']=md['customer_name'].str.split('"').str[3]
md['customer_email']=md['customer_email'].str.split('"').str[3]

md

df=pd.concat([df,md], axis=1)
df

df=df.drop(['TRANSACTION INITIATED', 'EXCHANGE RATE AT TIME OF TRANSACTION (USD-CRYPTO ASSET)','NETWORK','ASSET USED FOR PAYMENT','SUBTOTAL IN CRYPTO','ASSET USED FOR FEES','FEE EXCHANGE RATE AT TIME OF TRANSACTION (USD-CRYPTO ASSET)','COINBASE FEES IN CRYPTO','NETWORK FEES IN CRYPTO','CONVERSION STATUS','CONVERSION EXCHANGE RATE (USD-CRYPTO ASSET)','CONVERTED VALUE IN FIAT','ETHEREUM HOMESTEAD ADDRESS','METADATA','SYSTEM ID','RECEIVER ADDRESS'], axis=1)

df['TRANSACTION COMPLETED'] = pd.to_datetime(df['TRANSACTION COMPLETED']).dt.date

df_checkout=df[df['TRANSACTION TYPE']=='Product Checkout']
df_withdrawal=df[df['TRANSACTION TYPE']=='Withdrawal']
df_coinfees=df[~df['COINBASE FEES IN FIAT'].isna()]
df_netwfees=df[~df['NETWORK FEES IN FIAT'].isna()]
df_netwfees=df_netwfees[df_netwfees['NETWORK FEES IN FIAT']!="$0.00"]
print('df_checkout', df_checkout.head())
#print('df_withdrawal', df_withdrawal.head())
#print('df_coinfees', df_coinfees.head())
#print('df_netwfees', df_netwfees.head())

df_checkout.info()

#*Date	*Amount	Payee	Description	Reference	Check Number 11
df_checkout1=pd.DataFrame()
df_checkout1['*Date']=df_checkout['TRANSACTION COMPLETED']
df_checkout1['*Amount']=df_checkout['AMOUNT REQUESTED'].str.replace('$',"")
df_checkout1['*Amount']=df_checkout1['*Amount'].str.replace(',',"")
df_checkout1['Payee']=df_checkout['customer_name']
df_checkout1['Reference']=df_checkout['invoice_id']
df_checkout1['Description']=df_checkout['PRODUCT NAME OR INVOICE ID']
df_checkout1['Analysis code']=df_checkout['TRANSACTION ID CODE']
df_checkout1['Check Number']=df_checkout['TRANSACTION HASH']
df_checkout1

df_withdrawal1=pd.DataFrame()
df_withdrawal1['*Date']=df_withdrawal['TRANSACTION COMPLETED']
df_withdrawal1['*Amount']=df_withdrawal['SUBTOTAL IN FIAT'].str.replace('$',"")
df_withdrawal1['*Amount']=pd.to_numeric(df_withdrawal1['*Amount'].str.replace(',',""), errors='coerce')*(-1)
df_withdrawal1['Reference']='Withdrawal'
df_withdrawal1['Payee']=df_withdrawal['customer_name']
df_withdrawal1['Description']=df_withdrawal['PRODUCT NAME OR INVOICE ID']
df_withdrawal1['Analysis code']=df_withdrawal['TRANSACTION ID CODE']
df_withdrawal1['Check Number']=df_withdrawal['TRANSACTION HASH']
df_withdrawal1

df_coinfees1=pd.DataFrame()
df_coinfees1['*Date']=df_coinfees['TRANSACTION COMPLETED']
df_coinfees1['*Amount']=df_coinfees['COINBASE FEES IN FIAT'].str.replace('$',"")
df_coinfees1['*Amount']=pd.to_numeric(df_coinfees1['*Amount'].str.replace(',',""), errors='coerce')*(-1)
df_coinfees1['Reference']='Coinbase fees'
df_coinfees1['Payee']='Coinbase'
df_coinfees1['Description']=df_coinfees['PRODUCT NAME OR INVOICE ID']
df_coinfees1['Analysis code']=df_coinfees['TRANSACTION ID CODE']
df_coinfees1['Check Number']=df_coinfees['TRANSACTION HASH']
df_coinfees1

df_netwfees1=pd.DataFrame()
df_netwfees1['*Date']=df_netwfees['TRANSACTION COMPLETED']
df_netwfees1['*Amount']=df_netwfees['NETWORK FEES IN FIAT'].str.replace('$',"")
df_netwfees1['*Amount']=pd.to_numeric(df_netwfees1['*Amount'].str.replace(',',""), errors='coerce')*(-1)
df_netwfees1['Reference']='Network fees'
df_netwfees1['Payee']='Coinbase'
df_netwfees1['Description']=df_netwfees['PRODUCT NAME OR INVOICE ID']
df_netwfees1['Analysis code']=df_netwfees['TRANSACTION ID CODE']
df_netwfees1['Check Number']=df_netwfees['TRANSACTION HASH']
df_netwfees1

df_tocsv = pd.concat([df_checkout1,df_withdrawal1,df_coinfees1,df_netwfees1], axis=0)
df_tocsv.describe()

pd.DataFrame.to_csv(df_tocsv, sep=',', path_or_buf='Coinbase.csv', index=False)