python - list to pandas dataframe

Question

I have list of strings. Each string is a sentence with comma delimiter.

RMCs = [
        '$GPRMC,222112.184,A,3713.681121,N,12205.707716,W,2.580,44.9,181018,,,A,V*33', 
        '$GPRMC,222113.150,A,3713.804392,N,12205.729394,W,1.435,64.5,181018,,,A,V*32', 
        '$GPRMC,222114.153,A,3713.833715,N,12205.736514,W,0.180,323.4,181018,,,A,V*02', 
        '$GPRMC,222115.157,A,3713.834953,N,12205.735842,W,0.374,8.8,181018,,,A,V*0E', 
        '$GPRMC,222116.163,A,3713.834541,N,12205.733602,W,0.240,346.6,181018,,,A,V*08', 
        '$GPRMC,222117.163,A,3713.833123,N,12205.734873,W,0.664,238.3,181018,,,A,V*0E', 
        '$GPRMC,222118.166,A,3713.833402,N,12205.733397,W,0.242,308.0,181018,,,A,V*05'
       ]

I want to split each line by the comma and place them into Pandas dataframe. the expected output should be like the table below:

1  $GPRMC  222112.184  A  3713.681121  N  12205.707716  W  2.580  44.9  181018  NaN  NaN  A  V*33
2  $GPRMC  222113.150  A  3713.804392  N  12205.729394  W  1.435  64.5  181018  NaN  NaN  A  V*32
3  $GPRMC  222114.153  A  3713.833715  N  12205.736514  W  0.180 323.4  181018  NaN  NaN  A  V*02'
.
.
n  $GPRMC  ................................................................

** I can add headers if needed.

Tried in so many ways but could find the most efficient and clean way.

Please assist.

jezrael · Accepted Answer · 2018-10-25 11:23:23Z

1

Use DataFrame constructor with list comprehension and split:

df = pd.DataFrame([x.split(',') for x in RMCs])
print (df)

       0           1  2            3  4             5  6      7      8   \
0  $GPRMC  222112.184  A  3713.681121  N  12205.707716  W  2.580   44.9   
1  $GPRMC  222113.150  A  3713.804392  N  12205.729394  W  1.435   64.5   
2  $GPRMC  222114.153  A  3713.833715  N  12205.736514  W  0.180  323.4   
3  $GPRMC  222115.157  A  3713.834953  N  12205.735842  W  0.374    8.8   
4  $GPRMC  222116.163  A  3713.834541  N  12205.733602  W  0.240  346.6   
5  $GPRMC  222117.163  A  3713.833123  N  12205.734873  W  0.664  238.3   
6  $GPRMC  222118.166  A  3713.833402  N  12205.733397  W  0.242  308.0   

       9  10 11 12    13  
0  181018        A  V*33  
1  181018        A  V*32  
2  181018        A  V*02  
3  181018        A  V*0E  
4  181018        A  V*08  
5  181018        A  V*0E  
6  181018        A  V*05

If want also replace empty strings:

df = pd.DataFrame([[i if i != '' else np.nan for i in x.split(',')] for x in RMCs])

print (df)

       0           1  2            3  4             5  6      7      8   \
0  $GPRMC  222112.184  A  3713.681121  N  12205.707716  W  2.580   44.9   
1  $GPRMC  222113.150  A  3713.804392  N  12205.729394  W  1.435   64.5   
2  $GPRMC  222114.153  A  3713.833715  N  12205.736514  W  0.180  323.4   
3  $GPRMC  222115.157  A  3713.834953  N  12205.735842  W  0.374    8.8   
4  $GPRMC  222116.163  A  3713.834541  N  12205.733602  W  0.240  346.6   
5  $GPRMC  222117.163  A  3713.833123  N  12205.734873  W  0.664  238.3   
6  $GPRMC  222118.166  A  3713.833402  N  12205.733397  W  0.242  308.0   

       9   10  11 12    13  
0  181018 NaN NaN  A  V*33  
1  181018 NaN NaN  A  V*32  
2  181018 NaN NaN  A  V*02  
3  181018 NaN NaN  A  V*0E  
4  181018 NaN NaN  A  V*08  
5  181018 NaN NaN  A  V*0E  
6  181018 NaN NaN  A  V*05

edited Oct 25, 2018 at 11:23

answered Oct 25, 2018 at 11:19

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

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

2 Comments

KinGorbaZ Over a year ago

awesome! Thank you very much for the quick answer!

jezrael Over a year ago

@KinGorbaZ - Thanks, glad to help. Don't forget to accept the answer, if it suits you! :)

Collectives™ on Stack Overflow

python - list to pandas dataframe

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related