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