创建新的dataframe时会创建不必要的重复，该dataframe通过迭代列值从另一个dataframe获取值

Question

我试图通过迭代唯一值（合同号）来添加从一个dataframe列获取的值。对于较小的迭代次数，脚本可以完美地工作。但是，如果对1000个唯一值进行迭代，则会在生成的数据帧中创建重复的值，这反过来会减慢处理速度，并占用不必要的处理时间。 我该如何提高效率？你知道吗

https://imgur.com/3obXPne-原始数据帧

https://imgur.com/mEA8g6Z-新数据帧中不必要的重复数据帧

https://imgur.com/3i5gMoJ-新数据帧中不必要的重复数据帧

import pandas as pd
import numpy as np
from datetime import datetime

df = pd.DataFrame([["AB1111",'2018-08-15 00:00:00','164','123','123'],
                   ["AB1111",'2018-08-15 00:03:00','564','453','126'],
                   ["AB1111",'2018-08-15 00:10:00','364','1231','1223'],
                   ["AB1111",'2018-08-15 00:01:00','564','575','1523'],
                   ["CD1111",'2018-08-16 00:12:00','514','341','1213'],
                   ["CD1111",'2018-08-15 00:02:00','564','1234','123'],
                   ["CD1111",'2018-08-16 00:05:00','564','341','124'],
                   ["CD1111",'2018-08-16 00:03:00','64','341','123'],
                   ["EF1111",'2018-08-15 00:00:00','534','341','121'],
                   ["EF1111",'2018-08-17 00:01:00','564','341','163'],
                   ["EF1111",'2018-08-15 00:09:00','524','341','129']],
                   columns = ['contract', 'datetime',
                              'real_cons','solar_gen','battery_charge'])


# converting datetime column datatype to "datetime"
df['datetime'] = pd.to_datetime(df['datetime']) 

#aggregation dataframe (new dataframe)
df_agg1 = pd.DataFrame()

for contract in df['contract'].unique()[:1500]:
    print(contract)
    df_contract = df.copy()[df['contract']==contract]    # selecting each full dataframe from the main DF
    df_contract.set_index('datetime', inplace=True)      # set "datetime" column as an index
    df_contract.sort_index(inplace=True)                 # sort index
    df_contract = df_contract.loc['2018-8-15']           # select timeframe       
    # creating GB61074_cons column, which will be added to df_agg, from df_contract 'real_cons' column
    df_contract[f'{contract}_con'] = df_contract['real_cons']   

    if df_agg1.empty:
        df_agg1 = df_contract[[f'{contract}_con']]        # first column 
    else:
        df_agg1 = df_agg1.join(df_contract[f'{contract}_con'])     # subsequent columns 

df_agg1

如何创建新的数据帧而不创建这些不必要的副本？ 是什么导致了它们的产生？你知道吗

Answer 1

这是一种不使用for循环来获得完全相同结果的方法。为了便于阅读，我用了多行来添加解释。你知道吗

df = pd.DataFrame([["AB1111",'2018-08-15 00:00:00','164'],
                   ["AB1111",'2018-08-15 00:03:00','564'],
                   ["AB1111",'2018-08-15 00:10:00','364'],
                   ["AB1111",'2018-08-15 00:01:00','564'],
                   ["CD1111",'2018-08-16 00:12:00','514'],
                   ["CD1111",'2018-08-15 00:02:00','564'],
                   ["CD1111",'2018-08-16 00:05:00','564'],
                   ["CD1111",'2018-08-16 00:03:00','64'],
                   ["EF1111",'2018-08-15 00:00:00','534'],
                   ["EF1111",'2018-08-17 00:01:00','564'],
                   ["EF1111",'2018-08-15 00:09:00','524']],
                   columns = ['contract', 'datetime','real_cons'])


df = df.set_index(['datetime','contract']).unstack().add_suffix('_con')
df = df.droplevel(level=0,axis=1) #drops the 'real_cons' index
df = pd.DataFrame(df.to_records()) #workaround the remove multiindex
df['datetime'] = pd.to_datetime(df['datetime']) #change datetime column to datetime datatype
df = df.set_index('datetime').loc['2018-08-15'] #filter data on date

print(df.reset_index())

结果：

             datetime AB1111_con CD1111_con EF1111_con
0 2018-08-15 00:00:00        164        NaN        534
1 2018-08-15 00:01:00        564        NaN        NaN
2 2018-08-15 00:02:00        NaN        564        NaN
3 2018-08-15 00:03:00        564        NaN        NaN
4 2018-08-15 00:09:00        NaN        NaN        524
5 2018-08-15 00:10:00        364        NaN        NaN