python - 想要找到与 pandas dataframe 相对应的元素的列表索引(np.where 和 .index())

Question

我想找到满足条件的字典或列表项的索引，并将其写入数据帧中的新列。

我从以下设置开始:

import pandas as pd
import numpy as np
df = pd.DataFrame(data = {'col1': ['2018_08', '2008_02','2019_01','2017_04']})

dates = {0: ['2019-01-15 00:00:00', '2019_01', 1, 2019, 0],
         -1: ['2018-12-15 00:00:00', '2018_12', 12, 2018, -1],
         -2: ['2018-11-15 00:00:00', '2018_11', 11, 2018, -2],
         -3: ['2018-10-15 00:00:00', '2018_10', 10, 2018, -3],
         -4: ['2018-09-15 00:00:00', '2018_09', 9, 2018, -4],
         -5: ['2018-08-15 00:00:00', '2018_08', 8, 2018, -5]}

我想检查数据帧 df 中的 col1 列的值是否包含在字典 dates 中。如果是，则返回字典中相应列表的键或最后一个条目。如果不是，则返回 NaT 或 NaN。我试过:

df['month_seq'] = np.where(df.col1.isin([dates[i][1] for i in range(0,-6,-1)]), '?' ,pd.NaT)

它标识正确的条目，但不返回相应的负数。输出如下:

    col1    month_seq
0   2018_08     ?
1   2008_02     NaT
2   2019_01     ?
3   2017_04     NaT

如果尝试过

[dates[i][1] for i in range(0,-6,-1)].index(df.col1)

返回错误。

预先感谢您的帮助。

Answer 1

使用map通过字典理解创建的字典:

df = pd.DataFrame(data = {'col1': ['2018_08', '2008_02','2019_01','2017_04']})

dates = {0: ['2019-01-15 00:00:00', '2019_01', 1, 2019, 0],
         -1: ['2018-12-15 00:00:00', '2018_12', 12, 2018, -1],
         -2: ['2018-11-15 00:00:00', '2018_11', 11, 2018, -2],
         -3: ['2018-10-15 00:00:00', '2018_10', 10, 2018, -3],
         -4: ['2018-09-15 00:00:00', '2018_09', 9, 2018, -4],
         -5: ['2018-08-15 00:00:00', '2018_08', 8, 2018, -5]}

d = {v[1]:k for k, v in dates.items()}
print (d)
{'2019_01': 0, '2018_12': -1, '2018_11': -2, '2018_10': -3, '2018_09': -4, '2018_08': -5}

df['new'] = df['col1'].map(d)
print (df)
      col1  new
0  2018_08 -5.0
1  2008_02  NaN
2  2019_01  0.0
3  2017_04  NaN