Two text files, want to add a column value

[deanhystad]

You read the pandas documentation that describes how to use the read_csv and write_csv functions. When I read them again I realized the command to write a dataframe to a csv file is df.to_csv(), not df.write_csv().

When correcting file writing I noticed that the datetime column is messed up when adding the two dataframes. And I don't like that there is a requirement that the files have identical datetimes. The code below merges the two dataframes based on their date so you end up with a new dataframe that has three columns. Then I add the two "Value" columns to get a third. First I merge the dataframes using the "Date" column to decide how to merge the frames. If a data is only in one frame I want it included, so I set "how" to "outer".

import pandas as pd
import io

f1 = io.StringIO("""
20220617 14:30:00, 1.1
20220617 14:40:00, 1.3
20220617 14:50:00, 1.7
20220617 15:05:00, 1.4
""")

f2 = io.StringIO("""
20220617 14:30:00, 2.1
20220617 14:40:00, 2.3
20220617 14:50:00, 2.7
20220617 15:00:00, 2.4
""")

df1 = pd.read_csv(f1, names=("Date", "Value"))  # can replace StringIO with a file path
df2 = pd.read_csv(f2, names=("Date", "Value"))
df3 = df1.merge(df2, on="Date", how="outer")
print(df3)

Output:
0  20220617 14:30:00      1.1      2.1
1  20220617 14:40:00      1.3      2.3
2  20220617 14:50:00      1.7      2.7
3  20220617 15:05:00      1.4      NaN
4  20220617 15:00:00      NaN      2.4

Notice that Python fills empty cells with Nan (Not a Number). I cannot add numbers and NaN's, so I convert the NaN's to 0.0 before I sum the value columns to make a new column named "Total".

df3 = df3.fillna(0.0)
df3["Total"] = df3["Value_x"] + df3["Value_y"]
print(df3)

Output:
                Date  Value_x  Value_y  Total
0  20220617 14:30:00      1.1      2.1    3.2
1  20220617 14:40:00      1.3      2.3    3.6
2  20220617 14:50:00      1.7      2.7    4.4
3  20220617 15:05:00      1.4      0.0    1.4
4  20220617 15:00:00      0.0      2.4    2.4

Now it is time to write the combined frames to a csv file. You only want to write the "Date" and "Total" columns, and you want to remove the header and index column. Luckily you can specify all this in the to_csv() call.

df3.to_csv("data.csv", columns=("Date", "Total"), header=False, index=False)

20220617 14:30:00,3.2
20220617 14:40:00,3.5999999999999996
20220617 14:50:00,4.4
20220617 15:05:00,1.4
20220617 15:00:00,2.4

Reading the documentation not only fixed the write error, but resulted in a better solution. Now you can sum files with arbitrary timestamps and files of differing lengths.