merging multiple CSV files in one with same header but different csv files name with python

Question

I'm new in python ...I have tried to apply this code to merge multiple csv files but it doesn't work..basically, I have a files which contains stock prices with header: date,open,High,low,Close,Adj Close Volume... . but each csv file has a different name: Apl.csv,VIX.csv,FCHI.csv etc.. I would like to merge all these csv files in One.. but I would like to add a new columns which will disclose the name of the csv files example:

stock_id,date,open,High,low,Close,Adj Close Volume with stock_id = apl,Vix etc.. I used this code but I got stuck in line 4 here is the code:

  files = os.listdir() 
  file_list = list() 
  for file in os.listdir():
      if file.endswith(".csv")
      df=pd.read_csv(file,sep=";")
      df['filename'] = file
      file_list.append(df) 
  all_days = pd.concat(file_list, axis=0, ignore_index=True) 
  all_days.to_csv("all.csv")

Someone could help me to sort out this ?

sjc · Accepted Answer · 2019-12-10 19:00:19Z

0

In Python, the indentation level matters, and you need a colon at the end of an if statement. I can't speak to the method you're trying, but you can clean up the synax with this:

files = os.listdir() 
file_list = list() 
for file in os.listdir():
    if file.endswith(".csv"):
        df=pd.read_csv(file,sep=";")
        df['filename'] = file
        file_list.append(df) 
all_days = pd.concat(file_list, axis=0, ignore_index=True) 
all_days.to_csv("all.csv")

answered Dec 10, 2019 at 19:00

sjc

1,1373 gold badges20 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

10 Comments

KIKI99 Over a year ago

Hi Thank you sjc..but I don't think the error is coming from indentation..I got an error on this line if file.endswith(".csv"):, I'm not sure that the method is suitable for what I would klike to do

KIKI99 Over a year ago

My main issue is this: how to create a columns in the single files and put the name of each csv files, date time series as well

sjc Over a year ago

Right, in the code you posted you were missing the colon at the end of the if statement. It's there in the snippet I posted.

KIKI99 Over a year ago

Hi sjc , I used your code but still got an error...apparently the code is not able to pick up each csv files name and store it in column..any ideas please?

KIKI99 Over a year ago

Hi sjc is not aligned. A future version of pandas will change to not sort by default. To accept the future behavior, pass 'sort=False'. To retain the current behavior and silence the warning, pass 'sort=True'. # Remove the CWD from sys.path while we load stuff.

|

Mickael B. · Accepted Answer · 2020-01-18 16:07:02Z

I'm relatively new in python ..here is what I'd like to do..I got a folder with multiples csv files ( 2018.csv,2017.csv,2016.csv etc..)500 csv files to be precise.. each csv contains header "date","Code","Cur",Price etc..I'd like to concatenate all 500 csv files in one datafame...here is my code for one csv files but it's very slow , I want to do it for all 500 files and concantanate in one dataframe :

 DB_2017 = pd.read_csv("C:/folder/2018.dat",sep=",", header =None).iloc[: 0,4,5,6]

 DB_2017.columns =["date","Code","Cur",Price]

 DB_2017['Code'] =DB_2017['Code'].map(lambdax:x.lstrip('@').rstrip('@'))

 DB_2017['Cur'] =DB_2017['Cur'].map(lambdax:x.lstrip('@').rstrip('@'))

 DB_2017['date'] =DB_2017['date'].apply(lambdax:pd.timestamp(str(x)[:10)

 DB_2017['Price'] =pd.to_numeric(DB_2017.Price.replace(',',';')

Collectives™ on Stack Overflow

merging multiple CSV files in one with same header but different csv files name with python

2 Answers 2

10 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

10 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related