CODEWITHSUNDEEP
pythonpandasdataframenan
Ignpl
Ignpl

Reputation: 39

Split dataframe list column dealing with NaN values

I have a dataframe with a column where all its elements are lists but some elements are NaN. Something like this:

Date Value
01/01/2022 0, 16
02/01/2022 0, 22
03/01/2022 0, 15
04/01/2022 0, 2
05/01/2022 NaN

I'm trying to separate the values in two others columns, one for each data of the list with the pandas function to_list. But I can't do it works having NaN in the column. I could do dropna but I need the date data. My intention is to replace NaN with 0, 0.

At the end what I want to achieve is this result, no matter how to get there:

Date Value A B
01/01/2022 0, 16 0 16
02/01/2022 4, 22 4 22
03/01/2022 8, 15 8 15
04/01/2022 8, 2 8 2
05/01/2022 NaN 0 0

Thanks!!

Upvotes: 0

Views: 861

Answers (3)

Bowen Liu
Bowen Liu

Reputation: 1127

I think Chrysophylaxs's and BENY's answers are much better than mine but I will chime in with my solution:

df = pd.DataFrame(data = [['01/01/2022', [0, 16]],
                         ['02/01/2022', [0, 22]],
                          ['05/01/2022', np.nan]
                         ], columns = ['date', 'value'])

def split_value(row):
    try:
        return pd.Series({'value_A': row['value'][0], 'value_B': row['value'][1]})
    except:
        return pd.Series({'value_A': 0, 'value_B': 0})

df[['value_A', 'value_B']] = df.apply(split_value, axis=1)

This will return result

date    value   value_A value_B
0   01/01/2022  [0, 16] 0   16
1   02/01/2022  [0, 22] 0   22
2   05/01/2022  NaN     0   0

Upvotes: 1

Chrysophylaxs
Chrysophylaxs

Reputation: 6583

Assuming your Value column is in fact type list, not str, you could:

df[["A", "B"]] = df["Value"].apply(pd.Series).fillna(0)

Upvotes: 1

BENY
BENY

Reputation: 323316

In your case just do split

out = df.join(df['Value'].str.split(', ',expand=True).fillna(0))
#df['Value'].str.split(', ',expand=True).fillna(0)
Out[34]: 
   0   1
0  0  16
1  0  22
2  0  15
3  0   2
4  0   0

Upvotes: 1

Related Questions