CODEWITHSUNDEEP
pythonpython-3.xpandasdata-science
Arun Pal
Arun Pal

Reputation: 747

Merge two rows if value is Nan in panda

I have tables as below 

                   Error Code ID      Error Code              Description of  Error Code
0                                NaN      Value                                     NaN
1                     OE_ATO_IN_OPEN      16169      Order priced ATO cannot be entered
2                                NaN        NaN                when a security is open.
3                      e$dup_request      16198  Duplicate modification or cancellation
4                                NaN        NaN     request for the same trade has been
5                                NaN        NaN                            encountered.
6          TRD_CONT_MOD_NOT_ALLOWED      16231     Continuous session trades 
7       STR_PRO_PARTIVIPANT_INVALID      16233     Proprietary requests cannot be made

I want to convert it into

                   Error Code ID     Error Code Value        Description of  Error Code
1                     OE_ATO_IN_OPEN      16169      Order priced ATO cannot be entered               
                                                               when a security is open.
3                      e$dup_request      16198  Duplicate modification or cancellation
                                                    request for the same trade has been
                                                                           encountered.
6          TRD_CONT_MOD_NOT_ALLOWED      16231     Continuous session trades 
7       STR_PRO_PARTIVIPANT_INVALID      16233     Proprietary requests cannot be made

Missing index are just for understanding purpose.

Upvotes: 1

Views: 70

Answers (1)

jezrael
jezrael

Reputation: 862511

First forward filling missing values by ffill and then aggregate join:

c = ['Error Code ID', 'Error Code']
df[c] = df[c].ffill()
df = df.groupby(c).agg(' '.join).reset_index()

If want remove rows with missing values in Description of Error Code column use:

df = df.dropna(subset=['Description of  Error Code'])
c = ['Error Code ID', 'Error Code']
df[c] = df[c].ffill()
df = df.groupby(c).agg(' '.join).reset_index()

print (df)
                 Error Code ID Error Code  \
0               OE_ATO_IN_OPEN      16169   
1  STR_PRO_PARTIVIPANT_INVALID      16233   
2     TRD_CONT_MOD_NOT_ALLOWED      16231   
3                e$dup_request      16198   

                           Description of Error Code  
0  Order priced ATO cannot be entered when a secu...  
1                Proprietary requests cannot be made  
2                          Continuous session trades  
3  Duplicate modification or cancellation request...

Upvotes: 1

Related Questions