Sorting array data by common date

Question

I have a .csv file with many rows and 3 columns: Date, Rep, and Sales. I would like to use Python to generate a new array that groups the data by Date and, for the given date, sorts the Reps by Sales. As an example, my input data looks like this:

salesData = [[201703,'Bob',3000], [201703,'Sarah',6000], [201703,'Jim',9000], 
    [201704,'Bob',8000], [201704,'Sarah',7000], [201704,'Jim',12000], 
    [201705,'Bob',15000], [201705,'Sarah',14000], [201705,'Jim',8000],
    [201706,'Bob',10000], [201706,'Sarah',18000]]

My desired output would look like this:

sortedData = [[201703,'Jim', 'Sarah', 'Bob'], [201704,'Jim', 'Bob', 
    'Sarah'], [201705,'Bob', 'Sarah', 'Jim'], [201706, 'Sarah', 'Bob']]

I am new to Python, but I have searched quite a bit for a solution with no success. Most of my search results lead me to believe there may be an easy way to do this using pandas (which I have not used) or numpy (which I have used).

Any suggestions would be greatly appreciated. I am using Python 3.6.

biniow · Accepted Answer · 2017-06-12 17:37:03Z

2

Use Pandas!

import pandas as pd

salesData = [[201703, 'Bob', 3000], [201703, 'Sarah', 6000], [201703, 'Jim', 9000],
             [201704, 'Bob', 8000], [201704, 'Sarah', 7000], [201704, 'Jim', 12000],
             [201705, 'Bob', 15000], [201705, 'Sarah', 14000], [201705, 'Jim', 8000],
             [201706, 'Bob', 10000], [201706, 'Sarah', 18000]]

sales_df = pd.DataFrame(salesData)
result = []
for name, group in sales_df.groupby(0):
    sorted_df = group.sort_values(2, ascending=False)
    result.append([name] + list(sorted_df[1]))
print(result)

answered Jun 12, 2017 at 17:37

biniow

4012 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

CodingNewb Over a year ago

Wow - that's awesome! I am definitely going to need to read up on pandas - so easy and powerful. Thank you very much for the solution.

nikpod · Accepted Answer · 2017-06-13 02:48:52Z

0

Without pandas, you can try this one line answer:

sortedData = [[i]+[item[1] for item in salesData if item[0]==i] for i in sorted(set([item[0] for item in salesData]))]

EDIT:
You can do this to order each inner list by sales:

sortedData = [[i]+[item[1] for item in sorted(salesData, key=lambda x: -x[2]) if item[0]==i] for i in sorted(set([item[0] for item in salesData]))]

Note that sorted(salesData, key=lambda x: -x[2]) part performs the ordering

edited Jun 13, 2017 at 2:48

answered Jun 12, 2017 at 17:52

nikpod

1,27814 silver badges22 bronze badges

1 Comment

CodingNewb Over a year ago

Thank you for the reply. I am going to go with the pandas solution, but I'd still like to understand your code better as I'm new to learning Python. One problem I had running this line of code was that the output is in the correct format (i.e. unique dates followed by a list of names), but the names are not sorted by sales. Is there a way to add this functionality to your code? Thanks!

Collectives™ on Stack Overflow

Sorting array data by common date

2 Answers 2

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related