0

I am a beginnering in Python and I don't have an idea how I could perform the following transformation and analysis.

I have a file that looks similar to the following example:

RecordID   Comments                                name                      value
0                 Franco Moro is cool                 Franco Moro             100
0                 Franco Moro is cool                 Franco Minione          67
1                 Jane and Jon Mare                  Jane Love                63
1                 Jane and Jon Mare                  Jane Franklin            65
1                 Jane and Jon Mare                  Jon McDonagh             71
1                 Jane and Jon Mare                  Jon Mare                 100

Now I want to apply the logic that if a name has a value of 100 and there is another record in the same group with a lower score and either the first name or the surname is equal than it should be flagged as OUT. However, if there are many options on the same name but none scores 100 then it should be flagged with a "?".

RecordID   Comments                                name                      value      Flag
0                 Franco Moro is cool                 Franco Moro            100          OK
0                 Franco Moro is cool                 Franco Minione         67          OUT
1                 Jane and Jon Mare                  Jane Love               63            ?
1                 Jane and Jon Mare                  Jane Franklin           65            ?
1                 Jane and Jon Mare                  Jon McDonagh            71           OUT
1                 Jane and Jon Mare                  Jon Mare                100          OK

Can anybody help?

Thanks, Michael

Michael Altorfer
  • 21
  • 5
  • Does the data structure you use necessarily need to look like this? Can you use another data structure? If you can, I suggest you try using databases / json files. – SpiderPig1297 Oct 05 '20 at 11:31
  • The input looks like this and the output should also be in table format. However, if you have a suggestion for the in between processing in JSON format I'd be open to it – Michael Altorfer Oct 05 '20 at 12:43
  • What I'd do is to create a class that parses the table and saves it to a dictionary, where key represents the `RecordID` and its value is a `list` with all the matching comments. Then you will be able to perform any logic you want (For example, you will be able to do `if "Jane" in parsed_table[0]`). Once you finish doing your logic, serialize it back to the table format. How does it sounds? – SpiderPig1297 Oct 05 '20 at 13:03
  • I will definitely try it. Thanks! – Michael Altorfer Oct 06 '20 at 11:14

0 Answers0