CODEWITHSUNDEEP
pythonpandas
m-ketan
m-ketan

Reputation: 1308

Adding a column to dataframe while reading csv files [pandas]

I'm reading multiple csv files and combining them into a single dataframe like below:

pd.concat([pd.read_csv(f, encoding='latin-1') for f in glob.glob('*.csv')],
         ignore_index=False, sort=False)

Problem:

I want to add a column that doesn't exist in any csv (to the dataframe) based on the csv file name for every csv file that is getting concatenated to the dataframe. Any help will be appreciated.

Upvotes: 4

Views: 3623

Answers (2)

Ramin Melikov
Ramin Melikov

Reputation: 1005

I have multiple csv files in my local directory. Each filename contains some numbers. Some of those numbers identify years for which the file is. I need to add a column year to each file that I'm concatenating and while I do I want to get the year information from the filename and insert it into that column. I'm using regex to extract the year and concatenate it like 20 + 11 = 2011. Then, I'm setting the column's data type to int32.

pd.concat(
    [
        pd.read_csv(f)
            .assign(year = '20' + re.search('[a-z]+(?P<year>[0-9]{2})', f).group('year'))
            .astype({'year' : 'int32'})
        for f in glob.glob('stateoutflow*[0-9].csv')
    ],
    ignore_index = True
)

Upvotes: 1

pieca
pieca

Reputation: 2563

glob.glob returns normal string so you can just add a column to every individual dataframe in a loop.

Assuming you have files df1.csv and df2.csv in your directory:

import glob
import pandas as pd

files = glob.glob('df*csv')
dfs = []
for file in files:
    df = pd.read_csv(file)
    df['filename'] = file
    dfs.append(df)
df = pd.concat(dfs, ignore_index=True)
df

    a   b   filename
0   1   2   df1.csv
1   3   4   df1.csv
2   5   6   df2.csv
3   7   8   df2.csv

Upvotes: 6

Related Questions