CODEWITHSUNDEEP
python
bbsmfb
bbsmfb

Reputation: 79

How do I create one .xlsx file that merges three .txt files into their own columns?

Here are the .txt files where the colon represents the beginning of the file:

City.txt:
New York
Chicago
Sacramento

County.txt:
New York County
Chicago County
Sacramento County

State.txt:
New York
Illinois
California

Here is my code so far:

columnCity1 = open('City.txt', 'r')
columnCounty2 = open('County.txt', 'r')
columnState3 = open('State.txt', 'r')

This imports the files and creates them as a list (to my understanding)

The desired outcome would be:

enter image description here

Upvotes: 1

Views: 52

Answers (2)

Zach Young
Zach Young

Reputation: 11223

Here's how you can do it without Pandas, and without reading whole files into memory (if that's an issue):

#!/usr/bin/env python3
import csv

f_city = open('City.txt', 'r')
f_county = open('County.txt', 'r')
f_state = open('State.txt', 'r')

with open('out.csv', 'w', newline='') as f_out:
    writer = csv.writer(f_out)
    writer.writerow(['City', 'County', 'State'])

    # Presuming that all three files have the same number of lines...
    # Iterate the lines of any file to drive the iterations (`next()`) of the others
    for city in f_city:
        county = next(f_county)
        state = next(f_state)

        writer.writerow([city.strip(), county.strip(), state.strip()])

Upvotes: 1

dmitryro
dmitryro

Reputation: 3516

Under assumption that csv file can be used for Excel and you can reformat it if needed or just open, here's a Pandas solution (you'll need Pandas installed):

import pandas as pd


def read_files():
    # Read all lines, get rid of empty entries and next line characters
    with open('./city.txt', 'r') as f:
        cities = [c for c in  f.read().splitlines()) if len(c.rstrip())]

    with open('./county.txt', 'r') as f:
        counties = [c for c in  f.read().splitlines() if len(c.rstrip())]

    with open('./state.txt', 'r') as f:
        states = [s for s in  f.read().splitlines() if len(s.rstrip())]


    # Create dataframe
    df = pd.DataFrame(columns=['City', 'County', 'State'])

    # Go over cities, countries and states - place in DataFrame
    for group in zip(cities, counties, states):
        df.loc[len(df)] = group

    # Save to csv file - reformat to excel if different format needed.
    filename = 'results.csv'
    df.to_csv(filename)

#Call the above function - check the resulting file for output.
read_files()

Upvotes: 2

Related Questions