0

I am trying to take the first row from multiple text files and add them to a new row in a CSV file, separated by columns. Currently, I am struggling with:

  1. Not overwriting previous data on the CSV file
  2. Skipping the header
  3. Excluding commas in strings like Went From: 123 Address City, State

Currently, I have multiple text files each with information like date, time, etc. I wrote a bash script that takes each text files and pastes them into a CSV file:

paste -d "," col_Date col_Current_Time col_Weather col_From col_To col_Time_Elapsed > Database.csv

and I get:

2022-02-24,07:47:24,Went from: 2678, 20th Street, Ingleside Village, Tuscaloosa, Alabama 35401 United States, to: 2678 20th Street, Tuscaloosa, Alabama, 35401, United States, 1 s

and I want to get:

Date, Time, From, To, Time Elapsed
2022-02-24,07:47:24,Went from: 2678 20th Street Ingleside Village Tuscaloosa Alabama 35401 United States, to: 2678 20th Street Tuscaloosa Alabama 35401 United States, 1 s

Either I need to remove the commas in the string before, or somehow tell the CSV file to omit these specific commas in the From and To string. I write to each of the text files using this Python code:

with open(col_Date, 'w') as f3:
    print(today, file=f3)
f3.close()

This is my second week of coding, so there is no such thing as over explaining to me. Let me know if you need anything else! Thank you!

Nice18
  • 476
  • 2
  • 12
bennett suttles
  • 1
  • 2
  • 1
    Welcome. Will you please edit your question and include a few, short textual samples of the input text files, and what the single CSV output should be? And since you’ve already started coding, please include your code as well. All that is about the minimum we need to start helping you. – Zach Young Feb 27 '22 at 06:12
  • 1. Don't use `w` in a loop if you want to keep the file's contents over iterations. Opening a file in `w` mode erases the file before writing to it. [See here](/q/1466000/843953). 2. Don't reinvent the wheel. The standard library contains a module to handle csv operations. Use it. [See here](https://docs.python.org/3/library/csv.html) – Pranav Hosangadi Feb 28 '22 at 18:33
  • If your input file is malformed and that's what causes your csv reading to break, then try to fix the input file. In CSVs, you can escape commas by enclosing the entire field in quotes. Can you modify your bash script to add quotes around each field? – Pranav Hosangadi Feb 28 '22 at 18:37
  • Why do you do the pre-processing in a shell script? It would be simpler to read all your files in a _python_ script, since you already have separate files. Then, build a single data structure containing information from all the files, and write to CSV using python's builtin module instead of using a shell script that possibly mangles the csv file – Pranav Hosangadi Feb 28 '22 at 18:54
  • I’m still learning csv module in python and I felt comfortable using a bash script as a proof of concept. How would you read and write into a single data structure and then write to a csv? – bennett suttles Feb 28 '22 at 19:10

1 Answers1

-2

To work on csv files, I would recommend to use pandas dataframe.

To install pandas

pip install pandas

In the below Code snippet, we convert our text data into a dictionary containing the field as key & your data as the label.

After that, the already made csv file to which this data is to appended is read by pandas and converted to a pandas dataframe, and the new data is appended to it and flushed to the same/ another csv file.

import pandas
import os.path


# This is the new row to add to the already made csv file
# Convert your text data in a dictionary
# that will depend how your columns are separated in the txt file
df = pd.DataFrame.from_dict(data)

# Read csv to append to
csv_file_path = "data.csv"
if os.path.exists(csv_file_path):
    df = pd.concat([pd.read_csv(csv_file_path), df])
    # Save 
    df.to_csv(csv_file_path, header=True, index=False, columns=labels)

It will be of better if you could include a sample text file, to understand the problem statement better.

Caliber X
  • 1
  • 2
  • If the question doesn't have enough information to answer it, _don't answer it!_. Please see [answer]. Instead, leave a comment asking for more information. Since you do not yet have the [reputation](/help/privileges/comment) to leave a comment, contribute by asking or answering well-asked questions to earn the required reputation. – Pranav Hosangadi Feb 28 '22 at 18:52
  • Yeah I tried using pandas but just didn’t have enough python basics to learn everything – bennett suttles Feb 28 '22 at 19:12