Import a directory of CSV files at once and keep only oldest record per file

Viewed 80

I have database containing several csv files. Each csv file contains the last 7 days and only the oldest date is final data.

For example "variables_2019-08-12.csv" file contains data from 08-06 until 08-12 ( only 08-06 data is final data) and "variables_2019-08-13.csv" file contains data from 08-07 until 08-13 ( only 08-07 data is final data). I want to keep only records for date 08-06 from variables_2019-08-12.csv file and records for date 08-07 from variables_2019-08-13.csv file and so on. Server produce each data 7 times during 7 days and only after 7 days the data is considered as a final. Data after import will look like this:

import pandas as pd

source = ["data/variables_2019-08-12.csv",
"data/variables_2019-08-12.csv",
"data/variables_2019-08-12.csv", "data/variables_2019-08-12.csv",
"data/variables_2019-08-12.csv", "data/variables_2019-08-12.csv",
"data/variables_2019-08-12.csv", "data/variables_2019-08-13.csv",
"data/variables_2019-08-13.csv", "data/variables_2019-08-13.csv",
"data/variables_2019-08-13.csv", "data/variables_2019-08-13.csv",
"data/variables_2019-08-13.csv", "data/variables_2019-08-13.csv"]
date = ["2019-08-06", "2019-08-07", "2019-08-08", "2019-08-09",
"2019-08-10", "2019-08-11", "2019-08-12", "2019-08-07","2019-08-08",
"2019-08-09", "2019-08-10", "2019-08-11", "2019-08-12","2019-08-13"]
id = [18404487, 18404487, 18502437, 18502437, 18502437, 18502437,
18502437, 18502437, 18502437, 18502437, 18502437, 18502437,18502437, 18502437]
usage = [11, 146, 41, 1, 2, 8, 2, 152, 42, 1, 5, 100, 2, 15]


dict = {'source': source, 'date': date, 'id': id, 'usage': usage}

df = pd.DataFrame(dict)

I am reading all CSV files at once then group by source column and filter and keep only the oldest date from each source. What am I doing wrong here?

    # group by source
    # filter only oldest date
    # ungroup dataframe
    df['date'] =  pd.to_datetime(df['date'])
   df.groupby('source').filter(lambda x: (x['date'].min())).reset_index()

#error 
filter function returned a Timestamp, but expected a scalar bool
1 Answers

IIUC,

if you've already got your concatenated df you could possibly leverage the .agg function in groupby which lets you access columns

df.groupby('source').agg({'date' : min})

note this would by the same as

df.groupby('source')['date'].min().reset_index()
Related