Question

Reading and parsing a CSV data file in java

Note:

I.) the first row contains the field definition

II.) Columns are separated by comma

This is the data.csv file

These are the instructions

This is my code so far

A D F G H J K 1 FirstName LastName DateOfBirth SSN Role Salary Zip Phone 2 Radioactive Man BMockingbird 4Captain Triumph 5 Deathstroke, th Chief garlic 9/29/1912 846330158 Administration 39157 7166875260 69989 persimmon 9/22/1956 835340509 Administration 13884 39157 1421813391 usb 7/19/1940 979204716 Back Office 75710 39157 7752750033 lemon 8/8/1970 704192984 Administration 38259 39157 3917814119 broccoli 4/16/1964 3/27/2011 5/8/1946 221616019 Front Office 79981 39157 8190073149 Clayface B Anole carrot 218594031 Administration 42882 39157 4391168610 cherry 812410312 Administration 89208 39157 9134971042 Metamorpho 0 Aztek cabbage 4/28/1968 758339161 Back Office 32633 39157 3341671152 book 9/25/1987 688616471 Back Office 83848 39157 8586089659 1 Black Spider 2 Captain Canada 3 Solomon Grundy 4 Mr. Miracle 5 Bumblebee 6 Aquaman 7 Geo-Force calculator 8/19/1955 514638108 Back Office 39157 6224072023 60781 4/27/1900 4/20/1997 3/7/2008 pomegranate 348804383 Back Office 67225 39157 6883874410 candle 307431255 Back Office 21080 39157 2158660083 lemon 722299722 Administration 30442 39157 3117283474 kiwi 3/21/2001 275341494 Back Office 63293 39157 6481263373 peach headphones 4/4/1942 262465566 Back Office 96360 39157 9867353847 11/11/1952 335096045 Back Office 39157 8699281194 84953 8 Doctor Druid 8/16/1923 357678855 Back Office 19067 39157 1952511287 carrot 9 Doctor Mist o Crimson Avenger 1 Dove 2 Bulletman 3 Radioactive Man 224076302 Back Office pencil landrover 10/5/1921 17934 39157 1836460008 6/22/1937 761946418 Back Office 91790 39157 9399350958 11/19/1985 286820377 Back Office tv 18212 39157 1864933929 headphones 6/16/1942 11/21/1917 6/6/1952 10/12/1993 408855949 Back Office 44596 39157 4566651901 bucket 788178640 Back Office 19152 39157 1961261322 Air Wave 696411134 Back Office fiat 29882 39157 3059927923 s Captain Marvel 5 Damage 7 Alchemist B Empress Firehawk strawberry 472548670 Back Office 41825 39157 4282939794 345132584 Administration 10/20/1989 2/15/1922 4/19/2015 39157 2769846097 eggplant 27049 onion 333124581 Front Office 37165 39157 3805761300 375174368 Back Office 46453 39157 4756860570 bulb 39157 3929292623 eggplant 10/5/1969 38371 693878414 Back Office data Question 1 Review the attached CSV data file. You are going to need to parse this file into the application you write. Objective: The deliverable for this first question is to write a program that imports the contents of the CSV into memory so that various calculations and operations can be performed on it. You will be taking the following types of actions on this data after it is imported: Return the top X number of records Grouping Sorting Filtering etc. You may use any method you choose to manipulate the data, array list, database, etc. The programming languages used and the way you solve the problem is completely up to you. We would if you prefer if you use a Microsoft .NET language to develop this, but it's totally acceptable if you'd like to use another language. Be prepared to explain your reasoning for your approach and implementation of the solution to this assignment. Important notes relating to this assignment: The first row contains the field definition Columns are separated by comma a. b. Question 2 Using the data imported from the file, write a function or method to calculate and print the average salary for each role. Question 3 Using the data imported from the file, write a function or method to calculate and print the average salary by zip code for all zip codes that start with the characters 35 (example: 35425).

Answer 1

//American_Health_Tech_Assesment.java

