Dtype STR but output in Shell still int?

[johnjfk]

I'm fairly new in python so it happens that on certain problems I'm getting really stuck from time to time. I have already checked the web not finding a proper solution. I'm importing a CSV file having one column defined as (STR) with test_df=pd.read_csv(('Mappe41.csv'), dtype={'Payment Transaction ID':'str'}......

Once I'm running the code the output on python shell and subsequently in Excel shows still the data as int. Any idea why? here below the output

4528 2.00804E+17
4529 2.00804E+17
4530 2.00804E+17
4531 2.00805E+17
Name: Payment Transaction ID, Length: 4532, dtype: object

[hshivaraj]

Could you show us the first few lines of your CSV file?

[johnjfk]

sure..

here we go




hope this helps!

[hshivaraj]

Thanks. Thats the source code.
But i was asking for the CSV file your reading?

[johnjfk]

sorry
here we go mate..


Payment Transaction ID Bestellen Referenzcode Datum hinzufgen Bestell-Nr. Kunden-Nr. Kunden Name Kunden Vorname Zahlungsart Gesamt brutto Gesamt (netto) Total paid Tax
XKBKNABJK 28/07/2017 14:47 1 1 Payment by check 55 55 0
OHSATSERP 28/07/2017 14:47 2 1 Payment by check 75.9 75.9 0
UOYEVOLI 28/07/2017 14:47 3 1 Payment by check 76.01 76.01 0
FFATNOMMJ 28/07/2017 14:47 4 1 Payment by check 89.89 89.89 0
KHWLILZLL 28/07/2017 14:47 5 1 Bank wire 71.51 71.51 0
DZFHJCNJQ 20/08/2017 19:44 6 3 Bankberweisung 48.9 47.93 0.97
CQTYQBKZT 20/08/2017 20:32 7 3 Bankberweisung 0.2 0.2 0
IJVZLBKPD 21/09/2017 10:32 8 3 Kredit Karte 46.9 45.93 0.97
AAOQDUGZZ 21/09/2017 23:26 9 3 Kredit Karte 46.9 45.41 1.49

[deanhystad]

I do not see any proof that transaction ID is an integer. 4528 could be an integer or a string. To verify you should have your program print the type (use type(thing)).

You have not provided enough information to know, but I suspect that Excel is doing the conversion from string to int. When Excel opens a CSV file it sets the data type for each column based on the column contents. If everything in a column can be an integer, the column is converted to integers. So if your CSV file looks like this:

Output:
4528 2.00804E+17
4529 2.00804E+17
4530 2.00804E+17
4531 2.00805E+17

Excel will convert the first column to ints.

To stop this you can use "=""value""" to force Excel to accept value as a string.

Output:
4528 2.00804E+17
"=""4529""" 2.00804E+17
"=""4530""" 2.00804E+17
"=""4531""" 2.00805E+17

[johnjfk]

thanks for your reply

the first column is just the index i.e. row 4528
so the '2.00804E+17' must be visualized as str.

hope this helps for further investigation.

Best

4528 2.00804E+17
4529 2.00804E+17
4530 2.00804E+17
4531 2.00805E+17
Name: Payment Transaction ID, Length: 4532, dtype: object

---

Strangely that by asking the dtypes of the colums it is showing the correct data type ??!!

Payment Transaction ID string
MwSt float64
Zahlungsart object
Gesamt Versandkosten (inkl. MwSt.) float64
Sollkonto int32
Habenkonto int64