将行追加到DataFrame的最快，最有效的方法是什么？

Question

我有一个很大的数据集，我必须将其转换为.csv格式，我有29列和超过一百万行。我正在使用python和pandas数据框来处理此工作。我认为，随着数据框变大，将任何行追加到它会越来越耗时。我想知道是否有更快的方法，可以共享代码中的相关代码段。

任何建议，但欢迎。

df = DataFrame()

for startID in range(0, 100000, 1000):
    s1 = time.time()
    tempdf = DataFrame()
    url = f'https://******/products?startId={startID}&size=1000'

    r = requests.get(url, headers={'****-Token': 'xxxxxx', 'Merchant-Id': '****'})
    jsonList = r.json()  # datatype= list, contains= dict

    normalized = json_normalize(jsonList)
    # type(normal) = pandas.DataFrame
    print(startID / 1000) # status indicator
    for series in normalized.iterrows():  
        series = series[1] # iterrows returns tuple (index, series)
        offers = series['offers']
        series = series.drop(columns='offers')
        length = len(offers)

        for offer in offers:
            n = json_normalize(offer).squeeze()  # squeeze() casts DataFrame into Series
            concatinated = concat([series, n]).to_frame().transpose()
            tempdf = tempdf.append(concatinated, ignore_index=True)

    del normalized
    df = df.append(tempdf)
    f1 = time.time()
    print(f1 - s1, ' seconds')

df.to_csv('out.csv')

Answer 1

正如Mohit Motwani建议的最快方法是将数据收集到字典中，然后将所有内容加载到数据帧中。下面是一些速度测量示例：

import pandas as pd
import numpy as np
import time
import random

end_value = 10000

用于创建字典的度量，最后将所有内容加载到数据帧中

start_time = time.time()
dictinary_list = []
for i in range(0, end_value, 1):
    dictionary_data = {k: random.random() for k in range(30)}
    dictinary_list.append(dictionary_data)

df_final = pd.DataFrame.from_dict(dictinary_list)

end_time = time.time()
print('Execution time = %.6f seconds' % (end_time-start_time))

执行时间= 0.090153秒

将数据附加到列表中并连接到数据框中的度量：

start_time = time.time()
appended_data = []
for i in range(0, end_value, 1):
    data = pd.DataFrame(np.random.randint(0, 100, size=(1, 30)), columns=list('A'*30))
    appended_data.append(data)

appended_data = pd.concat(appended_data, axis=0)

end_time = time.time()
print('Execution time = %.6f seconds' % (end_time-start_time))

执行时间= 4.183921秒

附加数据帧的测量：

start_time = time.time()
df_final = pd.DataFrame()
for i in range(0, end_value, 1):
    df = pd.DataFrame(np.random.randint(0, 100, size=(1, 30)), columns=list('A'*30))
    df_final = df_final.append(df)

end_time = time.time()
print('Execution time = %.6f seconds' % (end_time-start_time))

执行时间= 11.085888秒

使用loc的插入数据测量：

start_time = time.time()
df = pd.DataFrame(columns=list('A'*30))
for i in range(0, end_value, 1):
    df.loc[i] = list(np.random.randint(0, 100, size=30))


end_time = time.time()
print('Execution time = %.6f seconds' % (end_time-start_time))

执行时间= 21.029176秒