How to handle inconsistent columns of CSV

user_9830821 0
Viewed 287

My CSV data looks like this:

ID;name;info
1;ABC;text1
2;DEF;text2;text3
3;GHI;text4;
4;JKL;text5;text6;text7

There are 3 named columns. The additional unnamed columns all relate to the last one (info), and the amount of those additional columns is not known.

Using df=pd.read_csv(filename, delimiter=";", dtype=object) returns a "Error tokenizing data. C error..." due to irregular shape.

Is it possible to merge the last columns into one column containing a list, to achieve the result below?

ID;name;info
1;ABC;[text1]
2;DEF;[text2, text3]
3;GHI;[text4]
4;JKL;[text5, text6, text7]
pandas csv python
3 Answers

You can read the entire file as one column, then use pd.Series.str.split with n (max_split) argument:

>>> df = pd.read_csv('sample.csv', header=None)
                         0
0             ID;name;info
1              1;ABC;text1
2        2;DEF;text2;text3
3             3;GHI;text4;
4  4;JKL;text5;text6;text7
>>> df = df[0].str.split(';', n=2, expand=True)
>>> df
    0     1                  2
0  ID  name               info
1   1   ABC              text1
2   2   DEF        text2;text3
3   3   GHI             text4;
4   4   JKL  text5;text6;text7
>>> df.columns = df.loc[0].tolist()
>>> df = df.drop(0).reset_index(drop=True)
>>> df['info'] = df['info'].str.strip(';').str.split(';')
>> df
  ID name                   info
0  1  ABC                [text1]
1  2  DEF         [text2, text3]
2  3  GHI                [text4]
3  4  JKL  [text5, text6, text7]

Alternatively,

>>> with open('sample.csv') as fh:
        header = next(fh).strip().split(';')
        df = pd.DataFrame([line.strip().split(';',2) for line in fh], columns=header)
>>> df['info'] = df['info'].str.strip(';').str.split(';')
>>> df
  ID name                   info
0  1  ABC                [text1]
1  2  DEF         [text2, text3]
2  3  GHI                [text4]
3  4  JKL  [text5, text6, text7]
user_54317910

Here is a general way where we count the number of delimiters in the columns and based on that construct the dataframe:

data = pd.read_csv("text.csv")
n_sep = data.columns[0].count(";")
headers = data.columns.str.split(";")[0]

data[headers] = data.iloc[:, 0].str.split(";", n=n_sep, expand=True)
data = data.iloc[:, 1:].assign(info=data['info'].str.split(";"))

  ID name                   info
0  1  ABC                [text1]
1  2  DEF         [text2, text3]
2  3  GHI              [text4, ]
3  4  JKL  [text5, text6, text7]
user_90812670

Unfortunately, pd.read_csv expects the number of fields to be constant in a file. But the standard csv module does not. So you could use:

with open(filename) as fd:
    rd = csv.reader(fd, delimiter=';')
    fn = next(rd)  # extract the headers line
    rd = csv.DictReader(fd, delimiter=';', fieldnames=fn[:-1], restkey=fn[-1])
    df = pd.DataFrame((row for row in rd), columns = fn)

It gives:

  ID name                   info
0  1  ABC                [text1]
1  2  DEF         [text2, text3]
2  3  GHI              [text4, ]
3  4  JKL  [text5, text6, text7]

The main drawback is that the csv module keeps everything as strings, so df.dtypes is:

ID      object
name    object
info    object
dtype: object
user_35452730
Related