我有兩個(gè) DataFrame 對(duì)象df1和df2，兩者都包含來(lái)自相同開(kāi)始和結(jié)束日期的數(shù)據(jù)。df1共有 17376 行。每行date有 48 行（時(shí)間戳 xx:00 和 xx:30 處每小時(shí) 2 個(gè)值），總共 362 天（請(qǐng)參閱下面的圖像鏈接）。df2是一個(gè)更大的 DataFrame，每天有 144 行（每小時(shí) 6 個(gè)值 - xx:00、xx:10、xx:20、xx:30、xx:40、xx:50）。（下面的圖片鏈接）我想加入 df1 和 df2，以便它們具有完全匹配的日期和時(shí)間戳以及相同的行數(shù)（刪除 df2 中的某些行）。理想情況下， 對(duì)應(yīng)的所有值都df1必須存在于 中df2，但中間有一些缺失值并且它們是未知的。我想合并df1并df2處理缺失的值。感謝幫助！
1 回答
慕村225694
TA貢獻(xiàn)1880條經(jīng)驗(yàn) 獲得超4個(gè)贊
鑒于描述，我建議使用pd.concator?merge。這是一個(gè)測(cè)試示例：
import pandas as pd
#generating test data
index1 = pd.date_range('1/1/2000', periods=9, freq='D')
index2 = pd.date_range('1/4/2000', periods=9, freq='D')
series = range(9)
df1 = pd.DataFrame([index1,series]).T
df2 = pd.DataFrame([index2,series]).T
df1.columns = ['Time','Data']
df2.columns = ['Time','Data']
df1：
? ? ? ? ? ? ? ? ? Time Data
0? 2000-01-01 00:00:00? ? 0
1? 2000-01-02 00:00:00? ? 1
2? 2000-01-03 00:00:00? ? 2
3? 2000-01-04 00:00:00? ? 3
4? 2000-01-05 00:00:00? ? 4
5? 2000-01-06 00:00:00? ? 5
6? 2000-01-07 00:00:00? ? 6
7? 2000-01-08 00:00:00? ? 7
8? 2000-01-09 00:00:00? ? 8? ? ? ? ? ? ? ? ?
df2：
? ? ? ? ? ? ? ? ? Time Data
0? 2000-01-04 00:00:00? ? 0
1? 2000-01-05 00:00:00? ? 1
2? 2000-01-06 00:00:00? ? 2
3? 2000-01-07 00:00:00? ? 3
4? 2000-01-08 00:00:00? ? 4
5? 2000-01-09 00:00:00? ? 5
6? 2000-01-10 00:00:00? ? 6
7? 2000-01-11 00:00:00? ? 7
8? 2000-01-12 00:00:00? ? 8
請(qǐng)注意，兩個(gè)數(shù)據(jù)框中的數(shù)據(jù)可用于不同的日期。
#convert Time to pandas datetime format
#df1['Time'].to_datetime(df1['Time']) # <- uncomment this for your case
#df1['Time'].to_datetime(df1['Time'])? # <- uncomment this for your case
#making the time the index of the dataframes
df1.set_index(['Time'],inplace=True)
df2.set_index(['Time'],inplace=True)
#concatenating the dataframe column wise (axis=1)
df3 = pd.concat([df1,df2],axis=1)
print(df3)
輸出：
? ? ? ? ? ?Data Data
Time? ? ? ? ? ? ? ??
2000-01-01? ? 0? NaN
2000-01-02? ? 1? NaN
2000-01-03? ? 2? NaN
2000-01-04? ? 3? ? 0
2000-01-05? ? 4? ? 1
2000-01-06? ? 5? ? 2
2000-01-07? ? 6? ? 3
2000-01-08? ? 7? ? 4
2000-01-09? ? 8? ? 5
2000-01-10? NaN? ? 6
2000-01-11? NaN? ? 7
2000-01-12? NaN? ? 8
處理缺失值：
pd.concat correctly merges the data as per the data. NaN indicate the missing values after combining, which can be handled mainly with fillna(filling something inplace of NaN) or dropna (dropping the data containing NaN). Here is an example of fillna (dropna is used exactly the same way but without 0) :
#filling 0's inplace of `NaN`. You can use also method='bfill' or 'ffill' or interpolate
df3 = df3.fillna(0,inplace=True)?
#df3 = df3.fillna(method='bfill',inplace=True) # <- uncomment if you want to use this
#df3 = df3.fillna(method='ffill',inplace=True) # <- uncomment if you want to use this
Output:
? ? ? ? ? ? ?Data? Data
Time? ? ? ? ? ? ? ? ??
2000-01-01? ? ?0? ? ?0
2000-01-02? ? ?1? ? ?0
2000-01-03? ? ?2? ? ?0
2000-01-04? ? ?3? ? ?0
2000-01-05? ? ?4? ? ?1
2000-01-06? ? ?5? ? ?2
2000-01-07? ? ?6? ? ?3
2000-01-08? ? ?7? ? ?4
2000-01-09? ? ?8? ? ?5
2000-01-10? ? ?0? ? ?6
2000-01-11? ? ?0? ? ?7
2000-01-12? ? ?0? ? ?8
添加回答
舉報(bào)
0/150
提交
取消