How can i convert this lists object into a dataframe?

Question

I have something which lokos like (called lines)

['                id\t    Name\t    Type\t      User\t     Q\t             country\t       Final-score\t       Progress\t                       website',
 'abcde\t                 jen\t      engineer\t  jenabc\t           RUNNING\t         UK\t             75%\t                                N/A',
 'fres\t        Penny\t               dr\t     dr123\t           RUNNING\t         DENMARK\t             67%\t                                N/A']

each line which is in the speech marks and separated by ',' is a dataframe row. However i cannot convert to dataframe.

new_df = pd.read_csv(StringIO(",".join(lines[1:])),sep = "\t")

i do [1:] since the first line is just a comment. i get the error: ParserError: Error tokenizing data. C error: Expected 963 fields in line 3, saw 1099

i'd like my datframe to be such that the first row is the headers, and the rest are the contents separated by \t. how can i do this?

score 1 · Accepted Answer · answered May 05 '21 at 13:49

1

df = pd.read_csv(StringIO("\n".join(lines)), sep=r"\s+")
print(df)

Prints:

      id   Name      Type    User        Q  country Final-score  Progress  website
0  abcde    jen  engineer  jenabc  RUNNING       UK         75%       NaN      NaN
1   fres  Penny        dr   dr123  RUNNING  DENMARK         67%       NaN      NaN

answered May 05 '21 at 13:49

Andrej Kesely

168,389
15
48
91

ParserError: Error tokenizing data. C error: Expected 11 fields in line 5, saw 12 < -- i get this. i just provided a sample earlier – Maths12 May 05 '21 at 14:03
1

@Maths12 Try `df = pd.read_csv(StringIO("\n".join(lines)), sep=r"\s{2,}", engine="python")` – Andrej Kesely May 05 '21 at 14:04
what does sep=r"\s{2,}" mean> – Maths12 May 05 '21 at 14:06
@Maths12 It's regular expression. It means that the separator is at minimum 2 whitespace characters long. – Andrej Kesely May 05 '21 at 14:11
actually this doesn't work for me i have values in different columns e.g. one value should be in col1 but in col2. how can i clear the whitespace in the above? – Maths12 May 05 '21 at 14:24

How can i convert this lists object into a dataframe?

1 Answers1