From Text file to a pandas dataframe

Question

I have a file which contains list of lists, its a text file, and it looks like that:

    [[ שומר
 ,קולורבי
 ,קיווי
 ,"תפו""ע פינק ליידי"
 ,גזר
 ,עגבניה
 ,Unknown
 ] ,
[ מארז נייצ'ר ואלי שיבולת שועל עם שבבי שוקולד
 ,פיטנס חטיפי פריכיות דקות קינמון 60 גרם
 ,פריכיות משולשות פלפל
 ,מארז נייצ'ר ואלי שיבולת שועל עם שבבי שוקולד
 ,Unknown
 ,בננה
 ,"תפו""ע פינק ליידי"
 ,"תפו""ע גרנד"
 ,מארז 5 חטיפי פרי דגנים תמר תפוח ללא סוכר
 ,Unknown
 ] ,
[ "תפו""א לבן ארוז דוד משה"
 ,כרוב לבן
 ,קישוא
 ,גזר
 ,בטטה
 ] ,
[ Unknown..........

I need to convert it to pandas DataFrame, like wide columnstore, just one list = one row. Any help would be amazing! I am working with Jupyter Notebook - Python 3.

@Yoav Barzilai to make list of lists into rows : `df = pd.DataFrame(list)` — Zaraki Kenpachi, Feb 19 '20 at 07:51
@ShubhamSharma I need it to be a dataframe that every row is a list, every value is a nubmer. — Yoav Barzilai, Feb 19 '20 at 08:10

RockStar · Accepted Answer · 2020-02-19T13:55:09.363

1

Read the file text and try below code -

import json
import pandas as pd

data = open('data.txt', 'r', encoding = 'windows-1255', errors='ignore').read().replace("\r","").replace("\n","")


remove_doulequotes = data.replace('""', '').replace('"', '')
list_of_str = list(map(lambda x: '"{x}"'.format(x=x), remove_doulequotes.split(",")))
final_data = ", ".join(list_of_str).replace('"[[', '[["').replace(']"','"]').replace(']]"', '"]]').replace('"[', '["').replace(']"]', '"]]')

data_in_list = json.loads(final_data)

df = pd.DataFrame(data_in_list)

Or You can use ast.literal_eval function also -

import ast

data = open('data.txt', 'r', encoding = 'windows-1255', errors='ignore').read().replace("\r","").replace("\n","")

remove_doulequotes = data.replace('""', '').replace('"', '')
list_of_str = list(map(lambda x: '"{x}"'.format(x=x), remove_doulequotes.split(",")))
final_data = ", ".join(list_of_str).replace('"[[', '[["').replace(']"','"]').replace(']]"', '"]]').replace('"[', '["').replace(']"]', '"]]')

data_in_list = ast.literal_eval(final_data)

df = pd.DataFrame(data_in_list)

Additional codes added for string manipulation to format string properly.

edited Feb 19 '20 at 13:55

answered Feb 19 '20 at 08:17

RockStar

1,304
2
13
35

Hey and thanks for the reply, my data is in a text file. – Yoav Barzilai Feb 19 '20 at 11:37
I mentioned that read file then store that in data named variable then you can able to reuse the code. https://stackoverflow.com/questions/8369219/how-to-read-a-text-file-into-a-string-variable-and-strip-newlines – RockStar Feb 19 '20 at 11:52
@YoavBarzilai edited answer. You can read a file in multiple ways. – RockStar Feb 19 '20 at 11:57
@YoavBarzilai using ast is the best approach. Check more details on same here - https://docs.python.org/3/library/ast.html – RockStar Feb 19 '20 at 12:00
@YoavBarzilai Improved answer, very rough code added to format strings. – RockStar Feb 19 '20 at 12:31
Hey and thanks for your help but im not getting what im trying to achive, I can send you a print screen if you like. this is my mail yoav.barzilay98@gmail.com – Yoav Barzilai Feb 19 '20 at 12:53
@YoavBarzilai Sure. – RockStar Feb 19 '20 at 12:59
@YoavBarzilai Improved answer, as per your file. – RockStar Feb 19 '20 at 13:57

score 0 · Answer 2 · answered Feb 19 '20 at 08:13

0

import json
with open('txt.txt') as f:
    lst = json.load(f)

df = pd.DataFrame(lst)

Should give you what you want.

answered Feb 19 '20 at 08:13

selib

39
2

From Text file to a pandas dataframe

2 Answers2