Python read csv file with different number of rows

Question

I have a csv file with following format

x1,y1,x2,y2,x3,y3
1,1,2,2,6.5,7.5
2,2,-1,-1,,
,,-2,-3,,
,,-5,-5,,

I want to plot columns (x1,y1), (x2,y2) and (x3,y3), for example,

rd1 = some_csv_reader('filename.csv')
matplotlib.pyplot.plot(rd1[:,0],rd1[:,1],rd1[:,2],rd1[:,3])

I tried using pandas.read_csv() but it puts nan for empty entries. pandas.fwf() doesn't separate out columns. I would like to exclude any empty positions in the array during reading itself instead of using something like https://stackoverflow.com/a/11453235/11638153. How can I do that?

Answer 1

• If the point is to plot the data, select the columns in groups of two, and plot each group.
  • The list comprehension creates a list of tuples
    • [Index(['x1', 'y1'], dtype='object'), Index(['x2', 'y2'], dtype='object'), Index(['x3', 'y3'], dtype='object')]

import pandas as pd
import matplotlib.pyplot as plt

# read the csv
df = pd.read_csv('test.csv')

# select ever two columns and plot them
N = 2  # number of consecutive columns to combine
for d in [df.columns[n:n+N] for n in range(0, len(df.columns), N)]:
    x, y = d
    plt.scatter(x, y, data=df, label=y)
plt.legend()

• Note that some points are overlapping.

As a line plot

• Use markers to help differentiate the data, if desired.

markers = ['o', '*', '+']

N = 2
for i, d in enumerate([df.columns[n:n+N] for n in range(0, len(df.columns), N)]):
    x, y = d
    plt.plot(x, y, '', marker=markers[i], data=df, label=y)
plt.legend()

Combine each group of x and y into a single group

# select each group of two columns and append the dataframe to the list
df_list = list()
N = 2
for d in [df.columns[n:n+N] for n in range(0, len(df.columns), N)]:
    d = df[d]
    d.columns = ['x', 'y']  # rename columns
    df_list.append(d)

# concat the list of dataframes
dfc = pd.concat(df_list)

# clean the dataframe
dfc = dfc.dropna().drop_duplicates().sort_values('x').reset_index(drop=True)

# display(dfc)
     x    y
0 -5.0 -5.0
1 -2.0 -3.0
2 -1.0 -1.0
3  1.0  1.0
4  2.0  2.0
5  6.5  7.5

# plot
plt.plot('x', 'y', '', data=dfc)