CODEWITHSUNDEEP
pythonpandas
Peter
Peter

Reputation: 11

Extract columns from multiple data files and save them into separate columns in a new data frame

I have 12 data files in a folder, each look like this:

,Unnamed: 0,Date & time Time (s)    00 Nil  01 P1   02 P2   03 P3
0,0,9/9/2013  03:24:36.956017   0.00E+00    0.00E+00    -6.25E-01   -6.25E-01   -4.93E-03
1,1,9/9/2013  03:24:36.966017   1.00E-02    0.00E+00    -6.25E-01   -6.25E-01   -4.49E-03
2,2,9/9/2013  03:24:36.976017   2.00E-02    0.00E+00    -6.25E-01   -6.25E-01   -5.29E-03
3,3,9/9/2013  03:24:36.986017   3.00E-02    0.00E+00    -6.25E-01   -6.25E-01   -4.08E-03
4,4,9/9/2013  03:24:36.996017   4.00E-02    0.00E+00    -6.25E-01   -6.25E-01   -3.32E-03
5,5,9/9/2013  03:24:37.006017   5.00E-02    0.00E+00    -6.25E-01   -6.25E-01   -4.80E-03
6,6,9/9/2013  03:24:37.016017   6.00E-02    0.00E+00    -6.25E-01   -6.25E-01   -4.37E-03
7,7,9/9/2013  03:24:37.026017   7.00E-02    0.00E+00    -6.25E-01   -6.25E-01   -3.68E-03
8,8,9/9/2013  03:24:37.036017   8.00E-02    0.00E+00    -6.25E-01   -6.25E-01   -4.59E-03
9,9,9/9/2013  03:24:37.046017   9.00E-02    0.00E+00    -6.25E-01   -6.25E-01   -3.65E-03
10,10,9/9/2013  03:24:37.056017 1.00E-01    0.00E+00    -6.25E-01   -6.25E-01   -3.93E-03
11,11,9/9/2013  03:24:37.066017 1.10E-01    0.00E+00    -6.25E-01   -6.26E-01   -3.86E-03
12,12,9/9/2013  03:24:37.076017 1.20E-01    0.00E+00    -6.25E-01   -6.25E-01   -3.89E-03

I want to extract column 4 for example from each data file, save them all in a new data frame as separate columns, and the header of each column is the name of the file this column came from. i have found this code

    import glob
    import numpy as np
    import matplotlib.pyplot
    import pandas as pd

    import glob
    import pandas as pd

    file_list = glob.glob('*.dat')
    cols = [4] # add more columns here

    df = pd.DataFrame()

    for f in file_list:
        df = df.append(
            pd.read_csv(f, delimiter='\s+', header=None, usecols=cols),
            ignore_index=True,    
        )

    arr = df.values

However, columns from all the data files are merged into one single column in a data frame. I want them into separate columns and each column header is the name of the original file the column is extracted from. Can anyone help me in this? I am a beginner in python

Upvotes: 1

Views: 435

Answers (1)

SM Abu Taher Asif
SM Abu Taher Asif

Reputation: 2331

You can read each csv as separate dataframe and save them in list then process those dataframes :

dataFrames = []
for f in file_list:
        df = pd.read_csv(f, delimiter='\s+', header=None, usecols=cols)
        dataFrames.append(df)

Upvotes: 1

Related Questions