pandas - 循环并合并具有相同索引、相同列的 DataFrame(但是每个 DataFrame 有几列是唯一的)
问题描述
所需任务描述
我使用以下代码合并df
和df1
(显示示例数据),它适用于我所需要的。但是,我需要遍历大量的 DataFrame(df2
例如,但 will bedf3
等df4
)并且不知道如何修改代码。我有具有相同索引、相同列的 DataFrame,但是每个 DataFrame 有几列是单独的。我使用下面的代码,它运行良好,但我希望修改它,以便我可以循环遍历df
and df1
,将它们合并在一起,创建requireddata
但然后重复这个 whererequireddata
与 合并df2
。同样的逻辑将继续requireddata
合并df3
等等。任何帮助都是极好的!!:)
df
ID AA TA TL
Date
2001 AAPL 1.0 44 50
2002 AAPL 3.0 33 51
2003 AAPL 2.0 22 53
2004 AAPL 5.0 11 76
2005 AAPL 2.0 33 44
2006 AAPL 3.0 22 12
df1
ID AA TA ML
Date
2001 MSFT 3.5 44 12
2002 MSFT 6.7 33 15
2003 MSFT 2.3 22 19
2004 MSFT 5.5 11 20
2005 MSFT 2.2 33 43
2006 MSFT 3.2 22 23
df2
例子
ID AA TA PP
Date
2001 TSLA 3.3 48 18
2002 TSLA 6.3 38 18
2003 TSLA 2.6 28 18
2004 TSLA 5.3 18 28
2005 TSLA 2.3 38 48
2006 TSLA 3.3 28 28
使用的代码
dfdates['Date'] # this has dates required for index
df
df1
cols_to_use = df.columns.difference(df1.columns) #compare column difference df and df1
cols_to_use1 = df1.columns.difference(df.columns) #compare column difference df1 and df
dataframe = pd.DataFrame(columns = cols_to_use, index = df['Date']) #dataframe with columns in df1 but not in df
dataframe1 = pd.DataFrame(columns = cols_to_use1, index = df1['Date']) #dataframe with columns in df but not in df1
datatesting = pd.concat([dataframe, df], axis=1) #merge missing columns into df
datatesting1 = pd.concat([dataframe1, df1], axis=1) #merge missing columns into df1
diff = datatesting1.columns.difference(datatesting.columns) #check difference (is 0)
print (diff)
frames = [datatesting, datatesting1] #list of dataframes
requireddata = pd.concat(frames) #merge dataframes
创建这个:
ID AA TA TL ML
Date
2001 AAPL 1.0 44 50 NaN
2002 AAPL 3.0 33 51 NaN
2003 AAPL 2.0 22 53 NaN
2004 AAPL 5.0 11 76 NaN
2005 AAPL 2.0 33 44 NaN
2006 AAPL 3.0 22 12 NaN
2001 MSFT 3.5 44 NaN 12
2002 MSFT 6.7 33 NaN 15
2003 MSFT 2.3 22 NaN 19
2004 MSFT 5.5 11 NaN 20
2005 MSFT 2.2 33 NaN 43
2006 MSFT 3.2 22 NaN 23
使用循环代码,会喜欢这样的东西:
ID AA TA TL ML PP
Date
2001 AAPL 1.0 44 50 NaN NaN
2002 AAPL 3.0 33 51 NaN NaN
2003 AAPL 2.0 22 53 NaN NaN
2004 AAPL 5.0 11 76 NaN NaN
2005 AAPL 2.0 33 44 NaN NaN
2006 AAPL 3.0 22 12 NaN NaN
2001 MSFT 3.5 44 NaN 12 NaN
2002 MSFT 6.7 33 NaN 15 NaN
2003 MSFT 2.3 22 NaN 19 NaN
2004 MSFT 5.5 11 NaN 20 NaN
2005 MSFT 2.2 33 NaN 43 NaN
2006 MSFT 3.2 22 NaN 23 NaN
2001 TSLA 3.3 48 NaN NaN 18
2002 TSLA 6.3 38 NaN NaN 18
2003 TSLA 2.6 28 NaN NaN 18
2004 TSLA 5.3 18 NaN NaN 28
2005 TSLA 2.3 38 NaN NaN 48
2006 TSLA 3.3 28 NaN NaN 28
解决方案
我相信这里不需要列差异,只使用concat
, 列正确对齐:
df = pd.concat([df,df1,df2], sort=False)
print (df)
ID AA TA TL ML PP
Date
2001 AAPL 1.0 44 50.0 NaN NaN
2002 AAPL 3.0 33 51.0 NaN NaN
2003 AAPL 2.0 22 53.0 NaN NaN
2004 AAPL 5.0 11 76.0 NaN NaN
2005 AAPL 2.0 33 44.0 NaN NaN
2006 AAPL 3.0 22 12.0 NaN NaN
2001 MSFT 3.5 44 NaN 12.0 NaN
2002 MSFT 6.7 33 NaN 15.0 NaN
2003 MSFT 2.3 22 NaN 19.0 NaN
2004 MSFT 5.5 11 NaN 20.0 NaN
2005 MSFT 2.2 33 NaN 43.0 NaN
2006 MSFT 3.2 22 NaN 23.0 NaN
2001 TSLA 3.3 48 NaN NaN 18.0
2002 TSLA 6.3 38 NaN NaN 18.0
2003 TSLA 2.6 28 NaN NaN 18.0
2004 TSLA 5.3 18 NaN NaN 28.0
2005 TSLA 2.3 38 NaN NaN 48.0
2006 TSLA 3.3 28 NaN NaN 28.0
推荐阅读
- android - react native mapbox在发布APK中不加载地图
- azure - 将本地 xamarin SQLite 数据库与 Azure 表存储后端同步
- html - 文本换行,css
- javascript - 如何在 javascript (vanilla) 中重放动画 GIF - 没有 JQuery
- ios - 某些 GADUnifiedNativeAdView 不可点击 (AdMob/Swift)
- java - 使用 yml 配置而不是standalone.xml
- python - 删除字符串中出现的元音
- android - 使用正则表达式进行选择时,ContentResolver.query() 的 Android-studio 问题
- c - 从具有指定字节顺序的二进制 unsigned char* 读取 C 中的 double
- python - Change plt.imshow() into cv2.imshow() in Python