更改小数点分隔符(Python，Sqlite，Pandas) [英] Change decimal separator (Python, Sqlite, Pandas)

问题描述

我有一个Excel电子表格(这是从SAP提取的)，其中包含数据(文本，数字).我将这些数据转换为DataFrame，进行一些计算，最后将其保存到sqlite数据库中. 而Excel电子表格使用逗号作为小数点分隔符. sqlite数据库包含带点的数字作为小数点分隔符.

I have an Excel spreadsheet (which is an extract from SAP) which has data in it (text, numbers). I turn this data into a DataFrame, do some calculations and finally save it to a sqlite database. Whereas the excel spreadsheet has a comma as the decimal separator. The sqlite database includes the numbers with a dot as the decimal separator.

从代码中提取:

df_mce3 = pd.read_excel('rawdata/filename.xlsx', header=0, converters={'supplier': str, 'month': str}, decimal=',')

(decimal ='，'是建议的解决方案，仅在使用csv时有效)

(decimal=',' was a suggested solution which only works when you are working with csv)

计算之后，我使用以下代码将结果保存到sqlite数据库中:

After doing the calculations I use the following code to save the results to a sqlite database:

conn = sqlite.connect("database.db")
df_mce3.to_sql("rawdata", conn, if_exists="replace")
df_ka_ext.to_sql("costanalysis_external", conn, if_exists="replace")
[...]

输入:

month   ordqty  ordprice    ordervolume invoiceqty  invoiceprice    
08.2017 10,000  14,90       149,00      10,000      14,90

输出:

month   ordqty  ordprice    ordervolume invoiceqty  invoiceprice    
08.2017 10.000  14.90       149.00      10.000      14.90

我确实需要这些数字与输入数据具有相同的小数点分隔符，但我找不到解决方法.

I do need those numbers to have the same decimal separator as the input data and I cannot find a way to do this.

因此，我要问你们中的某人是否有实现该想法的想法?

我在Mac OS X上将Python 3.5与pandas(0.19.1)和numpy(1.11.2)结合使用.

I am using Python 3.5 with pandas (0.19.1) and numpy (1.11.2) on Mac OS X.

谢谢！

推荐答案

在保存之前，您需要转换float值.只需使用包含.的值在列中循环，将每个值转换为字符串，然后就可以使用replace方法.

You need to convert the float values before saving. Just loop through the column with values containing ., convert each value to string and then you can use replace method.

这里我转换了列x

df['x'] = [str(val).replace('.', ',') for val in df['x']]
df.to_sql('rawdata', conn, if_exists='replace')

这篇关于更改小数点分隔符(Python，Sqlite，Pandas)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！