Follow

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use
Contact

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

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:

MEDevel.com: Open-source for Healthcare and Education

Collecting and validating open-source software for healthcare, education, enterprise, development, medical imaging, medical records, and digital pathology.

Visit Medevel

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

>Solution :

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
Add a comment

Leave a Reply

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use

Discover more from Dev solutions

Subscribe now to keep reading and get access to the full archive.

Continue reading