Search data in a list and export them into a .csv file

Question

I already finished another part of the code and get two sets of numbers:

set1(postcode and rents in Western Australia):

[['6004', '240'], ['6004', '350'], ['6004', '350'], ['6004', '315'], ['6004', '490'], ['6004', '280'], ['6004', '275'], ['6004', '240'], ['6050', '260'], ['6050', '330'], ['6050', '220'], ['6050', '250'], ['6000', '390'], ['6000', '220'], ['6000', '400'], ['6000', '250'], ['6000', '320'], ['6000', '390'], ['6000', '220'], ['6000', '400'], ['6004', '395'], ['6004', '230'], ['6004', '350'], ['6004', '300'], ['6004', '300'], ['6004', '340'], ['6000', '420'], ['6000', '190'], ['6000', '300'], ['6000', '380'], ['6000', '270'], ['6000', '380'], ['6000', '350'], ['6000', '380'], ['6004', '360'], ['6004', '450'], ['6004', '200'], ['6004', '250'], ['6004', '350']]

set2(postcodes in set1):

['6004', '6050', '6000']

What I need to do now is to put them in a .csv file like this:

Postcodes are in the frist row (like an index). The rest of the rows are rents in these postcode(example: in postcode 6004, there are 3 houses, the rents are 240,350,350)

What kind of method should I use to get what I want?

I tried dictionary but it says just 2 elements are required.

I believe https://stackoverflow.com/questions/3199171/append-multiple-values-for-one-key-in-python-dictionary answers your question... You build a dictionary with a list of related values... Then you have to work out how to write that accordingly as CSV (possibly using `csv.DictWriter`) — Jon Clements, Aug 19 '18 at 13:31
Thanks ^_^ maybe I use the dictionary in the wrong way... I just use code like `Main=tuple(mainText)` `di=dict(Main)` — Yiling Liu, Aug 19 '18 at 13:35

score 1 · Accepted Answer · answered Aug 19 '18 at 13:37

Given :

postcodes_rent=[['6004', '240'], ['6004', '350'],.......]
postcodes=['6004', '6050', '6000']

Create a dictionary :

postcodes_rent_dict={p:[pr[1] for pr in postcodes_rent if pr[0]==p] for p in postcodes}
{'6004': ['240', '350', '350'.....], 
 '6050': ['260', '330', '220'.....], 
 '6000': ['390', '220', '400'.....]}

Convert it to a dataframe (with postcodes as column names) :

import pandas as pd
df=pd.DataFrame.from_dict(postcodes_rent_dict,orient='index').transpose()

Write it to a csv file

df.to_csv("test.csv")

score 0 · Answer 2 · answered Aug 19 '18 at 14:13

A method using only builtins and transposing using zip_longest:

import csv
from itertools import zip_longest

data = [['6004', '240'], ['6004', '350'], ['6004', '350'], ['6004', '315'], ['6004', '490'], ['6004', '280'], ['6004', '275'], ['6004', '240'], ['6050', '260'], ['6050', '330'], ['6050', '220'], ['6050', '250'], ['6000', '390'], ['6000', '220'], ['6000', '400'], ['6000', '250'], ['6000', '320'], ['6000', '390'], ['6000', '220'], ['6000', '400'], ['6004', '395'], ['6004', '230'], ['6004', '350'], ['6004', '300'], ['6004', '300'], ['6004', '340'], ['6000', '420'], ['6000', '190'], ['6000', '300'], ['6000', '380'], ['6000', '270'], ['6000', '380'], ['6000', '350'], ['6000', '380'], ['6004', '360'], ['6004', '450'], ['6004', '200'], ['6004', '250'], ['6004', '350']]
postcodes = ['6004', '6050', '6000']
# convert to a set for O(1) lookup
pcs = set(postcodes)

# Accumulate all rent costs per postcode
pc_rents = {}
for pc, rent in data:
    # ignore unwanted postcodes...
    if pc not in postcodes:
        continue
    pc_rents.setdefault(pc, []).append(rent)

# Write transposed rows...
with open('output.csv', 'w') as fout:
    csvout = csv.DictWriter(fout, fieldnames=postcodes)
    csvout.writeheader()
    csvout.writerows(
        # build dictionary with field names expected
        dict(zip(pc_rents, row)) 
        # transposed rows...
        for row in zip_longest(*pc_rents.values(), fillvalue='')
    )

Gives you a CSV file of:

6004,6050,6000
240,260,390
350,330,220
350,220,400
315,250,250
490,,320
280,,390
275,,220
240,,400
395,,420
230,,190
350,,300
300,,380
300,,270
340,,380
360,,350
450,,380
200,,
250,,
350,,

Search data in a list and export them into a .csv file

2 Answers2