CODEWITHSUNDEEP
pythoncsvpandas
djohns
djohns

Reputation: 41

pandas read_csv is putting all values in one column and one row

I've sought out an answer on multiple forums and YouTube but to no avail, sorry in advance if it is widely available and my keywords just weren't right.

I'm attempting to execute a simple pandas.read_csv('.csv',sep=','). However, the output I'm receiving is not splitting the data out into multiple columns as I imagine it should.

I'm getting back all of my headers in one row, separated by commas. The same is true for each line item tied to the respective headers.

I've tried setting this data up in a dataframe, manipulating the headers, manually adding the headers with no success.

For better understanding I've copied and pasted from Ipython notebook of what I'm seeing:

In [15]:
import pandas as pd
pd.read_csv('C:\Users\Dale\Desktop\ShpData\TrackerTW0.csv',sep=',')

Out[15]:
PurchaseOrderNumber,ShipmentFinalDestinationCity,TransferPointCity,POType,PlannedMode,ProgramType,FreightPaymentTerms,ContainerNumber,BL/AWB#,Mode,ShipmentFinalDestinationLocation,CarrierSCAC,Carrier,Forwarder,BrandDesc,POLCity,PODCity,InDCOutlookDate,InDCOriginalDate,AnticipatedShipDate,PlannedStockedDate,ExFactoryActualDate(LT),OriginConsolActualDate(LT),DepartLoadPortActualDate(LT),FullOutGatefromOceanTerminal(CYorPort)ActualDate(LT),DPArrivalActualDate(LT),FreightAvailableActualDate(LT),DestConsolActualDate(LT),DomDepartActualDate(LT),YardArrivalActualDate(LT),CarrierDropActualDate(LT),InDCActualDate(LT),StockedActualDate(LT),Vessel,VesselETADischargePortCity,DPArrivalOutlookDate,VesselETADischargePortActualDate(LT),FullOutGatefromOceanTerminal(CYorPort)OutlookDate,StockedOutlookDate,ShipmentLeg#,Metrics,TotalShippedQty

0   1251708,Rugby,Tuticorin,Initial Order,Ocean,Re...
1   1262597,Rugby,Hong Kong,Initial Order,Ocean,Re...

Thanks

Upvotes: 4

Views: 16428

Answers (4)

Dino
Dino

Reputation: 21

I was also having the same issues. All of the columns were coming as one value. So the following worked for me.

df = pd.read_csv('/content/Reviews.csv',
                 sep=',',
                 error_bad_lines=False,
                 engine='python')

Upvotes: 1

Şenol Kurt
Şenol Kurt

Reputation: 41

It may not be the best option but it works!

Read the file as it is:

df = pd.read_csv('input.csv')

Get all the column names and assign them to a variable.

names= df.columns.str.split(',').tolist()

Split all the values by ','

df= df.iloc[:,0].str.split(',', expand=True)

Finally, assign 'names' to column names and that's it! 

df.columns = names

Upvotes: 1

Salmos
Salmos

Reputation: 21

Recently, I wanna process a csv file, the code is like this:

data = pd.read_csv(dir, sep=" ")
print(data)

the output also put all values in one row, then I just use the default "sep" value, the problem has been solved.

data = pd.read_csv(dir, sep=",")

the situation seems like different from which the asker raised, but I hope it's helpful for some other friends like me, and this is my first comment, I hope it's not too bad!

Upvotes: 2

Sitz Blogz
Sitz Blogz

Reputation: 1061

You might want to try this, you have like 40 columns. 

import pandas as pd 


df = pd.read_csv('input.csv', names=['PurchaseOrderNumber','ShipmentFinalDestinationCity','TransferPointCity','POType','PlannedMode','ProgramType','FreightPaymentTerms','ContainerNumber','BL/AWB#','Mode','ShipmentFinalDestinationLocation','CarrierSCAC','Carrier','Forwarder','BrandDesc','POLCity','PODCity','InDCOutlookDate','InDCOriginalDate','AnticipatedShipDate','PlannedStockedDate','ExFactoryActualDate(LT)','OriginConsolActualDate(LT)','DepartLoadPortActualDate(LT)','FullOutGatefromOceanTerminal(CYorPort)ActualDate(LT)','DPArrivalActualDate(LT)','FreightAvailableActualDate(LT)','DestConsolActualDate(LT)','DomDepartActualDate(LT)','YardArrivalActualDate(LT)','CarrierDropActualDate(LT)','InDCActualDate(LT)','StockedActualDate(LT)','Vessel','VesselETADischargePortCity','DPArrivalOutlookDate','VesselETADischargePortActualDate(LT)','FullOutGatefromOceanTerminal(CYorPort)OutlookDate','StockedOutlookDate','ShipmentLeg#','Metrics','TotalShippedQty']

print df

Upvotes: 2

Related Questions