来自TXT的Pandas DataFrame

Question

我有一个像这样的.txt：

       USA

Arizona - New Mexico
Interstate 40
Interstate 10

South Dakota - Minneapolis
Interstate 90

South Carolina - Washington

Arizona - California
Interstate 40
Interstate 10
Interstate 8

    ANOTHER COUNTRY

State A - State B
Highway 1
Highway 2
Highway 3
...
...

我想在熊猫中创建一个数据框和一个CSV，其中第一列包含状态，而第二列则是高速公路。

      States                    HW_Number
Arizona - New Mexico          Interstate 40
Arizona - New Mexico          Interstate 10
South Dakota - Minneapolis    Interstate 90
Arizona - California          Interstate 40
Arizona - California          Interstate 10
Arizona - California          Interstate 9
State A - State B             Highway 1
State A - State B             Highway 2
State A - State B             Highway 3

我该如何做到这一点？并非所有州都有相同数量的高速公路，并且可以拥有0个高速公路，而那些拥有0的高速公路，我不想将其集成到数据范围中。

该国的专栏也可以集成。

谢谢

Answer 1

正如我所说，一个非常简单的文件可以解析：

import pandas as pd

rows = []
state = None
for line in open('x.txt'):
    if line[0] == ' ':
        continue
    line = line.strip()
    if not line:
        continue
    if '-' in line:
        state = line
    else:
        rows.append( (state,line) )

df = pd.DataFrame(rows, columns=['state','road'])
print(df)

输出：

----------
                        state           road
0        Arizona - New Mexico  Interstate 40
1        Arizona - New Mexico  Interstate 10
2  South Dakota - Minneapolis  Interstate 90
3        Arizona - California  Interstate 40
4        Arizona - California  Interstate 10
5        Arizona - California   Interstate 8
6           State A - State B      Highway 1
7           State A - State B      Highway 2
8           State A - State B      Highway 3

Answer 2

您可以迭代行并使用结​​构化数据的特征来创建列表。这些列表可用于制作数据框架或系列。

1. 将文件中的行读取到列表（f.readlines（））
2. 删除空行
3. 跟踪当前状态（没有数字结束）
4. 附加状态和高速公路列表
5. 使用列表来制作dataframe或series

import pandas as pd
import io
f = io.StringIO(
    """
USA

Arizona - New Mexico
Interstate 40
Interstate 10

South Dakota - Minneapolis
Interstate 90

South Carolina - Washington

Arizona - California
Interstate 40
Interstate 10
Interstate 8

ANOTHER COUNTRY

State A - State B
Highway 1
Highway 2
Highway 3
    """
)
lines = f.readlines()
states = []
hw_numbers = []
current_state = None
for line in lines:
    line = line.strip() #removes \n
    
    if line == '': #remove empty rows
        continue
    elif line[-1].isdigit() == False: #if not a digit, then it's a state
        current_state = line
    else: #if it is a digit, then it's a highway
        states.append(current_state)
        hw_numbers.append(line)
pd.DataFrame({
    'States':states,
    'HW_number':hw_numbers
})