mongotop mongotop - 5 months ago 9
Python Question

from list of tuples to array with the 2nd row become the header

from list of tuples to array with the 2nd row become the header to print it to a csv file.

This is my data set for buss trips between cities
original list

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")
]


when I print it to csv I get using :

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))


I get:

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


How can I change it to this format?

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
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')

Output:

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  

More information about pandas reshaping and pivot table