CODEWITHSUNDEEP
pythonpandas
roberto swiss
roberto swiss

Reputation: 155

Split Pandas dataframe index into columns according to specific rule

How do I code with pandas to transform this dataframe called df1

index,client1,client2
name,bob,erika
email,gmail,yahoo
house_A,Paris,London
house_B,London,Milan
house_C,Berlin,Paris
code_name_A,Vaugirard,Windsor
code_name_B,Great,Brera
code_name_C,Mauer,Elysee
visa_id_num_A,FR001B,UK001E
visa_id_num_B,UK001B,IT001E
visa_id_num_C,GE001B,FR001E
food_A,apples,burgers
food_B,bananas,fries
food_C,burgers,pizzas
food_D,fries,oranges
food_E,pizzas,pears

to this dataframe called df2

 index,FR001B,UK001B,GE001B,UK001E,IT001E,FR001E
 client_number,client1,client1,client1,client2,client2,client2
 name,bob,bob,bob,erika,erika,erika
 email,gmail,gmail,gmail,yahoo,yahoo,yahoo
 house,Paris,London,Berlin,London,Milan,Paris
 code_name,Vaugirard,Great,Mauer,Windsor,Brera,Elysee
 visa_id_num,FR001B,UK001B,GE001B,UK001E,IT001E,FR001E
 food_A,apples,apples,apples,burgers,burgers,burgers
 food_B,bananas,bananas,bananas,fries,fries,fries
 food_C,burgers,burgers,burgers,pizzas,pizzas,pizzas
 food_D,fries,fries,fries,oranges,oranges,oranges
 food_E,pizzas,pizzas,pizzas,pears,pears,pears

I would need to split the index values and replace specific ones with new values. I tried with stack, unstack and groupby but it is messy.

Many thanks in advance

Upvotes: 1

Views: 64

Answers (1)

Scott Boston
Scott Boston

Reputation: 153460

Let's try this, using T, pd.wide_to_long, to handle multiple "melts", and set_index:

df1T = df1.T.reset_index().rename(columns={'index':'client_number'})
df1w = pd.wide_to_long(df1T, 
                       ['house','code_name','visa_id_num'],
                       ['client_number','name','email',
                        'food_A','food_B',
                        'food_C','food_D','food_E'],
                       'code', '_', '\w+')

df2 = df1w.reset_index().set_index('visa_id_num').T
print(df2)

Output:

visa_id_num       FR001B   UK001B   GE001B   UK001E   IT001E   FR001E
client_number    client1  client1  client1  client2  client2  client2
name                 bob      bob      bob    erika    erika    erika
email              gmail    gmail    gmail    yahoo    yahoo    yahoo
food_A            apples   apples   apples  burgers  burgers  burgers
food_B           bananas  bananas  bananas    fries    fries    fries
food_C           burgers  burgers  burgers   pizzas   pizzas   pizzas
food_D             fries    fries    fries  oranges  oranges  oranges
food_E            pizzas   pizzas   pizzas    pears    pears    pears
code                   A        B        C        A        B        C
house              Paris   London   Berlin   London    Milan    Paris
code_name      Vaugirard    Great    Mauer  Windsor    Brera   Elysee

Upvotes: 2

Related Questions