Creating a gradebook with the pandas module
Question: So I have recently started teaching a course and wanted to handle my grades
using python and the pandas module. For this class the students work in groups
and turn in one assignment per table. I have a file with all of the students
that is formatted like such

    
    
    Name, Email, Table
    "John Doe", jdoe@school.edu, 3
    "Jane Doe", jane@gmail.com, 5
    .
    .
    .
    

and another file with the grades for each table for the assignments done

    
    
    Table, worksheet, another assignment, etc
    1, 8, 15, 4
    2, 9, 23, 5
    3, 3, 20, 7
    .
    .
    .
    

What I want to do is assign the appropriate grade to each student based on
their table number. Here is what I have done

    
    
    import pandas as pd
    
    t_data = pd.read_csv('table_grades.csv')
    roster = pd.read_csv('roster.csv')
    
    for i in range(1, len(t_data.columns)):
        x = []
        for j in range(len(roster)):
            for k in range(len(t_data)): 
                if roster.Table.values[j] == k+1:
                    x.append(t_data[t_data.columns.values[i]][k])
        roster[t_data.columns.values[i]] = x
    

Which does what I want but I feel like there must be a better way to do a task
like this using the pandas. I am new to pandas and appreciate any help.


Answer: IIUC -- unfortunately your code doesn't run for me with your data and you
didn't give example output, so I can't be sure -- you're looking for `merge`.
Adding a new student, Fred Smith, to table 3:

    
    
    In [182]: roster.merge(t_data, on="Table")
    Out[182]: 
             Name               Email  Table  worksheet  another assignment  etc
    0    John Doe     jdoe@school.edu      3          3                  20    7
    1  Fred Smith  fsmith@example.com      3          3                  20    7
    
    [2 rows x 6 columns]
    

or maybe an outer merge, to make it easier to spot missing/misaligned data:

    
    
    In [183]: roster.merge(t_data, on="Table", how="outer")
    Out[183]: 
             Name               Email  Table  worksheet  another assignment  etc
    0    John Doe     jdoe@school.edu      3          3                  20    7
    1  Fred Smith  fsmith@example.com      3          3                  20    7
    2    Jane Doe      jane@gmail.com      5        NaN                 NaN  NaN
    3         NaN                 NaN      1          8                  15    4
    4         NaN                 NaN      2          9                  23    5
    
    [5 rows x 6 columns]
    

