How to write data to csv which doesn't has fix count of rows in multiple lists with help of pan-CodePudding

While writing data to csv with help of pandas, The output I got doesn't have all values. Because of unequal values present in list.

The code finds the lowest value present in the 3 list and then writes the data with exact number of rows found in the lowest list.

Here's the code:-

import os
import pandas as pd
import datetime as dt


a1 = []
b1 = []
c1 = []


for a in range(1,6):
    a1.append(a)

for b in range(1,7):
    b1.append(b)

for c in range(1,9):
    c1.append(c)

a1.append('None')
b1.append('None')
c1.append('None')


df = pd.DataFrame(zip(a1, b1, c1),
                  columns=['a', 'b', 'c'])
path = 'D:\\bhavyanew\\python_projects'
filename = dt.datetime.now().strftime("ABC_%d_%b_%y_%I_%M_%p.csv")
p1 = os.path.join(path, filename)
df.to_csv(p1, index = False)

Output I got in csv:-

a       b       c
1       1       1
2       2       2
3       3       3
4       4       4
5       5       5

None 6 6

Output I want in csv:-

a       b       c
1       1       1
2       2       2
3       3       3
4       4       4
5       5       5
None    6       6
        None    7
        None    8

Things I tried with other libraries(csv-writer) and it worked:-

pathout = 'E:\\Bhavya Lodaya\\
filename = dt.datetime.now().strftime("ABC_%d_%b_%y_%I_%M_%p.csv")
p1 = os.path.join(pathout, filename)
with open(p1, 'w') as outcsv:
    writer = csv.writer(outcsv)
    writer.writerow(["a", "b", "c"])
    for row in zip_longest(laptop, desktop, server):
        writer.writerow(row)

This code worked but it adds a blank line after every append in csv.

I want my expected output with help of pandas.

CodePudding user response：

Don't mess around with trying to figure out how many Nones to append to each list. Let pandas do work of aligning on an index by converting each list to a series first:

import pandas

a1 = []
b1 = []
c1 = []

for a in range(1,6):
    a1.append(a)

for b in range(1,7):
    b1.append(b)

for c in range(1,9):
    c1.append(c)
    
df = pandas.DataFrame({
    'a': pandas.Series(a1),
    'b': pandas.Series(b1),
    'c': pandas.Series(c1),
})

And I get:

     a    b  c
0  1.0  1.0  1
1  2.0  2.0  2
2  3.0  3.0  3
3  4.0  4.0  4
4  5.0  5.0  5
5  NaN  6.0  6
6  NaN  NaN  7
7  NaN  NaN  8

Or more consisely:

df = pandas.DataFrame({
    col: pandas.Series(numpy.arange(N))
    for col, N in [("a", 6), ("b", 7), ("c", 9)]
})