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

Question

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?

user17242583 · Accepted Answer · 2022-03-11 02:18:23Z

2

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

answered Mar 11, 2022 at 2:18

user17242583

Sign up to request clarification or add additional context in comments.

Comments

rhug123 · Accepted Answer · 2023-09-11 04:31:54Z

1

Here is another way:

First load data into a dictionary:

d = dict(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])

Then convert to a df:

df = pd.DataFrame({i:pd.Series(j) for i,j in d.items()})

or

i,v = zip(*d.items())

df = pd.DataFrame(v,index = i).T

Output:

      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

edited Sep 11, 2023 at 4:31

answered Mar 11, 2022 at 2:58

rhug123

8,8801 gold badge14 silver badges27 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related