Message Boards Message Boards

0
|
17662 Views
|
7 Replies
|
1 Total Likes
View groups...
Share
Share this post:

I need assistance graphing data imported from Excel.

Good Day,

I'm new to Mathematica and need assistance graphing data that I imported from Excel. I'm hoping to eventually create a reporting portal for users. Baby steps first. The import yields a list that resembles the following:

{"Ticket Id", "Minutes Worked", "Agent"}, {2039., 60., "Joey Drews"}, {2366., 15., "Frank Davis"}, {2379., 15., "Tuyen Collins"}, {2403., 45., "Joey Drews"}, {2404., 15., "Tuyen Collins"}

I'm hoping to create a bar chart that graphs the total minutes worked per agent. Any help will be appreciated.

Thanks,
Joey Drews
POSTED BY: Joey Drews
7 Replies
Sort By:
Paul Cleary
Paul Cleary
Posted 11 years ago
My only resourse is the virtual book found under the Help heading, as to why I used Flatten and Partition, well, experience has tought me that when I use AppendTo, Sow, or create any lists or lists of lists imported or not that I invariably need to Flatten out the data http://reference.wolfram.com/mathematica/ref/Flatten.html.  Experience has also tought me that it's easier to just Flatten the entire data, so you don't have to worry about how deep you need to Flatten things and then Partition that data back into the original size, original being in the example 3.  I have to point out here that I am a novice Mathematica user and the code I write will probably be far from efficient or concise and the reason I have to do certain things is as a result of it not being concise enough.
POSTED BY: Paul Cleary
Depending on how elaborated your charts would be you may consider taking a look at BEST Viewpoints which imports data from spreadsheets and databases and simplifies the process of creating charts and analytics using Mathematica.  In summary, you can use this application to define all the options you need to create your chart and then save all definitions as  a script which you can later use in your report generation app.  BEST Viewpoints 9 is now available at the Wolfram Store.  
POSTED BY: Ariel Sepulveda
Thanks so much for the information. Can you recommend good resources for learning everything there is to know about reporting in Mathematica? I read over the lessons on how to import, but nothing on flattening the data and why that is necessary. I'm going to be doing these types of tasks a lot and would like some reference material.
POSTED BY: Joey Drews
Paul Cleary
Paul Cleary
Posted 11 years ago
The following is a simplistic way of doing it.  If you leave off the headings in the excel file we dont have to worry too much about them and it makes life a little easier.  The path to the Excel files is assumed to be as shown in the code, ammend it to suit where it is located on your pc. Likewise remove any of the headings you don't need.  The list[[All,2]] and list[[All,3]] refer to the second and 3rd columns in the sheet.  The 3 in this line list=Partition[Flatten
    ,3];  partitions the list back into 3 columns after flattening it out, so if you wished to add further columns to 4 or 5 etc, ammend that number accordingly and adjust the number to point to the data you need in the chart.

    SetDirectory["C:\\Documents\\Employee"];
    list = Import["Employee.xlsx", {"Data"}];
    list = Partition[Flatten[list], 3];
    BarChart[list[[All, 2]],
    PlotLabel -> "Minutes Worked", ChartLabels -> list[[All, 3]],
    ChartLegends -> list[[All, 3]], ChartStyle -> "Pastel"]

    the following is a printout and clip of the excel file as illustration






    Paul.
POSTED BY: Paul Cleary
Also, the example data I provided was just a sample of a much bigger data set. The actual data set is a dump out of our ticketing system and will have multiple entries for each person. The above example has multiple bars for each person. I'm looking for something that will add the minutes worked on each individual ticket and display the result in a bar graph.
POSTED BY: Joey Drews
Based on the feedback received for my contribution to this thread I'm posting the following information:
- Follow this link to get a trial version of BEST Viewpoints.
- The default trial version is a perpetual copy of the application that only manages 100 rows of data.  
- After you have made the download I can send you the Key to convert the default trial version into a Full Trial Version that will last a copule of weeks.
POSTED BY: Ariel Sepulveda
Pat McCarthy
Pat McCarthy
Posted 10 years ago

If you don't leave off the headings, what command(s) is necessary. I have been trying to import an excel file with headings and can't figure out how to get excel interpret the first line as a header so that, for example, I can issue a command to find the maximum value or some other statistical value. Is this possible?

Thanks. Pat
POSTED BY: Pat McCarthy
Reply to this discussion
Community posts can be styled and formatted using the Markdown syntax.
Tag limit exceeded
Note: Only the first five people you tag will receive an email notification; the other tagged names will appear as links to their profiles.
Reply Preview
Attachments
Remove
or Discard

Group Abstract Group Abstract