how to make data into the same row

Question

I have a dataset like this

id event 2015 2016 2017
   a 2015   33    na  na
   a  2016   na    32  na
   a 2017   na    na  31
   b 2015   30    na  na
   b 2017   na    na   20

how do I make all the non-missing values in the same row:

id   2015   2016   2017
 a   33     32     31
 b   30     0      20

sorry the questions above do not solve my case, and the code does not work

So you need `df = df.replace('na', np.nan).groupby('id', as_index=False).first().fillna(0)` ? — jezrael, Apr 08 '21 at 13:00
1) I need to groupy id 2)within same id, I want 2015, 2016, 2017 all on the same row. For example, under id a, I want 2015 with 33, 2016 with 32, and 2017 with 31 in the same row. — totolow, Apr 08 '21 at 13:03

score 0 · Answer 1 · answered Apr 08 '21 at 12:36

try:

df = df.set_index('event').replace('na', np.nan)
df1 = pd.concat([df[col].dropna() for col in df.columns], axis=0).to_frame().T

df1:

event   2015    2016    2017
0       33      32      31

1st replace na by NaN then set event as index. Dropall the NaN from the column values.

score 0 · Answer 2 · answered Apr 08 '21 at 13:05

Use GroupBy.first for first non missing value per groups by id:

df = (df.drop('event', axis=1)
        .replace('na', np.nan)
        .groupby('id', as_index=False)
        .first()
        .fillna(0))
print (df)
  id 2015 2016 2017
0  a   33   32   31
1  b   30    0   20

how to make data into the same row

2 Answers2