How can I read and sort a csv file in python?

Question

I'm new to python and have a csv file with names and scores in like this:

Andrew,10
Steve,14
Bob,17
Andrew,11

I need to know how to read this file, and the file must display the two entries with the same name, for instance Andrew,10 and Andrew,11 as Andrew,10,11. I also need to be able to sort by either name, highest score, or average score. If possible, I'd also like it to use the last 3 entries for each name only. This is the code i've tried to use to read and sort by name:

with open("Class1.csv", "r") as f:
        Reader = csv.reader(f)
        Data = list(Reader)
        Data.sort()
        print(Data)

I've added the code I tried to use, like I said, Im very new to this and searched the internet and couldn't find a solution. — E.Barnes
– E.Barnes, Commented Oct 7, 2015 at 17:18

Javier GB · Accepted Answer · 2015-10-07 17:21:03Z

0

Pandas is very nice for it

import pandas as pd

df = pd.read_csv("<pathToFileIN>",index_col=None,header=None)
df.columns = ["name","x"]
n = df.groupby("name").apply(lambda x: ",".join([str(_) for _ in x["x"].values[-3:]])).values
df.drop_duplicates(subset="name",inplace=True)
df["x"] = n
df.sort("name",inplace=True)

df.to_csv("<pathToFileOUT>",index=None,sep=";")

answered Oct 7, 2015 at 17:21

Javier GB

12 bronze badges

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

1 Comment

Javier GB Over a year ago

Sorry I didn't answer faster @E.Barnes. It groups by name and for each name it appends all the scores together (lambda x: ",".join([str(_) for _ in x["x"].values[-3:]). Then we drop the duplicates so we have each name only once, and we add a new column (x) with the scores appended together.

Steven Rumbalski · Accepted Answer · 2015-10-07 17:27:14Z

0

To combine scores use collections.defaultdict:

scores_by_name = collections.defaultdict(list)
for row in Reader:
    name = row[0]
    score = int(row[1])
    scores_by_name[name].append(score)

To keep the last three scores take a 3 item slice:

scores_by_name = {name: scores[-3:] for name, score in scores_by_name.items()}

To iterate alphabetically:

for name, scores in sorted(scores_by_name.items()):
    ... # whatever

To iterate by highest score:

for name, scores in sorted(scores_by_name.items(), key=(lambda item: max(item[1]))):
    ...

edited Oct 7, 2015 at 17:27

answered Oct 7, 2015 at 17:22

Steven Rumbalski

45.7k10 gold badges96 silver badges125 bronze badges

Collectives™ on Stack Overflow

How can I read and sort a csv file in python?

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related