DataFrame - Insert an additional level_0 index from Series

user_9613338 0
Viewed 526

I have a multi-indexed DataFrame and I need to insert a new column that has the same amount of data as level 0 index.

The details and an example: 1-There is one period element per patient. 2-Each patient has a variable amount of notes, so I can't repeat fixed n_times each period element. 3-I need all the data in the same data frame.

I hope the example adds enough clarity

patient note_number info 
1          1        bla
1          2        bla
1          3        bla
2          4        bla
2          5        bla
3          6        bla
3          7        bla

period (each for one particular patient)
5 days
3 days
11 days

I have this multiindexed dataframe:
patient note_number info 
1          1        bla
           2        bla
           3        bla
2          4        bla
           5        bla
3          6        bla
           7        bla

I need something like:
patient period note_number info 
1        5 days   1        bla
                  2        bla
                  3        bla
2        3 days   4        bla
                  5        bla
3        11 days  6        bla
                  7        bla

How can I accomplish something similar? Correspondance is needed. First period element has to be paired with first patient and so on. Thanks

pandas dataframe indexing python
2 Answers

If number of values in period is same like unique values of patient in df1 then is possible use:

idx = df.index.get_level_values(0)

df2.index = idx.unique()
print (df2)
          period
patient         
1         5 days
2         3 days
3        11 days

df = (df.assign(period = idx.map(df2['period']))
        .set_index('period', append=True)
        .reorder_levels([0,2,1]))
print (df)
                            info
patient period  note_number     
1       5 days  1            bla
                2            bla
                3            bla
2       3 days  4            bla
                5            bla
3       11 days 6            bla
                7            bla
user_29010020 
first need to reset the index, assuming df is your dataframe, below will work
df = df.reset_index(drop=False)

create a mapping for each patient using dictionary

my_dict = {1:'5 days',2:'3 days',3:'11 days'}

df['period'] = df['patient'].map(my_dict)

Reset the index

df = df.set_index(['patient','period','note_number'])
user_33937690
Related