Making dataframe out of txt file with column containing a list

user_6288229 0
Viewed 18

I have the following txt file, I give few lines of it below. I want to make a dataframe using pandas having three columns. The first column titled 'Unique_id$ given 00001, 00002 etc, 2nd column titled as 'Labels' and third column titled as 'doc_id'.

I ran the following pandas command

df_text = pd.read_csv('trainset.txt', names = ['Unique_id','Lables', 'doc_id'],delim_whitespace=True)

and output is as follows

    Unique_id   Labels  doc_id
1   C   9149180 3781329.0
2   B   4396080 9207819.0
3   B   1519858 11734712.0
4   A   15547167    NaN
5   C   11392916    NaN

So the label got shifted and it's treating the last column where element represent the bunch of documents id as separate columns. How can I rectify this? Make the last column with where each element is a list of document id?

The txt file is given below.

The txt file

00001 C 9149180 3781329
00002 B 4396080 9207819 9757979 344087 361152 2099731
00003 B 1519858 11734712
00004 A 15547167
00005 C 11392916
00006 A 8942774 8942775 8036464 7497161
00007 A 15547167
00008 C 12913777
pandas dataframe csv
1 Answers

If I understand you correctly, you want the last numbers as one column where values are lists:

with open("your_file.txt", "r") as f_in:
    df_text = pd.DataFrame(f_in)

df_text["Unique_id"] = df_text[0].str.split().str[0]
df_text["Labels"] = df_text[0].str.split().str[1]
df_text["doc_id"] = df_text[0].apply(lambda x: x.split()[2:])
df_text = df_text.drop(columns=0)

print(df_text)

Prints:

  Unique_id Labels                                                doc_id
0     00001      C                                    [9149180, 3781329]
1     00002      B  [4396080, 9207819, 9757979, 344087, 361152, 2099731]
2     00003      B                                   [1519858, 11734712]
3     00004      A                                            [15547167]
4     00005      C                                            [11392916]
5     00006      A                  [8942774, 8942775, 8036464, 7497161]
6     00007      A                                            [15547167]
7     00008      C                                            [12913777]
user_100359850
Related