Reading data from multiple CSV files and adding each file to a new column

Question

I have the following code from multiple CSV files:

import pandas as p
import csv 
csv_list=['interview1.csv', 'interview2.csv', ...]
for itw in csv_list:
    df = p.read_csv(itw)
    df.to_csv('out.csv', mode='a')

Right now it adds everything to the new CSV file in one long column. How would I move to a new column after each CSV file is read in order to have multiple columns in the one output file? Essentially I would like each input file to be one column in a single output file.

All csv files have multiple columns, but I can add usecols = [6] for them to get the column I need. All CSVs have the same number of lines.

Trenton McKinney · Accepted Answer · 2023-11-14 18:48:18Z

Create a list of dataframes, and then concat them on axis=1
Use a list comprehension to create the list of dataframes
- The entire thing can be combined into one line, but multiple lines have been used for clarity.
  - df = pd.concat([pd.read_csv(itw) for itw in csv_list], axis=1)
Rows will be filled with NaN, for columns of shorter lengths.
Also see Import multiple CSV files into pandas and concatenate into one DataFrame and How to create a dict of dataframes from multiple csv files for more options.

import pandas as pd

# list of all files
csv_list=['interview1.csv', 'interview2.csv']

# create list of dataframes
df_list = [pd.read_csv(itw) for itw in csv_list]
    
# combine all the dataframes
df = pd.concat(df_list, axis=1)

# save
df.to_csv('combined_files.csv', index=False)

# display(df)
  col  col  col2
0   1    1  33.0
1   2   23  44.0
2   2   24  55.0
3   3   3t  66.0
4   3    3  77.0
5   4   45  88.0
6   5  NaN   NaN
7   5  NaN   NaN
8   6  NaN   NaN

list comprehension as a for loop

df_list = list()
for itw in csv_list:
    df_list.append(pd.read_csv(itw))

Sample CSV files

`interview1.csv`

`interview2.csv`

col,col2
1,33
23,44
24,55
3t,66
3,77
45,88

rodrigomd · Accepted Answer · 2020-08-11 20:07:01Z

0

If the datasets have a common column (in this example called 'ID'), you can use pd.merge():

import pandas as pd
import csv 
csv_list=['interview2.csv', 'interview3.csv', ...]
merged = pd.read_csv('interview1.csv')
for itw in csv_list:
    df = pd.read_csv(itw)
    merged = pd.merge(left=merged, right=df, on='ID', how='outer')
merged.to_csv('out.csv')

answered Aug 11, 2020 at 20:07

rodrigomd

674 bronze badges

Collectives™ on Stack Overflow

Reading data from multiple CSV files and adding each file to a new column

2 Answers 2

list comprehension as a for loop

Sample CSV files

`interview1.csv`

`interview2.csv`

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

list comprehension as a for loop

Sample CSV files

interview1.csv

interview2.csv

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related

`interview1.csv`

`interview2.csv`