How to append a sublist to a dataframe in a specific index and column based on an element in the sub-CodePudding

I have a nested list that is randomly generated shown below

[[[6008, 'BSC3', 'ST1'], ['LB1'], ['MTM2']], [[5227, 'BSC2', 'ST8'], ['LB1'], ['TTM1']]]

I also have a dataframe that looks like this:

My question is how do I fill a specific slot inside the timetable for every of the sublist generated above based on the element in each of those sublist? For example, inside the nested list the first sublist is:

[[6008, 'BSC3', 'ST1'], ['LB1'], ['MTM2']]

The 2 element of that list has 'MTM2' which represents Monday and timeslot #2 (which is 12:00PM - 2:00PM).

Second sublist

[[5227, 'BSC2', 'ST8'], ['LB1'], ['TTM1']]

and the second element is 'TTM1' which represents Tuesday and timeslot #1 (which is 9:30AM - 12:30PM)

My expectation for the example above, is that the dataframe will look something like this:

This is my test attempt (which has no effect):

 def append_to_timetable(self, solution):

        for x in solution:

            if x[2] == 'MTM1':
                self.m.loc['Monday', 0] = x
                print(self.m)

        return self.m

EDIT: Added 13 more sublists (now a total of 15 sublist)

[[[4220, 'BSC2', 'ST5'], ['CR1'], ['THTM2']], [[4201, 'BSC1', 'ST1'], ['LB1'], ['TTM3']], [[4222, 'BSC1', 'ST6'], ['CR1'], ['THTM2']], [[6226, 'BSC3', 'ST6'], ['LB1'], ['THTM3']], [[4220, 'BSC2', 'ST5'], ['LH1'], ['WTM1']], [[4227, 'BSC1', 'ST4'], ['LB1'], ['MTM2']], [[5225, 'BSC2', 'ST6'], ['LR1'], ['TTM3']], [[4227, 'BSC1', 'ST4'], ['LH2'], ['FTM1']], [[6224, 'BSC1', 'ST4'], ['LH1'], ['FTM3']], [[3225, 'BSC1', 'ST6'], ['LR1'], ['WTM2']], [[6224, 'BSC1', 'ST4'], ['LB1'], ['THTM1']], [[4220, 'BSC2', 'ST5'], ['LH2'], ['WTM2']], [[4210, 'BSC1', 'ST1'], ['LH2'], ['THTM2']], [[6224, 'BSC1', 'ST4'], ['LR1'], ['TTM3']], [[5227, 'BSC2', 'ST8'], ['LH1'], ['WTM1']]]

also wondering, is it possible to insert two or more sublists into the same specific slot? for example:

[[5225, 'BSC2', 'ST6'], ['LR1'], ['TTM3']]

and 

[[4201, 'BSC1', 'ST1'], ['LB1'], ['TTM3']]

are at the same time. It would look like this:

CodePudding user response：

Update

After your edit, the situation is a bit more complicated but the idea remains the same:

cols = {'M': 'Monday', 'T': 'Tuesday', 'W': 'Wednesday',
        'TH': 'Thursday', 'F': 'Friday'}
rows = df.index.tolist()

def get_loc(code):
    m, t = code.split('TM')
    col = cols[m]
    row = rows[int(t) - 1]
    return (row, col)

# Decode each element of the list
mi = pd.MultiIndex.from_tuples(tuple(get_loc(x[2][0]) for x in l), 
                               names=['row', 'col'])

out = pd.Series([l]).explode().to_frame('data').set_index(mi).reset_index() \
        .pivot_table('data', 'row', 'col', aggfunc=list, fill_value='') \
        .reindex(index=df.index, columns=df.columns, fill_value='')

Output:

	Monday	Wednesday	Thursday	Friday
