Combine 2 rows into one dataframe

Question

I try to combine two rows in dataframe into one

              ID           Value1         Value2    
0             ID_1           NaN            2           
1             ID_2           NaN            7    
2             ID_1           5             NaN   
3             ID_2           8             NaN

The result should be the following

              ID           Value1         Value2    
1             ID_1           5              2     
2             ID_2           8              7

Is it possible with a method of dataframe ?

score 1 · Answer 1 · answered May 10 '21 at 13:45

1

via stack/unstack

df = df.set_index('ID').stack().unstack().reset_index()

Output:

     ID  Value1  Value2
0  ID_1     5.0     2.0
1  ID_2     8.0     7.0

answered May 10 '21 at 13:45

Nk03

14,699
2
8
22

It's not working, I have this error ValueError: Index contains duplicate entries, cannot reshape – tferrari May 10 '21 at 13:55

Anurag Dabas · Answer 2 · 2021-05-10T14:15:17.400

1

Use set_index() , apply() method and sorted() method:

newdf=df.set_index('ID').apply(lambda x : sorted(x,key=pd.isnull))

Finally use boolean masking and isna() method:

newdf=newdf[~newdf.isna().all(1)]

Now If you print newdf you will get your desired output:

       Value1   Value2
ID      
ID_1    5.0     2.0
ID_2    8.0     7.0

If needed use reset_index() method:

newdf=newdf.reset_index()

Output of above code:

    ID      Value1  Value2
0   ID_1    5.0     2.0
1   ID_2    8.0     7.0

edited May 10 '21 at 14:15

answered May 10 '21 at 14:02

Anurag Dabas

23,866
9
21
41

If there are unequal amounts of NAN's in value1/value2 will it not drop all after sorting? – Nk03 May 10 '21 at 14:05
1

you are right....updated answer....btw thanks for noticing this @Nk03 **:)** – Anurag Dabas May 10 '21 at 14:15

Celius Stingher · Accepted Answer · 2021-05-10T17:27:29.873

0

For this particular case you can also use groupby():

df = df.groupby('ID')['Value1','Value2'].sum().reset_index()

edited May 10 '21 at 17:27

answered May 10 '21 at 14:18

Celius Stingher

17,835
6
23
53

Combine 2 rows into one dataframe

3 Answers3