Substituting values in a CSV file using python

Question

I'm running a couple of simulations for a number of case studies and the results are stored in a csv file in this

/   Var1  Var2 Var3
0    x      x    x
1    x      x    x
2    x      x    x
3    x      x    x
0    x      x    x
1    x      x    x
2    x      x    x

What i'm trying to do is substitute the first column (which is the index for the simulation numbar for each case) with the case name. What I want to do is for the csv to read like this:

/       Var1  Var2 Var3
Case1    x      x    x
Case1    x      x    x
Case1    x      x    x
Case1    x      x    x
Case2    x      x    x
Case2    x      x    x
Case2    x      x    x

I've thought of adding a condition where if the number in the first column is 0 the substitute it with case, when the next 0 is found then continue with case+1 (i've got the case names in a vector)

My issue is that I don't know how to access the values in the first row and evaluate them, and subsequently substitute them. I'd imagine pandas must have a convenient way of doing something like this

I'm not sure what's the best way to do this, but I do know how to do the inverse, if that helps: df.groupby(index).cumcount(). pandas.pydata.org/pandas-docs/stable/generated/… — John Zwinck
– John Zwinck, Commented Aug 31, 2017 at 14:28
df.iloc[2, 0] will for example access the first element of the third line if this is what you are looking for. — Silveris
– Silveris, Commented Aug 31, 2017 at 14:29
@Silveris The index column, so that 0,1,2 all become Case1 and then the next 0,1,2,3 become Case2 and so on — Jethro
– Jethro, Commented Aug 31, 2017 at 14:39
What you can do is get that column as a list using new_index = df.index.values, change whatever you want in this list, and then replace the old index column like this: df.index = new_index — Silveris
– Silveris, Commented Aug 31, 2017 at 14:43

Scott Boston · Accepted Answer · 2017-08-31 14:41:06Z

1

Let's try groupby, cumcount, and set_index:

df.set_index('Case'+df.groupby(df.index).cumcount().add(1).astype(str))

Output:

      Var1 Var2 Var3
Case1    x    x    x
Case1    x    x    x
Case1    x    x    x
Case1    x    x    x
Case2    x    x    x
Case2    x    x    x
Case2    x    x    x

answered Aug 31, 2017 at 14:41

Scott Boston

154k15 gold badges160 silver badges207 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Substituting values in a CSV file using python

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related