Strange output using Pandas when converting StringIO with read_csv

Question

When the pandas CSV reader function 'read_csv" is used to convert StringIO values strange characters ('.1') are being appended at the end of the second field when delimiting certain fields. The desired results is the first test, but all the fields do not have spaces after the delimiter (','). Splitting "1.5M, 1.5M" should always return "1.5M", but when there is no spaces it returns the second field with "1.5M.1" (adding '.1' at the end of the field). Is there a way to resolve this issue?

>>>import pandas as pd
>>>from io import StringIO
>>>pd.read_csv(StringIO("1.5M, 1.5M"))
Empty DataFrame
Columns: [1.5M,  1.5M]
Index: []
>>> pd.read_csv(StringIO("1.5M,1.5M"))
Empty DataFrame
Columns: [1.5M, 1.5M.1]
Index: []
>>>

Scott Boston · Accepted Answer · 2017-10-07 22:12:53Z

3

Notice in the first example with the space, your dataframe has zero rows and your column names include the space in the second column.

 df = pd.read_csv(StringIO("1.5M, 1.5M"))
 df.columns

 Index(['1.5M', ' 1.5M'], dtype='object')

In the second case, zero rows also, but you have duplicate column names without the space.

 df = pd.read_csv(StringIO("1.5M,1.5M"))
 df.columns

 Index(['1.5M', '1.5M.1'], dtype='object')

Hence, Pandas adds the '.1' to the duplicated column name.

However, if you want this '1.5M' as data in the dataframe and not as column headings.

Use

df = pd.read_csv(StringIO("1.5M, 1.5M"), header=None)

OR, it doesn't make a difference in this case:

df = pd.read_csv(StringIO("1.5M,1.5M"), header=None)

Output:

      0     1
0  1.5M  1.5M

edited Oct 7, 2017 at 22:12

answered Oct 7, 2017 at 22:05

Scott Boston

154k15 gold badges160 silver badges207 bronze badges

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

2 Comments

NobleVision Over a year ago

Thank you Scott for the breakdown.

Benison Sam Over a year ago

@Scott Boston: Could you please look into my question: stackoverflow.com/questions/72490042/…

Collectives™ on Stack Overflow

Strange output using Pandas when converting StringIO with read_csv

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related