python - 使用 pandas 函数合并 2 行

Question

我有一个数据框，其中一条记录分为两行。

try:
    from StringIO import StringIO
except ImportError:
    from io import StringIO

import pandas as pd

u_cols = ['Date', 'Particulars', 'Details', 'Type', 'No', 'Debit', 'Credit']
audit_trail = StringIO('''
Sr,Date,Particulars,Details,Type,No,Debit,Credit
0,2017-04-03,Dr,Axis Bank Savings Acct,Receipt,1.0,nan,1.0
1,NaT,nan,neft pratik vijay patil,nan,nan,nan,nan
2,2017-04-03,Dr,Axis Bank Savings Acct,Receipt,2.0,nan,45000.0
3,NaT,nan,NEFT Rajesh Joshi,nan,nan,nan,nan
4,2017-04-03,Cr,Axis Bank Savings Acct,Payment,1.0,1000.0,nan
5,2017-04-03,Cr,Axis Bank Savings Acct,Payment,2.0,20005.75,nan
6,2017-04-04,Cr,Axis Bank Savings Acct,Payment,3.0,15005.75,nan
7,2017-04-09,Dr,Axis Bank Savings Acct,Receipt,3.0,nan,17000.0
8,2017-04-13,Cr,Axis Bank Savings Acct,Payment,5.0,700.0,nan
''')

df = pd.read_csv(audit_trail, sep=",", usecols = u_cols  )

如果有空白Date，就是对上面提到的entry的解释。我如何在新列中获取这些详细信息？

---

更新:换句话说，我需要将具有“NaT”的索引行与前一个索引行连接起来。

df[df['Date'] == 'NaT'].index

Answer 1

找到 Date 为空的索引，然后将这些详细信息添加到上一行。

import pandas as pd
df['Date'] = pd.to_datetime(df.Date)

df.loc[df[df.Date.isnull()].index-1, 'Explanation'] = df.loc[df.Date.isnull(), 'Details'].values
# Remove null date columns
df = df[df.Date.notnull()]

输出:

        Date Particulars                 Details     Type   No     Debit   Credit              Explanation
0 2017-04-03          Dr  Axis Bank Savings Acct  Receipt  1.0       NaN      1.0  neft pratik vijay patil
2 2017-04-03          Dr  Axis Bank Savings Acct  Receipt  2.0       NaN  45000.0        NEFT Rajesh Joshi
4 2017-04-03          Cr  Axis Bank Savings Acct  Payment  1.0   1000.00      NaN                      NaN
5 2017-04-03          Cr  Axis Bank Savings Acct  Payment  2.0  20005.75      NaN                      NaN
6 2017-04-04          Cr  Axis Bank Savings Acct  Payment  3.0  15005.75      NaN                      NaN
7 2017-04-09          Dr  Axis Bank Savings Acct  Receipt  3.0       NaN  17000.0                      NaN
8 2017-04-13          Cr  Axis Bank Savings Acct  Payment  5.0    700.00      NaN                      NaN