CODEWITHSUNDEEP
pythonpython-3.xpandascsvtranspose
D2D Info
D2D Info

Reputation: 31

Transpose a dataframe for repeated columns in python

I have a dataframe which consists of two column that is 'info and value' . I need to transpose these datframe based on info as the column name and the value as the values in that column names .

But the problem I am facing is in the info column where some values are repeated and some are not repeated .So if the column names are repeated the data must be appended in that column and if not then it should add another column

df

info    value
AA      3M 
BB      Charterer
DD      Tartous
AA      Syria
BB      +963
CC      +96
DD      pal

The expected output is as follows:

AA     BB           CC     DD
3M     Charterer          Tartous
Syria   +963        +96    pal

In the dataframe df it strats with the column AA so till next AA occurs all the columns are converted to row . In these CC column is not present for AA so it must be empty but for next AA the column CC is present so it should append that values .

Upvotes: 2

Views: 195

Answers (1)

jezrael
jezrael

Reputation: 862511

Use Series.cumsum for counter if each groups starting by AA values, create MultiIndex by DataFrame.set_index, reshape by Series.unstack and last remove column name by DataFrame.rename_axis:

df1 = (df.set_index([df['info'].eq('AA').cumsum(), 'info'])['value']
         .unstack()
         .rename_axis(None, axis=1))
print (df1)
         AA         BB   CC       DD
info                                
1        3M  Charterer  NaN  Tartous
2     Syria       +963  +96      pal

Upvotes: 1

Related Questions