Combining .csv Files in Python - Merged File Data Error - Jupyter Lab

Question

I am trying to merge a large number of .csv files. They all have the same table format, with 60 columns each. My merged table results in the data coming out fine, except the first row consists of 640 columns instead of 60 columns. The remainder of the merged .csv consists of the desired 60 column format. Unsure where in the merge process it went wrong.

The first item in the problematic row is the first item in 20140308.export.CSV while the second (starting in column 61) is the first item in 20140313.export.CSV. The first .csv file is 20140301.export.CSV the last is 20140331.export.CSV (YYYYMMDD.export.csv), for a total of 31 .csv files. This means that the problematic row consists of the first item from different .csv files.

The Data comes from http://data.gdeltproject.org/events/index.html. In particular the dates of March 01 - March 31, 2014. Inspecting the download of each individual .csv file shows that each file is formatted the same way, with tab delimiters and comma separated values.

The code I used is below. If there is anything else I can post, please let me know. All of this was run through Jupyter Lab through Google Cloud Platform. Thanks for the help.

import glob
import pandas as pd

file_extension = '.export.CSV'
all_filenames = [i for i in glob.glob(f"*{file_extension}")]
combined_csv_data = pd.concat([pd.read_csv(f, delimiter='\t', encoding='UTF-8', low_memory= False) for f in all_filenames])
combined_csv_data.to_csv('2014DataCombinedMarch.csv')

I used the following bash code to download the data:

!curl -LO http://data.gdeltproject.org/events/[20140301-20140331].export.CSV.zip

I used the following code to unzip the data:

!unzip -a "********".export.CSV.zip

I used the following code to transfer to my storage bucket:

!gsutil cp 2014DataCombinedMarch.csv gs://ddeltdatabucket/2014DataCombinedMarch.csv

Nick ODell · Accepted Answer · 2020-10-19 06:39:02Z

Looks like these CSV files have no header on them, so Pandas is trying to use the first row in the file as a header. Then, when Pandas tries to concat() the dataframes together, it's trying to match the column names which it has inferred for each file.

I figured out how to suppress that behavior:

import glob
import pandas as pd


def read_file(f):
    names = [f"col_{i}" for i in range(58)]
    return pd.read_csv(f, delimiter='\t', encoding='UTF-8', low_memory=False, names=names)


file_extension = '.export.CSV'
all_filenames = [i for i in glob.glob(f"*{file_extension}")]

combined_csv_data = pd.concat([read_file(f) for f in all_filenames])
combined_csv_data.to_csv('2014DataCombinedMarch.csv')

You can supply your own column names to Pandas through the names parameter. Here, I'm just supplying col_0, col_1, col_2, etc for the names, because I don't know what they should be. If you know what those columns should be, you should change that names = line.

I tested this script, but only with 2 data files as input, not all 31.

PS: Have you considered using Google BigQuery to get the data? I've worked with GDELT before through that interface and it's way easier.

Collectives™ on Stack Overflow

Combining .csv Files in Python - Merged File Data Error - Jupyter Lab

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related