0
$\begingroup$

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

Data : https://drive.google.com/open?id=1SUhYd21U5iQmilABNwoaIXLfsc7Mo6dW

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
$\endgroup$
  • $\begingroup$ 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? $\endgroup$ – Fatemeh Asgarinejad Jul 18 '19 at 22:50
  • $\begingroup$ I need the 'Value' and 'Units' for each sample. $\endgroup$ – Kay Jul 18 '19 at 22:56
  • $\begingroup$ could you provide the columns and rows names of the table you are aiming to get? $\endgroup$ – Fatemeh Asgarinejad Jul 19 '19 at 0:58
  • $\begingroup$ 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 … $\endgroup$ – Kay Jul 24 '19 at 21:58
  • $\begingroup$ But there are duplicates of the same samples in your data. For instance the first and second rows are totally the same. $\endgroup$ – Fatemeh Asgarinejad Jul 24 '19 at 22:21
0
$\begingroup$

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)]
| improve this answer | |
$\endgroup$
  • $\begingroup$ 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). $\endgroup$ – Kay Jul 29 '19 at 22:30
  • $\begingroup$ You're welcome :) I added the rest of code to the end of my answer. $\endgroup$ – Fatemeh Asgarinejad Jul 30 '19 at 2:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.