How to feed multiple files to pandas to filter data and concatenate all the results

Question

I have written a code to perform some data cleaning to get the final columns and values from a tab spaced file.

import matplotlib.image as image
import numpy as np
import tkinter as tk
import matplotlib.ticker as ticker
from tkinter import filedialog
import matplotlib.pyplot as plt
root = tk.Tk()
root.withdraw()
root.call('wm', 'attributes', '.', '-topmost', True)
files1 = filedialog.askopenfilename(multiple=True) 
files = root.tk.splitlist(files1)

List = list(files) 

%gui tk
for i,file in enumerate(List,1):
    d = pd.read_csv(file,sep=None,engine='python')
    h = d.drop(d.index[19:])
    transpose = h.T
    header =transpose.iloc[0]
    df = transpose[1:]
    df.columns =header
    df.columns = df.columns.str.strip()
    all_columns = list(df)
    df[all_columns] = df[all_columns].astype(str)
    k =df.drop(columns =['Op:','Comment:','Mod Type:', 'PN', 'Irradiance:','Irr Correct:', 'Lamp Voltage:','Corrected To:', 'MCCC:', 'Rseries:', 'Rshunt:'], axis=1)     
k.head()

I want to run this code to multiple files and do the same and concatenate all the results to one data frame.

for eg, If I select 20 files, then new data frame with one line of header and all the 20 results below with increasing order of the value from the column['Module Temp:'].

It would be great if someone could provide a solution to this problem

Please find the link to sample data:https://drive.google.com/drive/folders/1sL2-CwCGeGm0-fvcpzMVzgFnYzN3wzVb?usp=sharing

score 2 · Answer 1 · answered Nov 16 '20 at 22:14

There are various ways to do it. You can append one dataframe to another (basically stack one on top of the other), and you can do it in the loop. Here is an example. I use fake dfs but you will use your own

import pandas as pd
import numpy as np

combined  = None
for _ in range(5):

    # stub df creation -- you will use your real code here
    df = pd.DataFrame(columns = ['Module Temp','A', 'B'], data = np.random.random((5,3)))
    
    if combined is None:
        # initialize with the first one
        combined = df.copy()
    else:
        # add the next one
        combined = combined.append(df, sort = False, ignore_index = True)

combined.sort_values('Module Temp', inplace = True)

Here combined will have all the dfs, sorted by 'Module Temp'

Trenton McKinney · Accepted Answer · 2020-11-17T00:53:26.530

The following code shows how to parse the files and extract the data. It doesn't show the tkinter GUI component. files will represent your selected files.
Assumptions:
- The first 92 rows of the files are always the measurement parameters
- Rows from 93 are the measurements.
- The 'Module Temp' for each file is different
The lists will be sorted based on the sort order of mod_temp, so the data will be in order in the DataFrame.
- The list sorting uses the accepted answer to Sorting list based on values from another list?

import pandas as p
from patlib import Path

# set path to files
path_ = Path('e:/PythonProjects/stack_overflow/data/so_data/2020-11-16')

# select the correct files
files = path_.glob('*.ivc')

# create lists for metrics
measurement_params = list()
mod_temp = list()
measurements = list()

# iterate through the files
for f in files:
    
    # get the first 92 rows with the measurement parameters
    mp = pd.read_csv(f, sep='\t', nrows=91, index_col=0)
    
    # remove the whitespace and : from the end of the index names
    mp.index = mp.index.str.replace(':', '').str.strip().str.replace('\\s+', '_')
    
    # get the column header
    col = mp.columns[0]
    
    # get the module temp
    mt = mp.loc['Module_Temp', col]
    
    # add Modult_Temp to mod_temp
    mod_temp.append(float(mt))
    
    # get the measurements
    m = pd.read_csv(f, sep='\t', skiprows=92, nrows=3512)
    
    # remove the whitespace and : from the end of the column names
    m.columns = m.columns.str.replace(':', '').str.strip()

    # add Module_Temp column
    m['mod_temp'] = mt
    
    # store the measure parameters
    measurement_params.append(mp.T)
    
    # store the measurements
    measurements.append(m)
    
# sort lists based on mod_temp sort order
measurement_params = [x for _, x in sorted(zip(mod_temp, measurement_params))]
measurements = [x for _, x in sorted(zip(mod_temp, measurements))]

# create a dataframe for the measurement parameters
df_mp = pd.concat(measurement_params)

# create a dataframe for the measurements
df_m = pd.concat(measurements).reset_index(drop=True)

`df_mp`

