pandas 从其他DF列中每个日期的DatetimeIndex中删除元素 [英] Pandas Remove Elements From DatetimeIndex per Dates in Other DF Column

问题描述

鉴于以下数据框架：

import pandas as pd
df=pd.DataFrame({'A':['a','b','c'],
        'first_date':['2015-08-31 00:00:00','2015-08-24 00:00:00','2015-08-25 00:00:00']})
df.first_date=pd.to_datetime(df.first_date) #(dtype='<M8[ns]')
df['last_date']=pd.to_datetime('5/6/2016') #(dtype='datetime64[ns]')
def fnl(x):
    l = pd.date_range(x.loc['first_date'], x.loc['last_date'], freq='B')
    return pd.Series([l])

df['range'] = df.apply(fnl, axis=1)
df

    A   first_date  last_date   range
0   a   2015-08-31  2016-05-06  DatetimeIndex(['2015-08-31', '2015-09-01', '20...
1   b   2015-08-24  2016-05-06  DatetimeIndex(['2015-08-24', '2015-08-25', '20...
2   c   2015-08-25  2016-05-06  DatetimeIndex(['2015-08-25', '2015-08-26', '20...

我想要对于属于其相应范围的每个日期（即，从df ['range']中删除exc ['A']匹配df ['A']的日期。如果日期在['A']在df ['A']的相应范围之外，则显然不能排除。

I'd like to have dates from exc (below) removed from df['range'] where the exc['A'] matches df['A'], for each date that falls into its corresponding range (i.e. if a date in exc['A'] is outside of its corresponding range in df['A'], it obviously could not be excluded.

exc=pd.DataFrame({'A':['a','a','b','b','c','c'],
                'Exclusions':['2014-12-30 00:00:00','2015-08-31 00:00:00',\
                              '2015-08-25 00:00:00','2015-10-20 00:00:00',\
                             '2015-08-26 00:00:00','2016-10-05 00:00:00']
                 })
exc

    A   Exclusions
0   a   2014-12-30 00:00:00
1   a   2015-08-31 00:00:00
2   b   2015-08-25 00:00:00
3   b   2015-10-20 00:00:00
4   c   2015-08-26 00:00:00
5   c   2016-10-05 00:00:00

所需结果：

    A   first_date  last_date   range
0   a   2015-08-31  2016-05-06  DatetimeIndex(['2015-09-01', '2015-09-02', '20...
1   b   2015-08-24  2016-05-06  DatetimeIndex(['2015-08-24', '2015-08-26', '20...
2   c   2015-08-25  2016-05-06  DatetimeIndex(['2015-08-25', '2015-08-27', '20...

提前！

推荐答案

我想您可以先创建新的列范围 concat ，并通过 fusion 。然后 合并 并通过 布尔索引进行过滤 与面具 df._merge =='left_only'：

I think you can first create new column range by concat and reshape by melt. Then merge and filter by boolean indexing with mask df._merge == 'left_only':

import pandas as pd
df=pd.DataFrame({'A':['a','b','c'],
        'first_date':['2015-08-31 00:00:00','2015-08-24 00:00:00','2015-08-25 00:00:00']})
df.first_date=pd.to_datetime(df.first_date) #(dtype='<M8[ns]')
df['last_date']=pd.to_datetime('5/6/2016') #(dtype='datetime64[ns]')
def fnl(x):
    l = pd.date_range(x.loc['first_date'], x.loc['last_date'], freq='B')
    return pd.Series(l)

df1 = df.apply(fnl, axis=1)
print (df1)
         0          1          2          3          4          5    \
0 2015-08-31 2015-09-01 2015-09-02 2015-09-03 2015-09-04 2015-09-07   
1 2015-08-24 2015-08-25 2015-08-26 2015-08-27 2015-08-28 2015-08-31   
2 2015-08-25 2015-08-26 2015-08-27 2015-08-28 2015-08-31 2015-09-01   

         6          7          8          9      ...            175  \
0 2015-09-08 2015-09-09 2015-09-10 2015-09-11    ...     2016-05-02   
1 2015-09-01 2015-09-02 2015-09-03 2015-09-04    ...     2016-04-25   
2 2015-09-02 2015-09-03 2015-09-04 2015-09-07    ...     2016-04-26   

         176        177        178        179        180        181  \
0 2016-05-03 2016-05-04 2016-05-05 2016-05-06        NaT        NaT   
1 2016-04-26 2016-04-27 2016-04-28 2016-04-29 2016-05-02 2016-05-03   
2 2016-04-27 2016-04-28 2016-04-29 2016-05-02 2016-05-03 2016-05-04   

         182        183        184  
0        NaT        NaT        NaT  
1 2016-05-04 2016-05-05 2016-05-06  
2 2016-05-05 2016-05-06        NaT  

[3 rows x 185 columns]

df = pd.concat([df,df1], axis=1)
df = pd.melt(df, id_vars=['A','first_date','last_date'], value_name='range')
df = df.dropna(subset=['range'])
print (df)
     A first_date  last_date variable      range
0    a 2015-08-31 2016-05-06        0 2015-08-31
1    b 2015-08-24 2016-05-06        0 2015-08-24
2    c 2015-08-25 2016-05-06        0 2015-08-25
3    a 2015-08-31 2016-05-06        1 2015-09-01
4    b 2015-08-24 2016-05-06        1 2015-08-25
5    c 2015-08-25 2016-05-06        1 2015-08-26
6    a 2015-08-31 2016-05-06        2 2015-09-02
7    b 2015-08-24 2016-05-06        2 2015-08-26
8    c 2015-08-25 2016-05-06        2 2015-08-27
9    a 2015-08-31 2016-05-06        3 2015-09-03
10   b 2015-08-24 2016-05-06        3 2015-08-27
11   c 2015-08-25 2016-05-06        3 2015-08-28
12   a 2015-08-31 2016-05-06        4 2015-09-04
13   b 2015-08-24 2016-05-06        4 2015-08-28
14   c 2015-08-25 2016-05-06        4 2015-08-31
15   a 2015-08-31 2016-05-06        5 2015-09-07
16   b 2015-08-24 2016-05-06        5 2015-08-31
...
...

exc=pd.DataFrame({'A':['a','a','b','b','c','c'],
                'Exclusions':['2014-12-30 00:00:00','2015-08-31 00:00:00',\
                              '2015-08-25 00:00:00','2015-10-20 00:00:00',\
                             '2015-08-26 00:00:00','2016-10-05 00:00:00']
                 })
#print (exc)

exc['Exclusions'] = pd.to_datetime(exc['Exclusions'])

df = (pd.merge(df, exc, left_on=['A', 'range'],
                right_on=['A','Exclusions'], 
                indicator=True, 
                how='left'))


df = df[df._merge == 'left_only'] 
df = df.drop(['Exclusions','_merge'], axis=1)               
print (df)                
     A first_date  last_date variable      range
1    b 2015-08-24 2016-05-06        0 2015-08-24
2    c 2015-08-25 2016-05-06        0 2015-08-25
3    a 2015-08-31 2016-05-06        1 2015-09-01
6    a 2015-08-31 2016-05-06        2 2015-09-02
7    b 2015-08-24 2016-05-06        2 2015-08-26
8    c 2015-08-25 2016-05-06        2 2015-08-27
9    a 2015-08-31 2016-05-06        3 2015-09-03
10   b 2015-08-24 2016-05-06        3 2015-08-27
11   c 2015-08-25 2016-05-06        3 2015-08-28
12   a 2015-08-31 2016-05-06        4 2015-09-04
13   b 2015-08-24 2016-05-06        4 2015-08-28
...
...

这篇关于 pandas 从其他DF列中每个日期的DatetimeIndex中删除元素的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！