Convert string formatted as Pandas DataFrame into an actual DataFrame

Question

I am trying to convert a formatted string into a pandas data frame.

[['CD_012','JM_022','PT_011','CD_012','JM_022','ST_049','MB_021','MB_021','CB_003'
,'FG_031','PC_004'],['NL_003','AM_006','MB_021'],
['JA_012','MB_021','MB_021','MB_021'],['JU_006'],
['FG_002','FG_002','CK_055','ST_049','NM_004','CD_012','OP_002','FG_002','FG_031',
'TG_005','SP_014'],['FG_002','FG_031'],['MD_010'],
['JA_012','MB_021','NL_003','MZ_020','MB_021'],['MB_021'],['PC_004'],
['MB_021','MB_021'],['AM_006','NM_004','TB_006','MB_021']]

I am trying to use the pandas.DataFrame method to do so but the result is that this whole string is placed inside one element in the DataFrame.

The expected output should be a DataFrame with all of the itemsets in brackets as elements — Sean goodlip, Jan 28 '20 at 18:08
that we understood but show us for above sample data what should be your expected output — Sociopath, Jan 28 '20 at 18:13
@AkshayNevrekar i do not understand the question. I will try to explain this as clearly as possible. Since I have formatted my string as a DataFrame I would like to try and insert all elements into a DataFrame. In this case an element is along the lines of `['CD_012','JM_022','PT_011','CD_012','JM_022','ST_049','MB_021','MB_021','CB_003' ,'FG_031','PC_004'],['NL_003','AM_006','MB_021']`. When I am trying to do this the data is inserted in only one record in the DataFrame. — Sean goodlip, Jan 28 '20 at 18:17
@SathishSanjeevi how do you propose that I convert the string into an array please? — Sean goodlip, Jan 28 '20 at 18:19
@Seangoodlip: Just assign your data to an variable and then use `pd.DataFrame(variable)`. — SKPS, Jan 28 '20 at 18:20

score 0 · Answer 1 · answered Jan 28 '20 at 18:17

Is this what you mean?

import pandas as pd


list_of_lists = [['CD_012','JM_022','PT_011','CD_012','JM_022','ST_049','MB_021','MB_021','CB_003'
                ,'FG_031','PC_004'],['NL_003','AM_006','MB_021'],
                ['JA_012','MB_021','MB_021','MB_021'],['JU_006'],
                ['FG_002','FG_002','CK_055','ST_049','NM_004','CD_012','OP_002','FG_002','FG_031',
                'TG_005','SP_014'],['FG_002','FG_031'],['MD_010'],
                ['JA_012','MB_021','NL_003','MZ_020','MB_021'],['MB_021'],['PC_004'],
                ['MB_021','MB_021'],['AM_006','NM_004','TB_006','MB_021']]


result = pd.DataFrame({'result': list_of_lists})

In my case the variable list_of_lists is of type string. This is the issue. — Sean goodlip, Jan 28 '20 at 18:24

Leila Abdelrahman · Accepted Answer · 2020-01-28T18:58:11.587

0

Best approach would be to split the string with the '],[' delimeter and then convert to df.


import numpy as np
import pandas as pd

def stringToDF(s):
    array = s.split('],[')

    # Adjust the constructor parameters based on your string
    df = pd.DataFrame(data=array,    
              #index=array[1:,0],    
             #columns=array[0,1:]
             ) 

    print(df)
    return df

stringToDF(s)

Good luck!

edited Jan 28 '20 at 18:58

answered Jan 28 '20 at 18:18

Leila Abdelrahman

178
6

I tried this approach but it left me with an array of type float: `1.40819e-76 5.59823e+25 1.57287e+131 6.6563e-120 5.22844e-76 ` – Sean goodlip Jan 28 '20 at 18:21
I changed it to specify string as the dtype. Try now – Leila Abdelrahman Jan 28 '20 at 18:24
Just tried it but I got the error `ValueError: zero-valued itemsize` when converting the string to an array – Sean goodlip Jan 28 '20 at 18:25
What is the intended shape of your array? Like 1x2? – Leila Abdelrahman Jan 28 '20 at 18:28
I was thinking that it would be 1x(amount of elemnts in the array) if that makes sense. Sorry for the poor description but I am still new to Python – Sean goodlip Jan 28 '20 at 18:31

Convert string formatted as Pandas DataFrame into an actual DataFrame

2 Answers2