0

I have a script which generates CSV files and names them as per time stamp

-rw-rw-r-- 1 9949 Oct 13 11:57 2018-10-13-11:57:10.796516.csv
-rw-rw-r-- 1 9649 Oct 13 12:58 2018-10-13-12:58:12.907835.csv
-rw-rw-r-- 1 9649 Oct 13 13:58 2018-10-13-13:58:10.502635.csv

I need to pick column C from these sheets and write to a new CSV file. However order of columns in new sheet should be as per the name of existing sheets. Example, Column C from the file generated at 11:57 should be in column A , from 12:58 in Column B and 13:38 in Column C of new sheet.

EDIT -- Code tried based on Bilal Input. It does move the C column from all existing sheets to a new sheet, however not in proper order. It just picks them randomly and keeps adding in columns on new file.

import os
import re
import pandas as pd

newCSV = pd.DataFrame.from_dict({})

# get a list of your csv files and put them files

files = [f for f in os.listdir('.') if os.path.isfile(f)]

results = []
for f in files:
    if re.search('.csv', f):
        results += [f]

for file in results:
    df = pd.read_csv(file,usecols=[2])

    newCSV = pd.concat((newCSV, df), axis=1)

newCSV.to_csv("new.csv")

EDIT -- Final Code that worked, Thanks Bilal

import os
import re
import pandas as pd



newCSV = pd.DataFrame.from_dict({})

files = [f for f in os.listdir('.') if os.path.isfile(f)]

# get a list of your csv files and put them files
results = []
for f in files:
        if re.search('.csv', f):
                results += [f]
result1=sorted(results)
for file in result1:
    df = pd.read_csv(file,usecols=[2])

    newCSV = pd.concat((newCSV, df), axis=1)

newCSV.to_csv("new.csv")
Zoro99
  • 185
  • 3
  • 13

1 Answers1

0
import pandas as pd

newCSV = pd.DataFrame.from_dict({})

# get a list of your csv files and put them files

for f in files:
    df = pd.read_csv(f)

    newCSV = pd.concat((newCSV, df.colum_name), axis=1)

newCSV.to_csv("new.csv")

See if that works for you.

If you don't know how to find all the files with a specific extension, look here.

Bilal Saleem
  • 633
  • 4
  • 8
  • Thanks for your reply, however this code is just putting the columns from existing sheets in new sheet but they are not in order of their file name which is a time stamp. Code I used based on your example, I have put as an Edit in my post – Zoro99 Oct 14 '18 at 15:58
  • Got it sir, added "sorted(results)" and that fixed it ... Thanks – Zoro99 Oct 14 '18 at 16:06