Rearranging data in a CSV file

user_17443521 0
Viewed 68

I'm trying to rearrange a csv file. The file is set up something like this. I'm using Python 3 with the numpy and pandas libraries.

Element Temperature Fy
Element A 27 320
Element A 100 310
Element A 200 300
Element A 300 290
Element A 400 280
Element B 27 320
Element B 100 310
Element B 200 300
Element B 350 290
Element B 400 280

The 1st column has the name of element, 2nd column has a temperature, 3rd column has a corresponding yield stress. Important to note that some elements may contain different temperature values. I need to reorganize the column to run horizontally.

Element 27 100 200 300 350 400
Element A 320 310 300 290 0 280
Element B 320 310 300 0 290 280

So far the code I have is this:

df = pd.read_csv('MatNavi.csv', header=0)
Elements = df['Element'].unique()                                      # Creates a numpy array of all elements, no duplicates, This will become my first column in the final csv file
X = df['Temperature'].drop(Mn[Mn['Element'] != 'Element A'].index)     #creates a numpy array with the temperature values then drops all temperature values that are not for Element A
Y = df['Fy'].drop(Mn[Mn['Element'] != 'Element A'].index)              #same thing but for Fy

This is where I am stuck. I need to make a list, array, or dataframe that has the X values (temperature) as my column headers and then the Fy values horizontally below that and have it correspond to the Element A row header. I'm assuming the temperature value should be the index (not sure if this is the correct term) that then pandas or numpy can use to categorize the Fy values in order. I just don't know how to start.

pandas csv numpy python
1 Answers

Use Pivot:

df = df.pivot(values = 'Fy', index = 'Element', columns = 'Temperature').fillna(0, downcast='infer')
print(df)

output:

Temperature  27   100  200  300  350  400
Element
Element A    320  310  300  290    0  280
Element B    320  310  300    0  290  280
user_162677930
Related