Pandas -need to change shape of dataframe to different one

Question

enter code herecould you help to solve one task. I have data frame

data_dict = {'Number': {0: 1, 1: 2, 2: 3}, 'mw link': {0: 'SAM3703_2SAM3944 2', 1: 'SAM3720_2SAM4115 2', 2: 'SAM3729_2SAM4121_ 2'}, 'site_a': {0: 'SAM3703', 1: 'SAM3720', 2: 'SAM3729'}, 'name_a': {0: 'Chelak', 1: 'KattakurganATC', 2: 'Payariq'}, 'site_b': {0: 'SAM3944', 1: 'SAM4115', 2: 'SAM4121'}, 'name_b': {0: 'Turkibolo', 1: 'Kattagurgon', 2: 'Payariq2'}, 'distance km': {0: 3.618, 1: 7.507, 2: 9.478}, 'manufacture': {0: 'ZTE NR 8150/8250', 1: 'ZTE NR 8150/8250', 2: 'ZTE NR 8150/8250'}}



Number    mw link               site_a     name_a         site_b    name_b                      distance km    manufacture
1         SAM3703_2SAM3944_2    SAM3703    Chelak         SAM3944  Turkibolo                    3.618          ZTE NR 8150/8250
2         SAM3720_2SAM4115_2    SAM3720    KattakurganATC SAM4115  Kattaqurgon                  7.507          ZTE NR 8150/8250
3        SAM3729_2SAM4121_2     SAM3729    Payariq        SAM4121   Payariq2                     9.478          ZTE NR 8150/8250

1st dataframe

https://i.stack.imgur.com/tPugN.png

need to change this like this needed output

Number     mw link                                      name     distance km     manufacture
1         SAM3703_2SAM3944_2     site_a     SAM3703     Chelak      3.618     ZTE NR 8150/8250
                                 site_b     SAM3944     Turkibolo
2     SAM3720_2SAM4115_2         site_a     SAM3720     KattakurganATC 7.507  ZTE NR 8150/8250
                                 site_b     SAM4115     Kattaqurgon 
3     SAM3729_2SAM4121_2         site_a     SAM3729     Payariq     9.478     ZTE NR 8150/8250
                                 site_b     SAM4121     Payariq2

Why are you vandalizing the question, I added data in the form of dictionary so that it becomes easier for people to read the data directly using the `pd.DataFrame` method. The main reason you are not getting answer for this question is because you are adding data as image — Himanshu Poddar, Jul 21 '22 at 06:48
I have added the dictionary back now, its your choice if you want to prettify it or not! For now I left it as you edited! — Himanshu Poddar, Jul 21 '22 at 06:49

jezrael · Answer 1 · 2022-07-21T07:25:52.157

Use wide_to_long with DataFrame.reset_index and rename:

df = (pd.wide_to_long(df, 
                      stubnames=['name','site'], 
                      i=['Number','mw link','manufacture','distance km'], 
                      j='i', 
                      sep='_',
                      suffix=r'\w+')
      .reset_index(['manufacture','distance km'])
      .rename(lambda x: f'site_{x}', level=2)
      )
print (df)
                                        manufacture  distance km  \
Number mw link             i                                       
1      SAM3703_2SAM3944 2  site_a  ZTE NR 8150/8250        3.618   
                           site_b  ZTE NR 8150/8250        3.618   
2      SAM3720_2SAM4115 2  site_a  ZTE NR 8150/8250        7.507   
                           site_b  ZTE NR 8150/8250        7.507   
3      SAM3729_2SAM4121_ 2 site_a  ZTE NR 8150/8250        9.478   
                           site_b  ZTE NR 8150/8250        9.478   

                                                       name     site  
Number mw link             i                                          
1      SAM3703_2SAM3944 2  site_a                    Chelak  SAM3703  
                           site_b                 Turkibolo  SAM3944  
2      SAM3720_2SAM4115 2  site_a            KattakurganATC  SAM3720  
                           site_b  Kattagurgon Sement Zavod  SAM4115  
3      SAM3729_2SAM4121_ 2 site_a                   Payariq  SAM3729  
                           site_b        Payariq Dehgonobod  SAM4121

Pandas -need to change shape of dataframe to different one

1 Answers1