3
  • I have a folder with about 100 CSVs (Downloads/challenges).
  • Each CSV has the same 50+ columns.
  • Each CSV is titled something like azerbaijan_challenge_entrants.csv.

I want to create one new CSV (all_entrants.csv) that includes all data from all 100 CSVs, adding one new column: challenge, which should include the name of the CSV that the row data came from.

I generally like Python for tasks like this. But I am struggling to make this work. Any help would be appreciated!

jpp
  • 159,742
  • 34
  • 281
  • 339
samthebrand
  • 3,020
  • 7
  • 41
  • 56
  • I had to do this the other day and a simple search would have yielded this duplicate question - https://stackoverflow.com/a/42757992/1313067 – Hatt Apr 27 '18 at 16:42
  • I looked at a couple [similar](https://stackoverflow.com/q/2909975/896802) [questions](https://stackoverflow.com/q/2512386/896802) prior to writing this one, but did not find my answer or the specific question you refer to @Hatt. – samthebrand Apr 27 '18 at 16:50

1 Answers1

5

This is possible with os from the standard library and 3rd party library pandas:

import os
import pandas as pd

mypath = os.path.join('Downloads', 'challenges')

# get list of files
files = [f for f in os.listdir(mypath) if os.path.isfile(os.path.join(mypath, f))]

# build list of dataframes, adding "challenge" column
dfs = [pd.read_csv(os.path.join(mypath, f)).assign(challenge=f) for f in files]

# concatenate dataframes into one
df = pd.concat(dfs, ignore_index=True)

# write to csv
df.to_csv('all_entrants.csv')
jpp
  • 159,742
  • 34
  • 281
  • 339