Transposing multiple columns into row in python

Question

I have a heavily nested json file which I have flattened and getting output as below having rows in one column with numeric values attached to it. Is there is any way I can remove it and have them into row wise as shown in the output
Input file

102_ip_addr, 102_ip_family, 102_ip_mask_addr, 102_email,    102_failed_attempts,103_ip_addr, 103_ip_family, 103_ip_mask_addr, 103_email,    103_failed_attempts,
3705824725, 2,  4294967295, [email protected],    0,3705824825,   4,  4294967625, [email protected],    0

Output:

ip_addr, ip_family, ip_mask_addr, email, failed_attempts
3705824725, 2,  4294967295, [email protected],    0
3705824825, 4,  4294967625, [email protected],    0

BENY · Accepted Answer · 2018-10-01 14:51:18Z

2

If every new row have fixed width 5 , you can using reshape

pd.DataFrame(df.values.reshape(-1,5),columns=['addr','family','mask_addr','email','attempts'])
Out[580]: 
         addr family   mask_addr         email attempts
0  3705824725      2  4294967295   [email protected]        0
1  3705824825      4  4294967625   [email protected]        0

Update

df.columns=df.columns.str.split('_',1).str[1]

df.melt().assign(newrow=lambda x : x.groupby(x['variable']).cumcount() ).pivot('newrow','variable','value')
Out[596]: 
variable         email failed_attempts     ip_addr ip_family ip_mask_addr
newrow                                                                   
0          [email protected]               0  3705824725         2   4294967295
1          [email protected]               0  3705824825         4   4294967625

edited Oct 1, 2018 at 14:51

answered Oct 1, 2018 at 14:37

BENY

324k22 gold badges176 silver badges250 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Abhis Over a year ago

thanks for the quick answer in my file there are many columns I just gave 5 columns as reference. I am trying removing the numeric values before the first _ and then bring them into rows

Collectives™ on Stack Overflow

Transposing multiple columns into row in python

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related