# How can I arrange data in different columns (for different constituents) from one?

I have a data file that has all the values for various constituents at different dates in same column. I want different constituents in different columns. Following is the example data format. I want Aluminium, Berrylium and other variables in that column to be in different columns. The data file is attached.

SITE_NAME   SAMP_DATE_TIME      SAMPLE      Value   Units   METHOD_CATEGORY
SITE-7-1    10/04/1988 00:00:00 Aluminum    150     ug/L    INORGANIC
SITE-7-1    10/04/1988 00:00:00 Aluminum    150     ug/L    INORGANIC
SITE-7-1    10/04/1988 00:00:00 Beryllium   5       ug/L    INORGANIC
SITE-7-1    10/04/1988 00:00:00 Beryllium   5       ug/L    INORGANIC


The output I am trying to get is:

SITE_NAME   SAMP_DATE_TIME  Aluminum    Beryllium   Bromide Bromomethane    Chloride    Hexachlorobenzene   Isosafrole  Zinc
SITE-7-1    10/4/1988 0:00   150        5           10      7250            10          10                  75
SITE-7-1    12/29/1988 0:00  150        5           10      8100            10          10                  16
SITE-7-1    5/10/1989 0:00   150        5           1000    10              8100        10                  10          16

• So do you want your columns to be something like after applying oneHotEncoder? meaning: $SITE_NAME, ..., Aluminum, Beryllium, Value$ and then fill these columns for each row with 0s and 1? Jul 18, 2019 at 22:50
• I need the 'Value' and 'Units' for each sample.
– Kay
Jul 18, 2019 at 22:56
• could you provide the columns and rows names of the table you are aiming to get? Jul 19, 2019 at 0:58
• Hi , I am trying to get it in the form as below: SITE_NAME SAMP_DATE_TIME ALUMINIUM(ug/L) Berilium(ug/L) Chloride (ug/l) and so on …
– Kay
Jul 24, 2019 at 21:58
• But there are duplicates of the same samples in your data. For instance the first and second rows are totally the same. Jul 24, 2019 at 22:21

It is passing 6 days from your question and I didn't want to waste your chance of getting an appropriate response by answering. So, my solution might be naive.

Step 1: Changing the categorical data of column Sample to numerical

dictionary = {'SITE_NAME':['SITE_7_1', 'SITE_7_1','SITE_7_1'],
'SAMP_DATE_TIME':['10/04/1988 00:00:00', '10/04/1988 00:00:00', ' 10/04/1988 00:00:00'],
'SAMPLE':['Aluminium','Aluminium','Beryllium'],
'Value':[150, 150,5]}
import pandas as pd
dataset = pd.DataFrame(dictionary)
X = dataset.iloc[:,:].values

from sklearn.preprocessing import LabelEncoder
labelencoder_X = LabelEncoder() #Creating an object from LabelEncoder class
X[:,2] =labelencoder_X.fit_transform(X[:,2])


Result so far:

array([['SITE_7_1', '10/04/1988 00:00:00', 0, 150],
['SITE_7_1', '10/04/1988 00:00:00', 0, 150],
['SITE_7_1', ' 10/04/1988 00:00:00', 1, 5]], dtype=object)


Step 2 Adding the dummy variables as columns

dummies = pd.get_dummies(dataset['SAMPLE'])
df = pd.concat([dataset, dummies], axis=1)  #concating the dataset and the dummies


The result so far:

  SITE_NAME        SAMP_DATE_TIME     SAMPLE  Value  Aluminium  Beryllium
0  SITE_7_1   10/04/1988 00:00:00  Aluminium    150          1          0
1  SITE_7_1   10/04/1988 00:00:00  Aluminium    150          1          0
2  SITE_7_1   10/04/1988 00:00:00  Beryllium      5          0          1


Step 3: filling the dataframe

The logic for filling the values in their corresponding columns is as follows :

for i in range(len(df)):
if df.iloc[i]['SAMPLE'] == 'Aluminium':
df.at[i, 'Aluminium'] = df.iloc[i]['Value']



If we want to apply this to the whole dataset, we can change that Aluminium each time for different samples After this, you can groupby your dataframe to have the rows based on SITE_NAME or whatever you want. feel free to ask if there is any vague point here.

Filling in the whole table

samples = list(df['SAMPLE'].unique()) #storing all the unique values of the SAMPLE column


Creating a dictionary of samples as keys and their values as values:

dictionary = {}
df_new = df.groupby(['SAMPLE'], as_index=False).mean() #dataframe in which SAMPLE is grouped.
for i in range(len(df_new)):
dictionary[df_new.iloc[i]['SAMPLE']] = df_new.iloc[i]['Value']


now dictionary equals: {'Aluminium': 150, 'Beryllium': 5} Now we can fill in the table:

for sample in samples:
for i in range(len(df)):
if df.iloc[i][str(sample)] == 1:
df.at[i, str(sample)] = dictionary[str(sample)]

• Thank you so much for your effort. However the data file I am working with, has more than 50 samples. Is there a way that we could just create column heading based on categories (sample names in 'SAMPLE' column).
– Kay
Jul 29, 2019 at 22:30
• You're welcome :) I added the rest of code to the end of my answer. Jul 30, 2019 at 2:50