create csv file where each row is some nested lists from one list

Viewed 49

I have list of lists of sports matches:

table = [['Volleyball', ' Europe', 'European Championships'],
['Today 17:00', 'Moldova - Cyprus', '2.00', '1.72'],
['Handball', ' Slovenia', '1. NLB Liga'],
['Today 17:00', 'Krka - Slovenj Gradec', '2.05', '1.98'],
['American Football', ' USA', 'NCAA'],
['Today 17:00', 'Marshall - Eastern Kentucky', '1.90', '1.90'],
['Today 20:00', 'Army - Middle Tennessee St', '2.01', '1.99'],
['Tomorrow 20:00', 'West Virginia - Florida State', '2.50', '1.50'],
['Soccer', ' World', 'Club Friendly'],
['Today 17:00', 'UE Sants (Esp) - CE Europa (Esp)', '1.84', '1.88'],
['Today 17:00', 'Spain - France', '1.20', '2.80'],
['Tennis', ' USA', 'ATP US Open'],
['Today 17:30', 'Berrettini M. - Ruud C.', '1.81', '2.02']]

columns are:

sport  country  competition  date  match  odd_1  odd_2

First 3 columns : [sport, country, competition] are always in one nested list, preceded by one or many list of columns [date, match, odd_1, odd_2]

I want to create the csv but I don't know how to associate each [date match odd_1 odd_2] data with it's specific [sport country competition]

I created this code:

with open(filename.csv, 'a', encoding='utf_8_sig') as csv_file: 
    w = csv.writer(csv_file, lineterminator='\n')
    header = 
    w.writerow(header)

    for row in table:
        w.writerow(row)
3 Answers

You iterate the list, if the iterated element has 3 parts you set the "sport, country, competition" fields - if it has 4 elements you write the row with the data, prepending the last "sport, country, competition" parts:

table = [['Volleyball', ' Europe', 'European Championships'],
['Today 17:00', 'Moldova - Cyprus', '2.00', '1.72'],
['Handball', ' Slovenia', '1. NLB Liga'],
['Today 17:00', 'Krka - Slovenj Gradec', '2.05', '1.98'],
['American Football', ' USA', 'NCAA'],
['Today 17:00', 'Marshall - Eastern Kentucky', '1.90', '1.90'],
['Today 20:00', 'Army - Middle Tennessee St', '2.01', '1.99'],
['Tomorrow 20:00', 'West Virginia - Florida State', '2.50', '1.50'],
['Soccer', ' World', 'Club Friendly'],
['Today 17:00', 'UE Sants (Esp) - CE Europa (Esp)', '1.84', '1.88'],
['Today 17:00', 'Spain - France', '1.20', '2.80'],
['Tennis', ' USA', 'ATP US Open'],
['Today 17:30', 'Berrettini M. - Ruud C.', '1.81', '2.02']]

import csv

with open("file.csv", "w", newline="") as f:
    writer = csv.writer(f)
    # write header
    writer.writerow( "sport  country  competition  date  match  odd_1  odd_2".split())
    # write data
    for inner_list in table:
        if len(inner_list) == 3:
            # decompose for clarity sake, could as well just store it in some
            # other list:    remember_me = inner_list 
            sport, country, competition = inner_list
        else:
            # and do writerow( remember_me + inner_list) here
            writer.writerow([sport, country, competition] + inner_list)

with open("file.csv") as f:
    print(f.read())

Output:

sport,country,competition,date,match,odd_1,odd_2
Volleyball, Europe,European Championships,Today 17:00,Moldova - Cyprus,2.00,1.72
Handball, Slovenia,1. NLB Liga,Today 17:00,Krka - Slovenj Gradec,2.05,1.98
American Football, USA,NCAA,Today 17:00,Marshall - Eastern Kentucky,1.90,1.90
American Football, USA,NCAA,Today 20:00,Army - Middle Tennessee St,2.01,1.99
American Football, USA,NCAA,Tomorrow 20:00,West Virginia - Florida State,2.50,1.50
Soccer, World,Club Friendly,Today 17:00,UE Sants (Esp) - CE Europa (Esp),1.84,1.88
Soccer, World,Club Friendly,Today 17:00,Spain - France,1.20,2.80
Tennis, USA,ATP US Open,Today 17:30,Berrettini M. - Ruud C.,1.81,2.02

You probably should throw in some str.strip() to clean up your data...

You can collect the data based on sports. The data can be worked into a dictionary, grouping by columns. Then its a one liner to export a dictionary to a csv using pandas.

import pandas as pd

def func(table: list, columns=None, sports=None, output_name='file') -> None:
    """
    Take in a table of data from <<<where is this data coming from>>>
    :param table: a table of data from <<<>>>
    :param columns: give the column names of the data
    :param sports: provide all of the sports being searched for
    :param output_name: the name of the csv file being output
    :return: Nothing, a file is created
    """
    if sports is None:
        sports = ['Volleyball', 'Handball', 'American Football', 'Soccer', 'Tennis']  # tell the program what a sport is
    if columns is None:
        columns = ['sport', 'country', 'competition', 'date', 'match', 'odd_1', 'odd_2']  # make headers one list

    current_info = list()  # track the current sport line

    # collect the data
    data = []
    for i in range(len(table)):
        if table[i][0] in sports:
            current_info = table[i]
        else:
            data.append(current_info + table[i])

    # write data to dictionary to be passed to pd DataFrame
    dn = {columns[i]: [] for i in range(len(columns))}
    for i in range(len(data)):
        for j in range(len(data[i])):
            dn[columns[j]].append(data[i][j])

    pd.DataFrame(dn).to_csv(output_name + '.csv')  # export dataframe to csv

    return None

Call this by typing

func(table)

You can parse your nested list of lists by using groupby from itertools:

import itertools as it

# Python 3.7+ only because of ordered dict...
di={}
for k,v in it.groupby(table, key=lambda li: len(li)==3):
    if k:
        di[tuple(list(v)[0])]=[]
    else:
        di[list(di.keys())[-1]].extend(v)   

Or, you can do:

di={}
for k,v in it.groupby(table, key=lambda li: len(li)==3):
    if k:
        last_key=tuple(list(v)[0])
        di[last_key]=[]
    else:
        di[last_key].extend(v)

Then iterate the dict:

# probably use csv but as an example...
for k, v in di.items():
    for match in v:
        print(','.join(k), ','.join(match)) 

Prints:

Volleyball, Europe,European Championships Today 17:00,Moldova - Cyprus,2.00,1.72
Handball, Slovenia,1. NLB Liga Today 17:00,Krka - Slovenj Gradec,2.05,1.98
American Football, USA,NCAA Today 17:00,Marshall - Eastern Kentucky,1.90,1.90
American Football, USA,NCAA Today 20:00,Army - Middle Tennessee St,2.01,1.99
American Football, USA,NCAA Tomorrow 20:00,West Virginia - Florida State,2.50,1.50
Soccer, World,Club Friendly Today 17:00,UE Sants (Esp) - CE Europa (Esp),1.84,1.88
Soccer, World,Club Friendly Today 17:00,Spain - France,1.20,2.80
Tennis, USA,ATP US Open Today 17:30,Berrettini M. - Ruud C.,1.81,2.02
Related