CODEWITHSUNDEEP
pythonpandas
Alz
Alz

Reputation: 805

Adding new column from existing columns using pd.Series() creates NaN values

I want to add a new column to a DataFrame based on existing columns. The new column is just a tuple of three values of three columns:

df0.shape
# (5410185, 17)
new_col = pd.Series(list(zip(df0['a'], df0['b'], df0['c'])))
new_col.shape
# (5410185,)
new_col.isnull().sum()
# 0
df0['abc'] = new_col
df0['abc'].isnull().sum()
# 14334

I tried the same method on a sample df and it works as expected:

test = pd.DataFrame(np.random.randint(0,1000,100000000).reshape(1000000,100))
test['new'] = pd.Series(list(zip(test[1], test[2], test[3])))
test['new'].isnull().sum()
# 0

'assign' also produce the same result:

df0 = df0.assign(new_col2 = pd.Series(list(zip(df0['a'], df0['b'], df0['c']))))
df0['new_col2'].isnull().sum()
# 14334

I found two similar questions, this and this. I suspect my problem has also something to do with indexing. It seems there are 89 non-identical values:

np.sum(df0.index == new_col.index)
# 89

Assigning the same Series as the index of df0 works:

df0.index = new_col
df0['abc'] = df0.index
df0['abc'].isnull().sum()
# 0

UPDATE Here is some benchmarking for @jezreal's solutions:

%time df0['abc'] = pd.Series(list(zip(df0['a'], df0['b'], df0['c'])), index=df0.index)
Wall time: 2.32 s

% time df0['abc'] = df0[['a','b','c']].apply(tuple, axis=1)
Wall time: 1min 42s

%time df0['abc'] = df0.set_index(['a','b','c']).index.values
Wall time: 8.68 s

% time df0['abc'] = pd.Series([tuple(x) for x in df0[['a','b','c']].values.tolist()], index=df0.index)
Wall time: 9.83 s

Upvotes: 1

Views: 426

Answers (1)

jezrael
jezrael

Reputation: 862511

I think need same index as df0 of new Series for align data:

df0['abc'] = pd.Series(list(zip(df0['a'], df0['b'], df0['c'])), index=df0.index)

Or use apply:

df0['abc'] = df0[['a','b','c']].apply(tuple, axis=1)

Sample:

df0 = pd.DataFrame({'a':list('abcdef'),
                   'b':[4,5,4,5,5,4],
                   'c':[7,8,9,4,2,3],
                   'D':[1,3,5,7,1,0],
                   'E':[5,3,6,9,2,4],
                   'F':list('aaabbb')}, index=[1,1,2,2,9,10])

print (df0)
    D  E  F  a  b  c
1   1  5  a  a  4  7
1   3  3  a  b  5  8
2   5  6  a  c  4  9
2   7  9  b  d  5  4
9   1  2  b  e  5  2
10  0  4  b  f  4  3

df0['abc'] = pd.Series(list(zip(df0['a'], df0['b'], df0['c'])))


print (df0)
    D  E  F  a  b  c        abc
1   1  5  a  a  4  7  (b, 5, 8)
1   3  3  a  b  5  8  (b, 5, 8)
2   5  6  a  c  4  9  (c, 4, 9)
2   7  9  b  d  5  4  (c, 4, 9)
9   1  2  b  e  5  2        NaN
10  0  4  b  f  4  3        NaN

df0['abc'] = pd.Series(list(zip(df0['a'], df0['b'], df0['c'])), index=df0.index)

df0['abc'] = df0[['a','b','c']].apply(tuple, axis=1)


print (df0)
    D  E  F  a  b  c        abc
1   1  5  a  a  4  7  (a, 4, 7)
1   3  3  a  b  5  8  (b, 5, 8)
2   5  6  a  c  4  9  (c, 4, 9)
2   7  9  b  d  5  4  (d, 5, 4)
9   1  2  b  e  5  2  (e, 5, 2)
10  0  4  b  f  4  3  (f, 4, 3)

Upvotes: 1

Related Questions