-1

I'm very new to programming- and I have a python question regarding reading and manipulating excel files. I've been trying out the xlrd and openpyxl modules, but still haven't been able to do exactly what I'm looking for.

I have two excel files: one with a list of people, their ID number, and their scores on a survey. In File #1, each person only has ONE line. The second file has a list of people's ID numbers, a date, and some other survey scores. In File #2, each person can have many lines of scores for administrations of the survey that occurred on different days.

My goal is to take each person's ID number from File #2, find any rows in File #1 that have the same ID number, and return the FIRST and LAST row per person from File #1. I then want to ADD these first & last rows onto the end of each person's row in File #2 (so just make each person's row longer).

I've made two test files of fake data and then a file of what I'd like it to look like in the end so that people might understand better what I'm trying to do...

File #1:

ID  first_name  last_name   Score1  Score2  Score3

12-34-56-7  joe schmoe  2   7   3

98-76-54-3  jane    doe 3   5   8

99-11-99-1  sally   jones   5   6   9

File #2:

ID  DATE    Score4  Score5  Score6

12-34-56-7  12/28/2000  8   9   4

12-34-56-7  12/29/2000  5   1   6

12-34-56-7  12/30/2000  2   5   5

12-34-56-7  12/31/2000  1   4   7

12-34-56-7  1/1/2001    4   6   3

12-34-56-7  1/2/2001    7   3   1

98-76-54-3  4/18/1999   9   2   2

98-76-54-3  9/6/2014    6   7   8

98-76-54-3  10/5/2020   3   8   9

99-11-99-1  6/7/2012    2   5   5

99-11-99-1  6/8/2012    5   2   4

Sorry I know that might have been confusing...this is what I want it to look like in the end. Ex. find any rows in File #1 that have the same ID number as the first person in File #2 (12-34-56-7) and grab his first and last rows of data from File #1, then tack them on to the end of his row in File #2. Rinse and repeat for every person in File #2. 

ID  first_name  last_name   Score1  Score2  Score3  ID  DATE Score4 Score5  Score6  ID  DATE    Score4  Score5  Score6

12-34-56-7  joe schmoe  2   7   3   12-34-56-7  12/28/2000  8   9   4   12-34-56-7  1/2/2001    7   3   1

98-76-54-3  jane    doe 3   5   8   98-76-54-3  4/18/1999   9   2   2   98-76-54-3  9/6/2014    6   7   8

99-11-99-1  sally   jones   5   6   9   99-11-99-1  10/5/2020   3   8   9   99-11-99-1  6/8/2012    5   2   4

Any advice for how to write this? Again, I'm really really new to writing scripts so any and all help would be much appreciated :)

shokboi94
  • 1
  • 2

1 Answers1

0
  1. Install Openpyxl
  2. Read Your sheet 2
  3. Store each row for a particular id in a 2D array. // convert all to string
  4. Read your sheet 1
  5. append the name to the array according to the ID
  6. take data from the arrays and write them to the excel sheet. // With converting back to corresponding data type.

If you are looking for the syntax. THis tutorial will help you.! Read this

Also this Stack-overflow question will help you.

Community
  • 1
  • 1
Gayan Kavirathne
  • 2,909
  • 2
  • 18
  • 26