CODEWITHSUNDEEP
pythonpandas
duckgoduck
duckgoduck

Reputation: 23

Pandas swap multi rows to columns

Does anyone know how to transpose pandas dataframe, I have dataset like that:

# 99564, Noam
Hop Company LLC, 10503 Cher rd
(123) 456-7890, Motor SR 5032
# 99843, Noam
Hop Prods,  902010 Rogg rd
(123) 456-7890, Ed CA 3534
# 99223, Noam
Skyline LLC,    12795 Rodf Road
(123) 456-7890, Sats Road 3922

And I want to transform it to this:

# 99564, Noam, Hop Company LLC, 10503 Cher rd,(123) 456-7890,   Motor SR 5032
# 99843, Noam, Hop Prods, 902010 Rogg rd, (123) 456-7890,   Ed CA 3534
# 99223, Noam, Skyline LLC, 12795 Rodf Road, (123) 456-7890,    Sats Road 3922

Can anyone help me with directions on how I can do it?

Thanks!

Upvotes: 2

Views: 100

Answers (1)

Umar.H
Umar.H

Reputation: 23099

If your result is already in a dataframe, you can just re-shape it with index filtering and re-join it with two concats.

df = pd.read_csv(StringIO(data),sep=',',header=None)


df2 = df.loc[~df.index.isin(df.iloc[::3].index.tolist())].reset_index(drop=True)

df2 = pd.concat([df2.iloc[::2].reset_index(drop=True),
           df2.iloc[1::2].reset_index(drop=True)],axis=1,ignore_index=False)


final = pd.concat([df.iloc[::3].reset_index(drop=True),df2],axis=1)


print(final)

         0      1                0                    1               0  \
0  # 99564   Noam  Hop Company LLC        10503 Cher rd  (123) 456-7890   
1  # 99843   Noam        Hop Prods       902010 Rogg rd  (123) 456-7890   
2  # 99223   Noam      Skyline LLC      12795 Rodf Road  (123) 456-7890   

                 1  
0    Motor SR 5032  
1       Ed CA 3534  
2   Sats Road 3922

Upvotes: 1

Related Questions