pythonpandascsvreaderwriter

python CSV writer, pandas , How to write the csv file in two column using the same text file has only one coulmn


suppose the text file contain these elements as follows:

A
B
C
D 

I want to write these elements in the CSV file using python as follows:

A,A
A,B
A,C
A,D
B,A
B,B
B,C
B,D
...

I try to write the text in csv with multiplicated data like this:

import csv

with open(r'C:\Users\soso-\Desktop\SVM\DataSet\drug_list.txt') as f:
    with open('integrated_x.csv', 'w') as out_file:
        for line in f:
            for x in range(548):
                out_file.write(f"{line.strip()}\n")
f.close()
out_file.close()

output:

A
A
A
A
B
B
B
....

Then add the new column for the csv file in the other csv file like this:

with open(r'C:\Users\soso-\Desktop\SVM\DataSet\drug_list.txt') as f:
    with open(r'integrated_x.csv') as read_obj, \
            open('integrated_x12.csv', 'w', newline='') as write_obj:
    # Create a csv.reader object from the input file object
                csv_reader = csv.reader(read_obj)
    # Create a csv.writer object from the output file object
                csv_writer = csv.writer(write_obj)
        
    # Read each row of the input csv file as list
                for row in csv_reader:
                    for line in f:
        # Append the default text in the row / list
                        row.append(line)
        # Add the updated row / list to the output file
                        csv_writer.writerow(row)

output:

A,"A"
A,"A,B"
A,"A,B,C"

So the output not as expected it append the column as list and replicate the previous row. How can I fix it?


Solution

  • Use itertools.permutations from the standard library:

    import itertools
    list(itertools.permutations(['A', 'B', 'C' ,'D'], 2))
    
    
    [('A', 'B'), ('A', 'C'), ('A', 'D'), ('B', 'A'), ('B', 'C'), ('B', 'D'), ('C', 'A'), ('C', 'B'), ('C', 'D'), ('D', 'A'), ('D', 'B'), ('D', 'C')]
    

    If you want to save it as a csv file, then by pandas:

    >>> import pandas as pd
    >>> import itertools
    >>> a = list(itertools.permutations(['A', 'B', 'C' ,'D'], 2))
    >>> df = pd.DataFrame(list(itertools.permutations(['A', 'B', 'C' ,'D'], 2)))
    >>> df
        0  1
    0   A  B
    1   A  C
    2   A  D
    3   B  A
    4   B  C
    5   B  D
    6   C  A
    7   C  B
    8   C  D
    9   D  A
    10  D  B
    11  D  C
    >>> df.to_csv('my_file.csv')
    

    This is easiest way to create this permutation