python - 如何获取 CSV 文件中的列并将文本分隔并保存到 python 中的其他列

Question

这个问题已经有答案了:

Split column into separate columns based on separator strings (2 个回答)

已关闭 5 年前。

例如，我们有一个带有

的 csv 文件

name       address           age 
vip     bang #@ india     12 
ags     myso ^% india     25 
dhs     bang #@ india     14 
fgn     nyk  @$ bangla    45         

如何制作并添加到不同的列

name    city     country   age 
vip     bang    india     12 
ags     myso    india     25 
dhs     bang    india     14 
fgn     nyk     bangla    45         

我使用的代码是

import re
import csv
with open("/home/vipul/Desktop/example.csv", 'rb') as f:
    mycsv = csv.reader(f)
    for row in mycsv:
        text = row[0]
        txt = re.findall(r'(\w+[\s\w]*)\b', text)  
        print txt

Answer 1

使用 pandas 很容易:

import pandas as pd

# Create dataframe
df = pd.DataFrame({
    "name": ["vip", "ags", "dhs", "fgn"],
    "address": ["bang #@ india", "myso ^% india", "bang %@ india", "nyk @$ bangla"],
    "age": [12, 25, 14, 45]
})

# Split "address" string on spaces, keep first split
# as city, last split as country
df["city"] = df["address"].str.split(" ").str[0]
df["country"] = df["address"].str.split(" ").str[-1]

print df

结果是:

         address  age name  city country
0  bang #@ india   12  vip  bang   india
1  myso ^% india   25  ags  myso   india
2  bang %@ india   14  dhs  bang   india
3  nyk @$ bangla   45  fgn   nyk  bangla

编辑:

(可选)仅保留某些列:

# Optionally, keep only certain columns
df = df[["name", "city", "country", "age"]]
print df

其产量:

  name  city country  age
0  vip  bang   india   12
1  ags  myso   india   25
2  dhs  bang   india   14
3  fgn   nyk  bangla   45

编辑2:

您可以使用 pandas 读取和写入文件，而不是自己创建数据框:

# Read the dataframe from file:
df = pd.read_csv("input_file.csv", sep=",")

# Split "address" string on spaces, keep first split
# as city, last split as country
df["city"] = df["address"].str.split(" ").str[0]
df["country"] = df["address"].str.split(" ").str[-1]

# Optionally, keep only certain columns
df = df[["name", "city", "country", "age"]]

# Write altered dataframe to file
df.to_csv("output_file.csv", sep=",", index=False)

编辑3:

正如评论中指出的，拆分两次是不必要的；你可以这样做:

split = df["address"].str.split(" ")
df["city"] = split.str[0]
df["country"] = split.str[-1]