0

Picture Of Excel Data

I have a data frame that looks like this above, I'm trying to locate the force based on the split strings in the filename column. So I split the strings based on the separator "_" and created a new column and now I'm trying to locate data based on the 2nd and 4th index of the list of strings(time_split column). This is my code so far but I keep getting value errors and key errors.

df = pd.read_excel(r'Path goes here to the excel data file')
filename_list = df["File Name"].str.split('_')
new_list = list(filename_list)
df['Time_Split'] = new_list
print(df)
for filename in filename_list:
    force_values = df.loc[df["Time_Split"][1] == '1' and df["Time_Split"][3] == '60' , "Peak Force (N)"]
Quang Hoang
  • 146,074
  • 10
  • 56
  • 74
ButterMan
  • 11
  • 2
  • 1
    Please see [this question](https://stackoverflow.com/questions/20109391/how-to-make-good-reproducible-pandas-examples), do not include your data as picture. – Quang Hoang Dec 14 '21 at 20:54

1 Answers1

0

You should break what you have in "Time Split" into separate columns and filter on those new columns:

df = pd.read_excel(r'Path goes here to the excel data file')
df["filter_one"] = df.apply(lambda row: row["File Name"].split("_")[1]
df["filter_three"] = df.apply(lambda row: row["File Name"].split("_")[3]
df_filtered = df.loc[( (df["filter_one"] == "1") & (df["filter_three"] == "3") )]
whatisreallygood
  • 82
  • 3