I have a table look like this in DataFrame:

I would like to rearrange the table like below:

How can I use Pandas to solve this? I can't figure it out!

Data:

{'couriers': ['Alfred Locker',
  'Hongkong Post',
  'ZTO Express (To-Point)',
  'Zeek2Door (To Home)',
  'Kerry Express'],
 'delivery': ['2 day(s)', '2 day(s)', '2 day(s)', '2 day(s)', '2 day(s)'],
 'price_list': ['HKD $20', 'HKD $50', 'HKD $20', 'HKD $40', 'HKD $30']}

CodePudding user response：

I think you could unstack convert MultiIndex to a plain index transpose:

s = df.unstack().sort_index(level=1)
s.index = [f'{x}_{y 1}' for x,y in s.index]
out = s.to_frame().T

Output:

      couriers_1 delivery_1 price_list_1     couriers_2 delivery_2    price_list_2              couriers_3 delivery_3 price_list_3              couriers_4 delivery_4 price_list_4     couriers_5 delivery_5    price_list_5  
0  Alfred Locker   2 day(s)      HKD $20  Hongkong Post   2 day(s)         HKD $50  ZTO Express (To-Point)   2 day(s)      HKD $20     Zeek2Door (To Home)   2 day(s)      HKD $40  Kerry Express   2 day(s)         HKD $30  

CodePudding user response：

So if you have a loop iterate over every element then use concat of pandas to attach each of them in order to form the data frame .

import pandas as pd

df = pd.DataFrame(zip(
["couriers", "Alfred Locker","Hongkong Post","ZTO Express(To-Point)","Zeek2Door(To Home)","Kerry Express"],
["delivery","2 day(s)","2 day(s)","2 day(s)","2 day(s)","2 day(s)"],
["price_list","HKD $20","HKD $50","HKD $20","HKD $40","HKD $30"],
))

counter_str = 1
#initialzing the df_result
df_result = ""
#iterating over the length so that we can add it to the string


for x in range(1,len(df)):
    for i in df:
        df_local = pd.DataFrame({df[i][0] "_"  str(x) : [df[i][counter_str]]})
        if len(df_result)>0:
            df_result = pd.concat([df_result,df_local], axis = 1, join='inner')
        else:
            df_result = df_local
    counter_str =1

print(df_result)
             

Output:

    couriers_1 delivery_1 price_list_1  ...     couriers_5 delivery_5 price_list_5
0  Alfred Locker   2 day(s)      HKD $20  ...  Kerry Express   2 day(s)      HKD $30