Convert 1D Data into Columns

Question

I have an imported a .csv that contains a 1-dimensional dataset that I am trying to filter and arrange into columns. Sample 1D data:

df_1d = pd.read_csv('file location')

df_1d

    timestamp          label   value
0   2022/03/26 00:00   A       1
1   2022/03/26 01:00   A       2
2   2022/03/26 02:00   A       3
3   2022/03/26 00:00   B       1
4   2022/03/26 01:00   B       2
5   2022/03/26 02:00   B       3
6   2022/03/26 00:00   C       1
7   2022/03/26 01:00   C       2
8   2022/03/26 02:00   C       3

The desired output:

    timestamp          A       B       C
0   2022/03/26 00:00   1       1       1
1   2022/03/26 01:00   2       2       2
2   2022/03/26 02:00   3       3       3

I have tried to extract the unique timestamps and then filter the 1-dimensional dataframe by the labels and timestamps.

df_2d = pd.DataFrame(columns=['timestamp', 'A', 'B', 'C'])

df_2d.timestamp = df_1d.timestamp.unique()

df_2d.A = df_1d.value[(df_1d.label == "A") & (df_1d.timestamp == df_2d.timestamp)]

However, i am getting a value error:

ValueError: Can only compare identically-labeled Series objects

Is there a better approach to rearranging the data?

Answer 1

you can use pivot and pivot_table to get the desire output as follows:

Source Code

df = pd.read_csv("./test.csv")
print(df)

OUTPUT

          timestamp label  value
0  2022/03/26 00:00     A      1
1  2022/03/26 01:00     A      2
2  2022/03/26 02:00     A      3
3  2022/03/26 00:00     B      1
4  2022/03/26 01:00     B      2
5  2022/03/26 02:00     B      3
6  2022/03/26 00:00     C      1
7  2022/03/26 01:00     C      2
8  2022/03/26 02:00     C      3

Solution Source Code

a = df.pivot_table(index="timestamp", columns="label", values="value").reset_index()
a.columns.name = ""
print(a)

Output

          timestamp  A  B  C
0  2022/03/26 00:00  1  1  1
1  2022/03/26 01:00  2  2  2
2  2022/03/26 02:00  3  3  3