将.txt文件（所有数据都作为列名的空数据框）转换为数据框

Question

我在显示.txt文件的内容时得到以下输出：

        Empty DataFrame
        Columns: [[{'city': 'Zurich, Switzerland', 'cost': '135.74'}, {'city': 'Basel, 
        Switzerland', 'cost': '135.36'}, {'city': 'Lausanne, Switzerland', 'cost': '131.24'}, 
        {'city': 'Lugano, Switzerland', 'cost': '130.32'}, {'city': 'Geneva, Switzerland', 
        'cost': '130.14'}, {'city': 'Bern, Switzerland', 'cost': '125.86'}, {'city': 'Tromso, 
        Norway', 'cost': '114.81'}, {'city': 'Stavanger, Norway', 'cost': '108.38'} etc.]

有人知道如何将其转换为包含“城市”和“成本”列的数据框吗？DataFrame（）不工作，它输出与原始文件相同的字典列表

Answer 1

url = "https://www.numbeo.com/cost-of-living/region_rankings_current.jsp?region=150"
response = requests.get(url)

soup = BeautifulSoup(response.content, "html.parser")
table = BeautifulSoup(str(soup.find_all("table", id="t2")[0]), "html.parser")
table_body = BeautifulSoup(str(table.find_all("tbody")[0]), "html.parser")

findings = table_body.find_all('tr')

living_costs= []

for finding in findings:
    city = finding.find("a", class_="discreet_link").string
    cost = finding.find("td", style ="text-align: right").string
    living_costs.append({"city": city, "cost": cost})

for dicti in living_costs:
    for word in dicti:
        word.replace("Columns: [", "").replace("\n", "")

df = pd.DataFrame(living_costs)
print(df)

Answer 2

如果您已经有一个具有相等键的dict列表，您应该能够执行以下操作：

pandas.__version__ ->>  '1.1.5'

dctlst = [{"a": 1, "b":1}, {"a":2, "b":2}]
from pandas import DataFrame
df = DataFrame(dctlst)
df
   a  b
0  1  1
1  2  2

否则，您可以使用json生成一个字典列表

但首先，你必须清理一下文本（阅读后）：

with open(r"C:\Users\User\Desktop\toDF.txt", "r") as txt:
        txt = txt.read()
txt = txt.replace("Columns: [", "").replace("etc.", "").replace("\n", "")

如果不移除额外的左括号和其他内容，json将不会加载它。 此外，json需要双引号，因此将单引号替换为双引号：

txt = txt.replace("'", '"')
txt
'[{"city": "Zurich, Switzerland", "cost": "135.74"}, {"city": "Basel,         
Switzerland", "cost": "135.36"}, {"city": "Lausanne, Switzerland", "cost": "131.24"},         
{"city": "Lugano, Switzerland", "cost": "130.32"}, {"city": "Geneva, Switzerland",         
"cost": "130.14"}, {"city": "Bern, Switzerland", "cost": "125.86"}, {"city": "Tromso,         
Norway", "cost": "114.81"}, {"city": "Stavanger, Norway", "cost": "108.38"} ]'

现在它看起来像一个合适的字典列表，可以通过json.loads进行转换

from json import loads
from pandas import DataFrame

lst = loads(txt)
df = DataFrame(lst)

df
                         city    cost
0         Zurich, Switzerland  135.74
1  Basel,         Switzerland  135.36
2       Lausanne, Switzerland  131.24
3         Lugano, Switzerland  130.32
4         Geneva, Switzerland  130.14
5           Bern, Switzerland  125.86
6      Tromso,         Norway  114.81
7           Stavanger, Norway  108.38

如果希望包含城市的行看起来更漂亮，可以查看字符串操作： pandas string operations

这会起作用，但显然取决于您想要什么：

df["city"] = df["city"].astype("string").str.replace(" ","")

    df
               city    cost
0    Zurich,Switzerland  135.74
1     Basel,Switzerland  135.36
2  Lausanne,Switzerland  131.24
3    Lugano,Switzerland  130.32
4    Geneva,Switzerland  130.14
5      Bern,Switzerland  125.86
6         Tromso,Norway  114.81
7      Stavanger,Norway  108.38

这将使它变得更好：

df[["city", "country"]] = df["city"].str.split(",", expand= True)

df
        city    cost      country
0     Zurich  135.74  Switzerland
1      Basel  135.36  Switzerland
2   Lausanne  131.24  Switzerland
3     Lugano  130.32  Switzerland
4     Geneva  130.14  Switzerland
5       Bern  125.86  Switzerland
6     Tromso  114.81       Norway
7  Stavanger  108.38       Norway