I am trying to add values to a new dataframe (df2) column (Discount%), the values in this column must be based on "Grid" and Entity from df1),
My structure is as following
因此，如果对于同一实体，DF1中的列是91-120，那么它应该在折扣%下将DF2加20，如果DF1中的列是61-90，那么它必须将5加到DF2，依此类推.
数据是从一个大的CSV文件导入的，到目前为止我已经try 过了，但如果只填0
for j in range(0,len(df1)):
for i in range(0,len(df2)):
if grid['91-120'][j] in df2['Grid'][i]:
#df['Grid%'][i] = grid['91-120'][j]
df2.loc[i, 'Grid%'] = df1['91-120'][j]
谢谢
我目前正在处理一个与在数据帧上迭代有关的类似问题.如果可以避免，您really不希望这样做，特别是当数据帧包含像您的df1这样的重复值时. 我建议将引用数据帧DF1转换为具有索引方向的字典，然后将该字典中的值赋给DF2，如下所示.
DF1 = pd.DataFrame({'Entity': ['F1', 'F2', 'F3', 'F4'], '0-60': [0, 0, 0, 0], '61-90': [0, 5, 10, 5], '91-120':[20, 5, 20, 20], '121-180':[10, 5, 12, 15], '181-240':[20, 5, 22, 25]})
DF2 = pd.DataFrame({'Entity': ['F1', 'F2', 'F3', 'F4'], 'Grid': ['360+', '61-90', '0-60', '91-120']})
print('DF2 before:')
print(DF2)
DF1.drop_duplicates(inplace=True)
DF1.set_index('Entity', inplace=True)
d = DF1.to_dict('index')
def get_discount(entity, grid):
if entity in d and grid in d[entity]:
return d[entity][grid]
else:
return None
DF2['Discount %'] = DF2.apply(lambda x: get_discount(x['Entity'], x['Grid']), axis=1)
print('DF2 after:')
print(DF2)
I found this solution because, as I mentioned before, I'm currently working on a similar problem.
Knowing how detrimental iteration over a dataframe can be to the performance of a function, I realized it would be faster to assign a value from a dictionary. I looked up how to convert a dataframe to a dictionary on Stack Overflow and in the pandas documentation. Next, I looked up how to assign a value to a dataframe from a dictionary on Stack Overflow. I was trying the "dict" orientation at first. I could get it to assign all discount values for each "Entity" based on "Grid", but I couldn't select the one right discount value.
I couldn't find any other solutions online for assigning a value to a dataframe from a 2D dictionary, so I turned to ChatGPT. After ChatGPT did its thing, I was getting "None" in every field. Eventually, I got it to recommend changing the orientation of the dictionary from "dict" to "series". That also didn't work, but I figured I would try all the other orientations. Index worked.
The downside is that Discount % values are floats. The upside is it can handle cases where you don't have a value for Grid in the dictionary (e.g. where Grid is "360+").