Reading data from txt file and storing in dataframe

Question

I have a txt file with has data like this:

[{"text" : "Random Text" , "location" : "place" , "date" : "01 Jan,2012"}, 
 {"text" : "Similar texts" , "location" : "xyz" , "date": "02 Jan, 2020" },
...}]

How do I read this txt file in python and store the data in a dataframe? Please help.
I tried using various delimiters but the results are pretty messed up.

score 1 · Accepted Answer · answered Oct 02 '20 at 17:29

1

You just import pandas and add it to a dataframe

import pandas as pd

d = [{"text" : "Random Text" , "location" : "place" , "date" : "01 Jan,2012"}, 
 {"text" : "Similar texts" , "location": "xyz" , "date": "02 Jan, 2020" }]


df = pd.DataFrame(data=d)

df

text    location    date
0   Random Text     place   01 Jan,2012
1   Similar texts   xyz     02 Jan, 2020

answered Oct 02 '20 at 17:29

john taylor

1,080
15
31

But then how do I read the file which has this data? Because doing this puts everything in one block: d = open("file.txt","r") df = pd.DataFrame(data=d) – VIPUL VAIBHAV Oct 02 '20 at 18:18
did you try json https://stackoverflow.com/questions/54489615/how-to-read-a-text-file-of-dictionaries-into-a-dataframe – john taylor Oct 03 '20 at 00:41

score 0 · Answer 2 · answered Oct 03 '20 at 12:23

For all those, who are still not able to get the solution try this:

import pandas as pd    
import json    
    
with open('file.txt') as f:    
    string = f.read()    
    jsonData = json.loads(string)    
    df=pd.DataFrame(jsonData)

And if you get

JSONDecodeError: Unterminated string starting at:...

This means your file has some missing a '}' or something else is missing in your txt file.

Reading data from txt file and storing in dataframe

2 Answers2