pandas dataframe: swap column headings by index

user_16144079 0
Viewed 488

I use pandas dataframe to plot csv. data taken with a spectrometer.

df = pd.read_csv("C:\\file.csv") # import file

The output table always consists of pairs

sample 1 Unnamed:1 sample 2 Unnamed:2 ...
wavelengths transmission 1 wavelengths transmission 2 ...

One column belonging to each sample ('sample 1', 'sample 2',...) where relevant information about the samples is stored in the header, but the column is only containing the wavelength information

One numbered column ('Unnamed: 1', 'Unnamed: 2',...) that actually contains the relevant measured information

I would now like to display the data as a function of the wavelength. If I delete all columns containing the redundant wavelength information by using

df = df.drop(data.columns[1,37], axis=1, inplace=False)

I lose the information about the samples contained in the heading I am now thinking about swapping the column headings and then deleting the columns I don't need. I could of course swap the columns by name using something

df[['sample 1','Unnamed: 1']]=df[['Unnamed: 1','sample 1']]

but then I would have to enter the names for each new data series that sometimes contain more than 10 paired columns.

Is there a way to swap the headings via index? Or can you think of a more elegant version? This form of tabular data output, where the header always spans two columns, is certainly not an isolated case. Thanks a lot

pandas multiple-columns dataframe python
3 Answers

I'm not sure what you mean exactly (some mock data in your sample table would be great), but assuming that right now each row is a separate dataframe and each two columns are samples, would something like this work?

# sample data
df = pd.DataFrame({
    'sample1':[23.1, 12.2, 15.8],
    'Unnamed:1':['alpha','beta','gamma'],
    'sample2':[12.1, 13.4, 11.1],
    'Unnamed:2':['alpha','beta','gamma'],
    'sample3':[0.1,0.43,0.29],
    'Unnamed:3':['alpha','beta','gamma']
})
sample1 Unnamed:1 sample2 Unnamed:2 sample3 Unnamed:3
0 23.1 alpha 12.1 alpha 0.1 alpha
1 12.2 beta 13.4 beta 0.43 beta
2 15.8 gamma 11.1 gamma 0.29 gamma 
# initiate a blank dataframe
new_df = pd.DataFrame()

# filter columns by the sample number, then append to new_f
n = 3 # number of samples
for i in range(1,n+1):
    temp_df = df[[col for col in df.columns if f'{i}' in col]]
    temp_df.columns = 'wavelength','transmission'
    temp_df['sample'] = i
    new_df = new_df.append(temp_df)
new_df = new_df.reset_index(drop=True)

Output:

wavelength transmission sample
0 23.1 alpha 1
1 12.2 beta 1
2 15.8 gamma 1
3 12.1 alpha 2
4 13.4 beta 2
5 11.1 gamma 2
6 0.1 alpha 3
7 0.43 beta 3
8 0.29 gamma 3

All the data relationships are still retained, and you can just do a new_df.groupby('wavelength').mean() to find the mean value of each wavelength. Substitute mean with apply() and add your own function as needed.

user_98666590

You can most easily manipulate the values, instead of the DataFrame as a whole.

Let's say your data is :

import pandas as pd
# Example data
df = pd.DataFrame([["sample 1", "Unnamed:1", "sample 2", "Unnamed:2"], [0.614, "transmission 1", 0.68168, "transmission 2"]])
0 1 2 3
0 sample 1 Unnamed:1 sample 2 Unnamed:2
1 0.614 transmission 1 0.68168 transmission 2

Now let's keep the values we want and their column header.

vals = df.values
new_df = pd.DataFrame(vals[1,::2], index= vals[0, ::2], columns=["wavelength")

new_df now is :

wavelength
sample 1 0.614
sample 2 0.68168
user_104090930

You can divide the column labels into 2 parts: even number columns and odd number columns. Then, swap their sequence within each pair of even-odd numbered columns, as follows:

swapped_cols = np.ravel([[y, x] for x, y in zip(df.columns[0::2], df.columns[1::2])])

Here, df.columns[0::2] and df.columns[1::2] contains the even and odd number columns.

print(swapped_cols)

['Unnamed:1' 'sample 1' 'Unnamed:2' 'sample 2']

Case 1: If you want to swap only the column labels, without swapping the column contents, you can do:

df.columns = swapped_cols

Result:

print(df)

     Unnamed:1        sample 1    Unnamed:2        sample 2
0  wavelengths  transmission 1  wavelengths  transmission 2

Case 2: If you want to swap the column sequence (with column labels and column contents swapped together), you can do:

df = df[swapped_cols]

Result:

print(df)

        Unnamed:1     sample 1       Unnamed:2     sample 2
0  transmission 1  wavelengths  transmission 2  wavelengths
user_150706970
Related