Title:             Comment     Op               ID     Mod_Type   PN        Date      Time Irradiance IrrCorr Irr_Correct Lamp_Voltage Module_Temp Corrected_To    MCCC      Voc      Isc  Rseries   Rshunt     Pmax      Vpm      Ipm Fill_Factor Active_Eff Aperture_Eff Segment_Area Segs_in_Ser Segs_in_Par Panel_Area Vload Ivld Pvld Frequency SweepDelay SweepLength SweepSlope SweepDir MCCC2  MCCC3  MCCC4    LampI     IntV   IntV2 IntV3 IntV4 LoadV PulseWidth1 PulseWidth2 PulseWidth3 PulseWidth4    TRef1 TRef2 TRef3 TRef4 MCMode Irradiance2 IrrCorr2 Voc2 Isc2 Pmax2 Vpm2 Ipm2 Fill_Factor2 Active_Eff2 ApertureEff2 LoadV2 PulseWidth12 PulseWidth22 Irradiance3 IrrCorr3 Voc3 Isc3 Pmax3 Vpm3 Ipm3 Fill_Factor3 Active_Eff3 ApertureEff3 LoadV3 PulseWidth13 PulseWidth23                RefCellID RefCellTemp RefCellIrrMM RefCelIscRaw RefCellIsc VTempCoeff ITempCoeff PTempCoeff MismatchCorr  Serial_No Soft_Ver
Nease 345W N345M72     STC  Admin    MCIND2021-058  ModuleType1  NaN  10-09-2020  19:12:52    100.007     100    Ref Cell         2400     25.2787           25  1.3669  46.4379  9.13215  0.43411  294.467  331.924  38.3403  8.65732     0.78269    1.89434       1.7106       243.36          72           1      19404     0    0    0    218000         10         100      0.025        0     1  1.155  1.155  20.4736  6.87023  6.8645     6     6  6.76     107.683     109.977           0           0  27.2224     0     0     0  False     -1.#INF       70    0    0     0    0    0            0           0            0      5      107.683      109.977     -1.#INF       40    0    0     0    0    0            0           0            0      5      107.683      109.977  WPVS mono C-Si Ref Cell     25.9834      1001.86      0.15142    0.15135      -0.31       0.05       -0.4       0.9985  S91-00052    5.5.1
Solarium SGE24P330     STC  Admin  MCIND_2021_0074  ModuleType1  NaN  17-09-2020  15:06:12    99.3671     100    Ref Cell         2400     25.3380           25  1.3669  45.2903  8.87987  0.48667  216.763  311.031  36.9665  8.41388     0.77338    1.77510      1.60292       243.36          72           1      19404     0    0    0    218000         10         100      0.025        0     1  1.155  1.155   20.405  6.82362  6.8212     6     6   6.6     107.660     109.977           0           0  25.9418     0     0     0  False     -1.#INF       70    0    0     0    0    0            0           0            0  4.943      107.660      109.977     -1.#INF       40    0    0     0    0    0            0           0            0  4.943      107.660      109.977  WPVS mono C-Si Ref Cell     25.3315      998.370      0.15085    0.15082      -0.31       0.05       -0.4       0.9985  S91-00052    5.5.1
Nease 345W N345M72     STC  Admin    MCIND2021-058  ModuleType1  NaN  10-09-2020  19:11:32    100.010     100    Ref Cell         2400     25.3557           25  1.3669  46.4381  9.11368  0.41608  299.758  331.418  38.3876  8.63345     0.78308    1.89144      1.70798       243.36          72           1      19404     0    0    0    218000         10         100      0.025        0     1  1.155  1.155  20.3820  6.87018  6.8645     6     6  6.76     107.683     109.977           0           0  27.2535     0     0     0  False     -1.#INF       70    0    0     0    0    0            0           0            0      5      107.683      109.977     -1.#INF       40    0    0     0    0    0            0           0            0      5      107.683      109.977  WPVS mono C-Si Ref Cell     25.9614      1003.80      0.15171    0.15164      -0.31       0.05       -0.4       0.9985  S91-00052    5.5.1
Nease 345W N345M72     STC  Admin    MCIND2021-058  ModuleType1  NaN  10-09-2020  19:14:09    99.9925     100    Ref Cell         2400     25.4279           25  1.3669  46.4445  9.14115  0.43428  291.524  332.156  38.2767  8.67776     0.78236    1.89566      1.71179       243.36          72           1      19404     0    0    0    218000         10         100      0.025        0     1  1.155  1.155  20.5044  6.87042  6.8645     6     6  6.76     107.660     109.977           0           0  27.1989     0     0     0  False     -1.#INF       70    0    0     0    0    0            0           0            0      5      107.660      109.977     -1.#INF       40    0    0     0    0    0            0           0            0      5      107.660      109.977  WPVS mono C-Si Ref Cell     26.0274      1000.93      0.15128    0.15121      -0.31       0.05       -0.4       0.9985  S91-00052    5.5.1

`df_m.head()`

    Voltage   Current mod_temp
0 -1.193405  9.202885  25.2787
1 -1.196560  9.202489  25.2787
2 -1.193403  9.201693  25.2787
3 -1.196558  9.201298  25.2787
4 -1.199711  9.200106  25.2787

`df_m.tail()`

        Voltage   Current mod_temp
14043  46.30869  0.315269  25.4279
14044  46.31411  0.302567  25.4279
14045  46.31949  0.289468  25.4279
14046  46.32181  0.277163  25.4279
14047  46.33039  0.265255  25.4279

Plot

import seaborn as sns
import matplotlib.pyplot as plt

plt.figure(figsize=(20, 8))
sns.scatterplot(x='Current', y='Voltage', data=df_m, hue='mod_temp', s=10)
plt.show()

Note

After doing this, I was having trouble plotting the data because the columns were not float type. However, an error occurred when trying to set the type. Looking back at the data, after row 92, there are multiple headers throughout the two columns.
- Row 93: Voltage: Current:
- Row 3631: Ref Cell: Lamp I:
- Row 7169: Voltage2: Current2:
- Row 11971: Ref Cell2: Lamp I2:
- Row 16773: Voltage3: Current3:
- Row 21575: Ref Cell3: Lamp I3:
- Row 26377: Raw Voltage: Raw Current :
- Row 29915: WPVS Voltage: WPVS Current:
I went back and used the nrows parameter when creating m, so only the first set of headers and associated measurements are extracted from the file.
I recommend writing a script using the csv module to read each file, and create a new file beginning at each blank row, this will make the files have consistent types of measurements.
- This should be a new question, if needed.

How to feed multiple files to pandas to filter data and concatenate all the results

2 Answers2

df_mp

df_m.head()

df_m.tail()

Plot

Note

`df_mp`

`df_m.head()`

`df_m.tail()`