read dictionaries from one file and create an excel (data frame)l from panda using python

user_10945951 0
Viewed 83

i have an input file like below with multpile rows of dictionaries where the key columns remains same:

{'TABSCHEMA': 'OP', 'TABNAME': 'T1', 'COLNO': 9, 'COLNAME': 'ACD'}
    {'TABSCHEMA': 'OP', 'TABNAME': 'T1', 'COLNO': 8, 'COLNAME': 'ACD1'}
     {'TABSCHEMA': 'OPE', 'TABNAME': 'T1', 'COLNO': 8, 'COLNAME': 'AC_CD'}
     {'TABSCHEMA': 'OPEW', 'TABNAME': 'T2', 'COLNO': 7, 'COLNAME': 'AC_ID'}

and i want to convert it to data frame like below:

Expected O/p:
    TABSCHEMA   TABNAME COLNO   COLNAME
0   OP  T1  8   ACD
1   OP  T1  9   ACD1
2   OPE T1  8   ACC_D
3   OPEW T2 7   AC_ID

SQL used:
import pandas as pd
def compare(File1,File2):
    with open(File1,'r') as f:
        d=set(f.readlines())
    with open(File2,'r') as f:
        e=set(f.readlines())
    with open('pandastry1.txt','w') as f:
        for line in list(e-d):
            dict_count = len(line)
            print(dict_count)
            df = pd.DataFrame(line[0], index=[0])
            print(df)
   
compare('OPd.txt','OPER.txt')

Seems like some issue with query,. Can help?

python
2 Answers

please check the following solution.

enter image description here

enter image description here

Hope this is what you looking for.

ss = [{'TABSCHEMA': 'OP', 'TABNAME': 'T1', 'COLNO': 9, 'COLNAME': 'ACD'},
 {'TABSCHEMA': 'OPE', 'TABNAME': 'T1', 'COLNO': 8, 'COLNAME': 'AC_CD'},
 {'TABSCHEMA': 'OPEW', 'TABNAME': 'T2', 'COLNO': 7, 'COLNAME': 'AC_ID'}]

import json
import pandas as pd
df = pd.io.json.json_normalize(ss)
df.to_excel(‘excel_filename.xlsx’)

After updating input:

enter image description here

user_126746290

You could try the following:

            ...
            df = pd.DataFrame(eval(line))
            ...

instead of df = pd.DataFrame(line[0], index=[0]).

For reading the full file into a dataframe you could do:

with open("input.txt", "r") as file:
    df = pd.DataFrame.from_records(eval(line) for line in file.readlines())

(Replace input.txt with your file name.)

EDIT: When I paste this

{'TABSCHEMA': 'OP', 'TABNAME': 'T1', 'COLNO': 9, 'COLNAME': 'ACD'}
    {'TABSCHEMA': 'OP', 'TABNAME': 'T1', 'COLNO': 8, 'COLNAME': 'ACD1'}
     {'TABSCHEMA': 'OPE', 'TABNAME': 'T1', 'COLNO': 8, 'COLNAME': 'AC_CD'}
     {'TABSCHEMA': 'OPEW', 'TABNAME': 'T2', 'COLNO': 7, 'COLNAME': 'AC_ID'}

into a file named input.txt and then run

with open("input.txt", "r") as file:
    df = pd.DataFrame.from_records(eval(line) for line in file.readlines())
print(df)

I get

  TABSCHEMA TABNAME  COLNO COLNAME
0        OP      T1      9     ACD
1        OP      T1      8    ACD1
2       OPE      T1      8   AC_CD
3      OPEW      T2      7   AC_ID

If you want to write a dataframe to excel then use .to_excel:

df.to_excel("input.xlsx")
user_143112630
Related