Having an issue with pandas df, trying to get the "Count" column based on the date, the code should search for the "date range' within the dates column, and if it is present the 'Count' should be copied into the "Posts" column for the corresponding date eg: date_range value = 16/02/2017 - code searches for 16/02/2017 in "Dates" column and makes "Posts" equal to the "Count" value of that Date - if the date_range value does not appear - Posts should = 0.
Data Example:
Dates Count date_range Posts
0 07/02/2017 1 16/12/2016 (should = 5)
1 01/03/2017 1 17/12/2016
2 15/02/2017 1 18/12/2016
3 23/01/2017 1 19/12/2016
4 28/02/2017 1 20/12/2016
5 09/02/2017 2 21/12/2016
6 20/03/2017 2 22/12/2016
7 16/12/2016 5
My code looks like this:
DateList = df['Dates'].tolist()
for date in df['date_range']:
if str(date) in DateList:
df['Posts'] = df['Count']
else:
dates_df['Posts'] = 0
However this makes the data map the wrong values to "Posts"
Hopefully I explained this correctly! Thanks in advance for the help!
You can first create dict for matching values and then map by date_range column:
print (df)
Dates Count date_range
0 07/02/2017 1 16/12/2016
1 01/03/2017 1 17/12/2016
2 15/02/2017 1 18/12/2016
3 23/01/2017 1 19/12/2016
4 28/02/2017 1 07/02/2017 <-change value for match
5 09/02/2017 2 21/12/2016
6 20/03/2017 2 22/12/2016
7 16/12/2016 5 22/12/2016
d = df[df['Dates'].isin(df.date_range)].set_index('Dates')['Count'].to_dict()
print (d)
{'16/12/2016': 5, '07/02/2017': 1}
df['Posts'] = df['date_range'].map(d).fillna(0).astype(int)
print (df)
Dates Count date_range Posts
0 07/02/2017 1 16/12/2016 5
1 01/03/2017 1 17/12/2016 0
2 15/02/2017 1 18/12/2016 0
3 23/01/2017 1 19/12/2016 0
4 28/02/2017 1 07/02/2017 1
5 09/02/2017 2 21/12/2016 0
6 20/03/2017 2 22/12/2016 0
7 16/12/2016 5 22/12/2016 0