Message Boards Message Boards

Generate multiple lists of an Excel file

Alex Teymouri
Posted 5 years ago

Hi,

I would like to have a separate list for each city. How do I do it?

enter image description here

I appreciate your help. Regards
POSTED BY: Alex Teymouri
5 Replies
Sort By:
Rohit Namjoshi
Rohit Namjoshi
Posted 5 years ago

A shorter form of

GroupBy[data, First, Map[Rest, #] &]

is

GroupBy[data, First -> Rest]
POSTED BY: Rohit Namjoshi

To help you, you need to upload an Excel file with a small example.
POSTED BY: Yehuda Ben-Shimol
Alex Teymouri
Alex Teymouri
Posted 5 years ago

Thank you so much Yahuda. There is the data:

In[2]:= data = 
 Import["C:\\Users\\Administrator\\Desktop\\Book1.xlsx"][[1]]

Out[2]= {{"OHIO", "1963", "2", "69.4"}, {"OHIO", "1963", "3", 
  "112.9"}, {"OHIO", "1963", "4", "53.4"}, {"TEXAS", "1963", "1", 
  "161.0"}, {"TEXAS", "1963", "2", "32.4"}, {"TEXAS", "1963", "3", 
  "166.7"}, {"TEXAS", "1963", "4", "59.7"}, {"TEXAS", "1963", "5", 
  "52.0"}, {"TEXAS", "1963", "6", "57.3"}, {"BOSTON", "1963", "1", 
  "60.6"}, {"BOSTON", "1967", "1", "0.3"}, {"BOSTON", "2001", "8", 
  "0.3"}, {"BOSTON", "2002", "1", "3.6"}, {"AUSTIN", "1959", "2", 
  "72.4"}, {"AUSTIN", "1959", "3", "162.4"}, {"AUSTIN", "1959", "4", 
  "32.5"}, {"AUSTIN", "1959", "5", "46.1"}, {"AUSTIN", "1959", "6", 
  "93.3"}, {"AUSTIN", "1959", "7", "88.6"}, {"AUSTIN", "1959", "8", 
  "58.0"}, {"AUSTIN", "1959", "12", "52.8"}, {"AUSTIN", "1960", "4", 
  "97.5"}}
POSTED BY: Alex Teymouri

Hi You need to be aware of some functions in Mathematica

SplitBy

GatherBy

GroupBy

For you specific case (although your data is already sorted, I cannot count on that), and assuming that a variable named data hold the information in Out[2] of your example

GatherBy[data, First]

or

GroupBy[data, First, Map[Rest, #] &]

yehuda
POSTED BY: Yehuda Ben-Shimol
Alex Teymouri
Alex Teymouri
Posted 5 years ago

A great solution.

Thank you .
POSTED BY: Alex Teymouri
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