python - 从元组列表到数组，第二行成为标题

Question

从元组列表到数组，第二行成为打印到 csv 文件的标题。

这是我的城市间巴士旅行数据集原始列表

L= [ 
("Seattle WA US","Seattle WA US","56"),
("Seattle WA US","North Bend WA US","1"),
("Seattle WA US","Candelaria 137 PR","2"),
("Seattle WA US","La Cienega NM US","2"),
("Seattle WA US","Thousand Palms CA US","1"),
("Oakhurst CA US","Thousand Palms CA US","10")
]

当我将它打印到 csv 时，我开始使用:

ifile  = open('test.csv', "rb")
reader = csv.reader(ifile)
ofile  = open('ttest.csv', "wb")
writer = csv.writer(ofile, delimiter='  ', quotechar='"', quoting=csv.QUOTE_ALL)
writer.writerow(["departure","destination", "trips_count"])
for row in L:
    writer.writerow(list(row))

我得到:

departure       destination             trips_count
Seattle WA US   Seattle WA US           56
Seattle WA US   North Bend WA US        1
Seattle WA US   Candelaria 137 PR       2
Seattle WA US   La Cienega NM US        2
Seattle WA US   Thousand Palms CA US    1
Oakhurst CA US  Thousand Palms CA US    10

如何将其更改为这种格式？

                    Seattle WA US   North Bend WA US    Candelaria 137 PR   La Cienega NM US    Thousand Palms CA US
Seattle WA US       56              1                   2                   2                   1
Oakhurst CA US      0               0                   0                   0                   10

Answer 1

import pandas as pd

L= [ 
("Seattle WA US","Seattle WA US","56"),
("Seattle WA US","North Bend WA US","1"),
("Seattle WA US","Candelaria 137 PR","2"),
("Seattle WA US","La Cienega NM US","2"),
("Seattle WA US","Thousand Palms CA US","1"),
("Oakhurst CA US","Thousand Palms CA US","2")
]

df = pd.DataFrame(L, columns=['departure', 'destination', 'trips_count'])
df = df.pivot(index='departure', columns='destination').fillna(0)
df.to_csv('test.csv')

输出:

In [17]: df = df.pivot(index='departure', columns='destination').fillna(0)

In [18]: df
Out[18]: 
                     trips_count                                    \
destination    Candelaria 137 PR La Cienega NM US North Bend WA US   
departure                                                            
Oakhurst CA US                 0                0                0   
Seattle WA US                  2                2                1   


destination    Seattle WA US Thousand Palms CA US  
departure                                          
Oakhurst CA US             0                    2  
Seattle WA US             56                    1  

关于 pandas reshaping and pivot table 的更多信息