# Calculation On Large File Data

I have a task where I need to read a big data file and process the data in it. Every row in the file looks like this:

``````CustomerId   ItemId   Amount   Price
``````

I need to calculate the total cost for the customer, but first I need to determine the most expensive item purchased. "Most expensive" means the item with the highest total price sum (per customer). I then have to subtract the most expensive item from the total cost.

My idea is to first make this table:

``````CustomerId   ItemId   Total_Cost
``````

Next, I sort the table, find the highest cost, and store this in a variable.

Then I make this table:

``````CustomerId   Total_Cost
``````

Then I subtract the highest cost from each row.

But I feel that this is a brute force approach, and I'd like to get advice on a more clever and efficient way to do this.

My Algorithm

``````Make Map with CustomerId key, i.e. Map[CustId, TotalCost] - calling this M1

Make another Map, i.e. Map[(CustId, ItemId), SubTotalCost] - calling this M2

// Open file and process line by line
for each line in file
M1(CustomerId) = running_total_cost
M2((CustomerId, ItemId) = running_subtotal_cost

// When finished processing the file
for each entery in M1
get highest subtotal in M2
substracting highest total in M2 from M1's running_total_cost
``````
• Do you literally want to find the most expensive item, or do you want to find the item where "Total amount of purchaes " x Price is maximized? May 4, 2019 at 12:02
• No sir. I wanting to produce table at end with all customers and their total cost of purchases minus the most expensive item they purchased because promotioning is applied
– Mojo
May 4, 2019 at 12:22
• Say a customer bought 5 apples of price 2\$, and 3 bananas of price 3\$, what counts as the most expensive item: the apples (since they have a total cost of 10\$ vs. 9\$), or the bananas (since they have a single cost of 3\$ vs 2\$)? May 4, 2019 at 12:25
• Apples will be most expensive and so this needing to be substracting from total cost
– Mojo
May 4, 2019 at 12:26

My idea is first I make this table:

``````CustomerId  ItemId Total_Cost
``````

That's ok, there is no way around this, you will need to determine the total price sum per item and per customer first before you can start picking the maximum.

Then I sort the table and find highest cost

For finding the maximum value in a set of numbers, you don't need to sort the numbers first, that is quite inefficient. Instead, implement something along the lines of

`````` for each val in "set of numbers" do:
current_max = Max(val,current_max))
``````
• Yes, this my thinking as well. But this meaning that I writing my own algorithm (which I can doing). My first thinking was to use big data library like Apache Spark, Apache Storm but I not like that because I do not knowing what they are doing inside. Makes me uncomfortable. Also I think with this solution I know exactly memory consumption but I not knowing that accurately with these libraries
– Mojo
May 4, 2019 at 12:25
• @Mojo: so your real problem is, you don't how if those Apache libs provide an aggregate function for finding the maximums? Then I recommend you ask a question about exactly this at stackoverflow, name the lib, post the code you already have and describe where your problem is. Besides this, a "maximum finding algorithm" is not rocket science, that's beginners stuff. May 4, 2019 at 12:30
• I have choice - I not have to use Appache libraries. I can make my own algorithm and I will do this first
– Mojo
May 4, 2019 at 12:31
• Then I will comparing with implementation with those libraries
– Mojo
May 4, 2019 at 12:31
• I will post my algorithm soon sir
– Mojo
May 4, 2019 at 13:11