• Post Reply Bookmark Topic Watch Topic
  • New Topic

Reading in data from excel for manipulations.  RSS feed

 
jihwan kim
Greenhorn
Posts: 14
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hello Ranchers

I have a few questions regarding the practicality of using java for some statistical analysis.
I recently started working on a project for my mathematical modeling class that involves solving a problem from a previous MCM, The Mathematical Contest in Modeling.
The question involves creating a new payment system for a hypothetical college given some constraints, such has compensation dependent on experience, starting wages dependent on Rank, and many assumptions that would help to optimize budgeting and compensation (articulation couldve been better).

I've inputted data on excel which consists of 204 rows and 4 columns, with column headers: Case (just a number), Years (years of experience), Rank (associate/prof/instructor), Salary ($).
This data was given by the way, but was only given in a written format (couldnt find anything online to copypasta).

I decided to post here because I am trying to practice coding, and I think that I can apply some of my skills to help aid in some arithmetic manipulation.

**Do you think that its worth creating a program to help me manipulate data to see how variables change**
Iam probably going to fit a logistic model (has an S shape with an upper bound) because the data seems to represent that type of relationship.
I say this because:
1) salaries start out low for Faculty, whether they're an instructor with no Ph.D or an associate prof with a Ph.D
2) prof with more experience should get more compensation than prof with fewer experience but
3) salary compensation equals out over time where years of experience doesnt really matter.

--So salary compensation starts low, gradually increases as faculty gains experience then levels out at somewhat of a "cap" where experience doesnt matter.


I apologize that this should be directed more towards a statistical crowd, however I am asking here because someone most likely has some experience.
Also, I am thinking about making a little program for this because I dont know excel functions very well.


*Some Ideas:
I had some help with creating parallel arrays here, but I think iam going to create a program where these cells are read into arrays, using a class/main, so that I can see where the differences are depending what I want to do. Probably going to create a menu and such, using While loops so that I can perform calculations.

Any ideas or suggestions? I am not that lazy, but would like to know if I should just work with excel or try to read in the data and use both: 1 for multiple manipulation scenarios and another for creating plots/residuals and such.

Thank You
Jihwan
 
Ashish Dutt
Ranch Hand
Posts: 172
Java MySQL Database Python
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi,
Interesting question.
Could you message me the full question here, I like questions like these so as to sharpen my programming skills as i am a java novice too.
Well, a few suggestions would be,
1. You will need an excel api that integrates with java. A quick google search can help you in finding one as such. Java Excel library or JExcel but its not a free library it gives you a 30 day trial
2. Once you have the API in place then you can start by creating a small program which does the following
a. Establishes connection with excel workbook
b. reads some specific rows or columns of the workbook and writes/stores them in an array. The choice is yours you can choose multidimensional array or single dimensional array. If it would be me, i would have chosen single dimensional array for simplicty.
c. Loop through the array and do some computation and check if they are correct. This checking will enforce that your method is correct
3. Close the connection.

Now if you want to do some statistical stuff then you can do the same on the array that you populated in Step 2b. There are many open source statistical API that work with Java, once again a google search can help you in that.Apache CommonSource API

Hope this helps.
Cheers!
 
jihwan kim
Greenhorn
Posts: 14
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks for the reply Ashish.
I didnt even think about looking up a program to help integrate excel with java because I've always done everything from scratch (fml).

=1995: Aluacha Balaclava College=
Aluacha Balaclava College, an undergraduate facility, has just hired a new provost whose first priority is the institution of a fair and reasonable faculty compensation system. She has hired your consulting team to design a compensation system that reflects the following circumstances and principles.

Faculty are ranked as Instructor, Assistant Professor, Associate Professor, and Professor. Those with Ph.D. degrees are hired at the rank of Assistant Professor. Ph.D. canidates are hired at the rank of Instructor and promoted automatically to Assistant Professor upon completion of their degrees. Faculty may apply for promotion from Associate Professor to Professor after serving at the rank of Associate for 7 or more years. Promotions are determined by the Provost, with recommendations from a faculty committee.

Faculty salaries are for the 10-month period September through June, with raises effective beginning in September. The total amount of money available for raises varies yearly and is generally disclosed in March for the following year.

The starting salary this year for an Instructor with no prior teaching experience was $27,000; $32,000 for an Assistant Professor. Upon hire, faculty can receive credit for up to 7 years of teaching experience at other institutions.

Principles
1. All faculty should get a raise any year that money is available.
2. Promotion should incur a substantial benefit; e.g., promotion in the minimum possible time should result in a benefit roughly equal to 7 years of normal raises.
3. Faculty promoted after 7 or 8 years in rank with careers of at least 25 years should make roughly twice as much at retirement as a starting Ph.D.
4. Experienced faculty should be paid more than less experienced in the same rank. The effect of additional years of experience should diminish over time; this is, if two faculty stay in the same rank, their salaries should equalize over time.

Design a new pay system, first without cost-of-living increases. Incorporate cost-of-living increases, and then design a transition process for current faculty that will move all salaries toward your system without reducing anyone's salary. Existing faculty salaries, ranks, and years of service are shown in Table A.11. Discuss any refinements you think would improve your system.
The Provost requires a detailed compensation system plan for implementation, as well as a brief, clear, executive summary outlining the model, its assumptions, its strengths, its weaknesses, and its expected results, which she can present to the Board and faculty.



**What files can I attach in here so that i can give you the data? I couldnt attach word, pdf, excel, or txt files**
 
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!