So, i'm a total noob when it comes to programming, especially python. I'm trying to merge two files(based on my requirements) and store the result into an output file. I am easily being able to do the merge, but the issue i'm facing is with the data format. You see, the data in the output file must be in the same format as the input file
These are my input files:
File1:
ID,CLASS,BOARD_ID
3620694,Smart,233049933699
3620724,Smart,233200309044
3620819,Smart,233200971094
3620831,Smart,233201075614
3620865,Smart,233201516374
3620870,Smart,233201553354
3620906,Smart,233201863244
3620929,Smart,233201972254
3620963,Smart,233202244014
3621008,Smart,233202600234
3621107,Smart,233203534474
3621158,Smart,233204019454
3621179,Smart,233204093854
3621252,Smart,233204801364
3621254,Smart,233204815324
3621266,Smart,233205000144
3621275,Smart,233205104774
3621288,Smart,233205182584
File2:
CDUS,CBSCS,CTRS,CTRS_ID
0,0,0.000000000375,233010056572
0,0,4.0746,233200309044
0,0,0.6182,233200971094
0,0,15.4834,233201075614
0,0,2.2459,233201516374
0,0,0.148,233201553354
0,0,0.0468,233201863244
0,0,0.5045,233201972254
0,0,0.0000000000485,233049933699
So this is my python script:
import pandas
#pandas.set_option('display.precision',13)
csv1 = pandas.read_csv('OUTPUT_1707000867_BundleCrossCellData_45432477_0_0.txt',dtype={'BOARD_ID': str})
csv2 = pandas.read_csv('I2.txt',dtype={'CTRS_ID': str}).rename(columns={'CTRS_ID':'BOARD_ID'})
merged = pandas.merge(csv1, csv2,left_on=['BOARD_ID'],right_on=['BOARD_ID'],how='left',suffixes=('#x', '#y'), sort=True)
merged.to_csv("Op2.txt", index=False,date_format='%Y/%m/%d %H:%M:%S.000',float_format='%.13f')
Output received upon execution:
Op.txt
ID,CLASS,BOARD_ID,CDUS,CBSCS,CTRS
3620694,Smart,233049933699,0.0000000000000,0.0000000000000,0.0000000000485
3620724,Smart,233200309044,0.0000000000000,0.0000000000000,4.0746000000000
3620819,Smart,233200971094,0.0000000000000,0.0000000000000,0.6182000000000
3620831,Smart,233201075614,0.0000000000000,0.0000000000000,15.4834000000000
3620865,Smart,233201516374,0.0000000000000,0.0000000000000,2.2459000000000
3620870,Smart,233201553354,0.0000000000000,0.0000000000000,0.1480000000000
3620906,Smart,233201863244,0.0000000000000,0.0000000000000,0.0468000000000
3620929,Smart,233201972254,0.0000000000000,0.0000000000000,0.5045000000000
3620963,Smart,233202244014,,,
3621008,Smart,233202600234,,,
3621107,Smart,233203534474,,,
3621158,Smart,233204019454,,,
3621179,Smart,233204093854,,,
3621252,Smart,233204801364,,,
3621254,Smart,233204815324,,,
3621266,Smart,233205000144,,,
3621275,Smart,233205104774,,,
3621288,Smart,233205182584,,,
Expected Output:
ID,CLASS,BOARD_ID,CDUS,CBSCS,CTRS
3620694,Smart,233049933699,0,0,0.0000000000485
3620724,Smart,233200309044,0,0,4.0746000000000
3620819,Smart,233200971094,0,0,0.6182000000000
3620831,Smart,233201075614,0,0,15.4834000000000
3620865,Smart,233201516374,0,0,2.2459000000000
3620870,Smart,233201553354,0,0,0.1480000000000
3620906,Smart,233201863244,0,0,0.0468000000000
3620929,Smart,233201972254,0,0,0.5045000000000
3620963,Smart,233202244014,,,
3621008,Smart,233202600234,,,
3621107,Smart,233203534474,,,
3621158,Smart,233204019454,,,
3621179,Smart,233204093854,,,
3621252,Smart,233204801364,,,
3621254,Smart,233204815324,,,
3621266,Smart,233205000144,,,
3621275,Smart,233205104774,,,
3621288,Smart,233205182584,,,
As you can see in Op.txt, values of CDUS and CBSCS are not in the same format as in File2.txt. For obvious reasons both have to be in same format.
Is there a way to resolve this issue?
Also since the input files are being generated at runtime, i cannot statically type-cast a certain column to a certain type.
I also referenced following links, but didnt find an appropriate solution. Any help would be very much appreciated.
How do I suppress scientific notation in Python? Force python to not output a float in standard form / scientific notation / exponential form Casting float to string without scientific notation supress scientific notation when writing python floats to files Suppressing scientific notation in pandas?
