索引使用多个条件匹配多个数据帧

Question

我试图让python读取excel文件，然后从.csv文件中创建数据帧，这些文件以excel文件中的行命名，并从.csv文件中索引数据，然后将它们粘贴到excel文件中

excel文件已放入具有以下布局的数据框中：

     Name  Location      Date Check_2  ...  Volume  VWAP  $Volume  Trades
0  Orange  New York  20200501       X  ...     NaN   NaN      NaN     NaN
1   Apple     Minsk  20200504       X  ...     NaN   NaN      NaN     NaN

空行应填充从放置在数据框中的.csv文件编制索引的数据，如下所示：

  Name      Date      Time  Open  High   Low  Close  Volume  VWAP  Trades
4   Orange  20200501  15:30:00  5.50  5.85  5.45   5.70    1500  5.73      95
5   Orange  20200501  17:00:00  5.65  5.70  5.50   5.60    1600  5.65      54
6   Orange  20200501  20:00:00  5.80  5.85  5.45   5.81    1700  5.73      41
7   Orange  20200501  22:00:00  5.60  5.84  5.45   5.65    1800  5.75      62
8   Orange  20200504  15:30:00  5.40  5.87  5.45   5.75    1900  5.83      84
9   Orange  20200504  17:00:00  5.50  5.75  5.40   5.60    2000  5.72      94
10  Orange  20200504  20:00:00  5.80  5.83  5.44   5.50    2100  5.40      55
11  Orange  20200504  22:00:00  5.40  5.58  5.37   5.80    2200  5.35      87
0    Apple  20200504  15:30:00  3.70  3.97  3.65   3.75    1000  3.60      55
1    Apple  20200504  17:00:00  3.65  3.95  3.50   3.80    1200  3.65      68
2    Apple  20200504  20:00:00  3.50  3.83  3.44   3.60    1300  3.73      71
3    Apple  20200504  22:00:00  3.55  3.58  3.35   3.57    1400  3.78      81
4    Apple  20200505  15:30:00  3.50  3.85  3.45   3.70    1500  3.73      95
5    Apple  20200505  17:00:00  3.65  3.70  3.50   3.60    1600  3.65      54
6    Apple  20200505  20:00:00  3.80  3.85  3.45   3.81    1700  3.73      41
7    Apple  20200505  22:00:00  3.60  3.84  3.45   3.65    1800  3.75      62

我一直在努力填充这些空单元格，因为我无法找到一种方法来正确索引这两个数据帧之间的匹配

例如，尝试：

intradayho = rdf2[(rdf2['Time']=='15:30:00')]
indexopen = pd.DataFrame(intradayho['Open'])

rdf1['Open'] = rdf1.Date.map(intradayho.set_index('Date')['Open'].to_dict())
print("Open prices rdf1")
print(rdf1['Open'])

产生：

Open prices rdf1
0    5.5
1    3.7

但只考虑日期，因此它将复制“日期”列的打开值，而不是“名称”和“日期”，这是一个问题，因为这两个值需要匹配

此外，此代码还会产生以下错误：

A value is trying to be set on a copy of a slice from a DataFrame.Try using .loc[row_indexer,col_indexer] = value instead

但是当我试着用

rdf1.loc[rdf1['Open']] = rdf1.Date.map(intradayho.set_index('Date')['Open'].to_dict())

我得到一个错误：

KeyError: "None of [Float64Index([nan, nan], dtype='float64')] are in the [index]"

这对我来说没有意义，因为整个目标是填充这些“NaN”值

这里有人能帮我做一些东西，可以索引匹配这些数据框中的数据并将其写入Excel文件吗

谢谢

编辑： 忘记发布我的完整代码，这是：

import pandas as pd
import os

#Opening 'Test Tracker.xlsx' to find entities to download
TEST = pd.ExcelFile("Trackers\TEST Tracker.xlsx")
df1 = TEST.parse("Entries")

values1 = df1[['Name', 'Location', 'Date', 'Check_2',
           'Open', 'High', 'Low', 'Close', 'Volume', 'VWAP', '$Volume', 
'Trades']]

#Searching for every row that contains the value 'X' in the column 'Check_2'
rdf1 = values1[values1.Check_2.str.contains("X")]

#Printing dataframe to check
print("First Dataframe")
print(rdf1)

#creating a list for the class objects
Fruits = []

#Generating dataframes from classobjects
for idx, rows in rdf1.iterrows():
    fle = os.path.join('Entities', rows.Location, rows.Name, 'TwoHours.csv')
    col_list = ['Name', 'Date', 'Time', 'Open', 'High', 'Low', 'Close', 'Volume', 'VWAP', 'Trades']
    df3 = pd.read_csv(fle, usecols=col_list, sep=";")
    Fruits.append(df3)

rdf2 = pd.concat(Fruits)
print("Printing Full Data Frame")
print(rdf2)

intradayh = rdf2[(rdf2['Time']>'15:30:00') & (rdf2['Time']<'22:00:00')]
intradayho = rdf2[(rdf2['Time']=='15:30:00')]
indexopen = pd.DataFrame(intradayho['Open'])
intradayhc = rdf2[(rdf2['Time']=='22:00:00')]
indexclose = pd.DataFrame(intradayhc['Close'])

rdf1.loc[rdf1['Open']] = rdf1.Date.map(intradayho.set_index('Date')['Open'].to_dict())
print("Open prices rdf1")
print(rdf1['Open'])

编辑：注释中要求的所需输出：

  Name  Location      Date    Open   High   Low    close  volume  VWAP ...
0  Orange  New York  20200501  5.5    5.95  5.45    5.65   6600   5.71  ...
1   Apple     Minsk  20200504  3.7    3.83  3.35    3.57   4900   3.69 ...

我将在“开放”中进行1:1匹配，“高”中进行最大值匹配，“低”中进行最小值匹配，“关闭”中进行1:1匹配，“交易量”和“交易量”的和值匹配。“VWAP”的平均值和“$Volume”中的“Volume*VWAP”值

Answer 1

df，您的nan datframe和df2；包含所有数据的更大数据帧

使用groupby和.agg()在多个列上查找多个聚合

df2=df1.groupby(['Name','Date']).agg(Open=('Open','first'), Close=('Close','last'),High=('High','max'),Low=('Low','min'),Volume=('Volume','sum'),VWAP=('VWAP','mean')).reset_index()

一种方法是进行内部合并并对更新的列进行切片

result = pd.merge(df2, df, how='inner', on=['Name', 'Date']).iloc[:,:-4]

或 聚合后，使用combine_first并删除所有NaNs

result= (df.set_index('Date').combine_first(df2.set_index('Date')).reset_index())
result=result[k.notna()]

结果