DataFrame conversion with combining multiple columns as a single array column

Question

I am new to python. I have a pandas dataframe as follows (with 4 columns) in Python version 3.7.4:

df = pd.DataFrame({'Patient_Key': [2333836, 2319735],
                   'DX1': ["N184", 'Z6827'],
                   'DX2': ['D649', 'N184'],
                   'DX3': ['E785', 'I10']})

   Patient_Key    DX1   DX2   DX3
0      2333836   N184  D649  E785
1      2319735  Z6827  N184   I10

How to we convert this to a new dataframe with only 2 columns?

-- Expected Conversion
2333836, ["N184", "D649", "E785"]
2319735, ["Z6827", "N184", "I10"]

You want to convert to a column of arrays or to a column of lists? — Bill
– Bill, Commented Jul 23, 2021 at 18:28

akuiper · Accepted Answer · 2021-07-23 18:34:54Z

4

Filter DX columns and convert each row to a list with apply:

df[['Patient_Key']].join(
  df.filter(regex='DX').apply(pd.Series.tolist, 1).rename('DX')
)

   Patient_Key                  DX
0      2333836  [N184, D649, E785]
1      2319735  [Z6827, N184, I10]

Or convert DX columns sub dataframe to a list and then assign it to a columnn:

df['DX'] = df.filter(regex='DX').values.tolist()
df[['Patient_Key', 'DX']]

   Patient_Key                  DX
0      2333836  [N184, D649, E785]
1      2319735  [Z6827, N184, I10]

edited Jul 23, 2021 at 18:34

answered Jul 23, 2021 at 18:28

akuiper

216k33 gold badges362 silver badges379 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Ch3steR Over a year ago

You can directly use list instead of pd.Series.tolist.

akuiper Over a year ago

@Ch3steR Yep. list works too and is more concise here.

akuiper Over a year ago

It could be a display issue. Do you need the column as string type or you need it to be the format when writing to csv ?

akuiper Over a year ago

If you need it as a string, then you can json.dumps the list, I believe. Something like: import json; df.filter(regex='DX').apply(lambda s: json.dumps(s.to_list()), 1)

Ch3steR Over a year ago

@LCJ Then you can try df['DX'] = df['DX'].astype('str') to make it a string.

|

Collectives™ on Stack Overflow

DataFrame conversion with combining multiple columns as a single array column

1 Answer 1

7 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related