Adding a column with a calculation to multiple CSVs

user_14639523 0
Viewed 63

I'm SUPER green to Python and am having some issues trying to automate some calculations.

I know that this works to add a new column called "Returns" that divides "value" of current to "value" of previous to a csv:

import pandas as pd
import numpy as np
import csv
a = pd.read_csv("/Data/a_data.csv", index_col = "time")    
a ["Returns"] = (a["value"]/a["value"].shift(1) -1)*100

However, I have a lot of these CSVs. I need this calculation to happen prior to merging them all together. So I was hoping to write something that just looped through all of the CSVs and did the calculation and added the column but clearly this was incorrect as I get Syntax error:

import pandas as pd
import numpy as np
import csv
a = pd.read_csv("/Data/a_data.csv", index_col = "time")
b = pd.read_csv("/Data/b_data.csv", index_col = "time")
c = pd.read_csv("/Data/c_data.csv", index_col = "time")
my_lists = ['a','b','c']

for my_list in my_lists:
    {my_list}["Returns"] = ({my_list}["close"]/{my_list}["close"].shift(1) -1)*100
    print(f"Calculating: {my_list.upper()}")

I'm sure there is an easy way to do this that I just haven't reached in my Python education yet, so any guidance would be greatly appreciated!

pandas
2 Answers

1.Do a, b, c data frames have the same dimension?

2.You don't need to import the CSV library because it includes in the Pandas library.

3.If you want to union data frames, you can use like this :

my_lists = [a,b,c]

and you can concatenate with this way:

result=pd.concat(my_lists)

Lastly, your calculation should be :

result["Returns"]=(result.loc[:, "close"].div(result.loc[:, "close"].shift()).fillna(0).replace([np.inf, -np.inf], 0))

You need to add an index-label selection (loc) function to the data frame in order to access the values. When numbers are dividing, results can be NaN(Not a Number) or infinite. Therefore, replace and fillna functions are related to NaN and Inf.

user_95714450
  1. Assuming "close" and "time" are fields defined in each of your csv files, you could define a function that reads each file, do the shift and returns a dataframe:
def your_func(my_file):  # this function takes a file name as an argument. 
    my_df = pd.read_csv(my_file, index_col = "time")  # The function reads its content into a data frame,
    my_df["Returns"] = (my_df["close"]/{my_df}["close"].shift(1) -1)*100  #  makes the calculation 
    return my_df   #and returns it as an output.
  1. Then as a main code, you collect all csv files from a folder with glob package. Using the above function, you build a data frame for each file with the calculation done.
    import glob  
    path =r'/Data/'  # path to the directory where you have the csv files
    filenames = glob.glob(path + "/*.csv")  # grab the csv files names using glob package with path+all csv files present 
    for filename in filenames:  # loop into all csv files names in the list of csv files present in the directory
        df= your_func (filename) # call the function, defined above block of code,  that reads the file from its name as argument, then makes the calculation and returns it. 
        print (df)

Above, there is a print of the data Frame which shows results; I am not sure what you intend to do with upper (I dont think this is a function on a data frame). Finally, this returns independent data frames with calculations done prior to other or final transformation.

user_54957580
Related