Jul-29-2020, 05:16 PM
Hello,
I have a txt file, so when I open it, everything is under 1 column. I want to convert to excel and break the data up and only use column 0, and 9. My code almost works, but the data I'm getting under column 9 isn't totally correct. Under column 9 instead of getting 34 or 222, I'm getting letters N or M from prior columns. Column 9 contains miles so it should all be numbers no letters. I believe this is happening because of the data that would be under column 1. Example of data that would appear under column 1:
NYK13
SIG 222/222 A
SIG 444/555 B
NYK17
Due to the spaces in some of the words (SIG 222/222/ A) it's shifting the data over into other columns. When its NYK13 I'm receiving the correct data for column 9. When it's SIG 22/22/ A I'm receiving that data that would be under column 8. Is there a way to stop this from happening?
I have a txt file, so when I open it, everything is under 1 column. I want to convert to excel and break the data up and only use column 0, and 9. My code almost works, but the data I'm getting under column 9 isn't totally correct. Under column 9 instead of getting 34 or 222, I'm getting letters N or M from prior columns. Column 9 contains miles so it should all be numbers no letters. I believe this is happening because of the data that would be under column 1. Example of data that would appear under column 1:
NYK13
SIG 222/222 A
SIG 444/555 B
NYK17
Due to the spaces in some of the words (SIG 222/222/ A) it's shifting the data over into other columns. When its NYK13 I'm receiving the correct data for column 9. When it's SIG 22/22/ A I'm receiving that data that would be under column 8. Is there a way to stop this from happening?
Node1 = pd.read_csv('MMM.NODE', sep='\t',engine='python')
Node1.to_excel('Node.xlsx', 'Sheet1',index=False)
Node2 = pd.read_excel('Node.xlsx',header=None,skiprows=10,names=['A'])
Node3 = Node2['A'].str.split(expand=True)[[0,9]]
Node3.columns = ['Node','Mile']
Node3.to_excel('Node.xlsx',index=False)