import java.io.File;
import java.io.FileNotFoundException;
import java.util.ArrayList;
import java.util.Collections;
import java.util.HashMap;
import java.util.Scanner;

public class American_Health_Tech_Assesment {
    public static void main(String[] args) {
        String fileName = "data.csv";
        File file = new File(fileName);
        // array list for storing individuals records
        ArrayList<String> firstName = new ArrayList<>();
        ArrayList<String> lastName = new ArrayList<>();
        ArrayList<String> dateOfBirth = new ArrayList<>();
        ArrayList<String> ssn = new ArrayList<>();
        ArrayList<String> role = new ArrayList<>();
        ArrayList<Float> salary = new ArrayList<>();
        ArrayList<String> zip = new ArrayList<>();
        ArrayList<String> phone = new ArrayList<>();
        Scanner inputStream = null;
        boolean isException = false;
        try {
            inputStream = new Scanner(file);
            inputStream.nextLine(); // skipping headers
            while (inputStream.hasNextLine()) {
                // splitting line by ,
                String words[] = inputStream.nextLine().trim().split(",");
                if (words.length >= 8) {
                    firstName.add(words[0].trim());
                    lastName.add(words[1].trim());
                    dateOfBirth.add(words[2].trim());
                    ssn.add(words[3].trim());
                    role.add(words[4].trim());
                    salary.add(Float.valueOf(words[5].trim()));
                    zip.add(words[6].trim());
                    phone.add(words[7].trim());
                }
            }

        } catch (
                FileNotFoundException e) {
            isException = true;
            e.printStackTrace();
        } finally {
            if (inputStream != null) {
                inputStream.close();
            }
        }
        if(!isException){
            // calling function
            printAvgSalaryForEachRole(role, salary);
            System.out.println();
            printAvgSalaryByZipCode(zip, salary, "35");
        }

    }

    public static void printAvgSalaryForEachRole(ArrayList<String> role, ArrayList<Float> salary) {
        int count;
        // map for storing role and sum of salary
        HashMap<String, Float> roleSalaryMap = new HashMap<>();
        for (int i = 0; i < role.size(); i++) {
            String curRole = role.get(i);
            float curSal = salary.get(i);
            // if role already added in map 
            // updating sum of salary
            if (roleSalaryMap.containsKey(curRole)) {
                float t = roleSalaryMap.get(curRole);
                roleSalaryMap.put(curRole, t + curSal);
            } else {
                // adding initial entry for role and salary
                roleSalaryMap.put(curRole, curSal);
            }

        }
        System.out.printf("%-20s%10s\n", "Role", "Average Salary");
        System.out.println("-------------------------------------\n");
        for (String name : roleSalaryMap.keySet()) {
            count = Collections.frequency(role, name);
            float curAvgSal = roleSalaryMap.get(name) / count;
            System.out.printf("%-20s%10.2f\n", name, curAvgSal);
        }

    }

    public static void printAvgSalaryByZipCode(ArrayList<String> zip, ArrayList<Float> salary, String zipStartWith){
        int count;
        HashMap<String, Float> zipSalaryMap = new HashMap<>();
        for (int i = 0; i < zip.size(); i++) {
            String curZip = zip.get(i);
            float curSal = salary.get(i);
            // if zip starts with given then only processing entry
            if(curZip.startsWith(zipStartWith)) {
                if (zipSalaryMap.containsKey(curZip)) {
                    float t = zipSalaryMap.get(curZip);
                    zipSalaryMap.put(curZip, t + curSal);
                } else {
                    zipSalaryMap.put(curZip, curSal);
                }
            }

        }

        System.out.printf("%-20s%10s\n", "Zip", "Average Salary");
        System.out.println("-------------------------------------\n");
        for (String name : zipSalaryMap.keySet()) {
            count = Collections.frequency(zip, name);
            float curAvgSal = zipSalaryMap.get(name) / count;
            System.out.printf("%-20s%10.2f\n", name, curAvgSal);
        }
    }
}

// Had tested with records here is the output

Average Salary Role Administration 38936.50 Zip Average Salary 35157 35936.50

Please do let me know if u have any concern..