Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
import pandas as pd
import glob
csv_files = glob.glob("/*.CSV") # get list of files in directory
for csv_file in csv_files: # loop over all files in directory
data = pd.read_csv(csv_file) # load csv file
data = data[data["Fee"] != 0] # remove all rows where fee=0 (aka non purchases)
data = data[data["Gross"] > 0] # remove all rows where income < 0 (i.e. -ve)
data = data[['Subject', 'Gross']] # choose only subject and gross income columns
data['Subject']= data['Subject'].replace({'Old name': 'New name'}) # Manually rename duplicates with different names
data = data.groupby('Subject')['Gross'].agg(['sum','count']) # group by subject and aggregate the rows
data.index.names = ['Item Name'] # rename index to 'Item Name'
# get month from filename and rename columns
month = csv_file.split(".")[0].split("/")[-1]
month_count = month + ' Quantity Sold'
month_value = month + ' Sales in £'
data.rename(columns={'sum': month_value, 'count': month_count}, inplace=True)
# merge the each months into one dataframe
if csv_file == csv_files[0]:
fulldf = data
else:
fulldf = fulldf.merge(data, on='Item Name', how='outer')
fulldf.fillna(0, inplace=True) # replace all NaN with 0
fulldf.drop(['Personal Item 1','Personal Item 2']) # remove personal sales with a specific name
quantity_cols = [col for col in fulldf.columns if 'Quantity' in col] # Choose quantity col labels
fulldf[quantity_cols] = fulldf[quantity_cols].astype(int) # convert quantity cols to type(int)
quantity_df = fulldf[quantity_cols] # select quantity cols only
quantity_df['Mean Quantity'] = quantity_df.mean(axis=1) # get mean over the time period
quantity_df.loc['Total']= quantity_df.sum() # get total of all rows
quantity_df.to_csv(r'/eBay Report by Quantity.csv') # Export dataframe back to CSV
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.