How can I add the "X" or "Y" value from a line above to the line that is missing the value?

Question

I have a .csv file that has this structure:

X310.433,Y9.6

X310.54,Y10

X143.52

X144.77

when there is no "X" or "Y" value in a line, I want to take the value from the line above and copy it to the line after that, that is missing the value. For this example copy the Y10 into the next line, and seperate it with a comma. How can i do this with python?

Answer 1

Without any utility modules you could do this:

Let's assume that the file content is:

X310.433,Y9.6
Y999
X310.54,Y10

X143.52

X144.77

...then...

lines: list[tuple[str, str]] = []

with open("foo.csv") as foo:
    for line in map(str.strip, foo):
        if line:
            a, *b = line.split(",")
            if a[0] == "X":
                if b:
                    lines.append((a, b[0]))
                else:
                    lines.append((a, lines[-1][1]))
            else:
                assert a[0] == "Y"
                if b:
                    lines.append((b[0], a))
                else:
                    lines.append((lines[-1][0], a))
for line in lines:
    print(",".join(line))

Output:

X310.433,Y9.6
X310.433,Y999
X310.54,Y10
X143.52,Y10
X144.77,Y10

Note:

If the first line of the file contains either one of X or Y (but not both) this will fail

EDIT:

More robust version that rewrites the original file:

with open("foo.csv", "r+") as foo:
    lines: list[tuple[str, str]] = []
    for line in map(str.strip, foo):
        if line:
            a, *b = line.split(",")
            if a.startswith("X"):
                y = b[0] if b else lines[-1][1]
                lines.append((a, y))
            elif a.startswith("Y"):
                x = b[0] if b else lines[-1][0]
                lines.append((x, a))
    foo.seek(0)
    for line in lines:
        print(",".join(line), file=foo)
    foo.truncate()

Answer 2

You could simply store the latest seen x and y values in a variable -

csv_str = '''
X310.433,Y9.6 
X310.54,Y10   
X143.52       
X144.77       
,Y71.21       
Y27           
'''
csv_file = io.StringIO(csv_str) # You'd probably do csv_file = open('fielname.csv', 'r') here instead of io.StringIO
for line in csv_file:
    if line.strip():
        xvals = [val for val in line.strip().split(',') if val.startswith('X')]
        yvals = [val for val in line.strip().split(',') if val.startswith('Y')]
        if xvals:
            latest_x = xvals.pop()
        if yvals:
            latest_y = yvals.pop()
        print(latest_x, latest_y, sep=',')

Output

X310.433,Y9.6
X310.54,Y10
X143.52,Y10
X144.77,Y10
X144.77,Y71.21
X144.77,Y27

Alternatively, you could use the re module instead of the startswith - I find this more readable, but YMMV -

for line in csv_file:
    if line.strip():
        xvals = re.search(r'X\d+\.?\d+', line.strip())
        yvals = re.search(r'Y\d+\.?\d+', line.strip())
        if xvals:
            latest_x = xvals.group()
        if yvals:
            latest_y = yvals.group()
        print(latest_x, latest_y, sep=',')

Answer 3

import pandas as pd

df = pd.read_csv('a.csv', names=['X', 'Y'])
df.fillna(method='ffill', inplace=True)
df.to_csv('ret.csv', index=False, header=False)

Answer 4

Try this:

import pandas as pd

df = pd.read_excel('loc to your csv/your_csv.xlsx')  

print(df)
df['a'] = df['a'].apply(lambda x: x.split(','))
print(df)
x1 = df.loc[0, 'a'][0].split(',')[0][1:]
y1 = df.loc[0, 'a'][1].split(',')[0][1:]
x2 = df.loc[1, 'a'][0].split(',')[0][1:]
y2 = df.loc[1, 'a'][1].split(',')[0][1:]
m=(float(y2)-float(y1))/(float(x2)-float(x1))
c=float(y1)-(m*float(x1))

y3=(m*float(df.loc[2,'a'][0][1:]))+c
y4=(m*float(df.loc[3,'a'][0][1:]))+c

print(f'{y3}{y4}')

df.loc[2]='X'+str(df.loc[2,'a'][0][1:])+'Y'+str(y3)
df.loc[3]='X'+str(df.loc[3,'a'][0][1:])+'Y'+str(y4)


df.to_csv('loc to output/output.csv', index=False)