Pandas read_csv import column with multiple values as list

Question

I have a .csv file containing several columns of which one contains multiple values:

"column1";"column2";"column3";
some_string; 1 ; 1,2,3,4 ;

Now, I would like to import the file using pandas read_csv:

import pandas as pd
df = pd.read_csv('file.csv', sep=';')

This obviously leads to the issue that the multiple values get imported as a single string object. Is there any way I could import these values as a list or an array that I could even call a single value in that cell? That would be tremendously helpful!

Thank you in advance!

You can convert the string to a list as post-load step. using df.column3.str.split() — Mohamed Ali JAMAOUI
– Mohamed Ali JAMAOUI, Commented Feb 26, 2020 at 9:47

Ruthger Righart · Accepted Answer · 2020-02-26 10:50:30Z

1

You can do this after loading the data, you would need a lambda function:

df['column3'] = df['column3'].apply(lambda x: str(x).split(","))

Check type of cell, this gives list:

type(df.iloc[0,2])

Inspect the length, this is 4:

len(df.iloc[0,2])

Select an element in your list:

df.iloc[0,2][0]

Turn the list elements in integers:

df['column3'] = df['column3'].apply(lambda x: list(map(int, x)))

Inspect type:

type(df.iloc[0,2][0])

edited Feb 26, 2020 at 10:50

answered Feb 26, 2020 at 10:06

Ruthger Righart

4,9692 gold badges32 silver badges35 bronze badges

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

2 Comments

nic Over a year ago

That's great and it works! The only thing I've observed using type(df.iloc[0,2][0]) that the elements within the list are indeed stored as a string not an integer. Can I do something about that as well? I mean for calculations, I could simply call it with int(dfSimp.iloc[0,2][0]) but the value itself would remain as a string.

Ruthger Righart Over a year ago

@nic: it is my pleasure! I have added a line of code that turns the elements into integers.

Sayandip Dutta · Accepted Answer · 2020-02-26 09:57:26Z

0

You can try converters argument of pd.read_csv to pass a function for the specified col:

>>> pd.read_csv(s, sep=';', usecols = range(3), 
                converters = {'column3':lambda x:x.split(',')})

       column1  column2         column3
0  some_string        1  [ 1, 2, 3, 4 ]
# or,
>>> pd.read_csv(s, sep=';', 
                converters = {'column3':lambda x:x.split(',')}).dropna(1)
       column1  column2         column3
0  some_string        1  [ 1, 2, 3, 4 ]

edited Feb 26, 2020 at 9:57

answered Feb 26, 2020 at 9:50

Sayandip Dutta

15.9k4 gold badges27 silver badges57 bronze badges

Collectives™ on Stack Overflow

Pandas read_csv import column with multiple values as list

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related