How to Transpose Column values to new dataframe column headers and add data below that headers in Python

user_11942810 0
Viewed 47

I want to create a table(dataframe) by using some live data fields which will be updating in some fixed time and as soon as data updates I need to add that data into my table I get data in tabular format like below

Time        col1        col2        col3
timestapm1   123        456         789
timestamp2   7584       4547        6545
timestamp3   8974       1241        2140

When script run for first time then it create new seprate list of column names for my desired table by using above data which looks like below

Timestamp col1_456  col1_4547   col1_1241   col3_456    col3_4547   col3_1241

col1 and col3 values changes regularly, col2 is static . I'm just getting confused at how can I match the column names of new table and add value under that column.

Time        col1        col2        col3
timestapm4   17823      456         10789
timestamp5   758404     4547        65045
timestamp6   89744      1241        14140

Desired output

Timestamp   col1_456    col1_4547   col1_1241   col3_456    col3_4547   col3_1241
timestamp4  17823       758404      89744       10789       65045       14140

Please help, thanks!

pandas dataframe python
1 Answers

You could try it like this. I used your example data for df1 and df2, pivoted it and concat them together, everytime you get new data, you can run these 3 lines of code ( change timestamp, pivot, concat) where the new data is appended as new rows to the existing dataframe output. Changing the column Time to a single value is needed to make it one row in your output, you can change that name for every data chunk you receive to make the rows distinct.

df1 = pd.DataFrame(
    {
        "Time": {0: "timestamp1", 1: "timestamp2", 2: "timestamp3"},
        "col1": {0: 123, 1: 7584, 2: 8974},
        "col2": {0: 456, 1: 4547, 2: 1241},
        "col3": {0: 789, 1: 6545, 2: 2140},
    }
)

df2 = pd.DataFrame(
    {
        "Time": {0: "timestapm4", 1: "timestamp5", 2: "timestamp6"},
        "col1": {0: 17823, 1: 758404, 2: 89744},
        "col2": {0: 456, 1: 4547, 2: 1241},
        "col3": {0: 10789, 1: 65045, 2: 14140},
    }
)

df1["Time"] = "timestamp_1"
tmp1 = df1.pivot(index="Time", columns="col2", values=["col1", "col3"]).reset_index()
tmp1.columns = tmp1.columns.map(lambda x: f"{x[0]}_{x[1]}")

df2["Time"] = "timestamp_2"
tmp2 = df2.pivot(index="Time", columns="col2", values=["col1", "col3"]).reset_index()
tmp2.columns = tmp2.columns.map(lambda x: f"{x[0]}_{x[1]}")

output = pd.concat([tmp1, tmp2])

print(output)

         Time_  col1_456  col1_1241  col1_4547  col3_456  col3_1241  col3_4547
0  timestamp_1       123       8974       7584       789       2140       6545
0  timestamp_2     17823      89744     758404     10789      14140      65045
user_155213920
Related