Separate Pandas Dataframe row in multiple rows

user_13281662 0
Viewed 33

I have a Pandas dataframe that represents an organigram with 11 levels that looks like this:

level0 level1 level2 level3
Emma John Jason
Emma John Hamza Michael
Emma John Hamza Olivia
Emma Adam Jaber
Emma Adam Noah
Emma Catherine Kate Frank

My desired output would look like this, where each new column cell is separated to a new row:

level0 level1 level2 level3
Emma
John
Jason
Hamza
Michael
Olivia
Adam
Jaber
Noah
Catherine
Kate
Frank

Using pandas.shift I've been able to get this dataframe:

level0 level1 level2 level3
Emma John Jason
Hamza Michael
Olivia
Adam Jaber
Noah
Catherine Kate Frank

But I can't find a way to add the needed depth. Any suggestions?

Here's my code:

shifted_organigram= organigram.shift(1)
final_organigram=organigram[organigram.loc[:]!=shifted_organigram.loc[:]]
final_organigram = final_organigram.fillna("")```
pandas dataframe python
1 Answers

You can use melt at starting point to flatten your dataframe and pivot at the end to reshape it:

out = (
    df.replace('', np.nan)
      .melt(ignore_index=False)
      .drop_duplicates('value')
      .sort_index()
      .assign(index=lambda x: range(len(x)))
      .pivot('index', 'variable', 'value')
      .fillna('')
      .rename_axis(index=None, columns=None)
)

You can execute this statements one by one from top to bottom to follow the transformation, i.e. df.replace(...) then df.replace(...).melt(...) and so on.

Output:

>>> out
   level0     level1 level2   level3
0    Emma                           
1               John                
2                     Jason         
3                                   
4                     Hamza         
5                            Michael
6                             Olivia
7               Adam                
8                     Jaber         
9                      Noah         
10         Catherine                
11                     Kate         
12                             Frank
user_152399510
Related