How to create a new column with values available in lists based on values in a diff column?-CodePudding

I have a df & lists as follows:

inital_data = {'day': ['Mon', 'Mon', 'Tue', 'Tue', 'Wed', 'Wed', 'Thur', 'Thur', 'Fri', 'Fri', 'Sat', 'Sat', 'Sun', 'Sun']}
inital_df = pd.DataFrame(inital_data)

Mon = [1, 2]
Tue = [3, 4]
Wed = [5, 6]
Thur = [7, 8]
Fri = [9, 10]
Sat = [11, 12]
Sun = [13, 14]

I would like to create a new column and assign it the values in the lists based on the values in the day column in the initial_df

The resulting df should be as follows:

final_data = {
    'day': ['Mon', 'Mon', 'Tue', 'Tue', 'Wed', 'Wed', 'Thur', 'Thur', 'Fri', 'Fri', 'Sat', 'Sat', 'Sun', 'Sun'],
    'values': [1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14]
    }
final_df = pd.DataFrame(final_data)

I tried using np.select but it doesn't seem to work in this case.

How can I achieve something like this?

Thanks in advance

CodePudding user response：

Create dictionary with enumerate for tuples d1 and then map MultiIndex created by GroupBy.cumcount:

d = {'Mon':Mon, 'Tue':Tue, 'Wed':Wed,'Thur':Thur,'Fri':Fri,'Sat':Sat, 'Sun':Sun}

d1 = {(k, i):x for k, v in d.items() for i, x in enumerate(v)}
print (d1)
{('Mon', 0): 1, ('Mon', 1): 2, ('Tue', 0): 3, ('Tue', 1): 4, ('Wed', 0): 5,
 ('Wed', 1): 6, ('Thur', 0): 7, ('Thur', 1): 8, ('Fri', 0): 9, 
 ('Fri', 1): 10, ('Sat', 0): 11, ('Sat', 1): 12, ('Sun', 0): 13, ('Sun', 1): 14}

mux = inital_df.set_index(['day', inital_df.groupby('day').cumcount()]).index

inital_df['values'] = mux.map(d1)
print (inital_df)
     day  values
0    Mon       1
1    Mon       2
2    Tue       3
3    Tue       4
4    Wed       5
5    Wed       6
6   Thur       7
7   Thur       8
8    Fri       9
9    Fri      10
10   Sat      11
11   Sat      12
12   Sun      13
13   Sun      14