Java - parsing a 2nd csv file to override a value
问题
https://stackoverflow.com/questions/68777361/java-parsing-a-2nd-csv-file-to-override-a-value
I have two csv files. One shows all crime data including City, State, Population etc. The other shows State and Abbreviation. I want to have the state set as the abbreviation, currently I have some very long code and I'm thinking there is definitely a better way at setting it based on the abbreviation csv file.
My main class:
public class StartApp {
public static ArrayList<CityCrime> crimes = new ArrayList<CityCrime>();
public static ArrayList<String> cities = new ArrayList<String>();
/**
* Start point for app. Directs the reads from file and shows the menu
* @param args
*/
public static void main(String[] args) {
try {
readCrimeData("crimeUSA.csv");
System.out.println("Total cities read:" + getTotalCities());
showMenu();
} catch (Exception e) {
e.printStackTrace();
}
}
/**
* Reads the crime data for each city from entered file
* Adds the CityCrime objects to the crimes ArrayList
*/
public static void readCrimeData(String fromFile) {
File file = new File(fromFile);
FileReader fileReader;
BufferedReader bufferedReader;
String crimeInfo;
String[] stats;
try {
fileReader = new FileReader(file);
bufferedReader = new BufferedReader(fileReader);
crimeInfo = bufferedReader.readLine();
crimeInfo = bufferedReader.readLine();
do {
CityCrime crime = new CityCrime(); // Default constructor
stats = crimeInfo.split(",");
{
if (stats[0] != null) {
crime.setCity(stats[0]);
}
if (stats[1] != null) {
crime.setState(stats[1]);
}
if (stats[2] != null) {
if (Integer.parseInt(stats[2]) >= 0) {
crime.setPopulation(Integer.parseInt(stats[2]));
}
}
if (stats[3] != null) {
if (Integer.parseInt(stats[3]) >= 0) {
crime.setMurder(Integer.parseInt(stats[3]));
}
}
if (stats[4] != null) {
if (Integer.parseInt(stats[4]) >= 0) {
crime.setRobbery(Integer.parseInt(stats[4]));
}
}
if (stats[5] != null) {
if (Integer.parseInt(stats[5]) >= 0) {
crime.setAssault(Integer.parseInt(stats[5]));
}
}
if (stats[6] != null) {
if (Integer.parseInt(stats[6]) >= 0) {
crime.setBurglary(Integer.parseInt(stats[6]));
}
}
if (stats[7] != null) {
if (Integer.parseInt(stats[7]) >= 0) {
crime.setLarceny(Integer.parseInt(stats[7]));
}
}
if (stats[8] != null) {
if (Integer.parseInt(stats[8]) >= 0) {
crime.setMotorTheft(Integer.parseInt(stats[8]));
}
}
crime.setTotalCrimes(Integer.parseInt(stats[3]), Integer.parseInt(stats[4]), Integer.parseInt(stats[5]), Integer.parseInt(stats[6]), Integer.parseInt(stats[7]), Integer.parseInt(stats[8]));
}
crimes.add(crime);
System.out.println(crime);
crimeInfo = bufferedReader.readLine();
} while (crimeInfo != null);
fileReader.close();
bufferedReader.close();
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
} catch (NumberFormatException e) {
e.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
}
}
/**
* For each crime, add the city to the cities ArrayList and return the count
*/
public static int getTotalCities() {
for(CityCrime crime : crimes) {
cities.add(crime.getCity());
}
int cityCount = cities.size();
return cityCount;
}
/**
* Displays the menu
* User can select which function they want to run
* @throws IOException
*/
@SuppressWarnings("unlikely-arg-type")
public static void showMenu() throws IOException {
@SuppressWarnings("resource")
Scanner menuSelect = new java.util.Scanner(System.in);
System.out.println("1. Display all crime stats by city");
System.out.println("2. Display all crime stats by selected city");
System.out.println("3. Display the murder stats by selected state");
System.out.println("4. Display highest crime city - all crimes");
System.out.println("5. Display each state (in alphabetical order with the number of car thefts");
System.out.println("6. Write / export all cities in descending order of Robbery rate");
System.out.println("7. Quit");
System.out.println("Enter option 1-7");
@SuppressWarnings("resource")
Scanner scanner = new Scanner(System.in);
int option = Integer.parseInt(menuSelect.next());
if(option<1 || option>7) {
System.out.println("Invalid input.");
return;
}
switch (option) {
case 1:
displayAllCityCrimeStats();
break;
case 2:
System.out.println("Enter city");
String cityOption = menuSelect.next();
displayCrimeStatsByCity(cityOption);
break;
case 3:
System.out.println("Enter state");
String stateOption = menuSelect.next();
displayMurdersByState(stateOption);
break;
case 4:
displayHighestCrimeStats();
break;
case 5:
displayStateCarThefts();
break;
case 6:
writeToFile("Robbery.csv");
break;
case 7:
return;
default:
option = Integer.parseInt(scanner.next());
}
}
My CityCrime file. It is a mess right now as I don't know what the right direction to go in is. I have cut down the setStates, there are 52 in reality so it's pretty long:
public class CityCrime {
//Instance variables
private String city;
private String state;
private int population;
private int murder;
private int robbery;
private int assault;
private int burglary;
private int larceny;
private int motorTheft;
public int totalCrimes;
public static ArrayList<CityState> abbreviations = new ArrayList<CityState>();
public String fromFile = ("C:/Users/ebeck/Downloads/StateAbbreviations.csv");
public static void main(String[] args) {
}
public static void readAbbrevData(String fromFile) {
File file = new File(fromFile);
FileReader fileReader;
BufferedReader bufferedReader;
String abbrevInfo;
String[] stats;
try {
fileReader = new FileReader(file);
bufferedReader = new BufferedReader(fileReader);
abbrevInfo = bufferedReader.readLine();
abbrevInfo = bufferedReader.readLine();
do {
CityState abbrev = new CityState(); // Default constructor
stats = abbrevInfo.split(",");
{
if (stats[0] != null) {
abbrev.setState(stats[0]);
}
if (stats[1] != null) {
abbrev.setAbbreviation(stats[1]);
}
}
abbreviations.add(abbrev);
System.out.println(abbrev);
abbrevInfo = bufferedReader.readLine();
} while (abbrevInfo != null);
fileReader.close();
bufferedReader.close();
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
} catch (NumberFormatException e) {
e.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
}
}
public String getCity() {
return city;
}
public void setCity(String city) {
this.city = city;
}
public String getState() {
return state;
}
public void setState(String state) {
if(state.equalsIgnoreCase("ALABAMA")) {
this.state = "AL";
}
else if(state.equalsIgnoreCase("ALASKA")) {
this.state = "AK";
}
else if(state.equalsIgnoreCase("ARIZONA")) {
this.state = "AR";
}
else if(state.equalsIgnoreCase("ARKANSAS")) {
this.state = "AR";
}
else if(state.equalsIgnoreCase("CALIFORNIA")) {
this.state = "CA";
}
else if(state.equalsIgnoreCase("COLORADO")) {
this.state = "CO";
}
else if(state.equalsIgnoreCase("CONNECTICUT")) {
this.state = "CT";
}
//etc
}
public int getPopulation() {
return population;
}
public void setPopulation(int population) {
this.population = population;
}
public int getMurder() {
return murder;
}
//etc
}
I created a CityState file, however may be unnecessary: public class CityState {
private String state;
private String abbreviation;
public static void main(String[] args) {
}
public String getState() {
return state;
}
public String getAbbreviation() {
return abbreviation;
}
public void setAbbreviation(String abbreviation) {
this.abbreviation = abbreviation;
}
public void setState(String state) {
this.state = state;
}
}
A couple reasons I want to change how I've set up the get state is 1. for my Junit test for state:
@Test
public void testValidState() {
CityCrime crimes = new CityCrime();
crimes.setState(state);
assertEquals(crimes.getState(), state);
}
I was getting the error:
expected: <A[K] but was: <A[LASKA]> if I set the state to "Alaska"
Then if I set it to "AK" I got the error:
expected: but was:
and 2. it doesn't look great either and I want to learn a better way
Thankyou for your help I appreciate it
Edit: For each crime, if the state in CityCrimes csv file is equal to the state in the StatesAbbreviations file, then set the state as the abbreviation in the StatesAbbreviations file and return
解答
这个问题需要把第一个csv 文件的值用第二个 csv 文件的对应值替换。用 JAVA 实现关联，代码很长。
用Java 下的开源包 SPL 很容易写，只要 1 句：
A |
|
1 |
=file("crimeUSA.csv").import@ct().switch(State,file("StateAbbreviations.csv").import@ct():State).new(City,State.Abbreviation: StateAbbreviation,Population,Murder,Robbery,Assault,Burglary,Larceny,MotorTheft,TotalCrimes) |
SPL提供了JDBC 供 JAVA 调用，把上面的脚本存为abbr.splx，在 JAVA 中以存储过程的方式调用脚本文件：
…
Class.forName("com.esproc.jdbc.InternalDriver");
con= DriverManager.getConnection("jdbc:esproc:local://");
st=con.prepareCall("call abbr()");
st.execute();
…
或在JAVA 中以 SQL 方式直接执行 SPL 串：
…
st = con.prepareStatement("==file(\"crimeUSA.csv\").import@ct().switch(State,file(\"StateAbbreviations.csv\").import@ct():State).new(City,State.Abbreviation,Population,Murder,Robbery,Assault,Burglary,Larceny,MotorTheft,TotalCrimes)");
st.execute();
…
English version