join two unique combinations of single DataFrame, convert it into column name

user_11089984 0
Viewed 57

I have an interesting issue and I tried to do it but It did not work. I have a single time series data frame with 4 columns: Source, Target, Timestamp and Values.

Each timestamp has multiple sources, targets and values as provided code:

import pandas as pd
data = 
    [['a','None','01.01.2020',20], ['a','None','02.01.2020',15],['a','None','03.01.2020',11],
    ['a','b','01.01.2020',100], ['a','b','02.01.2020',105], ['a','b','03.01.2020',101],
    ['c','d','01.01.2020',0], ['c','d','02.01.2020',0], ['c','d','03.01.2020',1],
    ['b','c','01.01.2020',50.45], ['b','c','02.01.2020',10.5], ['b','c','03.01.2020',500],
    ['a','d','01.01.2020',5000], ['a','d','02.01.2020',1500], ['a','d','03.01.2020',25],
    ['c','a','01.01.2020',2.2538], ['c','a','02.01.2020',105], ['c','a','03.01.2020',110]]

df = pd.DataFrame(data, columns = ['Source', 'Target', 'timestamp', 'values'])

I would like to return a new data formate as defined data frame:

resultdata = [['01.01.2020',20,100,0,50.45,5000,2.2538], ['02.01.2020',15,105,0,10.5, 1500,105],
          ['03.01.2020',11,101,1,500,25,110]]
result = pd.DataFrame(resultdata, columns = ['timestamp', 'aNone', 'ab', 'cd', 'bc', 'ad', 'ca'])

For that I tried to join the string column and drop the duplicate timestamp then run an iteration but I just receive only the last iteration data in dictionary format.

df['Source Target'] = df['Source']  + ' ' + df['Target']
st = df['Source Target'].drop_duplicates(keep= 'first').reset_index(drop=True)
timestamp = df['timestamp'].drop_duplicates(keep= 'first')

d ={}
for j in range(len(timestamp)):
    Time = timestamp ['timestamp'][j]
    for k in range(len(st)):
        Column = st[k] 
        for i in range(len(df)):
            time =  df['timestamp'][i]
            columnname =  df['Source Target'][i]
            if time==Time and columnname == Column:
                d[Column] = (time,df['values'][i])
pandas time-series dataframe for-loop python
2 Answers

Let's try a pivot_table instead:

import pandas as pd

data = [['a', 'None', '01.01.2020', 20], ['a', 'None', '02.01.2020', 15],
        ['a', 'None', '03.01.2020', 11], ['a', 'b', '01.01.2020', 100],
        ['a', 'b', '02.01.2020', 105], ['a', 'b', '03.01.2020', 101],
        ['c', 'd', '01.01.2020', 0], ['c', 'd', '02.01.2020', 0],
        ['c', 'd', '03.01.2020', 1], ['b', 'c', '01.01.2020', 50.45],
        ['b', 'c', '02.01.2020', 10.5], ['b', 'c', '03.01.2020', 500],
        ['a', 'd', '01.01.2020', 5000], ['a', 'd', '02.01.2020', 1500],
        ['a', 'd', '03.01.2020', 25], ['c', 'a', '01.01.2020', 2.2538],
        ['c', 'a', '02.01.2020', 105], ['c', 'a', '03.01.2020', 110]]

df = pd.DataFrame(data, columns=['Source', 'Target', 'timestamp', 'values'])

# Create Pivot Table
df = df.pivot_table(index='timestamp', 
                    columns=['Source', 'Target'], 
                    values='values').reset_index()

# Reduce mutli-index columns
df.columns = df.columns.map(''.join)

# Fix dtypes
df = df.convert_dtypes()

# For Display
print(df.to_string())

df:

    timestamp  aNone   ab    ad     bc      ca  cd
0  01.01.2020     20  100  5000  50.45  2.2538   0
1  02.01.2020     15  105  1500   10.5   105.0   0
2  03.01.2020     11  101    25  500.0   110.0   1
user_154978880

You could use pivot_wider from pyjanitor to reshape as well, the names_sep helps in merging multiIndex columns:

#pip install janitor
import pandas as pd
import janitor
df.pivot_wider(index="timestamp", 
               names_from=["Source","Target"],
               values_from="values", 
               names_sep="").convert_dtypes()
 
    timestamp  aNone   ab  cd     bc    ad      ca
0  01.01.2020     20  100   0  50.45  5000  2.2538
1  02.01.2020     15  105   0   10.5  1500   105.0
2  03.01.2020     11  101   1  500.0    25   110.0

pivot_wider is an abstraction over pandas pivot function.

user_71757130
Related