How to rearange pandas Dataframe?

Question

I want to use Data from an SAP-System to feed an IForest Model. For this I use RFC_READ_TABLE to get the Data. The Output of RFC_READ_TABLE is a dict object. When I put the dict object into a pandas DataFrame I get not the DataFrame I need and don't know how to get it. I want a DataFrame like:

    Col1  Col2  Col3
0:  val01 val02 val03
1:  val11 val12 val13

But get a DataFrame like:

                                                 DATA
0   {'WA': '700,AA ,0017,US ,NEW YORK            ,...

I thing every field is in one String. Fields should be 700 or AA. And I can't give the Columns Names the only Column is DATA.

I tried to put the RFC_READ_TABLE output directly into a pd.DataFrame. That failed because the output gives

result = conn.call('RFC_READ_TABLE',QUERY_TABLE='SPFLI',DELIMITER=',')
print(result)
Out:
{'DATA': [{'WA': '700,AA ,0017,US ,NEW YORK            ,JFK,US ,SAN        
FRANCISCO       ,SFO,      361 ,110000,140100,2572.0000,MI , , 0'}......
'FIELDS': [{'FIELDNAME': 'MANDT', 'OFFSET': '000000', 'LENGTH':   
'000003', 'TYPE': 'C', 'FIELDTEXT': 'Client'}, ......
'OPTIONS': []}

so Data, Fields and Options are Key and everything behind is a String (I guess). To get rid of 'FIELDS' and 'OPTIONS' I do:

result = dict(list(result.items())[:1])

Then I put 'result' into a pd.DataFrame:

df = pd.DataFrame.from_dict(result)
print(df)
Out:
                                                 DATA
0   {'WA': '700,AA ,0017,US ,NEW YORK            ,...
1   {'WA': '700,AA ,0064,US ,SAN FRANCISCO       ,...

So somehow some values are not shown and every Field of a row is in one Field. I want something like that:

    MANDT   CARRID   CONNID 
0   700      AA        0017
1   700      AA        0064

and so on.

I tried:

columnnames=['MANDT','CARRID','CONNID',...]
df = pd.DataFrame.from_dict(result,columns=columnnames)

But get:

ValueError: cannot use columns parameter with orient='columns'

So I tried:

orient='index'

But get:

AssertionError: 16 columns passed, passed data had 26 columns

I think thats not what I want.

The Table has 26 rows.

Answer 1

For creating the dict I have.

d = {'DATA': [{'WA': '700,AA ,0017,US ,NEW YORK            ,JFK,US ,SAN FRANCISCO           
,SFO,      361 ,110000,140100,2572.0000,MI , , 0'}, {'WA': '700,AA ,0064,US ,SAN  
FRANCISCO       ,SFO,US ,NEW YORK            ,JFK,      321 
,090000,172100,2572.0000,MI , , 0'}, {'WA': '700,AZ ,0555,IT ,ROME                
,FCO,DE ,FRANKFURT           ,FRA,      125 ,190000,210500,845.0000 ,MI , , 0'}, 
{'WA': '700,AZ ,0788,IT ,ROME                ,FCO,JP ,TOKYO               ,TYO,      
775 ,120000,085500,6130.0000,MI , , 1'}, {'WA': '700,AZ ,0789,JP ,TOKYO               
,TYO,IT ,ROME                ,FCO,      940 ,114500,192500,6130.0000,MI , , 0'}, 
{'WA': '700,AZ ,0790,IT ,ROME                ,FCO,JP ,OSAKA               ,KIX,      
815 ,103500,081000,6030.0000,MI ,X, 1'}, {'WA': '700,DL ,0106,US ,NEW YORK            
,JFK,DE ,FRANKFURT           ,FRA,      475 ,193500,093000,3851.0000,MI , , 1'}, 
{'WA': '700,DL ,1699,US ,NEW YORK            ,JFK,US ,SAN FRANCISCO       ,SFO,      
382 ,171500,203700,2572.0000,MI , , 0'}, {'WA': '700,DL ,1984,US ,SAN FRANCISCO       
,SFO,US ,NEW YORK            ,JFK,      325 ,100000,182500,2572.0000,MI , , 0'}, 
{'WA': '700,JL ,0407,JP ,TOKYO               ,NRT,DE ,FRANKFURT           ,FRA,      
725 ,133000,173500,9100.0000,KM , , 0'}, {'WA': '700,JL ,0408,DE ,FRANKFURT           
,FRA,JP ,TOKYO               ,NRT,      675 ,202500,154000,9100.0000,KM ,X, 1'}, 
{'WA': '700,LH ,0400,DE ,FRANKFURT           ,FRA,US ,NEW YORK            ,JFK,      
444 ,101000,113400,6162.0000,KM , , 0'}, {'WA': '700,LH ,0401,US ,NEW YORK            
,JFK,DE ,FRANKFURT           ,FRA,      435 ,183000,074500,6162.0000,KM , , 1'}, 
{'WA': '700,LH ,0402,DE ,FRANKFURT           ,FRA,US ,NEW YORK            ,JFK,      
455 ,133000,150500,6162.0000,KM ,X, 0'}, {'WA': '700,LH ,2402,DE ,FRANKFURT           
,FRA,DE ,BERLIN              ,SXF,       65 ,103000,113500,555.0000 ,KM , , 0'}, 
{'WA': '700,LH ,2407,DE ,BERLIN              ,TXL,DE ,FRANKFURT           ,FRA,       
65 ,071000,081500,555.0000 ,KM , , 0'}, {'WA': '700,QF ,0005,SG ,SINGAPORE           
,SIN,DE ,FRANKFURT           ,FRA,      825 ,225000,053500,*000.0000,KM , , 1'}, 
{'WA': '700,QF ,0006,DE ,FRANKFURT           ,FRA,SG ,SINGAPORE           ,SIN,      
670 ,205500,150500,*000.0000,KM , , 1'}, {'WA': '700,SQ ,0002,SG ,SINGAPORE           
,SIN,US ,SAN FRANCISCO       ,SFO,     1105 ,170000,192500,8452.0000,MI , , 0'}, 
{'WA': '700,SQ ,0015,US ,SAN FRANCISCO       ,SFO,SG ,SINGAPORE           ,SIN,     
1125 ,160000,024500,8452.0000,MI , , 2'}, {'WA': '700,SQ ,0158,SG ,SINGAPORE           
,SIN,ID ,JAKARTA             ,JKT,       95 ,152500,160000,560.0000 ,MI , , 0'}, 
{'WA': '700,SQ ,0988,SG ,SINGAPORE           ,SIN,JP ,TOKYO               ,TYO,      
400 ,163500,001500,3125.0000,MI , , 1'}, {'WA': '700,UA ,0941,DE ,FRANKFURT           
,FRA,US ,SAN FRANCISCO       ,SFO,      696 ,143000,170600,5685.0000,MI , , 0'}, 
{'WA': '700,UA ,3504,US ,SAN FRANCISCO       ,SFO,DE ,FRANKFURT           ,FRA,      
630 ,150000,103000,5685.0000,MI , , 1'}, {'WA': '700,UA ,3516,US ,NEW YORK            
,JFK,DE ,FRANKFURT           ,FRA,      445 ,162000,054500,6162.0000,KM , , 1'}, 
{'WA': '700,UA ,3517,DE ,FRANKFURT           ,FRA,US ,NEW YORK            ,JFK,      
495 ,104000,125500,6162.0000,KM , , 0'}], 'FIELDS': [{'FIELDNAME': 'MANDT', 
'OFFSET': '000000', 'LENGTH': '000003', 'TYPE': 'C', 'FIELDTEXT': 'Client'}, 
{'FIELDNAME': 'CARRID', 'OFFSET': '000004', 'LENGTH': '000003', 'TYPE': 'C', 
'FIELDTEXT': 'Airline Code'}, {'FIELDNAME': 'CONNID', 'OFFSET': '000008', 
'LENGTH': '000004', 'TYPE': 'N', 'FIELDTEXT': 'Flight Connection Number'}, 
{'FIELDNAME': 'COUNTRYFR', 'OFFSET': '000013', 'LENGTH': '000003', 'TYPE': 'C', 
'FIELDTEXT': 'Country Key'}, {'FIELDNAME': 'CITYFROM', 'OFFSET': '000017',  
'LENGTH': '000020', 'TYPE': 'C', 'FIELDTEXT': 'Departure city'}, {'FIELDNAME': 
'AIRPFROM', 'OFFSET': '000038', 'LENGTH': '000003', 'TYPE': 'C', 'FIELDTEXT': 
'Departure airport'}, {'FIELDNAME': 'COUNTRYTO', 'OFFSET': '000042', 'LENGTH': 
'000003', 'TYPE': 'C', 'FIELDTEXT': 'Country Key'}, {'FIELDNAME': 'CITYTO', 
'OFFSET': '000046', 'LENGTH': '000020', 'TYPE': 'C', 'FIELDTEXT': 'Arrival city'}, 
{'FIELDNAME': 'AIRPTO', 'OFFSET': '000067', 'LENGTH': '000003', 'TYPE': 'C', 
'FIELDTEXT': 'Destination airport'}, {'FIELDNAME': 'FLTIME', 'OFFSET': '000071', 
'LENGTH': '000010', 'TYPE': 'I', 'FIELDTEXT': 'Flight time'}, {'FIELDNAME': 
'DEPTIME', 'OFFSET': '000082', 'LENGTH': '000006', 'TYPE': 'T', 'FIELDTEXT': 
'Departure time'}, {'FIELDNAME': 'ARRTIME', 'OFFSET': '000089', 'LENGTH': 
'000006', 'TYPE': 'T', 'FIELDTEXT': 'Arrival time'}, {'FIELDNAME': 'DISTANCE', 
'OFFSET': '000096', 'LENGTH': '000009', 'TYPE': 'P', 'FIELDTEXT': 'Distance'}, 
{'FIELDNAME': 'DISTID', 'OFFSET': '000106', 'LENGTH': '000003', 'TYPE': 'C', 
'FIELDTEXT': 'Mass unit of distance (kms, miles)'}, {'FIELDNAME': 'FLTYPE', 
'OFFSET': '000110', 'LENGTH': '000001', 'TYPE': 'C', 'FIELDTEXT': 'Flight type'}, 
{'FIELDNAME': 'PERIOD', 'OFFSET': '000112', 'LENGTH': '000003', 'TYPE': 'b', 
'FIELDTEXT': 'Arrival n day(s) later'}], 'OPTIONS': []}