CODEWITHSUNDEEP
pythonpandas
babis95
babis95

Reputation: 622

Pandas - Append data to specific columns

I need to append data from a list to a specific column. I have 4 lists:

orderNumber = ['123456789']
countryOfOrigin = ['United Kingdom']
sizeList = ['2', '4']
quantityList = ['10', '12']

I also have a CSV file with following headers:

OrderNumber   COO   Size   QTY

I need these lists appended to this CSV in their correct columns. And if list only has one item in the data is simply dublicated to fill all rows

OrderNumber        COO         Size   QTY
123456789     United Kingdom    2     10
123456789     United Kingdom    4     20

This is what I tried. But all it does it adds everything one under another in the same column

orderNumber = ['123456789']
countryOfOrigin = ['United Kingdom']
sizeList = ['2', '4']
quantityList = ['10', '12']

header = ["OrderNumber", "COO", "Size", "QTY"]
order = pd.DataFrame(orderNumber)
order.to_csv('C:/Users/user/Desktop/Pandas-Example2.csv', mode='a', index=False)
size = pd.DataFrame(sizeList, columns=["Size"])
size.to_csv('C:/Users/user/Desktop/Pandas-Example2.csv', mode='a', index=False)

Any idea how this could be fixed?

Upvotes: 1

Views: 8434

Answers (1)

jezrael
jezrael

Reputation: 862511

If possible use instead one element lists only scalars, create dictionary of scalars/lists and pass to DataFrame constructor - all lists has to be with same lengths:

orderNumber = '123456789'
countryOfOrigin = 'United Kingdom'
sizeList = ['2', '4']
quantityList = ['10', '12']

d = {"OrderNumber": orderNumber, "COO":countryOfOrigin, "Size":sizeList, "QTY":quantityList}
order = pd.DataFrame(d)
print (order)
  OrderNumber             COO Size QTY
0   123456789  United Kingdom    2  10
1   123456789  United Kingdom    4  12

If inputs are always lists you can use some preprocessing:

orderNumber = ['123456789']
countryOfOrigin = ['United Kingdom']
sizeList = ['2', '4']
quantityList = ['10', '12']

vals = [orderNumber, countryOfOrigin, sizeList, quantityList]
header = ["OrderNumber", "COO", "Size", "QTY"]

d = {a: b[0] if len(b) == 1 else b for a, b in zip(header, vals)}
order = pd.DataFrame(d)
print (order)
  OrderNumber             COO Size QTY
0   123456789  United Kingdom    2  10
1   123456789  United Kingdom    4  12

Upvotes: 3

Related Questions