Drop several CSV files in a folder, based values on a column in them, using glob

babak asadolah 0
Viewed 27

I have hourly temperature and rainfall observations from nearly 2000 stations. Each station has its own csv file which temporally extend from May to August 2022. First by using the globe, I convert the hourly data to averaged daily and save new csv file named as "StationName - edited.csv".

df.head()
Out[5]: 
         date      station      lat       lon   Rain   Temp
0  2022-05-01  72750494999  46.5475 -93.67667  100.0  109.4
1  2022-05-02  72750494999  46.5475 -93.67667   93.0   96.8
2  2022-05-03  72750494999  46.5475 -93.67667   93.0   96.8
3  2022-05-04  72750494999  46.5475 -93.67667  100.0   96.8
4  2022-05-05  72750494999  46.5475 -93.67667   87.0   98.6

Next I want to put all these 2000 csv's in a one file using concat command. However, some of these csv's (nearly 150) does not have rainfall observation so their Rain column in the csv is empty, and manually finding them through 2000 files is vert tedious task.

Can anyone suggest a solution for me to eliminate this csv's files (the one with missing values) from entering the concat command, or only used those which have both columns. For example, I got the idea of indicating a IF in the code so that if the csv has missing value the code drop this csv from the concat command!

import numpy as np
import pandas as pd

# Read all the csv if Folder
import glob
filenames = sorted(glob.glob('*.csv'))
# Create an empty dataset
e = pd.DataFrame()

for f in filenames:
    # read the variables
    df = pd.read_csv(f, index_col=None)

    # I think I should add something here!

    e = pd.concat([e, df], axis=0)
glob if-statement pandas python
1 Answers

First a helpful comment:

concat doesn't need to get initialized with e and it doesn't need to be in a loop getting run multiple times. Something like the following is a more typical approach:

dfs=[]
for f in filenames:
    df = pd.read_csv(f)
    dfs.append(df)
e=pd.concat(dfs,axis=0)

Now a direct answer:

Using an if in the loop checking each df will work fine. You can mask each df to look for non-null values df[df['Rain'].notnull()] and check that there's still something left over len(df[df['Rain'].notnull()])>0 to confirm that some observations do exist in each file. You should be able to use something like this:

dfs=[]
for f in filenames:
    df = pd.read_csv(f)
    if len(df[df['Rain'].notnull()])>0:
        dfs.append(df)
e=pd.concat(dfs,axis=0)
jmb0
Related