append multiple columns to df while keeping other column values

Question

I have a df with multiple authors associated with one title and location:

title | location | author 1 | author 2 | author 3
---------------------------------------------------
A     |  US      |  jon smit| johnny   | brad
B     |  Asia    | Kenny lee| None     | None
C     |  Europe  | gutentag | bonjour  | None

And I want output to ignore any None values and look like:

title | location | author   | 
-----------------------------
A     |  US      |  jon smit|
A     |  US      | johnny   | 
A     |  US      | brad     |
B     |  Asia    | Kenny lee| 
C     |  Europe  | gutentag | 
C     |  Europe  | bonjour  |

Any help would be appreciated!

Like `df = df.set_index(['title','location']).stack().reset_index(level=2, drop=True).reset_index(name='author')` — jezrael, Nov 11 '20 at 06:10
@jezrael Also need to drop `None` values. For this reason only I answered the question. — Mayank Porwal, Nov 11 '20 at 06:14
@MayankPorwal - I think `None` is `Nonetype`, stack remove them, so not, no necessary.. — jezrael, Nov 11 '20 at 06:14

score 4 · Accepted Answer · edited Nov 11 '20 at 07:23

Use df.melt with df.replace to replace None values to NaN and df.dropna to drop NaN.

Lastly, use df.sort_values at last to sort the rows on column title:

In [1414]: import numpy as np

In [1415]: x = df.melt(id_vars=['title', 'location'],  value_name='author')[['title', 'location', 'author']].replace('None', np.nan).dropna().sort_values('title')

In [1416]: x
Out[1416]: 
  title location     author
0     A       US   jon smit
3     A       US     johnny
6     A       US       brad
1     B     Asia  Kenny lee
2     C   Europe   gutentag
5     C   Europe    bonjour

OR: If your None values are Nonetype and not strings, you don't need replace.

x = d.melt(id_vars=["title", "location"], value_name="author")[
    ["title", "location", "author"]
].dropna()

append multiple columns to df while keeping other column values

1 Answers1