0

I've been trying to get all my data from a dynamodb database with boto3. After receiving all the data im trying to export the data to .csv The problem is that all my decimal numbers loose their comma. For example 3.457 becomes : 3457 Which is a big difference for further calculations. I tried to use df.values.round(4) but that does nothing. When im printing the dataframe to the console i can see that all values are correct. Something goes wrong with the exporting part.

This is the code that i'm using:

#Get all items from Dynamodb
response = vopak_table.scan()
data = response['Items']

while 'LastEvaluatedKey' in response:
    response = vopak_table.scan(ExclusiveStartKey=response['LastEvaluatedKey'])
    data.extend(response['Items'])
    
#Write data to CSV 
df = pd.DataFrame(data)

#Time column first in sheet
first_column = df.pop('time')
df.insert(0,'time',first_column)
#Save as CSV
df.to_csv(r'C:\***\export.csv',index=False, header=True)
print(df)

Printing to the console the result is like this: before the save : before save after the save : 3: after save

Thanks!

UwUs
  • 67
  • 1
  • 9
  • Are you using comma or dot as a decimal seperator? You are talking about disappearing commas but your screenshot shows dots. The issue might be that a dot is a 1000 seperator, and therefore disappears. If you need them dots as decimal seperator, you can try convert them to comma or specify decimal = '.' in your to_csv as a parameter. – gittert Dec 20 '22 at 14:46
  • yea i tried that already also with no succes. Im using dots as separator – UwUs Dec 20 '22 at 14:56
  • seems you opened the csv in excel? try opening it in a text editor and tell us if there are still dots there? Excel might see the dot as a 1000 seperator and not show this unless you change the cell format first. – gittert Dec 20 '22 at 15:08
  • Alright when opening it in text editor it shows the data correct but it uses indeed the dot as seperator – UwUs Dec 20 '22 at 15:14
  • great. works as designed ;). you might want to switch to comma as decimal seperator or have a look as excel and its import functionality/cell formatting. – gittert Dec 20 '22 at 15:17
  • Any suggestions on how to do that ? – UwUs Dec 20 '22 at 15:19
  • I'm no excel expert, but changing dots to comma's is asked before on SO (a lot), your google is as good as mine. But have you tried to save as comma with by adding decimal = ',' to your line of code here df.to_csv(....) – gittert Dec 20 '22 at 15:24
  • Nahh very strange but that does nothing. – UwUs Dec 20 '22 at 15:30
  • you mean in text editor, right? otherwise look at something like this: https://stackoverflow.com/questions/31700691/convert-commas-decimal-separators-to-dots-within-a-dataframe. – gittert Dec 20 '22 at 15:41
  • Have no clue anymore , nothing changed even with parameters in the export function : sep=',',decimal='.',float_format='%.2f' – UwUs Dec 20 '22 at 15:48
  • 1
    Try sep =';' and decimal=',' ? And check result in a text editor. – gittert Dec 20 '22 at 18:33
  • alright seems to work! thanks! i guess it's something with the american system and the european that uses dots and commas for other purposes ? – UwUs Dec 21 '22 at 23:03
  • Geat! And yes that is correct. Decimal seperator and 1000 seperator are just the other way around. – gittert Dec 22 '22 at 06:21

0 Answers0