remove dictionary quotation from pandas dataframe

Question

Below is the output of my dataframe:

              0                                  1  
0  {"time": "2016-03-28T23:23:12Z"      "target": "Raffi-Antilian"}  
1  {"time": "2016-03-28T23:23:12Z"      "target": "Caroline-Kaiser"}

How can I convert individual records from type dictionary to normal dataframe records with columns names being dictionary keys and record values being dictionary values? My desired output should be:

              Time                Target  
0  2016-03-28T23:23:12Z      Raffi-Antilian   
1  2016-03-28T23:23:12Z      Caroline-Kaiser

I have about 2000 records, Appreciate any help/guidance.

Answer 1

import json
data = []
with open('filename', 'r') as f:
    for line in f:
        data.append(json.loads(line))
pd.DataFrame(data)

gives

Out[49]: 
            target                  time
0   Raffi-Antilian  2016-03-28T23:23:12Z
1  Caroline-Kaiser  2016-03-28T23:23:12Z

Answer 2

You can read_csv with sep=';' if in file is not ;, so all data are in one Series. Then convert string to dictionary by ast.literal_eval and last use pd.DataFrame:

import pandas as pd
import ast
import io

temp=u"""{"time": "2016-03-28T23:23:12Z","target": "Raffi-Antilian"}  
{"time": "2016-03-28T23:23:12Z","target": "Caroline-Kaiser"}"""
#after testing replace io.StringIO(temp) to filename
s = pd.read_csv(io.StringIO(temp), index_col=None, header=None, sep=';', squeeze=True)
print (s)
0    {"time": "2016-03-28T23:23:12Z","target": "Raf...
1    {"time": "2016-03-28T23:23:12Z","target": "Car...
Name: 0, dtype: object

L = s.apply(lambda x: ast.literal_eval(x)).tolist()
print (L)
[{'time': '2016-03-28T23:23:12Z', 'target': 'Raffi-Antilian'}, 
 {'time': '2016-03-28T23:23:12Z', 'target': 'Caroline-Kaiser'}]

print (pd.DataFrame(L))
            target                  time
0   Raffi-Antilian  2016-03-28T23:23:12Z
1  Caroline-Kaiser  2016-03-28T23:23:12Z

EDIT:

Another one line solution:

import pandas as pd
import json

print (pd.DataFrame([json.loads(line.strip()) for line in open('file.txt')]))

            target                  time
0   Raffi-Antilian  2016-03-28T23:23:12Z
1  Caroline-Kaiser  2016-03-28T23:23:12Z