9:30AM - 12:30PM		[[[4220, 'BSC2', 'ST5'], ['LH1'], ['WTM1']], [[5227, 'BSC2', 'ST8'], ['LH1'], ['WTM1']]]	[[[6224, 'BSC1', 'ST4'], ['LB1'], ['THTM1']]]	[[[4227, 'BSC1', 'ST4'], ['LH2'], ['FTM1']]]
12:00PM - 2:00PM	[[[4227, 'BSC1', 'ST4'], ['LB1'], ['MTM2']]]	[[[3225, 'BSC1', 'ST6'], ['LR1'], ['WTM2']], [[4220, 'BSC2', 'ST5'], ['LH2'], ['WTM2']]]	[[[4220, 'BSC2', 'ST5'], ['CR1'], ['THTM2']], [[4222, 'BSC1', 'ST6'], ['CR1'], ['THTM2']], [[4210, 'BSC1', 'ST1'], ['LH2'], ['THTM2']]]
2:00PM - 5:00PM			[[[6226, 'BSC3', 'ST6'], ['LB1'], ['THTM3']]]	[[[6224, 'BSC1', 'ST4'], ['LH1'], ['FTM3']]]

Data:

df = pd.DataFrame(columns=['Monday', 'Thuesday', 'Wednesday', 'Thursday', 'Friday'],
                  index=['9:30AM - 12:30PM', '12:00PM - 2:00PM', '2:00PM - 5:00PM'])

l = [[[4220, 'BSC2', 'ST5'], ['CR1'], ['THTM2']],
    [[4201, 'BSC1', 'ST1'], ['LB1'], ['TTM3']],
    [[4222, 'BSC1', 'ST6'], ['CR1'], ['THTM2']],
    [[6226, 'BSC3', 'ST6'], ['LB1'], ['THTM3']],
    [[4220, 'BSC2', 'ST5'], ['LH1'], ['WTM1']],
    [[4227, 'BSC1', 'ST4'], ['LB1'], ['MTM2']],
    [[5225, 'BSC2', 'ST6'], ['LR1'], ['TTM3']],
    [[4227, 'BSC1', 'ST4'], ['LH2'], ['FTM1']],
    [[6224, 'BSC1', 'ST4'], ['LH1'], ['FTM3']],
    [[3225, 'BSC1', 'ST6'], ['LR1'], ['WTM2']],
    [[6224, 'BSC1', 'ST4'], ['LB1'], ['THTM1']],
    [[4220, 'BSC2', 'ST5'], ['LH2'], ['WTM2']],
    [[4210, 'BSC1', 'ST1'], ['LH2'], ['THTM2']],
    [[6224, 'BSC1', 'ST4'], ['LR1'], ['TTM3']],
    [[5227, 'BSC2', 'ST8'], ['LH1'], ['WTM1']]]

Old answer

You can use a function to decode the index and the columns:

df = pd.DataFrame(columns=['Monday', 'Thuesday', 'Wednesday', 'Thursday', 'Friday'],
                  index=['9:30AM - 12:30PM', '12:00PM - 2:00PM', '2:00PM - 5:00PM'])

l = [[[6008, 'BSC3', 'ST1'], ['LB1'], ['MTM2']],
     [[5227, 'BSC2', 'ST8'], ['LB1'], ['TTM1']]]

# Check for Thursday encoding?
day_to_col = {'M': 0, 'T': 1, 'W': 2, 'TH': 3, 'F': 4}

def get_loc(code):
    m, t = code.split('TM')
    col = day_to_col[m]
    row = int(t) - 1
    return (row, col)

df.to_numpy()[tuple(get_loc(x[2][0]) for x in l)] = l

Output:

>>> df
                                              Monday                            Thuesday Wednesday Thursday Friday
9:30AM - 12:30PM                                 NaN  [[5227, BSC2, ST8], [LB1], [TTM1]]       NaN      NaN    NaN
12:00PM - 2:00PM  [[6008, BSC3, ST1], [LB1], [MTM2]]                                 NaN       NaN      NaN    NaN
2:00PM - 5:00PM                                  NaN                                 NaN       NaN      NaN    NaN