文字或词汇计数

from sqlalchemy import create_engine
import pandas as pd

con = create_engine('mysql+pymysql://user:password@host:3306/DB?charset=utf8')
sql = 'sql查询语句'

cur = con.execute(sql)
res = cur.fetchall()
print(len(res))
li = []
for i in res:
    # print(i[0],type(i[0]))
    if len(i[0]) == 4:
        a = i[0][:2]
        print(a, len(i[0]))
        li.append(a)

print(li)
a = [{i: li.count(i)} for i in sorted(set(li))]

# str = ''.join(li)
# print(str)
# a = [{i: str.count(i)} for i in sorted(set(str))]
# print(a)

file_path = 'xxx'
df = pd.DataFrame(a).stack().sort_values(ascending=False).reset_index() #  .sort_index(axis=1, ascending=False)
df1 = df.head(52)
print(df1)
# df1.to_excel(file_path, encoding='utf-8', index=False)