Pandas, Python – merging columns with same key, but with different values

March 11, 2022

From my for-loop, the resulted lists are as follow:

#These lists below are list types and in ordered/structured.
key=[1234,2345,2223,6578,9976] 
index0=[1,4,6,3,4,5,6,2,1]
index1=[4,3,2,1,6,8,5,3,1]
index2=[9,4,6,4,3,2,1,4,1]

How do I merge them all into a table by pandas? Below is the expectation.

key  | index0 |  index1 |  index2
1234 |   1    |    4    |    9
2345 |   4    |    3    |    4
...  |  ...   |   ...   |   ...
9967 |   1    |    1    |    1

I had tried using pandas, but only came across into an error about data type. Then I set the dtype into int64 and int32, but still came across the error about data type again.

And for an optional question, should I had approached assembling a table from such a similar data in lists with SQL? I am just learning SQL with mySQL and wonder if it would’ve been convenient than with pandas for record keeping and persistent storage?

Help is very much appreciated. If you have any question, please let me know.

>Solution :

Just use a dict and pass it to pd.DataFrame:

dct = {
    'key': pd.Series(key),
    'index0': pd.Series(index0),
    'index1': pd.Series(index1),
    'index2': pd.Series(index2),
}

df = pd.DataFrame(dct)

Output:

>>> df
      key  index0  index1  index2
0  1234.0       1       4       9
1  2345.0       4       3       4
2  2223.0       6       2       6
3  6578.0       3       1       4
4  9976.0       4       6       3
5     NaN       5       8       2
6     NaN       6       5       1
7     NaN       2       3       4
8     NaN       1       1       1