Some programs often store the results in JSON format, so if you need to analyze the data, it’s easier to put the data into a DataFrame. Let’s say we get a Txt file filled with lines of data like this:

{" tCity ", "suzhou", "tClassify" : "[for]", "tCancelReason" : ""," tProvince ":" jiangsu "} {" tCity ", "Shanghai", "tClassify" : "[inquiry, predestined relationship]", "tCancelReason" : "0901832", "tProvince" : "Shanghai"} {" tCity ":" guilin ", "tClassify" : "[inquiry, inquiry]", "tCancelReason" : "0912340", "tProvince" : "guangxi"}Copy the code

By analyzing the above data, it can be known that:

  • The data is stored in a Txt file, essentially a string STR
  • The string represents the form and is shaped like a dict type

So, what can be done to force Str directly to dict?

It turns out that the eval() method converts STR directly to dict

  • Pandas can write dataframes directly to Excel files
  • Dataframe appends a uniform Dict to a new row of the data box

So, we can convert the data format with the following code:

import pandas as pd txt = pd.read_table('c:/Users/ami/Documents/WeChat Files/helloMe/Files/mq(3).txt',encoding='utf-8',header=None) new = pd.DataFrame({},index=[0]) result = new for key,value  in txt[0].items(): new = eval(value) result = result.append(new, ignore_index=True) result.to_excel('c:/Users/ami/Desktop/result20180408.xlsx') print('done! ')Copy the code

A few caveats need to be highlighted:

  1. Pandas reads the TXT file using the read_table() method
  2. Read_table (), encoding= ‘UTF-8’, you don’t see garbled characters
  3. The read_table() method, which sets the presence or absence of a table header with the header argument
  4. The append() method of the dataframe, which can set the row index with the ignore_index parameter

👏👏👏 take a look at our previous article 😃😃😃 🌺 Excel data analysis tool library – correlation coefficient 🌺 dry goods, hand in hand to teach you to do correlation analysis 🌺 5 years of data analysis road, summary. 🌺 user segmentation and portrait analysis 🌺 K-nearest neighbor algorithm and practice

Welcome to our wechat official account.Home of data analysts“

Scan the QR code to follow us

💁 provides career planning, resume guidance, interview counseling services

QQ communication group: 254674155