用 pandas 的 csv 文件上同一行的下一列的值填充一行中的空值

Question

我有这种类型的DataFrame

name     surname       middle
Frank    Doe           NaN
John     Nan           Wood
Jack     Putt          Nan
Frank    Nan           Joyce

我想移动 NaN 上的"中间"值，与"姓氏"列上的行值相同。我该怎么做？我尝试使用 fillna 方法，但没有得到任何结果。这是我的代码：

import os
from pandas.io.parsers import read_csv

for csvFilename in os.listdir('.'):
   if not csvFilename.endswith('.csv'):
      continue
data=read_csv(csvFilename)
filtered_data["surname"].fillna(filtered_data["middle"].mean(),inplace=True)
filtered_data.to_csv('output.csv' , index=False)

Answer 1

条件列翻转

使用pd.isnull()，列可以有条件地重新排列。

import pandas as pd
from cStringIO import StringIO
# Create fake DataFrame... you can read this in however you like
df = pd.read_table(StringIO('''
name     surname       middle
Frank    Doe           NaN
John     NaN           Wood
Jack     Putt          NaN
Frank    NaN           Joyce'''), sep='s+')
print 'Original DataFrame:'
print df
print
# Assign the middle name to any surname with a NaN
df.loc[pd.isnull(df['surname']), 'surname'] = df[pd.isnull(df['surname'])]['middle']
print 'Manipulated DataFrame:'
print df
print

---

Original DataFrame:
    name surname middle
0  Frank     Doe    NaN
1   John     NaN   Wood
2   Jack    Putt    NaN
3  Frank     NaN  Joyce
Manipulated DataFrame:
    name surname middle
0  Frank     Doe    NaN
1   John    Wood   Wood
2   Jack    Putt    NaN
3  Frank   Joyce  Joyce

Answer 2

我认为有一种更简单的方法可以做到这一点：

df['surname'] = df['middle'].combine_first(df['surname'])
print(df)

输出：

    name surname middle
0  Frank     Doe    NaN
1   John    Wood   Wood
2   Jack    Putt    NaN
3  Frank   Joyce  Joyce