Transpose a dataframe for repeated columns in python

Question

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 .

jezrael · Accepted Answer · 2019-12-03 09:36:25Z

1

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

edited Dec 3, 2019 at 9:36

answered Dec 3, 2019 at 9:21

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

D2D Info Over a year ago

I have tried these but CC column has nan value in first row but it shuffles and takes the value of second row@jezrael

Jon Clements Over a year ago

@D2DInfo I don't see how you can achieve that robustly... What if there's another set of AA, BB and DDs, but still only one CC - what decides which of the 3 rows that CC goes into now?

jezrael Over a year ago

@D2DInfo - If is possible create some value for separator like AA in sample data, then use my solution. But like mentioned Jon Clements general solution is problem, if any data in first column

Collectives™ on Stack Overflow

Transpose a dataframe for repeated columns in python

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related